MySQL是预写日志方式提供原子性和持久性。对于服务层binlog来说,日常处理故障和分析上经常会用到。
整理了实用的相关命令。希望工作中有帮助。
mysqlbinlog相关命令分两种,mysql客户端命令 和 执行文件。
客户端命令: binlog相关SHOW ,PURGE命令
SHOW命令介绍:
SHOW BINARY LOGS;
SHOW MASTER LOGS;
#A user with the SUPER or REPLICATION CLIENT privilege may execute this statement
mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 201 |
+------------------+-----------+
mysql> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 201 |
+------------------+-----------+
BINLOG + RELAYLOG EVENT 相关命令:
##binlog event
SHOW BINLOG EVENTS
[IN 'log_name']
[FROM pos]
[LIMIT [offset,] row_count]
##Relay event
SHOW RELAYLOG EVENTS
[IN 'log_name']
[FROM pos]
[LIMIT [offset,] row_count]
[channel_option]
channel_option:
FOR CHANNEL channel
mysql> SHOW BINLOG EVENTS IN ‘mysql-bin.000087’;
mysql> SHOW BINLOG EVENTS IN ‘mysql-bin.000087’ FROM 1008;
mysql> SHOW BINLOG EVENTS IN ‘mysql-bin.000087’ FROM 1008 limit 2,5;
mysql> SHOW RELAYLOG EVENTS IN ‘relay-log.000004’ FROM 1062 LIMIT 5,2;
清除日志:
PURGE { BINARY | MASTER } LOGS {
TO 'log_name'
| BEFORE datetime_expr
}
mysql> PURGE BINARY LOGS TO 'mysql-bin.000080';
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000080 | 194 |
| mysql-bin.000081 | 194 |
| mysql-bin.000082 | 194 |
| mysql-bin.000083 | 217 |
| mysql-bin.000084 | 408 |
| mysql-bin.000085 | 517 |
| mysql-bin.000086 | 7474 |
+------------------+-----------+
mysql> PURGE BINARY LOGS BEFORE '2020-04-27 10:00:00';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000086 | 7474 |
+------------------+-----------+
1 row in set (0.00 sec)
mysqlbinlog命令:执行的时候一定是mysqlbinlog全路径 或 配置全局里。因为每个mysql版本对应的mysqlbinlog都有部分内容被改写,不同的客户端解析出来,会存在差异。
1)解析命令
#mysqlbinlog mysql-bin.000001
备注:命令将会显示出在该系统上数据库发生的所有改变事件。但执行的SQL语句是乱码。屏蔽业务数据,提供第三方参考。
2)显示binlog执行的sql 语句内容
#mysqlbinlog --no-defaults --base64-output=decode-row -vv mysql-bin.000001
备注:完整显示SQL语句,这个事务的相关内容。
3)使用 -d, --database=name选项,可以指定一个数据库名称,只显示指定该数据库操作。(local log only)
#mysqlbinlog --no-defaults -d db1 --base64-output=decode-row -v -v mysql-bin.000001
注意:只有local log only才能使用,还有个人认为mysqlbinlog应该把这些不用的内容从打印出来的结果里,提出掉。
4)-D, --disable-log-bin 禁止恢复过程产生日志
#mysqlbinlog --no-defaults -d db1 --base64-output=decode-row -v -v mysql-bin.000001
备注:主要作用就是SQL_LOG_BIN=0,不记录日志,特别是恢复的时候因为io性能特别差的时候建议使用。
5)-r 或则 > 输出文件
#mysqlbinlog --no-defaults --base64-output=decode-row -vv mysql-bin.000001 > /opt/bin001.sql
#mysqlbinlog --no-defaults --base64-output=decode-row -vv mysql-bin.000001 -r /opt/bin002.sql
6) 指定server-id
#mysqlbinlog --no-defaults --base64-output=decode-row --server-id=1301 -vv mysql-bin.000001
注意:特别是主从复制的情况下,通过指定serverid 方式,区分binlog提供方。
7) 指定开始时间,结束时间 或 开始positon 结束position 方式,经常用的参数。
-j = --start-position 效果一样:
#mysqlbinlog --no-defaults --base64-output=decode-row -vv --start-position=123 --stop-position=290 mysql-bin.000001
#mysqlbinlog --no-defaults --base64-output=decode-row -vv -j 123 --stop-position=290 mysql-bin.000001
time方式:
#mysqlbinlog --no-defaults --base64-output=decode-row -vv --start-datetime='2020-05-02 22:36:46 ’ --stop-datetime=‘2020-05-02 23:25:46’ mysql-bin.000001
8)从远程服务器读取:R选项与-read-from-remote-server相同,指定服务器mysql服务必须存在才可以
- -R 选项指示mysqlbinlog命令从远程服务器读取日志文件
- -h 指定远程服务器的ip地址
- -p 将提示输入密码。默认情况下,它将使用“root”作为用户名。也可以使用 -u 选项指定用户名。
- -P 大写P 显示mysql服务端口。
- mysqld-bin.000001 这是在这里读到的远程服务器的二进制日志文件的名称。
#mysqlbinlog --no-defaults -R -h 192.168.244.130 -uroot -p123456 -P3410 --base64-output=decode-row -vv mysql-bin.000001
9) --skip-gtids不要在输出中显示任何GTID
#mysqlbinlog --no-defaults --base64-output=decode-row -vv --skip-gtids mysql-bin.000001
备注:这个语句里无gtid信息,有便于在任何服务器上重复日志。
10)include-gtids 和 exclude-gtids
- include-gtids
指定需要回滚的gtid,支持gtid的单个和范围两种形式。 - exclude-gtids
指定不需要回滚的gtid,用法同include-gtids
#mysqlbinlog --no-defaults --base64-output=decode-row -vv -t mysql-bin.000001 --exclude-gtids=‘39d0a7f2-702c-11ea-92a0-000c29b9a76d:1-2’
#mysqlbinlog --no-defaults --base64-output=decode-row -vv -t mysql-bin.000001 --include-gtids=‘39d0a7f2-702c-11ea-92a0-000c29b9a76d:1-2’
11)–rewrite-db=‘from_name->to_name’ 重写所有出现的数据库对应的名称 from_name to to_name。
#mysqlbinlog --no-defaults --base64-output=decode-row -vv -f --rewrite-db=‘db1->db11’ mysql-bin.000001
备注:多源复制场景中,通过binlog方式恢复
12)–force-read, -f 果mysqlbinlog读取一个不识别的二进制日志事件,它会打印一条警告,忽略事件并继续。如果没有这个选项,mysqlbinlog会在读取这样的事件时停止
#mysqlbinlog --no-defaults --base64-output=decode-row -vv -f mysql-bin.000001
备注:偶尔会碰上故障导致日志写入阶段的现象,mysqlbinlog无法分析日志的情况。