MySQL口试必备二之binlog日记

本文首发于公众号：Hunter后端
原文链接：MySQL口试必备二之binlog日记

关于 binlog，常被问到几个口试问题如下：

• binlog 是什么
  
• binlog 都记录什么数据
  
• binlog 都有哪些类型，都有什么特点
  
• 如何使用 binlog 恢复数据
  
• binlog 都有哪些作用
  
• binlog 属于逻辑日记照旧物理日记
  

基于上面这些问题，在看完本篇条记之后，大概就会有一个清晰的认知了。
以下是本篇条记目录：

• binlog 是什么
  
• binlog 设置和检察
  
• binlog 的类型
  
• binlog 如何恢复数据
  
• binlog 是逻辑日记照旧物理日记
  
• binlog 作用
  

留意：以下所有的操纵都在 MySQL 8.0 版本实现。
1、binlog 是什么

首先先容一下，对于一个 SQL 语句，它常常被分为以下几种类型：

• DDL：Data Definition Language，数据定义语言
  
  
  • 用来操纵数据库、表、列等，比如 CREATE、ALTER、DROP
    
  
  
• DML：Data Manipulation Language，数据操纵语言
  
  
  • 用来操纵表里的数据，比如 INSERT、UPDATE、DELETE
    
  
  
• DCL：Data Control Language，数据控制语言
  
  
  • 用来操纵访问权限等，比如 GRANT
    
  
  
• DQL：Data Query Language，数据查询语言
  
  
  • 用来查询数据，比如 SELECT
    
  
  
• TCL：Transaction Control Language，事务控制语言
  
  
  • 用于数据库的事务管理，比如 COMMIT，ROLLBACK
    
  
  

而 binlog，即 binary log，是 MySQL 的二进制日记文件，这个文件记录了我们所有的 DDL，DML，TCL 等操纵，比如表的创建，数据的插入、更新和删除等。
比如我们下面执行的创建数据库、表，插入、更新数据，在 binlog 设置开启的情况下，都会被记录到 binlog 中：

1. CREATE DATABASE db_test DEFAULT CHARACTER set utf8;
3. create table users (
4.         id int not null auto_increment primary key,
5.         name varchar(20) not null,
6.         email varchar(100) default ""
7. );
9. insert into users (name, email) values("张三", "12345@qq.com"), ("李四", "345123@qq.com");
11. update users set email = "4123@qq.com" where id=1;

复制代码

2、binlog 设置和检察

1) binlog 检察设置

我们可以通过下面的命令检察 binlog 日记是否打开，以及存储的文件夹：

1. show variables like '%log_bin%';

复制代码

表现的内容如下：

其主要字段含义分别如下：

• log_bin 表现是否开启 binlog
  
• log_bin_basename 表现的是 binlog 所在的文件夹
  
• log_bin_index 表现的是 binlog 的索引文件，我们可以在其中找到所有的 binlog 日记
  
• sql_log_bin 用于主从复制，这个参数关闭后，主库的改动不会记录到 binlog，不会复制到从库
  

log_bin_basename 指向的是 binlog 存储的文件夹，在后面我们检察 binlog 具体内容的时候，会需要进入到这个文件夹进行检察。
2) binlog 检察

我们可以通过下面的命令检察全部的 binlog 文件，在这里，一个 binlog 版本就是一个文件：

1. show binary logs;
3. show master logs;

复制代码

上面这两条命令都可以表现出 binlog 文件，内容表现如下：

3、binlog 的类型

binlog 记录的类型有三种，一种是 STATEMENT，一种是 ROW，一种是 MIXED。
在先容其具体含义前，我们先先容一下如何检察和修改 binlog 类型，以及检察 binlog 内容。
1. 检察 binlog 类型

我们可以通过下面的命令检察 binlog 生存的类型：

1. SHOW VARIABLES LIKE 'binlog_format';

复制代码

输出内容如下：

表现 binlog 生存的类型是 ROW
2. 修改 binlog 类型

修改 binlog 类型我们到 binlog 文件中修改，这里我们修改 mysqld.cnf，修改 binlog_format 的值，没有这个设置项的话加一行，如下：

1. binlog_format = STATEMENT

复制代码

生存之后重启 MySQL，再次查询就可以看到这个参数有变化了：

3. binlog 类型及具体含义

其含义和示例分别如下：
1) STATEMENT

STATEMENT 类型记录的是执行 SQL 语句的内容，比如 INSERT、UPDATE、DELETE 等语句本身。
比如：

1. insert into users (name, email) values("张三", "12345@qq.com"), ("李四", "345123@qq.com");
2. update users set email = "4123@qq.com" where id=1;

复制代码

在 STATEMENT 类型下，上面这两句 SQL 在 binlog 中记录的就是这几句 SQL 本身。
通过查询 binlog 日记，比如我们下面执行的语句，可以看到输出的 info 那一列有我们刚刚执行的 insert 和 update 语句：

1. show binlog events in 'binlog.000017';

复制代码

我们也可以使用 mysqlbinlog 命令在 shell 对这个日记文件进行检察：

1. mysqlbinlog binlog.000017

复制代码

就可以看到输出的信息里会包罗我们前面执行的两条命令
2) ROW

ROW 类型记录的是执行 SQL 语句前后的变动，会包罗变动前后完备数据行的内容。
将 binlog_format 的设置改为 ROW 后，重新执行几条插入或者更新数据的命令，在 MySQL 客户端执行 show binlog events 命令，就可以看到日记记录里有对应的记录，但是这里输出的信息并不全，我们照旧可以使用 mysqlbinlog 来检察。

1. mysqlbinlog binlog.000018

复制代码

但是可以看到输出的内容里，数据的变化都被处置惩罚过：

1. BEGIN
2. /*!*/;
3. # at 323
4. #240415  1:03:05 server id 1  end_log_pos 386 CRC32 0x0e2e4dda         Table_map: `db_test`.`users` mapped to number 93
5. # has_generated_invisible_primary_key=0
6. # at 386
7. #240415  1:03:05 server id 1  end_log_pos 474 CRC32 0xcebf294e         Update_rows: table id 93 flags: STMT_END_F
9. BINLOG '
10. SQwcZhMBAAAAPwAAAIIBAAAAAF0AAAAAAAEAB2RiX3Rlc3QABXVzZXJzAAMDDw8EPAAsAQQBAQAC
11. ASHaTS4O
12. SQwcZh8BAAAAWAAAANoBAAAAAF0AAAAAAAEAAgAD//8AAQAAAAblvKDkuIkLADQxMjNAcXEuY29t
13. AAEAAAAG5byg5LiJDQAxMTExMTNAcXEuY29tTim/zg==
14. '/*!*/;
15. # at 474
16. #240415  1:03:05 server id 1  end_log_pos 505 CRC32 0xd7ae1d48         Xid = 55
17. COMMIT/*!*/;

复制代码

会看不到真实记录的信息，我们直接使用 cat 或者 vim 命令检察文件内容，可以从其中窥得一二，可以看到一些我们修改或者新增的字段内容。
或者我们照旧可以使用 mysqlbin，但是加上 -vv 参数，我们可以看到原始执行的 SQL 语句，雷同于下面的内容：

1. BINLOG '
2. 2RAcZhMBAAAAPwAAANUCAAAAAF0AAAAAAAEAB2RiX3Rlc3QABXVzZXJzAAMDDw8EPAAsAQQBAQAC
3. ASGMO256
4. 2RAcZh4BAAAAPwAAABQDAAAAAF0AAAAAAAEAAgAD/wADAAAABueOi+S6lA4AMTIxMjM0NUBxcS5j
5. b22PdURn
6. '/*!*/;
7. ### INSERT INTO `db_test`.`users`
8. ### SET
9. ###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
10. ###   @2='王五' /* VARSTRING(60) meta=60 nullable=0 is_null=0 */
11. ###   @3='1212345@qq.com' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */

复制代码

3) MIXED

MIXED 类型则是 STATEMENT 和 ROW 两种类型的综合，它会根据具体的情况来选择 STATEMENT 或者是 ROW 其中一种类型来进行存储。
比如说涉及函数或者一些比较复杂和无法还原的操纵时，就会选择 ROW 格式来存储变动的数据行，假如是一些比较简朴的插入、更新或者删除操纵，那么则会选择 STATEMENT 格式来进行存储。
4. binlog 三种类型的优缺点

关于这三种 binlog 类型的优缺点总结如下：
1) STATEMENT

STATEMENT 记录的 SQL 语句，适用于简朴 SQL 语句的场景，比如单条数据的插入、更新、删除等。
优点是 binlog 日记文件相对较小，而且可读性强，因为可以直接检察 SQL 语句了解操纵
但同时存在的问题是某些复杂操纵大概会出现不一致的情况。
2) ROW

ROW 类型存储的是数据行的变动，会包罗变动前后的内容，适用于需要精确记录数据行变动的场景。
优点是可以精确记录数据行的变动情况，避免 STATEMENT 类型大概出现的不一致的问题。
缺点则是因为记录了完备的数据行内容，所以大概导致文件较大，而且可读性较差，比如我们前面使用 -vv 参数才气了解其执行的 SQL 语句。
3) MIXED

MIXED 则会根据具体情况自动选择 STATEMENT 或 ROW 类型来记录数据更改。
优点是兼顾了 STATEMENT 和 ROW 两种类型的有点，既可以节约空间，也可以精确记录数据变动。
缺点的话，因为是体系是自动选择类型来记录，所以大概存在选择不够理想的记录方式从而导致一些不一致或者是性能问题。
4、binlog 如何恢复数据

对于 MySQL 中的操纵，我们有时候大概会误操纵，导致会意外的更新或者删除一些数据、或者是删除表、删除库等，那么这些操纵如何使用 binlog 来进行恢复呢，以下做一个简朴的先容。
在先容如何恢复数据前，先先容前面的 binlog 文件生存的机制。
对于 MySQL 而言，一个 binlog 文件内容为从某一刻开始生存的日记记录，当我们使用 sudo service mysql restart 重启操纵时，体系会自动为我们新建一个 binlog 日记文件，重启之后的所有数据操纵都会被放到新的 binlog 文件里。
除了 MySQL 的手动重启，我们还可以使用 flush logs 操纵来手动新建一个 binlog 日记。
下面正式开始先容如何使用 binlog 恢复数据。
1. 恢复数据的前提

恢复数据的前提就是我们已经打开了 binlog 日记记录设置，而且最好有一个备份库，这个备份库的作用是用于在误操纵数据后可以快速从最近的时间点进行恢复。
备份库的内容则是与当宿世产库，或者说是目标库落后肯定时间间隔的数据，来源可以是我们每天或者更小时间粒度对目标数据库的定时生存。
2. 恢复数据的步调

有了 binlog 日记和备份数据库的基础之后，比如我们误操纵更新了大范围的数据，或者删除了某张表，下面便开始恢复数据的步调。
1) 制止写入更新操纵

假如大概的话，应该尽量先制止往 MySQL 对应表的写入操纵，避免数据污染操纵。
2) 执行 flush logs

执行 flush logs 操纵，重新创建一个 binlog，将误操纵确定在某个 binlog 中。
3) 导出 binlog 日记

确定备份库的时间点，然后找到包罗失误操纵的 binlog，导出从备份库的时间点开始不停到发生误操纵的时间点前的 binlog。
将这部分 binlog 在备份库重新执行，如许，我们的备份库就可以恢复数据到发生误操纵的前一个时间点。
假如我们没有第一步制止写入更新的操纵，就可以跳过误操纵的 SQL，将发生误操纵后的 SQL 语句也导入到备份库，如许备份库就拥有了不发生误操纵情况下的全部数据。
4) 替换

将备份库的数据替换到目标库，然后重新开启服务。
以上，就是使用 binlog 进行恢复数据的大致操纵，具体图示见下图：

尽管恢复数据在某些情况下是可行的，但是我们仍然要留意尽量避免在生产中直接操纵数据库，而应该尽量通过业务代码来实现对数据库的操纵。
5、binlog 是逻辑日记照旧物理日记

先容一下 MySQL 中关于逻辑日记与物理日记的区别。
所谓逻辑日记指的是记录数据库操纵的日记，比如 INSERT、UPDATE 等逻辑操纵。
而物理日记则是指记录数据的各种存储细节，比如 MySQL 具体某个数据页的写入和修改数据等。
所以，binlog 则属于逻辑日记。
6、binlog 作用

binlog 作为 MySQL 日记的一种，有很多作用，比如前面先容的数据备份与恢复，还可以用于数据同步，比如主从复制模式下将主库的更改操纵操纵就是通过 binlog 的方式来同步到从库的。
固然，通过 binlog 本身我们可以还原所有的数据操纵，我们也可以针对这部分进行对应的数据统计和分析等。
以上就是本篇条记关于 binlog 日记的全部内容。
假如想获取更多后端相关文章，可扫码关注阅读：

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。