2025-07-15

bigdata / doris

7 分钟读完 (大约 981 个字) 0次访问

250715-doris

mysql binlog常用操作

mysql binlog常用操作

查看 binlog 基本信息

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

查看binlog文件列表

进入mysql命令行:

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)

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

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)

查看binlog事件信息

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

查看 binlog 文件单个大小
默认为1G
1
show variables like '%binlog%';

mysqlbinlog 命令

查看 binlog 文件
1
mysqlbinlog mysql-bin.000882

查看某个范围的binlog

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

    # 开始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

查看解码后的 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

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

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

输出 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

输出 binlog 日志到压缩文件中

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

删除或重置 binlog 日志

删除所有binlog日志，新日志编号从头开始
1
RESET MASTER;
删除 bin-log.000004 之前所有日志
1
PURGE MASTER LOGS TO 'bin-log.000004';
删除 2021-05-01 00:00:00 之前产生的所有日志
1
PURGE MASTER LOGS BEFORE '2020-04-24 22:00:00';
在 my.cnf 配置文件中指定日志过期时间
1
expire_logs_days=30
开启一个新的 binlog 日志文件
1
flush logs;
每次服务器（数据库）重启，服务器会调用 flush logs; 新创建一个binlog日志

从 binlog 中恢复数据

找到要操作的日志文件

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/

按 pos 点进行数据恢复

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

按日期进行数据恢复

查看 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 ```

指定库进行数据恢复

指定库恢复整个 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常用操作

# doris

文章

160

分类

标签

关注我