• mysql binlog常用操作
  • 查看 binlog 基本信息
  • mysqlbinlog 命令
  • 删除或重置 binlog 日志
  • 从 binlog 中恢复数据
  • 参考资料

mysql binlog常用操作

查看 binlog 基本信息

binlog文件的位置配置可在 /etc/my.cnf 配置和查看

1. 登录 mysql

   1	mysql -h localhost -u root -p

2. 查看binlog文件列表

   进入mysql命令行:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

   mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000873 | 524288149 | | mysql-bin.000874 | 524288126 | | mysql-bin.000875 | 524289185 | | mysql-bin.000876 | 524288897 | | mysql-bin.000877 | 524294885 | | mysql-bin.000878 | 524289473 | | mysql-bin.000879 | 524288253 | | mysql-bin.000880 | 524288740 | | mysql-bin.000881 | 524288889 | | mysql-bin.000882 | 164902202 | +------------------+-----------+ 10 rows in set (0.00 sec)

3. 查看当前正在写入的binlog文件

   1 2 3 4 5 6 7

   mysql> show master status; +------------------+-----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+-----------+--------------+------------------+-------------------+ | mysql-bin.000882 | 185416398 | | | | +------------------+-----------+--------------+------------------+-------------------+ 1 row in set (0.01 sec)

4. 查看binlog事件信息

   1	show binlog events in 'mysql-bin.000882';

5. 查看 binlog 文件单个大小
   默认为1G

   1	show variables like '%binlog%';

mysqlbinlog 命令

1. 查看 binlog 文件

   1	mysqlbinlog mysql-bin.000882

2. 查看某个范围的binlog

   有四个参数可以选择性加入:

   1 2 3 4 5 6 7 8 9 10 11

   # 开始pos点 --start-position=953 # 结束pos点 --stop-position=1437 # 起始时间点: --start-datetime="2022-01-01 00:00:00" # 结束时间点: --stop-datetime="2022-01-02 00:00:00"

   查看时间范围的 binlog

   1	mysqlbinlog --start-datetime="2022-01-01 00:00:00" --stop-datetime="2022-01-02 01:00:00" mysql-bin.000882

   查看 pos 范围的 binlog

   1	mysqlbinlog --start-position=3648579 --stop-position=3656656 mysql-bin.000882

3. 查看解码后的 binlog 文件

   mysqlbinlog 默认只能查看到 base-64 编码的信息, 添加 –base64-output=value 选项可以进行解码等操作:

   • AUTO 或 UNSPEC: 在必要时自动显示 BINLOG 语句（即，用于格式描述事件和行事件）。如果没有给出 –base64-output 选项，则效果与 –base64-output = AUTO 相同。

   • NEVER: 不显示 BINLOG 语句

   • DECODE-ROWS: 通过配合–verbose 选项将行事件解码并显示为注释的SQL语句。使用参数–verbose(或-v)，将生成带注释的语句，如果使用两次这个参数(如-v -v)，会生成字段的类型、长度、是否为null等属性信息。

     生成带注释的语句

     1	mysqlbinlog -v --base64-output=DECODE-ROWS --start-position=3648579 --stop-position=3656656 mysql-bin.000882

4. 生成字段的类型、长度、是否为null等属性信息

   1	mysqlbinlog -v -v --base64-output=DECODE-ROWS --start-position=3648579 --stop-position=3656656 mysql-bin.000882

5. 输出 binlog 日志到指定文件

   1	mysqlbinlog --start-position=3648579 --stop-position=3656656 mysql-bin.000882 > test.log

   或者

   1	mysqlbinlog --start-position=3648579 --stop-position=3656656 mysql-bin.000882 --result-file=test2.log

6. 输出 binlog 日志到压缩文件中

   1	mysqlbinlog --start-position=3648579 --stop-position=3656656 mysql-bin.000882|gzip > test.zip

删除或重置 binlog 日志

1. 删除所有binlog日志，新日志编号从头开始

   1	RESET MASTER;

2. 删除 bin-log.000004 之前所有日志

   1	PURGE MASTER LOGS TO 'bin-log.000004';

3. 删除 2021-05-01 00:00:00 之前产生的所有日志

   1	PURGE MASTER LOGS BEFORE '2020-04-24 22:00:00';

4. 在 my.cnf 配置文件中指定日志过期时间

   1	expire_logs_days=30

5. 开启一个新的 binlog 日志文件

   1	flush logs;

   每次服务器（数据库）重启，服务器会调用 flush logs; 新创建一个binlog日志

从 binlog 中恢复数据

1. 找到要操作的日志文件

   1 2	show binlog events in 'mysql-bin.000883'; show binlog events in 'mysql-bin.000883' from 6269555 limit 5;

   日志文件	开始位置	事件类型	server_id	结束位置	info
   mysql-bin.000883	6269555	Query	1	6269658	BEGIN
   mysql-bin.000883	6269658	Query	1	6270097	UPDATE QRTZ_FIRED_TRIGGERS SET INSTANCE_NAME = ‘NON_CLUSTERED’, FIRED_TIME = 1656952860037, SCHED_TIME = 1656952860000, STATE = ‘EXECUTING’, JOB_NAME = ‘task_5221033110030001580326’, JOB_GROUP = ‘taskSystemJobGroup’, IS_NONCONCURRENT = 0, REQUESTS_RECOVERY = 0 WHERE SCHED_NAME = ‘quartzScheduler’ AND ENTRY_ID = ‘NON_CLUSTERED1655442134354’
   mysql-bin.000883	6270097	Query	1	6270662	UPDATE QRTZ_TRIGGERS SET JOB_NAME = ‘task_5221033110030001580326’, JOB_GROUP = ‘taskSystemJobGroup’, DESCRIPTION = null, NEXT_FIRE_TIME = 1656952870000, PREV_FIRE_TIME = 1656952860000, TRIGGER_STATE = ‘WAITING’, TRIGGER_TYPE = ‘CRON’, START_TIME = 1656665122000, END_TIME = 0, CALENDAR_NAME = null, MISFIRE_INSTR = 0, PRIORITY = 5 WHERE SCHED_NAME = ‘quartzScheduler’ AND TRIGGER_NAME = ‘taskToData_5221033110030001580326’ AND TRIGGER_GROUP = ‘taskToData_5221033110030001580326_group’
   mysql-bin.000883	6270662	Query	1	6271010	UPDATE QRTZ_CRON_TRIGGERS SET CRON_EXPRESSION = ‘*/10 * * * * ?’, TIME_ZONE_ID = ‘Asia/Shanghai’ WHERE SCHED_NAME = ‘quartzScheduler’ AND TRIGGER_NAME = ‘taskToData_5221033110030001580326’ AND TRIGGER_GROUP = ‘taskToData_5221033110030001580326_group’
   mysql-bin.000883	6271010	Xid	1	6271041	COMMIT /xid=152162255/

2. 按 pos 点进行数据恢复

   1 2	mysqlbinlog --start-position=6269555 --stop-position=6271010 mysql-bin.000883 | mysql -uroot -p123zxcv ```

3. 按日期进行数据恢复

   • 查看 binlog 日志

     1	mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000883 | mysql -uroot -p123zxcv

   • 不指定库恢复整个 binlog 日志的数据

     1 2	mysqlbinlog --start-datetime='2022-04-24 16:08:00' --stop-datetime='2022-04-24 16:28:00' bin-log.000002 | mysql -uroot -p123zxcv ```

4. 指定库进行数据恢复

   指定库恢复整个 binlog 日志的数据

   • 按 pos 点:

     1	mysqlbinlog --start-position=4 --stop-position=5010 --database=abc bin-log.000004 | mysql -uroot -p123zxcv

   • 按时间:

     1 2	mysqlbinlog --start-datetime='2022-04-24 16:08:00' --stop-datetime='2022-04-24 16:28:00' --database=abc bin-log.000002 | mysql -uroot -p123zxcv ```

参考资料

• mysql binlog常用操作