MySQL binlog日志详解查询和数据恢复细节 ops范围数据恢复时间节点范围 MySQL数据恢复数据库备份主从复制增量恢复

MySQL Binlog是二进制格式的日志文件（归档日志），二进制日志记录对数据发生或潜在发生更改的SQL语句，并以二进制的形式保存在磁盘中。用来记录Mysql内部对数据库的改动（只记录对数据的修改操作）。可以用来查看数据库的变更历史（具体的时间点所有的SQL操作）、数据库增量备份和恢复（增量备份和基于时间点的恢复）、Mysql的复制（主主数据库的复制、主从数据库的复制）。

1. binlog日志的信息

1.1 日志状态查看

// log_bin    on表示当前已开启二进制日志
// log_bin_basename    binlog存储的位置
// log_bin_index     binlog索引的位置

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)

1.2 开启binlog日志的方法

修改MySQL的配置问题在 my.cnf 里面加上配置如下配置重启mysql。

# binlog存储的位置
log-bin=/var/lib/mysql/mysql-bin
# 日志过期时间
expire_logs_days=30
# 不加这个启动会报错
server-id=123454

一般来说 my.cnf 在：/etc/mysql/my.cnf 或 /etc/my.cnf

文件的命名方式：名称为hostname-bin.xxxxx （重启mysql一次将会自动生成一个新的binlog）

文件位置：默认存放位置为数据库文件所在目录下

1.3 查看binlog配置文件

// binlog_format binlog存储格式ROW

mysql> show variables like 'binlog%';
+--------------------------------------------+--------------+
| Variable_name                              | Value        |
+--------------------------------------------+--------------+
| binlog_cache_size                          | 32768        |
| binlog_checksum                            | CRC32        |
| binlog_direct_non_transactional_updates    | OFF          |
| binlog_error_action                        | ABORT_SERVER |
| binlog_format                              | ROW          |
| binlog_group_commit_sync_delay             | 0            |
| binlog_group_commit_sync_no_delay_count    | 0            |
| binlog_gtid_simple_recovery                | ON           |
| binlog_max_flush_queue_time                | 0            |
| binlog_order_commits                       | ON           |
| binlog_row_image                           | FULL         |
| binlog_rows_query_log_events               | OFF          |
| binlog_stmt_cache_size                     | 32768        |
| binlog_transaction_dependency_history_size | 25000        |
| binlog_transaction_dependency_tracking     | COMMIT_ORDER |
+--------------------------------------------+--------------+
15 rows in set (0.01 sec)

2. 查看binlog日志的内容

2.1 查看binlog日志目录存储文件

[root@ mysql]# pwd
/var/lib/mysql
[root@ mysql]# ls|grep mysql
mysql
mysql-bin.000001
mysql-bin.index
mysql.sock
mysql.sock.lock

2.2 mysqlbinlog 查看 binlog 日志

binlog不能直接用文本的方式打开，mysql提供了相应的查看工具：mysqlbinlog，直接查看单个二进制日志文件：mysqlbinlog filename。

2.2.1 普通查看

 mysqlbinlog /var/lib/mysql/mysql-bin.000001

2.2.2 遇乱码时查看

mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000001

2.2.3 查看示例结果

[root@ mysql]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#221205 15:56:47 server id 123454  end_log_pos 123 CRC32 0xc944b9c0 	Start: binlog v 4, server v 5.7.38-log created 221205 15:56:47 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 123
#221205 15:56:47 server id 123454  end_log_pos 154 CRC32 0x07fba24c 	Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

3. binlog日志的管理

3.1 开启或关闭二进制日志配置

3.1.1 修改my.cnf参数文件

开启时修改 my.cnf 参数文件，该方法需要重启，具体配置方法见 1.2开启binlog日志的方法。

关闭二进制日志的方法，将binlog日志开启的配置注释掉即可

3.1.2 不重启修改二进制日志配置

该方法mysql的版本需要5.6以上。

// 开启binlog日志
mysql > SET @@global.log_bin=1|0          （1为开启，0为关闭）

mysql > SET @@global.binlog_size=37268    (单位bytes)

// 暂停binlog日志
mysql > SET sql_log_bin={0|1}

3.2 二进制日志的大小

3.2.1 查看二进制日志大小

通过命令来查看binlog 单个文件大小默认是1G，如果超过了1G就会新增一个文件。重启MySQL的时候也会新增一个新的binlog文件上文中也有提到。

mysql> show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)

3.2.2 修改二进制日志大小

修改my.cnf参数文件中的 max_binlog_size 的值；

说明：如果你的二进制文件的大小超过了 max_binlog_size，它就是自动创建新的二进制文件。当然如果恰好在日志文件到达它的最大尺寸时写入了大的事务，那么日志文件还是会超过max_binlog_size 的大小。

3.3 二进制日志的切换

默认情况下当二进制日志写满了或者数据库重启了才会进行切换，但是也可以手工的进行切换的动作。

// 手动切换新的binlog日志命令 flush logs

mysql >  flush logs;
Query OK, 0 rows affected (0.10 sec)

3.4 其他参数

// 查看全部的日志 
mysql > show master logs;

// 查看日志的最后一次操作
mysql > show master status;

// 刷新binlog日志，也就是新开启一个日志文件,在恢复数据的时候很有用
mysql > flush logs;

// 设置二进制日志缓存大小
binlog-cache-size=100m

// 事务提交同步配置
sync-binlog=N

PS:每个N秒将缓存中的二进制日志记录写回硬盘，默认值为0。不过，你经常会陷入group commit函数与I/O之间二选一的矛盾。如果在replication环境中，由于考虑到耐久性与一致性，则需要设置1。同时，还需要设置innodb_flush_log_at_trx_commit=1以及innodb-support-xa=1默认开启；

// sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。
// sync_binlog=1，强一致，每次事物提交都进行磁盘同步。
// sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘

3.5 删除二进制日志

二进制日志会不断的增长并产生多个文件。因此需要制定备份计划和管理策略。无用的二进制日志要记得及时删除。

3.5.1 过期自动删除

可以使用修改 my.cnf 配置文件 expire_logs_days=30 来配置日志保存时间

3.5.2 删除日志有三种方法

最好不要手动去删除binlog日志，这样会导致过期删除出错，如果非要删除要记得更新一下 xxxxx.mysql-bin.index

1、操作系统命令直接删除

2、reset master

3、PURGE BINARY LOGS BEFORE '2014-07-09 12:40:26′;

4. binlog日志三种格式

binlog有三种编码格式分别是 statement、row、mixed。

statement，基于SQL语句的复制(statement-based replication, SBR)

row，基于行的复制(row-based replication, RBR)

mixed，混合模式复制(mixed-based replication, MBR)

4.1 statement

statement格式每一条会修改数据的sql都会记录在binlog中。

4.1.1 statement 优点

不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)

4.1.2 statement 缺点

由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题)。

4.1.3 statement 无法复制的情况

* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)
同时在INSERT ...SELECT 会产生比 RBR 更多的行级锁

4.2 row

不记录sql语句上下文相关信息，仅保存哪条记录被修改。

4.2.1 row 优点

binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。

4.2.2 row 缺点

所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

4.3 mixed

mixed是以上Statement、Row两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog。

MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种.新版本的MySQL中对row level模式也被做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

4.4 修改binlog日志格式

通过修改my.cnf配置文件中的配置项，重启服务器完成binlog格式更改

binlog_format = statement

binlog_format = row

binlog_format = mixed

5. 通过binlog恢复数据

5.1 测试数据操作步骤

1、查看binlog日志记录文件，现正在记录mysql-bin.0000xx编号

2、mysql > flush logs; // 为了方便测试，刷新日志开启新的binlog日志

3、添加数据，向目标数据库中添加多条测试数据

4、删除数据，删除目标数据库中的其中1条测试数据

5、mysql > flush logs; // 步骤2和3操作完毕后再次刷新日志，生成单独记录步骤文件

6、查看binlog日志记录文件，查看新生成的mysql-bin.0000xx编号

7、测试数据准备完毕，准备恢复数据

5.2 恢复数据

5.2.1 使用 pos 恢复数据

5.2.1.1 查看日志命令

show binlog events in 'mysql-bin.000003';

查询具体结果如下

mysql> show binlog events in 'mysql-bin.000003';
+------------------+-------+----------------+-----------+-------------+-----------------------------------------+
| Log_name         | Pos   | Event_type     | Server_id | End_log_pos | Info                                    |
+------------------+-------+----------------+-----------+-------------+-----------------------------------------+
| mysql-bin.000003 |     4 | Format_desc    |    123454 |         123 | Server ver: 5.7.38-log, Binlog ver: 4   |
| mysql-bin.000003 |   123 | Previous_gtids |    123454 |         154 |                                         |
| mysql-bin.000003 |   154 | Anonymous_Gtid |    123454 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'    |
| mysql-bin.000003 |   219 | Query          |    123454 |         305 | BEGIN                                   |
| mysql-bin.000003 |   305 | Table_map      |    123454 |         409 | table_id: 110 (HttpRunnerPlatform.task) |
| mysql-bin.000003 |   409 | Write_rows     |    123454 |        1329 | table_id: 110 flags: STMT_END_F         |
| mysql-bin.000003 |  1329 | Xid            |    123454 |        1360 | COMMIT /* xid=190 */                    |
| mysql-bin.000003 |  1360 | Anonymous_Gtid |    123454 |        1425 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'    |
| mysql-bin.000003 |  1425 | Query          |    123454 |        1511 | BEGIN                                   |
| mysql-bin.000003 |  1511 | Table_map      |    123454 |        1615 | table_id: 110 (HttpRunnerPlatform.task) |
| mysql-bin.000003 |  1615 | Update_rows    |    123454 |        3476 | table_id: 110 flags: STMT_END_F         |
| mysql-bin.000003 |  3476 | Xid            |    123454 |        3507 | COMMIT /* xid=191 */                    |
| mysql-bin.000003 |  3507 | Anonymous_Gtid |    123454 |        3572 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'    |
| mysql-bin.000003 |  3572 | Query          |    123454 |        3658 | BEGIN                                   |
| mysql-bin.000003 |  3658 | Table_map      |    123454 |        3762 | table_id: 110 (HttpRunnerPlatform.task) |
| mysql-bin.000003 |  3762 | Write_rows     |    123454 |        4684 | table_id: 110 flags: STMT_END_F         |
| mysql-bin.000003 |  4684 | Xid            |    123454 |        4715 | COMMIT /* xid=202 */                    |
| mysql-bin.000003 |  4715 | Anonymous_Gtid |    123454 |        4780 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'    |
| mysql-bin.000003 |  4780 | Query          |    123454 |        4866 | BEGIN                                   |
| mysql-bin.000003 |  4866 | Table_map      |    123454 |        4970 | table_id: 110 (HttpRunnerPlatform.task) |
| mysql-bin.000003 |  4970 | Update_rows    |    123454 |        6835 | table_id: 110 flags: STMT_END_F         |
| mysql-bin.000003 |  6835 | Xid            |    123454 |        6866 | COMMIT /* xid=203 */                    |
| mysql-bin.000003 |  6866 | Anonymous_Gtid |    123454 |        6931 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'    |
| mysql-bin.000003 |  6931 | Query          |    123454 |        7017 | BEGIN                                   |
| mysql-bin.000003 |  7017 | Table_map      |    123454 |        7121 | table_id: 110 (HttpRunnerPlatform.task) |
| mysql-bin.000003 |  7121 | Update_rows    |    123454 |        9037 | table_id: 110 flags: STMT_END_F         |
| mysql-bin.000003 |  9037 | Xid            |    123454 |        9068 | COMMIT /* xid=214 */                    |
| mysql-bin.000003 |  9068 | Anonymous_Gtid |    123454 |        9133 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'    |
| mysql-bin.000003 |  9133 | Query          |    123454 |        9219 | BEGIN                                   |
| mysql-bin.000003 |  9219 | Table_map      |    123454 |        9323 | table_id: 110 (HttpRunnerPlatform.task) |
| mysql-bin.000003 |  9323 | Update_rows    |    123454 |       11332 | table_id: 110 flags: STMT_END_F         |
| mysql-bin.000003 | 11332 | Xid            |    123454 |       11363 | COMMIT /* xid=224 */                    |
| mysql-bin.000003 | 11363 | Rotate         |    123454 |       11410 | mysql-bin.000004;pos=4                  |
+------------------+-------+----------------+-----------+-------------+-----------------------------------------+
33 rows in set (0.00 sec)

下图已标记pos的开始和结束的位置，即可恢复此阶段的数据

5.2.1.2 使用ops恢复数据命令

// 进入到 binlog 目录下
cd /var/lib/mysql

// 数据恢复命令
mysqlbinlog --start-position=开始的pos --stop-position=结束的pos --database=要操作的数据库 binlog的名称 | mysql -u登陆名 -p登陆密码 -v 要操作的数据库

// 最终执行命令
mysqlbinlog --start-position=219 --stop-position=130 --database=my_test mysql-bin.000003 | mysql -uroot -p123456 -v my_test

// 查询数据库，数据是否恢复

5.2.2 使用时间恢复数据

5.2.2.1 查看日志命令

[root@ mysql]#mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000003

部分查询结果如下

BEGIN
/*!*/;
# at 305
#221205 17:10:59 server id 123454  end_log_pos 409 CRC32 0xa872d467 	Table_map: `HttpRunnerPlatform`.`task` mapped to number 110
# at 409
#221205 17:10:59 server id 123454  end_log_pos 1329 CRC32 0xf2db1a24 	Write_rows: table id 110 flags: STMT_END_F
### INSERT INTO `HttpRunnerPlatform`.`task`
### SET
###   @1=12342 /* INT meta=0 nullable=0 is_null=0 */
###   @2='2022-12-05 17:10:59' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
###   @3='App_rpc' /* VARSTRING(800) meta=800 nullable=0 is_null=0 */
###   @4='App' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
###   @5='STAGE' /* VARSTRING(200) meta=200 nullable=0 is_null=0 */
###   @6='' /* VARSTRING(44) meta=44 nullable=1 is_null=0 */
###   @7='Benjaimin' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @8='0' /* VARSTRING(200) meta=200 nullable=1 is_null=0 */
###   @9=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @10=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @11=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @12=NULL /* INT meta=0 nullable=1 is_null=1 */
###   @13='' /* VARSTRING(4000) meta=4000 nullable=1 is_null=0 */
###   @14='' /* VARSTRING(4000) meta=4000 nullable=1 is_null=0 */
###   @15='' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */
###   @16=2 /* INT meta=0 nullable=1 is_null=0 */
###   @17=1 /* INT meta=0 nullable=1 is_null=0 */
###   @18='App' /* VARSTRING(1020) meta=1020 nullable=1 is_null=0 */
###   @19='{\x27ipInfo\x27: \x27当前 IP：106.xx.xx.xx  来自于：中国 北京 北京  电信\x5cn\x27, .......' /* VARSTRING(4000) meta=4000 nullable=1 is_null=0 */
# at 1329
#221205 17:10:59 server id 123454  end_log_pos 1360 CRC32 0x74ec16b4 	Xid = 190
COMMIT/*!*/;
# at 1360
#221205 17:10:59 server id 123454  end_log_pos 1425 CRC32 0xbccd2751 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1425
#221205 17:10:59 server id 123454  end_log_pos 1511 CRC32 0x16e6253b 	Query	thread_id=34	exec_time=0	error_code=0
SET TIMESTAMP=1670231459/*!*/;

5.2.2.1 使用时间恢复数据命令

mysqlbinlog --start-datetime='2022-12-05 17:00:00' --stop-datetime='2022-12-05 17:30:00' --database=my_test mysql-bin.000003 | mysql -uroot -p123456 -v my_test

BenjaminQA

关注

1
点赞
踩
9

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL binlog日志详解查询和数据恢复细节 ops范围数据恢复时间节点范围 MySQL数据恢复数据库备份主从复制增量恢复

MySQL binlog日志详解查询和数据恢复细节按时间点按操作id顺序范围数据恢复数据库备份主从复制增量恢复MySQL Binlog是二进制格式的日志文件，二进制日志记录对数据发生或潜在发生更改的SQL语句，并以二进制的形式保存在磁盘中。用来记录Mysql内部对数据库的改动（只记录对数据的修改操作）。可以用来查看数据库的变更历史（具体的时间点所有的SQL操作）、数据库增量备份和恢复（增量备份和基于时间点的恢复）、Mysql的复制（主主数据库的复制、主从数据库的复制）。
复制链接

扫一扫