共计 3612 个字符,预计需要花费 10 分钟才能阅读完成。
1.binlog 日志基本知识
MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志,它记录了所有的 DDL 和 DML 语句(除了数据查询语句 select),以事件形式记录,还包含语句所执行的消耗的时间。
binlog 有三种格式:Statement、Row 以及 Mixed。分别是:基于 SQL 语句的复制 (statement-based replication,SBR)、基于行的复制(row-based replication,RBR) 和混合模式复制(mixed-based replication,MBR)。
格式 | 说明 | 优点 | 缺点 |
Statement 模式 | 每一条会修改数据的 sql 语句都会记录到 binlog 中。 | 不需要记录每一行的变化,减少了 binlog 日志量,节约了 IO,提高性能。 | 缺点是在某些情况下会导致 master-slave 中的数据不一致 (如 sleep() 函数,last_insert_id(),以及 user-defined functions(udf)等会出现问题)。 |
Row 模式 |
不记录每条 sql 语句的上下文信息,仅需记录哪条数据被修改了,修改成什么样了。 而且不会出现某些特定情况下的存储过程、或 function、或 trigger 的调用和触发无法被正确复制的问题。 新版本的 MySQL 中对 row level 模式也被做了优化,并不是所有的修改都会以 row level 来记录,像遇到表结构变更的时候就会以 statement 模式来记录,如果 sql 语句确实就是 update 或者 delete 等修改数据的语句,那么会记录所有行的变更。 |
任何情况都可以被复制,这对复制来说是最安全可靠的。多数情况下,从服务器上的表如果有主键的话,复制就会快了很多。 |
binlog 大了很多 复杂的回滚时 binlog 中会包含大量的数据 主服务器上执行 UPDATE 语句时,所有发生变化的记录都会写到 binlog 中,这会导致频繁发生 binlog 的并发写问题 UDF 产生的大 BLOB 值会导致复制变慢 无法从 binlog 中看到都复制了写什么语句 |
MIXED 模式 | 实际上就是前两种模式的结合,在 mixed 模式下,mysql 会根据执行的每一条具体的 sql 语句来区分对待记录的日志形式,也就是在 statement 和 row 之间选一种。 | 在 Mixed 模式下,一般的语句修改使用 statment 格式保存 binlog,如一些函数,statement 无法完成主从复制的操作,则采用 row 格式保存 binlog,MySQL 会根据执行的每一条具体的 sql 语句来区分对待记录的日志形式,也就是在 Statement 和 Row 之间选择一种。 |
2. 查看 Row 模式和 Statement 模式的 binlog
为了加深印象,下面我们看看两者模式下的 binlog 到底长什么样子。
2.1 测试案例
分别在 Row 模式 和 Statement 模式 下执行以下语句。
创建一张表
CREATE TABLE IF NOT EXISTS `tt` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL,
`sex` enum('m','w') NOT NULL DEFAULT 'm',
`age` tinyint(3) unsigned NOT NULL,
`classid` char(6) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
向表中 insert 5 笔数据
insert into zyyshop.tt(`name`,`sex`,`age`,`classid`) values('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'),('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6');
用一条 SQL 语句修改前两笔数据
update tt set name ='XXX' where id in (1,2);
在 Row 模式下形成的 binlog 文件为:mysql3306_bin.000011
在 Statement 模式下生成的 binlog 文件为:mysql3306_bin.000012。
2.2 用 show binlog events in 命令去查看分析 2 各个文件
查询 Row 模式 记录如下:
查询 Statement 模式记录如下:
通过这个命令查看 log,两者差距不大。
2.3 通过 mysqlbinlog 命令解析
执行的命令分别如下
指定路径 /bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v 指定路径 /mysql_log/mysql3306_bin.000011
指定路径 /bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v 指定路径 /mysql_log/mysql3306_bin.000012
我们可以看到 Row 模式下的 binlog 记录丰富的多,例如针对 update 的那条语句。
Row 模式 记录如下:
Statement 模式 记录如下:
总结: 通过 show binlog events in 命令,查看 Row 模式下记录 和 Statement 模式下的记录,两者基本一致。通过 mysqlbinlog 可以查看 binlog 具体的信息。Row 模式下的 binlog 记录比 Statement 模式下丰富的多。
3. 通过 mysqlbinlog 和 grep 命令定位 binlog 文件中指定操作
既然 binlog 文件中有详细的操作信息,如果有人误操作,我们是否可以快速定位到对应操作信息呢?
快速定位可以帮助我们找到当时具体的操作是什么,也可以找到 POS(position)点,方便精准恢复。
例如,书接上回,我们发现表 tt 不在了,被人删除了。ERROR 1146 (42S02): Table ‘TestBinlog2.tt’ doesn’t exist。
那么我们就可以在 binlog 查找 drop 相关的操作,命令如下:
指定路径 /bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v 指定路径 /mysql_log/mysql3306_bin.000012
| grep drop
可惜没有数据,这是什么情况呢?不应该啊!!!
会不会大小写的问题?那么命令修改如下:
指定路径 /bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v 指定路径 /mysql_log/mysql3306_bin.000012
| grep -i drop
找到了,但是信息不是很完整,我们可不可以找到,这条命令的更信息信息呢?例如,drop 前后各 10 条数据。
指定路径 /bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v 指定路径 /mysql_log/mysql3306_bin.000012
| grep -i -A 10 -B 10 drop
这正是我们想要的,完美!
4. 其它知识补充
4.1 mysqlbinlog 工具
此处主要讲解用于查看 binglog 日志的部分参数,用于还原 binlog 的参数在此不细讲。
mysqlbinlog 工具参数说明【用于查看的部分】 | |
参数 | 说明 |
-base64-output |
inlog 输出语句的 base64 解码 分为三类: |
–verbose | 重新构建伪 SQL 语句的行信息输出,- v 会增加列类型的注释信息。 |
–database=name | 列出数据库的名称(仅限 binlog 文件存储在本地) |
4.2 grep 命令
grep 是一个强大的文本搜索工具命令,用于查找文件中符合指定格式的字符串,支持正则表达式。
grep 命令常用参数说明 | |
参数 | 说明 |
-A | 除了显示符合条件的那一行之外,并显示该列之后的指定行的内容内容。 |
-B | 除了显示符合条件的那一行之外,并显示该列之前的指定行的内容内容。 |
-c | 计算符合结果的行数。 |
-i | 忽略字符大小写 |
-v | 反向查找 |
-e | 按指定字符串查找 |
-E | 按指定字符串指定的正则查找 |
-n | 在显示符合条件的那一行前,标识出该行的行数标号。 |
: