MySQL二进制日志深入解析与应用
1.1 MySQL Binlog 二进制日志概述
这个文件记录了mysql数据库所有的dml，ddl语句事件（不包括select），记录增删改操作，也可以记录SQL语句，也可以记录行变化记录，还可以记录这些操作的时间。
#比如如下这条SQL
update test_t set name='name' where id between 1 and 5;
记录：
-
可能是这条语句
-
可能是记录5条数据的修改情况
-
可能两个都同时记录。
三种日志的区别：
- general log：记录数据库里面所有的SQL操作记录。
- redo log：值记录innodb存储引擎的修改日志
- binlog：只记录数据库server层面内部的修改情况。–select/show不记录
开启binlog二进制日志有什么好处？以应用场景为说明：
-
MySQL主从复制：通过binlog实现数据复制
-
数据恢复：如宕机恢复，异常操作的恢复
不好的地方就是：大概损失1%左右的性能，占用空间。
1.2 MySQL binlog 的三种工作模式
mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
工作模式
-
row level （默认级别：mysql5.7.6 之后+8.0）
日志中记录每一行数据修改的情况。
优点：可以更方便查看每一行数据修改的细节。
缺点：数据量比较大
使用场景：希望数据最安全，复制强一致。
-
statement level （默认级别：mysql5.7.6 之前）
记录每一条修改的SQL。
优点：解决了数据量比较大的问题
缺点：容易出现主从复制不一致
使用场景：使用mysql的功能比较少，又不使用存储过程/触发器/函数
-
mixed (混合模式)
结合row level和statement level的优点
1.3 配置MySQL binlog 二进制日志
mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | OFF | #二进制日志是否打开
| log_bin_basename | | #二进制日志的名字
| log_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)
mysql> show variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%binlog_rows_query_log_events%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| binlog_rows_query_log_events | OFF | #记录的是事件还是语句，打开的话记录会比较详细
+------------------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 3306 |
+---------------+-------+
1 row in set (0.00 sec)
#该设置（server_id）只能是数字，建议ip+端口，例如513306，因为一个局域网内IP是不会重复的，配置主从一定要设置server_id，要指定一个不能和其他集群中重名的字符串
修改参数
cat >> /mysql/data/3306/my.cnf << EOF
[mysqld]
log_bin=/mysql/log/3306/binlog/testdb-binlog
log_bin_index=/mysql/log/3306/binlog/testdb-binlog.index
binlog_format='row'
binlog_rows_query_log_events=on
EOF
#创建目录并授权：
mkdir -p /mysql/log/3306/binlog
chown -R mysql:mysql /mysql/log/3306/binlog
chmod -R 755 /mysql/log/3306/binlog
#重启MySQL服务
service mysql restart
检查参数
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------+
| log_bin | ON |
| log_bin_basename | /mysql/log/3306/binlog/testdb-binlog |
| log_bin_index | /mysql/log/3306/binlog/testdb-binlog.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)
mysql> show variables where Variable_name in ('binlog_format','binlog_rows_query_log_events');
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| binlog_format | ROW |
| binlog_rows_query_log_events | ON |
+------------------------------+-------+
2 rows in set (0.00 sec)
参数说明
-
log_bin=/mysql/log/3306/binlog/testdb-binlog
这个名字会自动加后缀：.00000*） log_bin默认值为off，我们修改的时候不能写on，要输入具体的binlog的路径和文件名，对应的目录要事先创建好，设置好之后，默认的off就自动变为on了
-
log_bin_index=/mysql/log/3306/binlog/testdb-binlog.index
-
binlog_format=‘row’
可设置的值为：row，statement，mixed，默认就是行模式
-
log_bin_basename
basename参数我们不能手动设置，log_bin设置了binlog的路径后，转为on，log_bin_basename参数就会自动填充binlog的路径信息
查看二进制日志文件
#服务器上生成的文件
[root@centos7 binlog]# pwd
/mysql/log/3306/binlog
[root@centos7 binlog]# ll -h
total 8.0K
-rw-r----- 1 mysql mysql 154 Aug 13 18:01 testdb-binlog.000001
-rw-r----- 1 mysql mysql 44 Aug 13 18:01 testdb-binlog.index
[root@centos7 binlog]#
[root@centos7 binlog]# mysqlbinlog testdb-binlog.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#230813 18:01:13 server id 3306 end_log_pos 123 CRC32 0x09afc30a Start: binlog v 4, server v 5.7.43-log created 230813 18:01:13 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
6anYZA/qDAAAdwAAAHsAAAABAAQANS43LjQzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADpqdhkEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AQrDrwk=
'/*!*/;
# at 123
#230813 18:01:13 server id 3306 end_log_pos 154 CRC32 0x5a309a75 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*/;
[root@centos7 binlog]#
#刷新日志
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
#如下刷新日志后生成了新的二进制日志
[root@centos7 binlog]# ll -h
total 12K
-rw-r----- 1 mysql mysql 205 Aug 13 18:13 testdb-binlog.000001
-rw-r----- 1 mysql mysql 154 Aug 13 18:13 testdb-binlog.000002
-rw-r----- 1 mysql mysql 88 Aug 13 18:13 testdb-binlog.index
[root@centos7 binlog]#
1.4 MySQL Binary 日志相关参数详解
1.4.1 常用必配参数
show variables like '%log_bin%';
show variables like '%binlog%';
show variables like '%server%';
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------------+
| log_bin | ON |
| log_bin_basename | /mysql/log/3306/binlog/testdb-binlog |
| log_bin_index | /mysql/log/3306/binlog/testdb-binlog.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)
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 | ON |
| binlog_stmt_cache_size | 32768 |
| binlog_transaction_dependency_history_size | 25000 |
| binlog_transaction_dependency_tracking | COMMIT_ORDER |
| innodb_api_enable_binlog | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| log_statements_unsafe_for_binlog | ON |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sync_binlog | 1 |
+--------------------------------------------+----------------------+
22 rows in set (0.01 sec)
mysql> show variables like '%server%';
+---------------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------+
| character_set_server | utf8 |
| collation_server | utf8_general_ci |
| innodb_ft_server_stopword_table | |
| server_id | 3306 |
| server_id_bits | 32 |
| server_uuid | e2646e6f-38d6-11ee-b291-000c29151c55 |
+---------------------------------+--------------------------------------+
6 rows in set (0.00 sec)
mysql>
1.4.2 文件相关参数
show variables like '%binlog%';
参数说明
-
max_binlog_size
范围4K-1G，默认1G。但如果一个SQL 事务太大，比如这个SQL 产生5G 日志，binlog 也会达到5G。
mysql> show variables like '%max_binlog_size%'; +-----------------+------------+ | Variable_name | Value | +-----------------+------------+ | max_binlog_size | 1073741824 | +-----------------+------------+ 1 row in set (0.00 sec) mysql> select round(1073741824/1024/1024/1024,0) "size/GB"; +---------+ | size/GB | +---------+ | 1 | +---------+ 1 row in set (0.00 sec) -
binlog_row_image
针对binlog_format='row’格式来设置记录的日志范围：
默认是full, 还可以是minimal,noblob
mysql> show variables like '%binlog_row_image%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | binlog_row_image | FULL | +------------------+-------+ 1 row in set (0.00 sec) -
binlog_gtid_simple_recovery
在主从复制时有影响，现在是on，用于重启/清理时数据库只打开最老和最新的两个binlog 计算gtid_purged 和gtid_executed，不需要打开所有的文件。如果主从复制计算gtid 出错，改为off，需要打开所有文件计算，影响性能。
mysql> show variables like '%binlog_gtid_simple_recovery%'; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | binlog_gtid_simple_recovery | ON | +-----------------------------+-------+ 1 row in set (0.00 sec)
1.4.3 缓存大小
1. binlog_cache_size:
二进制日志写缓存区大小，默认32K
mysql> show variables like 'binlog_cache_size';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| binlog_cache_size | 32768 |
+-------------------+-------+
1 row in set (0.00 sec)
mysql> select 32768/1024;
+------------+
| 32768/1024 |
+------------+
| 32.0000 |
+------------+
1 row in set (0.00 sec)
写binlog 的流程：
数据在buffer pool中排序和其他操作，并在二进制日志缓存中记录，在我们提交之前，会先将二进制日志缓存写到磁盘系统缓存中，当提交的时候再写到二进制日志文件里面去。
数据操作buffer pool > binlog buffer > file system buffer > commit > binlog file
状态监控
mysql> show status like 'binlog_cache%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec)
Binlog_cache_use 表示用到binlog 缓存区的次数。
Binlog_cache_disk_use 表示使用临时文件来存放binlog 缓存的次数，该值过多就需要提高binlog_cache_size的值，让它刷的少一点。
2. max_binlog_cache_size
mysql> show variables like 'max_binlog_cache_size';
+-----------------------+----------------------+
| Variable_name | Value |
+-----------------------+----------------------+
| max_binlog_cache_size | 18446744073709547520 |
+-----------------------+----------------------+
1 row in set (0.00 sec)
mysql> select round(18446744073709547520/1024/1024/1024,2) "size/GB";
+----------------+
| size/GB |
+----------------+
| 17179869184.00 |
+----------------+
1 row in set (0.00 sec)
3. binlog_stmt_cache_size
非事务语句文件缓存大小，默认32K
mysql> show variables like 'binlog_stmt_cache_size';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| binlog_stmt_cache_size | 32768 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql> select 32768/1024;
+------------+
| 32768/1024 |
+------------+
| 32.0000 |
+------------+
1 row in set (0.00 sec)
4. max_binlog_stmt_cache_size
mysql> show variables like 'max_binlog_stmt_cache_size';
+----------------------------+----------------------+
| Variable_name | Value |
+----------------------------+----------------------+
| max_binlog_stmt_cache_size | 18446744073709547520 |
+----------------------------+----------------------+
1 row in set (0.00 sec)
1.4.4 flush disk 相关的
1. sync_binlog：
mysql> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 1 |
+---------------+-------+
1 row in set (0.00 sec)
写binlog 的流程：
数据在buffer pool中排序和其他操作，并在二进制日志缓存中记录，在我们提交之前，会先将二进制日志缓存写到磁盘系统缓存中，当提交的时候再写到二进制日志文件里面去。
数据操作buffer pool > binlog buffer > file system buffer > commit > binlog file
在写binlog file 之前commit 有3 种模式，分别是:0，1，N
-
sync_binlog=0
mysql 不会主动同步binlog 内容到磁盘文件中，而是依赖操作系统刷新文件的机制来刷binlog file.一般是1秒 刷一次
-
sync_binlog=1
默认值，mysql 主动刷新file system buffer 到磁盘上的binlog file中，每1 次commit，就主动fsync 一次
-
sync_binlog=N（不是0，也不是1，自定义设置的值，比如5）
mysql 主动刷新file system buff 到磁盘上的binlog file 中，每N 次commit，就主动fsync 一次，
数据库先写redo log 还是先写binlog？
先写redo log,再写binlog。
2. innodb_flush_log_at_trx_commit
mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
1 row in set (0.00 sec)
sync_binlog 配合另一个参数innodb_flush_log_at_trx_commit;如果都是1，数据库挂了以后，最多只丢一条语句或一个事务的数据。
1.5 MySQL Binlog 二进制格式详解
涉及到3 个参数：
- binlog_format=‘row’ – (row,statement,mixed)
- binlog_row_image=full – (full,minimal,noblob)
- binlog_rows_query_log_events=on – (on,off)
1.5.1 binlog_format=‘statement’
#修改行格式
mysql> set session binlog_format='statement';
Query OK, 0 rows affected (0.00 sec)
mysql> set global binlog_format='statement';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.binlog_format,@@binlog_format;
+------------------------+-----------------+
| @@global.binlog_format | @@binlog_format |
+------------------------+-----------------+
| STATEMENT | STATEMENT |
+------------------------+-----------------+
1 row in set (0.00 sec)
#修改隔离级别为重复读
set session transaction_isolation='repeatable-read';
set global transaction_isolation='repeatable-read'; # 新的设置方法
#set global tx_isolation='repeatable-read'; # 旧的设置方法
mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ | REPEATABLE-READ |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)
#查看当前的日志状态
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000002 | 154 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#刷新日志
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000003 | 154 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#刷新日志的目的是保证后续的所有操作都记录到003这个二进制日志里面（一个新的二进制日志）
模拟DDL与DML操作：
#创建测试表
create database testdb;
use testdb
create table test_t (
`id` int(11) primary key not null auto_increment,
`rowformat` varchar(50) not null,
`uuids` varchar(50) not null,
`timepoint` datetime not null default current_timestamp,
CurrentVersion timestamp not null default current_timestamp on update
current_timestamp
) engine=innodb;
#插入数据
mysql> insert into test_t(rowformat,uuids) select 'test',uuid();
Query OK, 1 row affected, 1 warning (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 1
#有个警告，查看警告的信息
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1592
Message: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
1 row in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000003 | 1121 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
如上position由之前的154变为了1121，154~1121之间就记录了我们上面的操作。
接下来看154~1121 之间的操作记录，用mysqlbinlog 读取。
[root@centos7 binlog]# pwd
/mysql/log/3306/binlog
[root@centos7 binlog]# mysqlbinlog --start-position=154 --stop-position=1121 testdb-binlog.000003 > 1.txt
[root@centos7 binlog]# cat -n 1.txt
1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
3 DELIMITER /*!*/;
4 # at 4
5 #230813 20:15:46 server id 3306 end_log_pos 123 CRC32 0x4780b5e1 Start: binlog v 4, server v 5.7.43-log created 230813 20:15:46
6 # Warning: this binlog is either in use or was not closed properly.
7 BINLOG '
8 csnYZA/qDAAAdwAAAHsAAAABAAQANS43LjQzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
9 AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
10 AeG1gEc=
11 '/*!*/;
12 # at 154
13 #230813 20:17:46 server id 3306 end_log_pos 219 CRC32 0x35f420b2 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
14 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
15 # at 219
16 #230813 20:17:46 server id 3306 end_log_pos 319 CRC32 0x28367c06 Query thread_id=3 exec_time=0 error_code=0
17 SET TIMESTAMP=1691929066/*!*/;
18 SET @@session.pseudo_thread_id=3/*!*/;
19 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
20 SET @@session.sql_mode=1436549120/*!*/;
21 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
22 /*!\C utf8 *//*!*/;
23 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
24 SET @@session.lc_time_names=0/*!*/;
25 SET @@session.collation_database=DEFAULT/*!*/;
26 create database testdb
27 /*!*/;
28 # at 319
29 #230813 20:18:06 server id 3306 end_log_pos 384 CRC32 0x906cd222 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
30 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
31 # at 384
32 #230813 20:18:06 server id 3306 end_log_pos 760 CRC32 0x646a3091 Query thread_id=3 exec_time=0 error_code=0
33 use `testdb`/*!*/;
34 SET TIMESTAMP=1691929086/*!*/;
35 SET @@session.explicit_defaults_for_timestamp=1/*!*/;
36 create table test_t (
37 `id` int(11) primary key not null auto_increment,
38 `rowformat` varchar(50) not null,
39 `uuids` varchar(50) not null,
40 `timepoint` datetime not null default current_timestamp,
41 CurrentVersion timestamp not null default current_timestamp on update
42 current_timestamp
43 ) engine=innodb
44 /*!*/;
45 # at 760
46 #230813 20:18:58 server id 3306 end_log_pos 825 CRC32 0x127a1ece Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no
47 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
48 # at 825
49 #230813 20:18:58 server id 3306 end_log_pos 916 CRC32 0xf3544700 Query thread_id=3 exec_time=0 error_code=0
50 SET TIMESTAMP=1691929138/*!*/;
51 SET @@session.time_zone='SYSTEM'/*!*/;
52 BEGIN
53 /*!*/;
54 # at 916
55 # at 948
56 #230813 20:18:58 server id 3306 end_log_pos 948 CRC32 0x489e144a Intvar
57 SET INSERT_ID=1/*!*/;
58 #230813 20:18:58 server id 3306 end_log_pos 1090 CRC32 0x17060eb7 Query thread_id=3 exec_time=0 error_code=0
59 SET TIMESTAMP=1691929138/*!*/;
60 insert into test_t(rowformat,uuids) select 'test',uuid()
61 /*!*/;
62 # at 1090
63 #230813 20:18:58 server id 3306 end_log_pos 1121 CRC32 0xc6d7e6b3 Xid = 54
64 COMMIT/*!*/;
65 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
66 DELIMITER ;
67 # End of log file
68 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
69 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos7 binlog]#
#如上GTID_NEXT就表示一个事务的结束，开启了一个新的事务
#如上26行、33行、36行、60行、64行，刚所做的操作
#delete操作
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000003 | 1121 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> delete from test_t where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000003 | 1407 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@centos7 binlog]# mysqlbinlog --start-position=1121 --stop-position=1407 testdb-binlog.000003 > 1.txt
[root@centos7 binlog]# cat -n 1.txt
1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
3 DELIMITER /*!*/;
4 # at 4
5 #230813 20:15:46 server id 3306 end_log_pos 123 CRC32 0x4780b5e1 Start: binlog v 4, server v 5.7.43-log created 230813 20:15:46
6 # Warning: this binlog is either in use or was not closed properly.
7 BINLOG '
8 csnYZA/qDAAAdwAAAHsAAAABAAQANS43LjQzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
9 AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
10 AeG1gEc=
11 '/*!*/;
12 # at 1121
13 #230813 20:30:56 server id 3306 end_log_pos 1186 CRC32 0x5d6e4264 Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=no
14 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
15 # at 1186
16 #230813 20:30:56 server id 3306 end_log_pos 1269 CRC32 0xa9e57e03 Query thread_id=3 exec_time=0 error_code=0
17 SET TIMESTAMP=1691929856/*!*/;
18 SET @@session.pseudo_thread_id=3/*!*/;
19 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
20 SET @@session.sql_mode=1436549120/*!*/;
21 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
22 /*!\C utf8 *//*!*/;
23 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
24 SET @@session.lc_time_names=0/*!*/;
25 SET @@session.collation_database=DEFAULT/*!*/;
26 BEGIN
27 /*!*/;
28 # at 1269
29 #230813 20:30:56 server id 3306 end_log_pos 1376 CRC32 0x70277753 Query thread_id=3 exec_time=0 error_code=0
30 use `testdb`/*!*/;
31 SET TIMESTAMP=1691929856/*!*/;
32 delete from test_t where id=1
33 /*!*/;
34 # at 1376
35 #230813 20:30:56 server id 3306 end_log_pos 1407 CRC32 0x3b117e96 Xid = 64
36 COMMIT/*!*/;
37 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
38 DELIMITER ;
39 # End of log file
40 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
41 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos7 binlog]#
#drop操作
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000003 | 1407 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> drop table test_t;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000003 | 1595 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
[root@centos7 binlog]# mysqlbinlog --start-position=1407 --stop-position=1595 testdb-binlog.000003 > 1.txt
[root@centos7 binlog]# cat -n 1.txt
1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
3 DELIMITER /*!*/;
4 # at 4
5 #230813 20:15:46 server id 3306 end_log_pos 123 CRC32 0x4780b5e1 Start: binlog v 4, server v 5.7.43-log created 230813 20:15:46
6 # Warning: this binlog is either in use or was not closed properly.
7 BINLOG '
8 csnYZA/qDAAAdwAAAHsAAAABAAQANS43LjQzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
9 AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
10 AeG1gEc=
11 '/*!*/;
12 # at 1407
13 #230813 20:33:29 server id 3306 end_log_pos 1472 CRC32 0xd5d84f49 Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=no
14 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
15 # at 1472
16 #230813 20:33:29 server id 3306 end_log_pos 1595 CRC32 0x00bc24a8 Query thread_id=3 exec_time=0 error_code=0
17 use `testdb`/*!*/;
18 SET TIMESTAMP=1691930009/*!*/;
19 SET @@session.pseudo_thread_id=3/*!*/;
20 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
21 SET @@session.sql_mode=1436549120/*!*/;
22 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
23 /*!\C utf8 *//*!*/;
24 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
25 SET @@session.lc_time_names=0/*!*/;
26 SET @@session.collation_database=DEFAULT/*!*/;
27 DROP TABLE `test_t` /* generated by server */
28 /*!*/;
29 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
30 DELIMITER ;
31 # End of log file
32 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
33 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos7 binlog]#
总结：
binlog_format=‘statement’ 的时候，DDL及DML都是明文按SQL记录存储
对主从复制的影响：
- 对有些参数，在不用的服务器和不用的时间，执行结果不一样，会导致主从不一致
- 特别是一些函数：uuid()，user()，时间函数，now()，等等
- 性能问题（同步到从库的全部是SQL语句，假设主库的某条SQL性能差，在从库也会执行这条性能差的语句，这就会导致性能差）
- 数据异常（假设主端插入了某张表的数据而数据量异常，从端也就跟着异常）
每一条会修改数据的sql语句会记录到binlog中，slave在复制的时候sql进程会解析成master端执行过的相同的sql在slave库上再次执行。
- **优点：**statement level下的优点首先就是解决了row level下的缺点，不需要每一条sql语句和记录每一行的变化，较少binlog日志量，节约IO，提高性能（从库应用日志快）。因为它只需要记录在master上所执行的语句的细节，以及执行语句时候的上下文信息。
- **缺点：**由于它是记录执行语句，所以，为了让这些语句在slave端也能正确执行，那么它还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，来保证所有语句在slave端能够得到和在master端相同的执行结果。由于mysql更新较快，使mysql的赋值遇到了不小的挑战，自然赋值的时候就会涉及到越复杂的内容，bug也就容易出现。在statement level下，目前就已经发现了不少情况会造成mysql的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现。比如：sleep（）函数在有些版本中就不能正确赋值，在存储过程中使用了last_insert_id（）函数，以及user-defined functions(udf)等，可能会使slave和master上得到不一致的id等等。由于row level是基于每一行记录的裱花，所以不会出现类似的问题。
1.5.2 binlog_format=‘row’
1.5.2.1 binlog_row_image=full,binlog_rows_query_log_events=off
#修改行格式
set session binlog_format='row';
set global binlog_format='row';
set session binlog_rows_query_log_events=0; -- 默认
set global binlog_rows_query_log_events=0; -- 默认
set session binlog_row_image=full; -- 默认
set global binlog_row_image=full; -- 默认
mysql> select @@global.binlog_format,@@binlog_format,@@global.binlog_rows_query_log_events,@@binlog_rows_query_log_events,@@global.binlog_row_image,@@binlog_row_image\G
*************************** 1. row ***************************
@@global.binlog_format: ROW
@@binlog_format: ROW
@@global.binlog_rows_query_log_events: 0
@@binlog_rows_query_log_events: 0
@@global.binlog_row_image: FULL
@@binlog_row_image: FULL
1 row in set (0.00 sec)
#修改隔离级别为重复读
set session transaction_isolation='repeatable-read';
set global transaction_isolation='repeatable-read'; -- 新的设置方法
-- set global tx_isolation='repeatable-read'; -- 旧的设置方法
mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ | REPEATABLE-READ |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000004 | 154 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
模拟DDL 与DML 操作
use testdb
create table test_t1 (
`id` int(11) primary key not null auto_increment,
`rowformat` varchar(50) not null,
`uuids` varchar(50) not null,
`timepoint` datetime not null default current_timestamp,
CurrentVersion timestamp not null default current_timestamp on update
current_timestamp
) engine=innodb;
insert into test_t1(rowformat,uuids) select 'test',uuid();
insert into test_t1(rowformat,uuids) select 'test',uuid();
mysql> select * from test_t1;
+----+-----------+--------------------------------------+---------------------+---------------------+
| id | rowformat | uuids | timepoint | CurrentVersion |
+----+-----------+--------------------------------------+---------------------+---------------------+
| 1 | test | d818bfdd-39d6-11ee-8bb8-000c29151c55 | 2023-08-13 20:42:20 | 2023-08-13 20:42:20 |
| 2 | test | d8c09cf1-39d6-11ee-8bb8-000c29151c55 | 2023-08-13 20:42:21 | 2023-08-13 20:42:21 |
+----+-----------+--------------------------------------+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000004 | 1258 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
接下来看154~1258之间的操作记录，用mysqlbinlog 读取。
[root@centos7 binlog]# mysqlbinlog --start-position=154 --stop-position=1258 testdb-binlog.000004 > 1.txt
[root@centos7 binlog]# cat -n 1.txt
1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
3 DELIMITER /*!*/;
4 # at 4
5 #230813 20:39:53 server id 3306 end_log_pos 123 CRC32 0x38b62d6f Start: binlog v 4, server v 5.7.43-log created 230813 20:39:53
6 # Warning: this binlog is either in use or was not closed properly.
7 BINLOG '
8 Gc/YZA/qDAAAdwAAAHsAAAABAAQANS43LjQzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
9 AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
10 AW8ttjg=
11 '/*!*/;
12 # at 154
13 #230813 20:42:14 server id 3306 end_log_pos 219 CRC32 0x3b927b87 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
14 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
15 # at 219
16 #230813 20:42:14 server id 3306 end_log_pos 596 CRC32 0x48ff9e90 Query thread_id=3 exec_time=0 error_code=0
17 use `testdb`/*!*/;
18 SET TIMESTAMP=1691930534/*!*/;
19 SET @@session.pseudo_thread_id=3/*!*/;
20 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
21 SET @@session.sql_mode=1436549120/*!*/;
22 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
23 /*!\C utf8 *//*!*/;
24 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
25 SET @@session.lc_time_names=0/*!*/;
26 SET @@session.collation_database=DEFAULT/*!*/;
27 SET @@session.explicit_defaults_for_timestamp=1/*!*/;
28 create table test_t1 (
29 `id` int(11) primary key not null auto_increment,
30 `rowformat` varchar(50) not null,
31 `uuids` varchar(50) not null,
32 `timepoint` datetime not null default current_timestamp,
33 CurrentVersion timestamp not null default current_timestamp on update
34 current_timestamp
35 ) engine=innodb
36 /*!*/;
37 # at 596
38 #230813 20:42:20 server id 3306 end_log_pos 661 CRC32 0x1c7489f5 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
39 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
40 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
41 # at 661
42 #230813 20:42:20 server id 3306 end_log_pos 743 CRC32 0x1cce2d0c Query thread_id=3 exec_time=0 error_code=0
43 SET TIMESTAMP=1691930540/*!*/;
44 SET @@session.time_zone='SYSTEM'/*!*/;
45 BEGIN
46 /*!*/;
47 # at 743
48 #230813 20:42:20 server id 3306 end_log_pos 805 CRC32 0x140aa72f Table_map: `testdb`.`test_t1` mapped to number 111
49 # at 805
50 #230813 20:42:20 server id 3306 end_log_pos 896 CRC32 0xdbeb9eaa Write_rows: table id 111 flags: STMT_END_F
51
52 BINLOG '
53 rM/YZBPqDAAAPgAAACUDAAAAAG8AAAAAAAEABnRlc3RkYgAHdGVzdF90MQAFAw8PEhEGlgCWAAAA
54 AC+nChQ=
55 rM/YZB7qDAAAWwAAAIADAAAAAG8AAAAAAAEAAgAF/+ABAAAABHRlc3QkZDgxOGJmZGQtMzlkNi0x
56 MWVlLThiYjgtMDAwYzI5MTUxYzU1mbDbSpRk2M+sqp7r2w==
57 '/*!*/;
58 # at 896
59 #230813 20:42:20 server id 3306 end_log_pos 927 CRC32 0x34570eae Xid = 85
60 COMMIT/*!*/;
61 # at 927
62 #230813 20:42:21 server id 3306 end_log_pos 992 CRC32 0xfc5364e0 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes
63 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
64 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
65 # at 992
66 #230813 20:42:21 server id 3306 end_log_pos 1074 CRC32 0x074ad55a Query thread_id=3 exec_time=0 error_code=0
67 SET TIMESTAMP=1691930541/*!*/;
68 BEGIN
69 /*!*/;
70 # at 1074
71 #230813 20:42:21 server id 3306 end_log_pos 1136 CRC32 0x16b6ac5b Table_map: `testdb`.`test_t1` mapped to number 111
72 # at 1136
73 #230813 20:42:21 server id 3306 end_log_pos 1227 CRC32 0x73f114f0 Write_rows: table id 111 flags: STMT_END_F
74
75 BINLOG '
76 rc/YZBPqDAAAPgAAAHAEAAAAAG8AAAAAAAEABnRlc3RkYgAHdGVzdF90MQAFAw8PEhEGlgCWAAAA
77 AFusthY=
78 rc/YZB7qDAAAWwAAAMsEAAAAAG8AAAAAAAEAAgAF/+ACAAAABHRlc3QkZDhjMDljZjEtMzlkNi0x
79 MWVlLThiYjgtMDAwYzI5MTUxYzU1mbDbSpVk2M+t8BTxcw==
80 '/*!*/;
81 # at 1227
82 #230813 20:42:21 server id 3306 end_log_pos 1258 CRC32 0xc7ebc97c Xid = 86
83 COMMIT/*!*/;
84 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
85 DELIMITER ;
86 # End of log file
87 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
88 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos7 binlog]#
#如上17行、28行，28行可以看出创建表的语句是明文的
#如上50行、73行 Write_rows表示插入数据，插入的数据是密文
update操作
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000004 | 1258 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
mysql> update test_t1 set rowformat='1111111' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> delete from test_t1 where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> drop table test_t1;
Query OK, 0 rows affected (0.02 sec)
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000004 | 2156 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@centos7 binlog]# mysqlbinlog --start-position=1258 --stop-position=2156 testdb-binlog.000004 > 1.txt
[root@centos7 binlog]# cat -n 1.txt
1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
3 DELIMITER /*!*/;
4 # at 4
5 #230813 20:39:53 server id 3306 end_log_pos 123 CRC32 0x38b62d6f Start: binlog v 4, server v 5.7.43-log created 230813 20:39:53
6 # Warning: this binlog is either in use or was not closed properly.
7 BINLOG '
8 Gc/YZA/qDAAAdwAAAHsAAAABAAQANS43LjQzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
9 AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
10 AW8ttjg=
11 '/*!*/;
12 # at 1258
13 #230813 20:50:04 server id 3306 end_log_pos 1323 CRC32 0xf49fe531 Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=yes
14 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
15 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
16 # at 1323
17 #230813 20:50:04 server id 3306 end_log_pos 1397 CRC32 0x33cbd9f4 Query thread_id=3 exec_time=0 error_code=0
18 SET TIMESTAMP=1691931004/*!*/;
19 SET @@session.pseudo_thread_id=3/*!*/;
20 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
21 SET @@session.sql_mode=1436549120/*!*/;
22 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
23 /*!\C utf8 *//*!*/;
24 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
25 SET @@session.lc_time_names=0/*!*/;
26 SET @@session.collation_database=DEFAULT/*!*/;
27 BEGIN
28 /*!*/;
29 # at 1397
30 #230813 20:50:04 server id 3306 end_log_pos 1459 CRC32 0x01e91b52 Table_map: `testdb`.`test_t1` mapped to number 111
31 # at 1459
32 #230813 20:50:04 server id 3306 end_log_pos 1610 CRC32 0x6d382dcc Update_rows: table id 111 flags: STMT_END_F
33
34 BINLOG '
35 fNHYZBPqDAAAPgAAALMFAAAAAG8AAAAAAAEABnRlc3RkYgAHdGVzdF90MQAFAw8PEhEGlgCWAAAA
36 AFIb6QE=
37 fNHYZB/qDAAAlwAAAEoGAAAAAG8AAAAAAAEAAgAF///gAQAAAAR0ZXN0JGQ4MThiZmRkLTM5ZDYt
38 MTFlZS04YmI4LTAwMGMyOTE1MWM1NZmw20qUZNjPrOABAAAABzExMTExMTEkZDgxOGJmZGQtMzlk
39 Ni0xMWVlLThiYjgtMDAwYzI5MTUxYzU1mbDbSpRk2NF8zC04bQ==
40 '/*!*/;
41 # at 1610
42 #230813 20:50:04 server id 3306 end_log_pos 1641 CRC32 0xc3c2435e Xid = 90
43 COMMIT/*!*/;
44 # at 1641
45 #230813 20:50:11 server id 3306 end_log_pos 1706 CRC32 0x41285694 Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=yes
46 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
47 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
48 # at 1706
49 #230813 20:50:11 server id 3306 end_log_pos 1780 CRC32 0x23a20b10 Query thread_id=3 exec_time=0 error_code=0
50 SET TIMESTAMP=1691931011/*!*/;
51 BEGIN
52 /*!*/;
53 # at 1780
54 #230813 20:50:11 server id 3306 end_log_pos 1842 CRC32 0x6430e979 Table_map: `testdb`.`test_t1` mapped to number 111
55 # at 1842
56 #230813 20:50:11 server id 3306 end_log_pos 1936 CRC32 0x6dbf6048 Delete_rows: table id 111 flags: STMT_END_F
57
58 BINLOG '
59 g9HYZBPqDAAAPgAAADIHAAAAAG8AAAAAAAEABnRlc3RkYgAHdGVzdF90MQAFAw8PEhEGlgCWAAAA
60 AHnpMGQ=
61 g9HYZCDqDAAAXgAAAJAHAAAAAG8AAAAAAAEAAgAF/+ABAAAABzExMTExMTEkZDgxOGJmZGQtMzlk
62 Ni0xMWVlLThiYjgtMDAwYzI5MTUxYzU1mbDbSpRk2NF8SGC/bQ==
63 '/*!*/;
64 # at 1936
65 #230813 20:50:11 server id 3306 end_log_pos 1967 CRC32 0x26a95f6d Xid = 91
66 COMMIT/*!*/;
67 # at 1967
68 #230813 20:50:16 server id 3306 end_log_pos 2032 CRC32 0x3bb38644 Anonymous_GTID last_committed=5 sequence_number=6 rbr_only=no
69 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
70 # at 2032
71 #230813 20:50:16 server id 3306 end_log_pos 2156 CRC32 0xb299b69f Query thread_id=3 exec_time=0 error_code=0
72 use `testdb`/*!*/;
73 SET TIMESTAMP=1691931016/*!*/;
74 DROP TABLE `test_t1` /* generated by server */
75 /*!*/;
76 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
77 DELIMITER ;
78 # End of log file
79 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
80 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos7 binlog]#
#如上32行，update也是加密的
#56行，delete也是加密的
#74行，drop是明文的
针对加密的操作反解析加参数-v
mysqlbinlog --base64-output=decode-rows -v --start-position=1258 --stop-position=2156 testdb-binlog.000004 > 1.txt
[root@centos7 binlog]# mysqlbinlog --base64-output=decode-rows -v --start-position=1258 --stop-position=2156 testdb-binlog.000004 > 1.txt
[root@centos7 binlog]#
[root@centos7 binlog]# cat -n 1.txt
1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
3 DELIMITER /*!*/;
4 # at 1258
5 #230813 20:50:04 server id 3306 end_log_pos 1323 CRC32 0xf49fe531 Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=yes
6 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
7 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
8 # at 1323
9 #230813 20:50:04 server id 3306 end_log_pos 1397 CRC32 0x33cbd9f4 Query thread_id=3 exec_time=0 error_code=0
10 SET TIMESTAMP=1691931004/*!*/;
11 SET @@session.pseudo_thread_id=3/*!*/;
12 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
13 SET @@session.sql_mode=1436549120/*!*/;
14 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
15 /*!\C utf8 *//*!*/;
16 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
17 SET @@session.lc_time_names=0/*!*/;
18 SET @@session.collation_database=DEFAULT/*!*/;
19 BEGIN
20 /*!*/;
21 # at 1397
22 #230813 20:50:04 server id 3306 end_log_pos 1459 CRC32 0x01e91b52 Table_map: `testdb`.`test_t1` mapped to number 111
23 # at 1459
24 #230813 20:50:04 server id 3306 end_log_pos 1610 CRC32 0x6d382dcc Update_rows: table id 111 flags: STMT_END_F
25 ### UPDATE `testdb`.`test_t1`
26 ### WHERE
27 ### @1=1
28 ### @2='test'
29 ### @3='d818bfdd-39d6-11ee-8bb8-000c29151c55'
30 ### @4='2023-08-13 20:42:20'
31 ### @5=1691930540
32 ### SET
33 ### @1=1
34 ### @2='1111111'
35 ### @3='d818bfdd-39d6-11ee-8bb8-000c29151c55'
36 ### @4='2023-08-13 20:42:20'
37 ### @5=1691931004
38 # at 1610
39 #230813 20:50:04 server id 3306 end_log_pos 1641 CRC32 0xc3c2435e Xid = 90
40 COMMIT/*!*/;
41 # at 1641
42 #230813 20:50:11 server id 3306 end_log_pos 1706 CRC32 0x41285694 Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=yes
43 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
44 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
45 # at 1706
46 #230813 20:50:11 server id 3306 end_log_pos 1780 CRC32 0x23a20b10 Query thread_id=3 exec_time=0 error_code=0
47 SET TIMESTAMP=1691931011/*!*/;
48 BEGIN
49 /*!*/;
50 # at 1780
51 #230813 20:50:11 server id 3306 end_log_pos 1842 CRC32 0x6430e979 Table_map: `testdb`.`test_t1` mapped to number 111
52 # at 1842
53 #230813 20:50:11 server id 3306 end_log_pos 1936 CRC32 0x6dbf6048 Delete_rows: table id 111 flags: STMT_END_F
54 ### DELETE FROM `testdb`.`test_t1`
55 ### WHERE
56 ### @1=1
57 ### @2='1111111'
58 ### @3='d818bfdd-39d6-11ee-8bb8-000c29151c55'
59 ### @4='2023-08-13 20:42:20'
60 ### @5=1691931004
61 # at 1936
62 #230813 20:50:11 server id 3306 end_log_pos 1967 CRC32 0x26a95f6d Xid = 91
63 COMMIT/*!*/;
64 # at 1967
65 #230813 20:50:16 server id 3306 end_log_pos 2032 CRC32 0x3bb38644 Anonymous_GTID last_committed=5 sequence_number=6 rbr_only=no
66 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
67 # at 2032
68 #230813 20:50:16 server id 3306 end_log_pos 2156 CRC32 0xb299b69f Query thread_id=3 exec_time=0 error_code=0
69 use `testdb`/*!*/;
70 SET TIMESTAMP=1691931016/*!*/;
71 DROP TABLE `test_t1` /* generated by server */
72 /*!*/;
73 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
74 DELIMITER ;
75 # End of log file
76 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
77 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos7 binlog]#
#如上25~31行是更新前的值
#33行-37行是更新后的值
#54~60行，是delete的记录
总结：
binlog_format=‘row’，其它参数默认，ddl操作是明文的，而dml操作是加密的吗，但是只记录行的操作，不记录SQL语句。
mysql> show binlog events in 'testdb-binlog.000004';
对主从复制的影响：
-
同步最安全
-
不管是更新还是删除，或者批量录数据，都是按行进行，依次处理所有行的记录，而不是整条SQL
1.5.2.2 binlog_rows_query_log_events=on
#修改行格式
set session binlog_rows_query_log_events=1;
set global binlog_rows_query_log_events=1;
select @@global.binlog_rows_query_log_events,@@binlog_rows_query_log_events;
mysql> select @@global.binlog_rows_query_log_events,@@binlog_rows_query_log_events;
+---------------------------------------+--------------------------------+
| @@global.binlog_rows_query_log_events | @@binlog_rows_query_log_events |
+---------------------------------------+--------------------------------+
| 1 | 1 |
+---------------------------------------+--------------------------------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000005 | 154 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
模拟DDL 与DML 操作
create table test_t (
`id` int(11) primary key not null auto_increment,
`rowformat` varchar(50) not null,
`uuids` varchar(50) not null,
`timepoint` datetime not null default current_timestamp,
CurrentVersion timestamp not null default current_timestamp on update
current_timestamp
) engine=innodb;
insert into test_t(rowformat,uuids) select 'test',uuid();
insert into test_t(rowformat,uuids) select 'test',uuid();
select * from test_t;
update test_t set rowformat='test_update' where id=1;
delete from test_t where id=1;
drop table test_t;
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000005 | 2447 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
查看154~2447
mysqlbinlog --base64-output=decode-rows -v -v --start-position=154 --stop-position=2447 testdb-binlog.000005
[root@centos7 binlog]# mysqlbinlog --base64-output=decode-rows -v -v --start-position=154 --stop-position=2447 testdb-binlog.000005 > 1.txt
[root@centos7 binlog]# cat -n 1.txt
1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
3 DELIMITER /*!*/;
4 # at 154
5 #230813 21:06:41 server id 3306 end_log_pos 219 CRC32 0x055c5b56 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
6 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
7 # at 219
8 #230813 21:06:41 server id 3306 end_log_pos 595 CRC32 0xbe79b944 Query thread_id=3 exec_time=0 error_code=0
9 use `testdb`/*!*/;
10 SET TIMESTAMP=1691932001/*!*/;
11 SET @@session.pseudo_thread_id=3/*!*/;
12 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
13 SET @@session.sql_mode=1436549120/*!*/;
14 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
15 /*!\C utf8 *//*!*/;
16 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
17 SET @@session.lc_time_names=0/*!*/;
18 SET @@session.collation_database=DEFAULT/*!*/;
19 SET @@session.explicit_defaults_for_timestamp=1/*!*/;
20 create table test_t (
21 `id` int(11) primary key not null auto_increment,
22 `rowformat` varchar(50) not null,
23 `uuids` varchar(50) not null,
24 `timepoint` datetime not null default current_timestamp,
25 CurrentVersion timestamp not null default current_timestamp on update
26 current_timestamp
27 ) engine=innodb
28 /*!*/;
29 # at 595
30 #230813 21:06:46 server id 3306 end_log_pos 660 CRC32 0x160a5f00 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
31 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
32 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
33 # at 660
34 #230813 21:06:46 server id 3306 end_log_pos 742 CRC32 0xc875902d Query thread_id=3 exec_time=0 error_code=0
35 SET TIMESTAMP=1691932006/*!*/;
36 SET @@session.time_zone='SYSTEM'/*!*/;
37 BEGIN
38 /*!*/;
39 # at 742
40 #230813 21:06:46 server id 3306 end_log_pos 822 CRC32 0x4824b43a Rows_query
41 # insert into test_t(rowformat,uuids) select 'test',uuid()
42 # at 822
43 #230813 21:06:46 server id 3306 end_log_pos 883 CRC32 0x8d5bc9a4 Table_map: `testdb`.`test_t` mapped to number 112
44 # at 883
45 #230813 21:06:46 server id 3306 end_log_pos 974 CRC32 0xfc87e974 Write_rows: table id 112 flags: STMT_END_F
46 ### INSERT INTO `testdb`.`test_t`
47 ### SET
48 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
49 ### @2='test' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
50 ### @3='41cc8806-39da-11ee-8bb8-000c29151c55' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
51 ### @4='2023-08-13 21:06:46' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
52 ### @5=1691932006 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
53 # at 974
54 #230813 21:06:46 server id 3306 end_log_pos 1005 CRC32 0xfd2a0b05 Xid = 102
55 COMMIT/*!*/;
56 # at 1005
57 #230813 21:06:47 server id 3306 end_log_pos 1070 CRC32 0x005e6701 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes
58 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
59 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
60 # at 1070
61 #230813 21:06:47 server id 3306 end_log_pos 1152 CRC32 0x8734898d Query thread_id=3 exec_time=0 error_code=0
62 SET TIMESTAMP=1691932007/*!*/;
63 BEGIN
64 /*!*/;
65 # at 1152
66 #230813 21:06:47 server id 3306 end_log_pos 1232 CRC32 0x2a55a475 Rows_query
67 # insert into test_t(rowformat,uuids) select 'test',uuid()
68 # at 1232
69 #230813 21:06:47 server id 3306 end_log_pos 1293 CRC32 0xaac8d3ec Table_map: `testdb`.`test_t` mapped to number 112
70 # at 1293
71 #230813 21:06:47 server id 3306 end_log_pos 1384 CRC32 0xe8430487 Write_rows: table id 112 flags: STMT_END_F
72 ### INSERT INTO `testdb`.`test_t`
73 ### SET
74 ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
75 ### @2='test' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
76 ### @3='42462284-39da-11ee-8bb8-000c29151c55' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
77 ### @4='2023-08-13 21:06:47' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
78 ### @5=1691932007 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
79 # at 1384
80 #230813 21:06:47 server id 3306 end_log_pos 1415 CRC32 0xa1039ab5 Xid = 103
81 COMMIT/*!*/;
82 # at 1415
83 #230813 21:06:56 server id 3306 end_log_pos 1480 CRC32 0x35523673 Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=yes
84 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
85 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
86 # at 1480
87 #230813 21:06:56 server id 3306 end_log_pos 1554 CRC32 0x9b25c7f2 Query thread_id=3 exec_time=0 error_code=0
88 SET TIMESTAMP=1691932016/*!*/;
89 BEGIN
90 /*!*/;
91 # at 1554
92 #230813 21:06:56 server id 3306 end_log_pos 1630 CRC32 0xea07258b Rows_query
93 # update test_t set rowformat='test_update' where id=1
94 # at 1630
95 #230813 21:06:56 server id 3306 end_log_pos 1691 CRC32 0x84a5b22e Table_map: `testdb`.`test_t` mapped to number 112
96 # at 1691
97 #230813 21:06:56 server id 3306 end_log_pos 1846 CRC32 0x8405f560 Update_rows: table id 112 flags: STMT_END_F
98 ### UPDATE `testdb`.`test_t`
99 ### WHERE
100 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
101 ### @2='test' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
102 ### @3='41cc8806-39da-11ee-8bb8-000c29151c55' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
103 ### @4='2023-08-13 21:06:46' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
104 ### @5=1691932006 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
105 ### SET
106 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
107 ### @2='test_update' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
108 ### @3='41cc8806-39da-11ee-8bb8-000c29151c55' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
109 ### @4='2023-08-13 21:06:46' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
110 ### @5=1691932016 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
111 # at 1846
112 #230813 21:06:56 server id 3306 end_log_pos 1877 CRC32 0x1d6230cd Xid = 105
113 COMMIT/*!*/;
114 # at 1877
115 #230813 21:07:03 server id 3306 end_log_pos 1942 CRC32 0xc6c4f5cc Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=yes
116 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
117 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
118 # at 1942
119 #230813 21:07:03 server id 3306 end_log_pos 2016 CRC32 0x6fb13330 Query thread_id=3 exec_time=0 error_code=0
120 SET TIMESTAMP=1691932023/*!*/;
121 BEGIN
122 /*!*/;
123 # at 2016
124 #230813 21:07:03 server id 3306 end_log_pos 2069 CRC32 0x0158585c Rows_query
125 # delete from test_t where id=1
126 # at 2069
127 #230813 21:07:03 server id 3306 end_log_pos 2130 CRC32 0x376dba32 Table_map: `testdb`.`test_t` mapped to number 112
128 # at 2130
129 #230813 21:07:03 server id 3306 end_log_pos 2228 CRC32 0xcf02a748 Delete_rows: table id 112 flags: STMT_END_F
130 ### DELETE FROM `testdb`.`test_t`
131 ### WHERE
132 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
133 ### @2='test_update' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
134 ### @3='41cc8806-39da-11ee-8bb8-000c29151c55' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
135 ### @4='2023-08-13 21:06:46' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
136 ### @5=1691932016 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
137 # at 2228
138 #230813 21:07:03 server id 3306 end_log_pos 2259 CRC32 0x1df900c9 Xid = 106
139 COMMIT/*!*/;
140 # at 2259
141 #230813 21:07:07 server id 3306 end_log_pos 2324 CRC32 0xc0fc0508 Anonymous_GTID last_committed=5 sequence_number=6 rbr_only=no
142 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
143 # at 2324
144 #230813 21:07:07 server id 3306 end_log_pos 2447 CRC32 0xb84eedef Query thread_id=3 exec_time=0 error_code=0
145 SET TIMESTAMP=1691932027/*!*/;
146 DROP TABLE `test_t` /* generated by server */
147 /*!*/;
148 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
149 DELIMITER ;
150 # End of log file
151 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
152 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos7 binlog]#
#如上46~52行 可以看出insert更详细的信息
这就是binlog_rows_query_log_events=on开启下能看到更详细的信息。
mysql> show binlog events in 'testdb-binlog.000005';
总结：
binlog_rows_query_log_events=on，这个参数为on 的时候，不但可以记录行的操作，而且记录这条SQL 语句。
1.5.2.3 binlog_row_image=full，minimal，noblob
1. binlog_row_image=full
set session binlog_row_image=full;
set global binlog_row_image=full;
select @@global.binlog_row_image,@@binlog_row_image;
mysql> select @@global.binlog_row_image,@@binlog_row_image;
+---------------------------+--------------------+
| @@global.binlog_row_image | @@binlog_row_image |
+---------------------------+--------------------+
| FULL | FULL |
+---------------------------+--------------------+
1 row in set (0.00 sec)
[root@centos7 binlog]# mysqlbinlog --base64-output=decode-rows -v -v --start-position=154 --stop-position=2447 testdb-binlog.000005 > 1.txt
[root@centos7 binlog]# cat -n 1.txt
1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
3 DELIMITER /*!*/;
4 # at 154
5 #230813 21:06:41 server id 3306 end_log_pos 219 CRC32 0x055c5b56 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
6 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
7 # at 219
8 #230813 21:06:41 server id 3306 end_log_pos 595 CRC32 0xbe79b944 Query thread_id=3 exec_time=0 error_code=0
9 use `testdb`/*!*/;
10 SET TIMESTAMP=1691932001/*!*/;
11 SET @@session.pseudo_thread_id=3/*!*/;
12 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
13 SET @@session.sql_mode=1436549120/*!*/;
14 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
15 /*!\C utf8 *//*!*/;
16 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
17 SET @@session.lc_time_names=0/*!*/;
18 SET @@session.collation_database=DEFAULT/*!*/;
19 SET @@session.explicit_defaults_for_timestamp=1/*!*/;
20 create table test_t (
21 `id` int(11) primary key not null auto_increment,
22 `rowformat` varchar(50) not null,
23 `uuids` varchar(50) not null,
24 `timepoint` datetime not null default current_timestamp,
25 CurrentVersion timestamp not null default current_timestamp on update
26 current_timestamp
27 ) engine=innodb
28 /*!*/;
29 # at 595
30 #230813 21:06:46 server id 3306 end_log_pos 660 CRC32 0x160a5f00 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
31 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
32 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
33 # at 660
34 #230813 21:06:46 server id 3306 end_log_pos 742 CRC32 0xc875902d Query thread_id=3 exec_time=0 error_code=0
35 SET TIMESTAMP=1691932006/*!*/;
36 SET @@session.time_zone='SYSTEM'/*!*/;
37 BEGIN
38 /*!*/;
39 # at 742
40 #230813 21:06:46 server id 3306 end_log_pos 822 CRC32 0x4824b43a Rows_query
41 # insert into test_t(rowformat,uuids) select 'test',uuid()
42 # at 822
43 #230813 21:06:46 server id 3306 end_log_pos 883 CRC32 0x8d5bc9a4 Table_map: `testdb`.`test_t` mapped to number 112
44 # at 883
45 #230813 21:06:46 server id 3306 end_log_pos 974 CRC32 0xfc87e974 Write_rows: table id 112 flags: STMT_END_F
46 ### INSERT INTO `testdb`.`test_t`
47 ### SET
48 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
49 ### @2='test' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
50 ### @3='41cc8806-39da-11ee-8bb8-000c29151c55' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
51 ### @4='2023-08-13 21:06:46' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
52 ### @5=1691932006 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
53 # at 974
54 #230813 21:06:46 server id 3306 end_log_pos 1005 CRC32 0xfd2a0b05 Xid = 102
55 COMMIT/*!*/;
56 # at 1005
57 #230813 21:06:47 server id 3306 end_log_pos 1070 CRC32 0x005e6701 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes
58 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
59 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
60 # at 1070
61 #230813 21:06:47 server id 3306 end_log_pos 1152 CRC32 0x8734898d Query thread_id=3 exec_time=0 error_code=0
62 SET TIMESTAMP=1691932007/*!*/;
63 BEGIN
64 /*!*/;
65 # at 1152
66 #230813 21:06:47 server id 3306 end_log_pos 1232 CRC32 0x2a55a475 Rows_query
67 # insert into test_t(rowformat,uuids) select 'test',uuid()
68 # at 1232
69 #230813 21:06:47 server id 3306 end_log_pos 1293 CRC32 0xaac8d3ec Table_map: `testdb`.`test_t` mapped to number 112
70 # at 1293
71 #230813 21:06:47 server id 3306 end_log_pos 1384 CRC32 0xe8430487 Write_rows: table id 112 flags: STMT_END_F
72 ### INSERT INTO `testdb`.`test_t`
73 ### SET
74 ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
75 ### @2='test' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
76 ### @3='42462284-39da-11ee-8bb8-000c29151c55' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
77 ### @4='2023-08-13 21:06:47' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
78 ### @5=1691932007 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
79 # at 1384
80 #230813 21:06:47 server id 3306 end_log_pos 1415 CRC32 0xa1039ab5 Xid = 103
81 COMMIT/*!*/;
82 # at 1415
83 #230813 21:06:56 server id 3306 end_log_pos 1480 CRC32 0x35523673 Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=yes
84 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
85 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
86 # at 1480
87 #230813 21:06:56 server id 3306 end_log_pos 1554 CRC32 0x9b25c7f2 Query thread_id=3 exec_time=0 error_code=0
88 SET TIMESTAMP=1691932016/*!*/;
89 BEGIN
90 /*!*/;
91 # at 1554
92 #230813 21:06:56 server id 3306 end_log_pos 1630 CRC32 0xea07258b Rows_query
93 # update test_t set rowformat='test_update' where id=1
94 # at 1630
95 #230813 21:06:56 server id 3306 end_log_pos 1691 CRC32 0x84a5b22e Table_map: `testdb`.`test_t` mapped to number 112
96 # at 1691
97 #230813 21:06:56 server id 3306 end_log_pos 1846 CRC32 0x8405f560 Update_rows: table id 112 flags: STMT_END_F
98 ### UPDATE `testdb`.`test_t`
99 ### WHERE
100 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
101 ### @2='test' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
102 ### @3='41cc8806-39da-11ee-8bb8-000c29151c55' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
103 ### @4='2023-08-13 21:06:46' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
104 ### @5=1691932006 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
105 ### SET
106 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
107 ### @2='test_update' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
108 ### @3='41cc8806-39da-11ee-8bb8-000c29151c55' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
109 ### @4='2023-08-13 21:06:46' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
110 ### @5=1691932016 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
111 # at 1846
112 #230813 21:06:56 server id 3306 end_log_pos 1877 CRC32 0x1d6230cd Xid = 105
113 COMMIT/*!*/;
114 # at 1877
115 #230813 21:07:03 server id 3306 end_log_pos 1942 CRC32 0xc6c4f5cc Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=yes
116 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
117 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
118 # at 1942
119 #230813 21:07:03 server id 3306 end_log_pos 2016 CRC32 0x6fb13330 Query thread_id=3 exec_time=0 error_code=0
120 SET TIMESTAMP=1691932023/*!*/;
121 BEGIN
122 /*!*/;
123 # at 2016
124 #230813 21:07:03 server id 3306 end_log_pos 2069 CRC32 0x0158585c Rows_query
125 # delete from test_t where id=1
126 # at 2069
127 #230813 21:07:03 server id 3306 end_log_pos 2130 CRC32 0x376dba32 Table_map: `testdb`.`test_t` mapped to number 112
128 # at 2130
129 #230813 21:07:03 server id 3306 end_log_pos 2228 CRC32 0xcf02a748 Delete_rows: table id 112 flags: STMT_END_F
130 ### DELETE FROM `testdb`.`test_t`
131 ### WHERE
132 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
133 ### @2='test_update' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
134 ### @3='41cc8806-39da-11ee-8bb8-000c29151c55' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
135 ### @4='2023-08-13 21:06:46' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
136 ### @5=1691932016 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
137 # at 2228
138 #230813 21:07:03 server id 3306 end_log_pos 2259 CRC32 0x1df900c9 Xid = 106
139 COMMIT/*!*/;
140 # at 2259
141 #230813 21:07:07 server id 3306 end_log_pos 2324 CRC32 0xc0fc0508 Anonymous_GTID last_committed=5 sequence_number=6 rbr_only=no
142 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
143 # at 2324
144 #230813 21:07:07 server id 3306 end_log_pos 2447 CRC32 0xb84eedef Query thread_id=3 exec_time=0 error_code=0
145 SET TIMESTAMP=1691932027/*!*/;
146 DROP TABLE `test_t` /* generated by server */
147 /*!*/;
148 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
149 DELIMITER ;
150 # End of log file
151 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
152 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos7 binlog]#
如上46行~52行
binlog_row_image=full会详细记录值的内容（insert记录新值的详细内容，update记录旧值和新值的详细内容，delete）
2. binlog_row_image=minimal
set session binlog_row_image=minimal;
set global binlog_row_image=minimal;
select @@global.binlog_row_image,@@binlog_row_image;
mysql> select @@global.binlog_row_image,@@binlog_row_image;
+---------------------------+--------------------+
| @@global.binlog_row_image | @@binlog_row_image |
+---------------------------+--------------------+
| MINIMAL | MINIMAL |
+---------------------------+--------------------+
1 row in set (0.00 sec)
flush logs;
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000006 | 154 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
测试
create table test_t (
`id` int(11) primary key not null auto_increment,
`rowformat` varchar(50) not null,
`uuids` varchar(50) not null,
`timepoint` datetime not null default current_timestamp,
CurrentVersion timestamp not null default current_timestamp on update
current_timestamp
) engine=innodb;
insert into test_t(rowformat,uuids) select 'test',uuid();
select * from test_t;
update test_t set rowformat='test_update' where id=1;
delete from test_t where id=1;
show master status;
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000006 | 1694 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
查看日志信息
mysqlbinlog --base64-output=decode-rows -v -v --start-position=154 --stop-position=1694 testdb-binlog.000006
[root@centos7 binlog]# mysqlbinlog --base64-output=decode-rows -v -v --start-position=154 --stop-position=1694 testdb-binlog.000006 > 1.txt
[root@centos7 binlog]# cat -n 1.txt
1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
3 DELIMITER /*!*/;
4 # at 154
5 #230813 21:28:25 server id 3306 end_log_pos 219 CRC32 0x15443c8b Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
6 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
7 # at 219
8 #230813 21:28:25 server id 3306 end_log_pos 595 CRC32 0xa4d48a1f Query thread_id=3 exec_time=0 error_code=0
9 use `testdb`/*!*/;
10 SET TIMESTAMP=1691933305/*!*/;
11 SET @@session.pseudo_thread_id=3/*!*/;
12 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
13 SET @@session.sql_mode=1436549120/*!*/;
14 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
15 /*!\C utf8 *//*!*/;
16 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
17 SET @@session.lc_time_names=0/*!*/;
18 SET @@session.collation_database=DEFAULT/*!*/;
19 SET @@session.explicit_defaults_for_timestamp=1/*!*/;
20 create table test_t (
21 `id` int(11) primary key not null auto_increment,
22 `rowformat` varchar(50) not null,
23 `uuids` varchar(50) not null,
24 `timepoint` datetime not null default current_timestamp,
25 CurrentVersion timestamp not null default current_timestamp on update
26 current_timestamp
27 ) engine=innodb
28 /*!*/;
29 # at 595
30 #230813 21:28:30 server id 3306 end_log_pos 660 CRC32 0x061238dd Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
31 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
32 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
33 # at 660
34 #230813 21:28:30 server id 3306 end_log_pos 742 CRC32 0x03b11e08 Query thread_id=3 exec_time=0 error_code=0
35 SET TIMESTAMP=1691933310/*!*/;
36 SET @@session.time_zone='SYSTEM'/*!*/;
37 BEGIN
38 /*!*/;
39 # at 742
40 #230813 21:28:30 server id 3306 end_log_pos 822 CRC32 0x7f386f1d Rows_query
41 # insert into test_t(rowformat,uuids) select 'test',uuid()
42 # at 822
43 #230813 21:28:30 server id 3306 end_log_pos 883 CRC32 0x281a62fa Table_map: `testdb`.`test_t` mapped to number 113
44 # at 883
45 #230813 21:28:30 server id 3306 end_log_pos 974 CRC32 0x85d713b3 Write_rows: table id 113 flags: STMT_END_F
46 ### INSERT INTO `testdb`.`test_t`
47 ### SET
48 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
49 ### @2='test' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
50 ### @3='4ac94ef1-39dd-11ee-8bb8-000c29151c55' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
51 ### @4='2023-08-13 21:28:30' /* DATETIME(0) meta=0 nullable=0 is_null=0 */
52 ### @5=1691933310 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
53 # at 974
54 #230813 21:28:30 server id 3306 end_log_pos 1005 CRC32 0x85070359 Xid = 120
55 COMMIT/*!*/;
56 # at 1005
57 #230813 21:28:40 server id 3306 end_log_pos 1070 CRC32 0x9314d909 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes
58 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
59 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
60 # at 1070
61 #230813 21:28:40 server id 3306 end_log_pos 1144 CRC32 0xabef0383 Query thread_id=3 exec_time=0 error_code=0
62 SET TIMESTAMP=1691933320/*!*/;
63 BEGIN
64 /*!*/;
65 # at 1144
66 #230813 21:28:40 server id 3306 end_log_pos 1220 CRC32 0x67bb9e1d Rows_query
67 # update test_t set rowformat='test_update' where id=1
68 # at 1220
69 #230813 21:28:40 server id 3306 end_log_pos 1281 CRC32 0xeb5a8fda Table_map: `testdb`.`test_t` mapped to number 113
70 # at 1281
71 #230813 21:28:40 server id 3306 end_log_pos 1339 CRC32 0x69c1b046 Update_rows: table id 113 flags: STMT_END_F
72 ### UPDATE `testdb`.`test_t`
73 ### WHERE
74 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
75 ### SET
76 ### @2='test_update' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
77 ### @5=1691933320 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
78 # at 1339
79 #230813 21:28:40 server id 3306 end_log_pos 1370 CRC32 0x48fe8129 Xid = 122
80 COMMIT/*!*/;
81 # at 1370
82 #230813 21:28:46 server id 3306 end_log_pos 1435 CRC32 0x2d69f8df Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=yes
83 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
84 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
85 # at 1435
86 #230813 21:28:46 server id 3306 end_log_pos 1509 CRC32 0xb7a6a571 Query thread_id=3 exec_time=0 error_code=0
87 SET TIMESTAMP=1691933326/*!*/;
88 BEGIN
89 /*!*/;
90 # at 1509
91 #230813 21:28:46 server id 3306 end_log_pos 1562 CRC32 0xe08b5a4a Rows_query
92 # delete from test_t where id=1
93 # at 1562
94 #230813 21:28:46 server id 3306 end_log_pos 1623 CRC32 0xc8d05b2e Table_map: `testdb`.`test_t` mapped to number 113
95 # at 1623
96 #230813 21:28:46 server id 3306 end_log_pos 1663 CRC32 0x727fefb1 Delete_rows: table id 113 flags: STMT_END_F
97 ### DELETE FROM `testdb`.`test_t`
98 ### WHERE
99 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
100 # at 1663
101 #230813 21:28:46 server id 3306 end_log_pos 1694 CRC32 0x5ea127b6 Xid = 123
102 COMMIT/*!*/;
103 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
104 DELIMITER ;
105 # End of log file
106 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
107 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos7 binlog]#
#如上45行~52行 insert这里没啥变化
#如上72行~77行 update的信息变少了，只记录了变化的信息，没有变化前的信息了
#如上97~99行， delete的信息也变少了
3. binlog_row_image=noblob
set session binlog_row_image=noblob;
set global binlog_row_image=noblob;
select @@global.binlog_row_image,@@binlog_row_image;
不记录blog字段的信息，待测试！！！
总结：
- binlog_row_image=full，insert/update语句的set部分是全部的新纪录，delete/update的where部分是全部的旧记录
- binlog_row_image=minimal，则update语句只有修改的列内容，delete及update的where部分都没有（如果是主键或唯一索引会有）
- binlog_row_image=noblog，待测试！！！
不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。日志中会记录成每一行数据修改的形式，然后在slave端再对相同的数据进行修改。
- **优点：**在row level的模式下，binlog中可以不记录执行的sql语句的上下文信息，仅仅只需要记录哪一条记录被修改，修改成什么样。所以row level的日志内容会非常清楚的记录每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程，或fuction，以及trigger的调用或触发无法被正确复制的问题。
- 缺点：row level模式下，所有的执行语句都会记录到日志中，同时都会以每行记录修改的来记录，这样可能会产生大量的日志内容。
1.5.3 binlog_format=mixed
理解了statement和row模式以后，mixed=statement和row
以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式，也是在statement和row之间选择一种。
新版本中的mysql中对row level模式也做了优化，并不是所有的修改都会以row level来记录，像遇到表结构变更的时候就会以statement模式来记录，如果sql语句确实就是update或者delete等修改数据的语句，那么还是会记录所有行的变更。
大多数情况下是以statement记录binlog，当隔离级别是读提交的时候，则为row的方式记录。
#修改回参数
#修改行格式
set session binlog_format='row';
set global binlog_format='row';
set session binlog_rows_query_log_events=1;
set global binlog_rows_query_log_events=1;
set session binlog_row_image=full;
set global binlog_row_image=full;
select
@@global.binlog_format,@@binlog_format,@@global.binlog_rows_query_log_events,@@binlog_rows_query_log_events,
@@global.binlog_row_image,@@binlog_row_image;
mysql> select
-> @@global.binlog_format,@@binlog_format,@@global.binlog_rows_query_log_events,@@binlog_rows_query_log_events,
-> @@global.binlog_row_image,@@binlog_row_image\G
*************************** 1. row ***************************
@@global.binlog_format: ROW
@@binlog_format: ROW
@@global.binlog_rows_query_log_events: 1
@@binlog_rows_query_log_events: 1
@@global.binlog_row_image: FULL
@@binlog_row_image: FULL
1 row in set (0.00 sec)
#修改隔离级别为读已提交
set session transaction_isolation='read-committed';
set global transaction_isolation='read-committed';
select @@global.transaction_isolation,@@transaction_isolation;
mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| READ-COMMITTED | READ-COMMITTED |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)
不建议随便去修改binlog 格式（数据库级别，非要修改建议修改session级别）（修改后可能应用程序不兼容就会出现很多乱码、高IO、主从复制延迟高等问题）
1.5.4 binlog日志的清理的方法
#日志过期天数
mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 90 | #90天到期后，90天之前的日志会被自动删除
+------------------+-------+
1 row in set (0.00 sec)
#清除过期日志，从库没有同步的一定不能清除
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000006 | 1694 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#手动清理日志
purge binary logs to 'xxx';
show binary logs;
mysql> show binary logs;
+----------------------+-----------+
| Log_name | File_size |
+----------------------+-----------+
| testdb-binlog.000001 | 205 |
| testdb-binlog.000002 | 205 |
| testdb-binlog.000003 | 1646 |
| testdb-binlog.000004 | 2207 |
| testdb-binlog.000005 | 2498 |
| testdb-binlog.000006 | 1694 |
+----------------------+-----------+
6 rows in set (0.00 sec)
[root@centos7 binlog]# ll -h
total 28K
-rw-r----- 1 mysql mysql 205 Aug 13 18:13 testdb-binlog.000001
-rw-r----- 1 mysql mysql 205 Aug 13 20:15 testdb-binlog.000002
-rw-r----- 1 mysql mysql 1.7K Aug 13 20:39 testdb-binlog.000003
-rw-r----- 1 mysql mysql 2.2K Aug 13 21:04 testdb-binlog.000004
-rw-r----- 1 mysql mysql 2.5K Aug 13 21:26 testdb-binlog.000005
-rw-r----- 1 mysql mysql 1.7K Aug 13 21:28 testdb-binlog.000006
-rw-r----- 1 mysql mysql 264 Aug 13 21:26 testdb-binlog.index
[root@centos7 binlog]# cat testdb-binlog.index
/mysql/log/3306/binlog/testdb-binlog.000001
/mysql/log/3306/binlog/testdb-binlog.000002
/mysql/log/3306/binlog/testdb-binlog.000003
/mysql/log/3306/binlog/testdb-binlog.000004
/mysql/log/3306/binlog/testdb-binlog.000005
/mysql/log/3306/binlog/testdb-binlog.000006
[root@centos7 binlog]#
mysql> purge binary logs to 'testdb-binlog.000005';
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+----------------------+-----------+
| Log_name | File_size |
+----------------------+-----------+
| testdb-binlog.000005 | 2498 |
| testdb-binlog.000006 | 1694 |
+----------------------+-----------+
2 rows in set (0.00 sec)
[root@centos7 binlog]# ll -h
total 12K
-rw-r----- 1 mysql mysql 2.5K Aug 13 21:26 testdb-binlog.000005
-rw-r----- 1 mysql mysql 1.7K Aug 13 21:28 testdb-binlog.000006
-rw-r----- 1 mysql mysql 88 Aug 13 21:46 testdb-binlog.index
[root@centos7 binlog]# cat testdb-binlog.index
/mysql/log/3306/binlog/testdb-binlog.000005
/mysql/log/3306/binlog/testdb-binlog.000006
[root@centos7 binlog]#
#全部清理二进制日志
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+----------------------+-----------+
| Log_name | File_size |
+----------------------+-----------+
| testdb-binlog.000001 | 154 |
+----------------------+-----------+
1 row in set (0.00 sec)
[root@centos7 binlog]# ll -h
total 8.0K
-rw-r----- 1 mysql mysql 154 Aug 13 21:47 testdb-binlog.000001
-rw-r----- 1 mysql mysql 44 Aug 13 21:47 testdb-binlog.index
[root@centos7 binlog]# cat testdb-binlog.index
/mysql/log/3306/binlog/testdb-binlog.000001
[root@centos7 binlog]#
参考资料
https://blog.csdn.net/weixin_33989058/article/details/92452951
1.6 MySQL binlog 查看工具及使用案例
1.6.1 show binlog
show binlog events;
1. in ‘日志文件’，指定要查询的binlog 文件名，不指定就默认看第一个。
mysql> show binlog events in 'testdb-binlog.000001';
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
| testdb-binlog.000001 | 4 | Format_desc | 3306 | 123 | Server ver: 5.7.43-log, Binlog ver: 4 |
| testdb-binlog.000001 | 123 | Previous_gtids | 3306 | 154 | |
+----------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
Pos表示日志的起始点，也就是从什么地方开始的
Event_type 表示日志类型
End_log_pos 表示日志的终点
#ddl和dml操作
mysql> create table test(id int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test values (1);
Query OK, 1 row affected (0.00 sec)
mysql> delete from test;
Query OK, 1 row affected (0.01 sec)
mysql> drop table test;
Query OK, 0 rows affected (0.01 sec)
如上pos 154是一个标识，标志下一个事务，219322是建表的事务，387632是insert的事务，697~931是delete的事务，如果需要回退操作可以关注对于的pos段
2. from pos，指定从哪个pos 起始点开始查起。
mysql> show binlog events in 'testdb-binlog.000001' from 697;
+----------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------+
| testdb-binlog.000001 | 697 | Query | 3306 | 771 | BEGIN |
| testdb-binlog.000001 | 771 | Rows_query | 3306 | 811 | # delete from test |
| testdb-binlog.000001 | 811 | Table_map | 3306 | 860 | table_id: 114 (testdb.test) |
| testdb-binlog.000001 | 860 | Delete_rows | 3306 | 900 | table_id: 114 flags: STMT_END_F |
| testdb-binlog.000001 | 900 | Xid | 3306 | 931 | COMMIT /* xid=152 */ |
| testdb-binlog.000001 | 931 | Anonymous_Gtid | 3306 | 996 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| testdb-binlog.000001 | 996 | Query | 3306 | 1117 | use `testdb`; DROP TABLE `test` /* generated by server */ |
+----------------------+-----+----------------+-----------+-------------+-----------------------------------------------------------+
7 rows in set (0.00 sec)
3. limit row_count，查询总条数
mysql> show binlog events in 'testdb-binlog.000001' limit 5;
+----------------------+-----+----------------+-----------+-------------+-----------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+-----+----------------+-----------+-------------+-----------------------------------------+
| testdb-binlog.000001 | 4 | Format_desc | 3306 | 123 | Server ver: 5.7.43-log, Binlog ver: 4 |
| testdb-binlog.000001 | 123 | Previous_gtids | 3306 | 154 | |
| testdb-binlog.000001 | 154 | Anonymous_Gtid | 3306 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| testdb-binlog.000001 | 219 | Query | 3306 | 322 | use `testdb`; create table test(id int) |
| testdb-binlog.000001 | 322 | Anonymous_Gtid | 3306 | 387 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
+----------------------+-----+----------------+-----------+-------------+-----------------------------------------+
5 rows in set (0.00 sec)
mysql> show binlog events in 'testdb-binlog.000001' from 219 limit 2;
+----------------------+-----+----------------+-----------+-------------+-----------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+-----+----------------+-----------+-------------+-----------------------------------------+
| testdb-binlog.000001 | 219 | Query | 3306 | 322 | use `testdb`; create table test(id int) |
| testdb-binlog.000001 | 322 | Anonymous_Gtid | 3306 | 387 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
+----------------------+-----+----------------+-----------+-------------+-----------------------------------------+
2 rows in set (0.00 sec)
4. limit offset，偏移量,从某一个位置开始，查多少条
#从第5行偏移一行
mysql> show binlog events in 'testdb-binlog.000001' limit 5,1;
+----------------------+-----+------------+-----------+-------------+-------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+-----+------------+-----------+-------------+-------+
| testdb-binlog.000001 | 387 | Query | 3306 | 461 | BEGIN |
+----------------------+-----+------------+-----------+-------------+-------+
1 row in set (0.00 sec)
#从219开始查3行后偏移显示紧接着的2行
mysql> show binlog events in 'testdb-binlog.000001' from 219 limit 3,2;
+----------------------+-----+------------+-----------+-------------+-------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+-----+------------+-----------+-------------+-------------------------------+
| testdb-binlog.000001 | 461 | Rows_query | 3306 | 512 | # insert into test values (1) |
| testdb-binlog.000001 | 512 | Table_map | 3306 | 561 | table_id: 114 (testdb.test) |
+----------------------+-----+------------+-----------+-------------+-------------------------------+
2 rows in set (0.00 sec)
1.6.2 mysqlbinlog
恢复的时候、用于闪回、可以读取日志内容。
帮助信息
[root@centos7 ~]# mysqlbinlog --help
mysqlbinlog Ver 3.4 for linux-glibc2.12 at x86_64
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Dumps a MySQL binary log in a format usable for viewing or for piping to
the mysql command line client.
Usage: mysqlbinlog [options] log-files
-?, --help Display this help and exit.
--base64-output=name #编码格式
Determine when the output statements should be
base64-encoded BINLOG statements: 'never' disables it and
works only for binlogs without row-based events;
'decode-rows' decodes row events into commented
pseudo-SQL statements if the --verbose option is also
given; 'auto' prints base64 only when necessary (i.e.,
for row-based events and format description events). If
no --base64-output[=name] option is given at all, the
default is 'auto'.
--bind-address=name IP address to bind to. #IP地址
--character-sets-dir=name #字符集
Directory for character set files.
-d, --database=name List entries for just this database (local log only). #数据库名
--rewrite-db=name Rewrite the row event to point so that it can be applied
to a new database
-#, --debug[=#] This is a non-debug version. Catch this and exit.
--debug-check This is a non-debug version. Catch this and exit.
--debug-info This is a non-debug version. Catch this and exit.
--default-auth=name Default authentication client-side plugin to use.
-D, --disable-log-bin #禁止恢复过程中产生日志
Disable binary log. This is useful, if you enabled
--to-last-log and are sending the output to the same
MySQL server. This way you could avoid an endless loop.
You would also like to use it when restoring after a
crash to avoid duplication of the statements you already
have. NOTE: you will need a SUPER privilege to use this
option.
-F, --force-if-open Force if binlog was not closed properly.
(Defaults to on; use --skip-force-if-open to disable.)
-f, --force-read Force reading unknown binlog events.
-H, --hexdump Augment output with hexadecimal and ASCII event dump.
-h, --host=name Get the binlog from server.
-i, --idempotent Notify the server to use idempotent mode before applying
Row Events
-l, --local-load=name
Prepare local temporary files for LOAD DATA INFILE in the
specified directory.
-o, --offset=# Skip the first N entries.
-p, --password[=name]
Password to connect to remote server.
--plugin-dir=name Directory for client-side plugins.
-P, --port=# Port number to use for connection or 0 for default to, in
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/services, built-in default (3306).
--protocol=name The protocol to use for connection (tcp, socket, pipe,
memory).
-R, --read-from-remote-server
Read binary logs from a MySQL server. This is an alias
for read-from-remote-master=BINLOG-DUMP-NON-GTIDS.
--read-from-remote-master=name
Read binary logs from a MySQL server through the
COM_BINLOG_DUMP or COM_BINLOG_DUMP_GTID commands by
setting the option to either BINLOG-DUMP-NON-GTIDS or
BINLOG-DUMP-GTIDS, respectively. If
--read-from-remote-master=BINLOG-DUMP-GTIDS is combined
with --exclude-gtids, transactions can be filtered out on
the master avoiding unnecessary network traffic.
--raw Requires -R. Output raw binlog data instead of SQL
statements, output is to log files.
-r, --result-file=name
Direct output to a given file. With --raw this is a
prefix for the file names.
--secure-auth Refuse client connecting to server if it uses old
(pre-4.1.1) protocol. Deprecated. Always TRUE
--server-id=# Extract only binlog entries created by the server having
the given id.
--server-id-bits=# Set number of significant bits in server-id
--set-charset=name Add 'SET NAMES character_set' to the output.
-s, --short-form Just show regular queries: no extra info and no row-based
events. This is for testing only, and should not be used
in production systems. If you want to suppress
base64-output, consider using --base64-output=never
instead.
-S, --socket=name The socket file to use for connection.
--ssl-mode=name SSL connection mode.
--ssl Deprecated. Use --ssl-mode instead.
(Defaults to on; use --skip-ssl to disable.)
--ssl-verify-server-cert
Deprecated. Use --ssl-mode=VERIFY_IDENTITY instead.
--ssl-ca=name CA file in PEM format.
--ssl-capath=name CA directory.
--ssl-cert=name X509 cert in PEM format.
--ssl-cipher=name SSL cipher to use.
--ssl-key=name X509 key in PEM format.
--ssl-crl=name Certificate revocation list.
--ssl-crlpath=name Certificate revocation list path.
#ssl相关的加密信息
--tls-version=name TLS version to use, permitted values are: TLSv1, TLSv1.1,
TLSv1.2
--server-public-key-path=name
File path to the server public RSA key in PEM format.
--get-server-public-key
Get server public key
--start-datetime=name #从什么时间点开始读取日志的
Start reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
-j, --start-position=# #开始的位置
Start reading the binlog at position N. Applies to the
first binlog passed on the command line.
--stop-datetime=name #什么时间点结束的
Stop reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
--stop-never Wait for more data from the server instead of stopping at
the end of the last log. Implicitly sets --to-last-log
but instead of stopping at the end of the last log it
continues to wait till the server disconnects.
--stop-never-slave-server-id=#
The slave server_id used for --read-from-remote-server
--stop-never. This option cannot be used together with
connection-server-id.
--connection-server-id=#
The slave server_id used for --read-from-remote-server.
This option cannot be used together with
stop-never-slave-server-id.
--stop-position=# Stop reading the binlog at position N. Applies to the last binlog passed on the command line.
#结束的位置
-t, --to-last-log Requires -R. Will not stop at the end of the requested
binlog but rather continue printing until the end of the
last binlog of the MySQL server. If you send the output
to the same MySQL server, that may lead to an endless
loop.
-u, --user=name Connect to the remote server as username. #远程访问需要输入用户名
-v, --verbose Reconstruct pseudo-SQL statements out of row events. -v
-v adds comments on column data types.
-V, --version Print version and exit.
--open-files-limit=#
Used to reserve file descriptors for use by this program.
-c, --verify-binlog-checksum
Verify checksum binlog events.
--binlog-row-event-max-size=#
The maximum size of a row-based binary log event in
bytes. Rows will be grouped into events smaller than this
size if possible. This value must be a multiple of 256.
--skip-gtids Do not preserve Global Transaction Identifiers; instead
make the server execute the transactions as if they were
new.
--include-gtids=name
Print events whose Global Transaction Identifiers were
provided.
--exclude-gtids=name
Print all events but those whose Global Transaction
Identifiers were provided.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
base64-output (No default value)
bind-address (No default value)
character-sets-dir (No default value)
database (No default value)
rewrite-db (No default value)
default-auth (No default value)
disable-log-bin FALSE
force-if-open TRUE
force-read FALSE
hexdump FALSE
host (No default value)
idempotent FALSE
local-load (No default value)
offset 0
plugin-dir (No default value)
port 0
read-from-remote-server FALSE
read-from-remote-master (No default value)
raw FALSE
result-file (No default value)
secure-auth TRUE
server-id 0
server-id-bits 32
set-charset (No default value)
short-form FALSE
socket (No default value)
ssl TRUE
ssl-verify-server-cert FALSE
ssl-ca (No default value)
ssl-capath (No default value)
ssl-cert (No default value)
ssl-cipher (No default value)
ssl-key (No default value)
ssl-crl (No default value)
ssl-crlpath (No default value)
tls-version (No default value)
server-public-key-path (No default value)
get-server-public-key FALSE
start-datetime (No default value)
start-position 4
stop-datetime (No default value)
stop-never FALSE
stop-never-slave-server-id -1
connection-server-id -1
stop-position 18446744073709551615
to-last-log FALSE
user (No default value)
open-files-limit 64
verify-binlog-checksum FALSE
binlog-row-event-max-size 4294967040
skip-gtids FALSE
include-gtids (No default value)
exclude-gtids (No default value)
[root@centos7 ~]#
默认查看日志
reset master;
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000001 | 154 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
use testdb
create table test_t (id int);
insert into test_t values(1);
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000001 | 638 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#默认查看日志
[root@centos7 binlog]# mysqlbinlog testdb-binlog.000001 > 1.txt
[root@centos7 binlog]# cat -n 1.txt
1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
3 DELIMITER /*!*/;
4 # at 4
5 #230813 22:31:30 server id 3306 end_log_pos 123 CRC32 0x68ee453e Start: binlog v 4, server v 5.7.43-log created 230813 22:31:30 at startup
6 # Warning: this binlog is either in use or was not closed properly.
7 ROLLBACK/*!*/;
8 BINLOG '
9 QunYZA/qDAAAdwAAAHsAAAABAAQANS43LjQzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
10 AAAAAAAAAAAAAAAAAABC6dhkEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
11 AT5F7mg=
12 '/*!*/;
13 # at 123
14 #230813 22:31:30 server id 3306 end_log_pos 154 CRC32 0xcd2300de Previous-GTIDs
15 # [empty]
16 # at 154
17 #230813 22:33:22 server id 3306 end_log_pos 219 CRC32 0x43fb0e59 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
18 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
19 # at 219
20 #230813 22:33:22 server id 3306 end_log_pos 325 CRC32 0x3f787d12 Query thread_id=3 exec_time=0 error_code=0
21 use `testdb`/*!*/;
22 SET TIMESTAMP=1691937202/*!*/;
23 SET @@session.pseudo_thread_id=3/*!*/;
24 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
25 SET @@session.sql_mode=1436549120/*!*/;
26 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
27 /*!\C utf8 *//*!*/;
28 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
29 SET @@session.lc_time_names=0/*!*/;
30 SET @@session.collation_database=DEFAULT/*!*/;
31 create table test_t (id int)
32 /*!*/;
33 # at 325
34 #230813 22:33:25 server id 3306 end_log_pos 390 CRC32 0xfdb90f5a Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
35 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
36 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
37 # at 390
38 #230813 22:33:25 server id 3306 end_log_pos 464 CRC32 0x868ba03f Query thread_id=3 exec_time=0 error_code=0
39 SET TIMESTAMP=1691937205/*!*/;
40 BEGIN
41 /*!*/;
42 # at 464
43 # at 516
44 #230813 22:33:25 server id 3306 end_log_pos 567 CRC32 0xeeeacf88 Table_map: `testdb`.`test_t` mapped to number 115
45 # at 567
46 #230813 22:33:25 server id 3306 end_log_pos 607 CRC32 0x194fb475 Write_rows: table id 115 flags: STMT_END_F
47
48 BINLOG '
49 tenYZBPqDAAAMwAAADcCAAAAAHMAAAAAAAEABnRlc3RkYgAGdGVzdF90AAEDAAGIz+ru
50 tenYZB7qDAAAKAAAAF8CAAAAAHMAAAAAAAEAAgAB//4BAAAAdbRPGQ==
51 '/*!*/;
52 # at 607
53 #230813 22:33:25 server id 3306 end_log_pos 638 CRC32 0x5d7f5c93 Xid = 168
54 COMMIT/*!*/;
55 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
56 DELIMITER ;
57 # End of log file
58 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
59 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos7 binlog]#
#如上46~50行加密的insert 信息
输出二进制格式
[root@centos7 binlog]# mysqlbinlog -H testdb-binlog.000001 > 1.txt
[root@centos7 binlog]# cat -n 1.txt
1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
3 DELIMITER /*!*/;
4 # at 4
5 #230813 22:31:30 server id 3306 end_log_pos 123 CRC32 0x68ee453e
6 # Position Timestamp Type Master ID Size Master Pos Flags
7 # 4 42 e9 d8 64 0f ea 0c 00 00 77 00 00 00 7b 00 00 00 01 00
8 # 17 04 00 35 2e 37 2e 34 33 2d 6c 6f 67 00 00 00 00 |..5.7.43.log....|
9 # 27 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
10 # 37 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
11 # 47 00 00 00 00 42 e9 d8 64 13 38 0d 00 08 00 12 00 |....B..d.8......|
12 # 57 04 04 04 04 12 00 00 5f 00 04 1a 08 00 00 00 08 |................|
13 # 67 08 08 02 00 00 00 0a 0a 0a 2a 2a 00 12 34 00 01 |.............4..|
14 # 77 3e 45 ee 68 |.E.h|
15 # Start: binlog v 4, server v 5.7.43-log created 230813 22:31:30 at startup
16 # Warning: this binlog is either in use or was not closed properly.
17 ROLLBACK/*!*/;
18 BINLOG '
19 QunYZA/qDAAAdwAAAHsAAAABAAQANS43LjQzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
20 AAAAAAAAAAAAAAAAAABC6dhkEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
21 AT5F7mg=
22 '/*!*/;
23 # at 123
24 #230813 22:31:30 server id 3306 end_log_pos 154 CRC32 0xcd2300de
25 # Position Timestamp Type Master ID Size Master Pos Flags
26 # 7b 42 e9 d8 64 23 ea 0c 00 00 1f 00 00 00 9a 00 00 00 80 00
27 # 8e 00 00 00 00 00 00 00 00 de 00 23 cd |............|
28 # Previous-GTIDs
29 # [empty]
30 # at 154
31 #230813 22:33:22 server id 3306 end_log_pos 219 CRC32 0x43fb0e59
32 # Position Timestamp Type Master ID Size Master Pos Flags
33 # 9a b2 e9 d8 64 22 ea 0c 00 00 41 00 00 00 db 00 00 00 00 00
34 # ad 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
35 # bd 00 00 00 00 00 00 00 00 00 02 00 00 00 00 00 00 |................|
36 # cd 00 00 01 00 00 00 00 00 00 00 59 0e fb 43 |..........Y..C|
37 # Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
38 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
39 # at 219
40 #230813 22:33:22 server id 3306 end_log_pos 325 CRC32 0x3f787d12
41 # Position Timestamp Type Master ID Size Master Pos Flags
42 # db b2 e9 d8 64 02 ea 0c 00 00 6a 00 00 00 45 01 00 00 00 00
43 # ee 03 00 00 00 00 00 00 00 06 00 00 23 00 00 00 00 |................|
44 # fe 00 00 01 00 00 a0 55 00 00 00 00 06 03 73 74 64 |......U......std|
45 # 10e 04 21 00 21 00 21 00 0c 01 74 65 73 74 64 62 00 |.........testdb.|
46 # 11e 74 65 73 74 64 62 00 63 72 65 61 74 65 20 74 61 |testdb.create.ta|
47 # 12e 62 6c 65 20 74 65 73 74 5f 74 20 28 69 64 20 69 |ble.test.t..id.i|
48 # 13e 6e 74 29 12 7d 78 3f |nt...x.|
49 # Query thread_id=3 exec_time=0 error_code=0
50 use `testdb`/*!*/;
51 SET TIMESTAMP=1691937202/*!*/;
52 SET @@session.pseudo_thread_id=3/*!*/;
53 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
54 SET @@session.sql_mode=1436549120/*!*/;
55 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
56 /*!\C utf8 *//*!*/;
57 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
58 SET @@session.lc_time_names=0/*!*/;
59 SET @@session.collation_database=DEFAULT/*!*/;
60 create table test_t (id int)
61 /*!*/;
62 # at 325
63 #230813 22:33:25 server id 3306 end_log_pos 390 CRC32 0xfdb90f5a
64 # Position Timestamp Type Master ID Size Master Pos Flags
65 # 145 b5 e9 d8 64 22 ea 0c 00 00 41 00 00 00 86 01 00 00 00 00
66 # 158 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
67 # 168 00 00 00 00 00 00 00 00 00 02 01 00 00 00 00 00 |................|
68 # 178 00 00 02 00 00 00 00 00 00 00 5a 0f b9 fd |..........Z...|
69 # Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
70 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
71 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
72 # at 390
73 #230813 22:33:25 server id 3306 end_log_pos 464 CRC32 0x868ba03f
74 # Position Timestamp Type Master ID Size Master Pos Flags
75 # 186 b5 e9 d8 64 02 ea 0c 00 00 4a 00 00 00 d0 01 00 00 08 00
76 # 199 03 00 00 00 00 00 00 00 06 00 00 1a 00 00 00 00 |................|
77 # 1a9 00 00 01 00 00 a0 55 00 00 00 00 06 03 73 74 64 |......U......std|
78 # 1b9 04 21 00 21 00 21 00 74 65 73 74 64 62 00 42 45 |.......testdb.BE|
79 # 1c9 47 49 4e 3f a0 8b 86 |GIN....|
80 # Query thread_id=3 exec_time=0 error_code=0
81 SET TIMESTAMP=1691937205/*!*/;
82 BEGIN
83 /*!*/;
84 # at 464
85 # at 516
86 #230813 22:33:25 server id 3306 end_log_pos 567 CRC32 0xeeeacf88
87 # Position Timestamp Type Master ID Size Master Pos Flags
88 # 204 b5 e9 d8 64 13 ea 0c 00 00 33 00 00 00 37 02 00 00 00 00
89 # 217 73 00 00 00 00 00 01 00 06 74 65 73 74 64 62 00 |s........testdb.|
90 # 227 06 74 65 73 74 5f 74 00 01 03 00 01 88 cf ea ee |.test.t.........|
91 # Table_map: `testdb`.`test_t` mapped to number 115
92 # at 567
93 #230813 22:33:25 server id 3306 end_log_pos 607 CRC32 0x194fb475
94 # Position Timestamp Type Master ID Size Master Pos Flags
95 # 237 b5 e9 d8 64 1e ea 0c 00 00 28 00 00 00 5f 02 00 00 00 00
96 # 24a 73 00 00 00 00 00 01 00 02 00 01 ff fe 01 00 00 |s...............|
97 # 25a 00 75 b4 4f 19 |.u.O.|
98 # Write_rows: table id 115 flags: STMT_END_F
99
100 BINLOG '
101 tenYZBPqDAAAMwAAADcCAAAAAHMAAAAAAAEABnRlc3RkYgAGdGVzdF90AAEDAAGIz+ru
102 tenYZB7qDAAAKAAAAF8CAAAAAHMAAAAAAAEAAgAB//4BAAAAdbRPGQ==
103 '/*!*/;
104 # at 607
105 #230813 22:33:25 server id 3306 end_log_pos 638 CRC32 0x5d7f5c93
106 # Position Timestamp Type Master ID Size Master Pos Flags
107 # 25f b5 e9 d8 64 10 ea 0c 00 00 1f 00 00 00 7e 02 00 00 00 00
108 # 272 a8 00 00 00 00 00 00 00 93 5c 7f 5d |............|
109 # Xid = 168
110 COMMIT/*!*/;
111 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
112 DELIMITER ;
113 # End of log file
114 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
115 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos7 binlog]#
指定数据库
mysqlbinlog -d testdb testdb-binlog.000001
mysqlbinlog --database testdb testdb-binlog.000001
[root@centos7 binlog]# mysqlbinlog -d testdb testdb-binlog.000001 > 1.txt
WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.
[root@centos7 binlog]#
[root@centos7 binlog]# cat -n 1.txt
1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
3 DELIMITER /*!*/;
4 # at 4
5 #230813 22:31:30 server id 3306 end_log_pos 123 CRC32 0x68ee453e Start: binlog v 4, server v 5.7.43-log created 230813 22:31:30 at startup
6 # Warning: this binlog is either in use or was not closed properly.
7 ROLLBACK/*!*/;
8 BINLOG '
9 QunYZA/qDAAAdwAAAHsAAAABAAQANS43LjQzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
10 AAAAAAAAAAAAAAAAAABC6dhkEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
11 AT5F7mg=
12 '/*!*/;
13 # at 123
14 #230813 22:31:30 server id 3306 end_log_pos 154 CRC32 0xcd2300de Previous-GTIDs
15 # [empty]
16 # at 154
17 #230813 22:33:22 server id 3306 end_log_pos 219 CRC32 0x43fb0e59 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
18 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
19 # at 219
20 #230813 22:33:22 server id 3306 end_log_pos 325 CRC32 0x3f787d12 Query thread_id=3 exec_time=0 error_code=0
21 use `testdb`/*!*/;
22 SET TIMESTAMP=1691937202/*!*/;
23 SET @@session.pseudo_thread_id=3/*!*/;
24 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
25 SET @@session.sql_mode=1436549120/*!*/;
26 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
27 /*!\C utf8 *//*!*/;
28 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
29 SET @@session.lc_time_names=0/*!*/;
30 SET @@session.collation_database=DEFAULT/*!*/;
31 create table test_t (id int)
32 /*!*/;
33 # at 325
34 #230813 22:33:25 server id 3306 end_log_pos 390 CRC32 0xfdb90f5a Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
35 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
36 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
37 # at 390
38 #230813 22:33:25 server id 3306 end_log_pos 464 CRC32 0x868ba03f Query thread_id=3 exec_time=0 error_code=0
39 SET TIMESTAMP=1691937205/*!*/;
40 BEGIN
41 /*!*/;
42 # at 464
43 # at 516
44 #230813 22:33:25 server id 3306 end_log_pos 567 CRC32 0xeeeacf88 Table_map: `testdb`.`test_t` mapped to number 115
45 # at 567
46 #230813 22:33:25 server id 3306 end_log_pos 607 CRC32 0x194fb475 Write_rows: table id 115 flags: STMT_END_F
47
48 BINLOG '
49 tenYZBPqDAAAMwAAADcCAAAAAHMAAAAAAAEABnRlc3RkYgAGdGVzdF90AAEDAAGIz+ru
50 tenYZB7qDAAAKAAAAF8CAAAAAHMAAAAAAAEAAgAB//4BAAAAdbRPGQ==
51 '/*!*/;
52 # at 607
53 #230813 22:33:25 server id 3306 end_log_pos 638 CRC32 0x5d7f5c93 Xid = 168
54 COMMIT/*!*/;
55 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
56 DELIMITER ;
57 # End of log file
58 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
59 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos7 binlog]#
禁止恢复过程中产生日志
mysqlbinlog -D testdb-binlog.000001 > 1.txt
mysqlbinlog --disable-log-bin testdb-binlog.000001 > 1.txt
[root@centos7 binlog]# mysqlbinlog -D testdb-binlog.000001 > 1.txt
[root@centos7 binlog]# cat -n 1.txt
1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
2 /*!32316 SET @OLD_SQL_LOG_BIN=@@SQL_LOG_BIN, SQL_LOG_BIN=0*/;
3 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
4 DELIMITER /*!*/;
5 # at 4
6 #230813 22:31:30 server id 3306 end_log_pos 123 CRC32 0x68ee453e Start: binlog v 4, server v 5.7.43-log created 230813 22:31:30 at startup
7 # Warning: this binlog is either in use or was not closed properly.
8 ROLLBACK/*!*/;
9 BINLOG '
10 QunYZA/qDAAAdwAAAHsAAAABAAQANS43LjQzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
11 AAAAAAAAAAAAAAAAAABC6dhkEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
12 AT5F7mg=
13 '/*!*/;
14 # at 123
15 #230813 22:31:30 server id 3306 end_log_pos 154 CRC32 0xcd2300de Previous-GTIDs
16 # [empty]
17 # at 154
18 #230813 22:33:22 server id 3306 end_log_pos 219 CRC32 0x43fb0e59 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
19 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
20 # at 219
21 #230813 22:33:22 server id 3306 end_log_pos 325 CRC32 0x3f787d12 Query thread_id=3 exec_time=0 error_code=0
22 use `testdb`/*!*/;
23 SET TIMESTAMP=1691937202/*!*/;
24 SET @@session.pseudo_thread_id=3/*!*/;
25 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
26 SET @@session.sql_mode=1436549120/*!*/;
27 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
28 /*!\C utf8 *//*!*/;
29 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
30 SET @@session.lc_time_names=0/*!*/;
31 SET @@session.collation_database=DEFAULT/*!*/;
32 create table test_t (id int)
33 /*!*/;
34 # at 325
35 #230813 22:33:25 server id 3306 end_log_pos 390 CRC32 0xfdb90f5a Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
36 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
37 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
38 # at 390
39 #230813 22:33:25 server id 3306 end_log_pos 464 CRC32 0x868ba03f Query thread_id=3 exec_time=0 error_code=0
40 SET TIMESTAMP=1691937205/*!*/;
41 BEGIN
42 /*!*/;
43 # at 464
44 # at 516
45 #230813 22:33:25 server id 3306 end_log_pos 567 CRC32 0xeeeacf88 Table_map: `testdb`.`test_t` mapped to number 115
46 # at 567
47 #230813 22:33:25 server id 3306 end_log_pos 607 CRC32 0x194fb475 Write_rows: table id 115 flags: STMT_END_F
48
49 BINLOG '
50 tenYZBPqDAAAMwAAADcCAAAAAHMAAAAAAAEABnRlc3RkYgAGdGVzdF90AAEDAAGIz+ru
51 tenYZB7qDAAAKAAAAF8CAAAAAHMAAAAAAAEAAgAB//4BAAAAdbRPGQ==
52 '/*!*/;
53 # at 607
54 #230813 22:33:25 server id 3306 end_log_pos 638 CRC32 0x5d7f5c93 Xid = 168
55 COMMIT/*!*/;
56 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
57 DELIMITER ;
58 # End of log file
59 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
60 /*!32316 SET SQL_LOG_BIN=@OLD_SQL_LOG_BIN*/;
61 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos7 binlog]#
#到最后一个文件
mysqlbinlog -to-last-log testdb-binlog.000001 > 1.txt
在输出时控制内容的编码显示格式
--base64-output:auto,never,decode-rows,unspec
-v (-verbose)
-vv
[root@centos7 binlog]# mysqlbinlog --base64-output=auto testdb-binlog.000001 > 1.txt
[root@centos7 binlog]# cat -n 1.txt
1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
3 DELIMITER /*!*/;
4 # at 4
5 #230813 22:31:30 server id 3306 end_log_pos 123 CRC32 0x68ee453e Start: binlog v 4, server v 5.7.43-log created 230813 22:31:30 at startup
6 # Warning: this binlog is either in use or was not closed properly.
7 ROLLBACK/*!*/;
8 BINLOG '
9 QunYZA/qDAAAdwAAAHsAAAABAAQANS43LjQzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
10 AAAAAAAAAAAAAAAAAABC6dhkEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
11 AT5F7mg=
12 '/*!*/;
13 # at 123
14 #230813 22:31:30 server id 3306 end_log_pos 154 CRC32 0xcd2300de Previous-GTIDs
15 # [empty]
16 # at 154
17 #230813 22:33:22 server id 3306 end_log_pos 219 CRC32 0x43fb0e59 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
18 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
19 # at 219
20 #230813 22:33:22 server id 3306 end_log_pos 325 CRC32 0x3f787d12 Query thread_id=3 exec_time=0 error_code=0
21 use `testdb`/*!*/;
22 SET TIMESTAMP=1691937202/*!*/;
23 SET @@session.pseudo_thread_id=3/*!*/;
24 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
25 SET @@session.sql_mode=1436549120/*!*/;
26 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
27 /*!\C utf8 *//*!*/;
28 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
29 SET @@session.lc_time_names=0/*!*/;
30 SET @@session.collation_database=DEFAULT/*!*/;
31 create table test_t (id int)
32 /*!*/;
33 # at 325
34 #230813 22:33:25 server id 3306 end_log_pos 390 CRC32 0xfdb90f5a Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
35 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
36 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
37 # at 390
38 #230813 22:33:25 server id 3306 end_log_pos 464 CRC32 0x868ba03f Query thread_id=3 exec_time=0 error_code=0
39 SET TIMESTAMP=1691937205/*!*/;
40 BEGIN
41 /*!*/;
42 # at 464
43 # at 516
44 #230813 22:33:25 server id 3306 end_log_pos 567 CRC32 0xeeeacf88 Table_map: `testdb`.`test_t` mapped to number 115
45 # at 567
46 #230813 22:33:25 server id 3306 end_log_pos 607 CRC32 0x194fb475 Write_rows: table id 115 flags: STMT_END_F
47
48 BINLOG '
49 tenYZBPqDAAAMwAAADcCAAAAAHMAAAAAAAEABnRlc3RkYgAGdGVzdF90AAEDAAGIz+ru
50 tenYZB7qDAAAKAAAAF8CAAAAAHMAAAAAAAEAAgAB//4BAAAAdbRPGQ==
51 '/*!*/;
52 # at 607
53 #230813 22:33:25 server id 3306 end_log_pos 638 CRC32 0x5d7f5c93 Xid = 168
54 COMMIT/*!*/;
55 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
56 DELIMITER ;
57 # End of log file
58 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
59 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos7 binlog]#
#如上46~50行可以看出--base64-output=auto的时候，dml的sql语句是加密的
mysqlbinlog --base64-output=decode-rows -v testdb-binlog.000001> 1.txt
[root@centos7 binlog]# mysqlbinlog --base64-output=decode-rows -v testdb-binlog.000001> 1.txt
[root@centos7 binlog]# cat -n 1.txt
1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
3 DELIMITER /*!*/;
4 # at 4
5 #230813 22:31:30 server id 3306 end_log_pos 123 CRC32 0x68ee453e Start: binlog v 4, server v 5.7.43-log created 230813 22:31:30 at startup
6 # Warning: this binlog is either in use or was not closed properly.
7 ROLLBACK/*!*/;
8 # at 123
9 #230813 22:31:30 server id 3306 end_log_pos 154 CRC32 0xcd2300de Previous-GTIDs
10 # [empty]
11 # at 154
12 #230813 22:33:22 server id 3306 end_log_pos 219 CRC32 0x43fb0e59 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
13 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
14 # at 219
15 #230813 22:33:22 server id 3306 end_log_pos 325 CRC32 0x3f787d12 Query thread_id=3 exec_time=0 error_code=0
16 use `testdb`/*!*/;
17 SET TIMESTAMP=1691937202/*!*/;
18 SET @@session.pseudo_thread_id=3/*!*/;
19 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
20 SET @@session.sql_mode=1436549120/*!*/;
21 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
22 /*!\C utf8 *//*!*/;
23 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
24 SET @@session.lc_time_names=0/*!*/;
25 SET @@session.collation_database=DEFAULT/*!*/;
26 create table test_t (id int)
27 /*!*/;
28 # at 325
29 #230813 22:33:25 server id 3306 end_log_pos 390 CRC32 0xfdb90f5a Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
30 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
31 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
32 # at 390
33 #230813 22:33:25 server id 3306 end_log_pos 464 CRC32 0x868ba03f Query thread_id=3 exec_time=0 error_code=0
34 SET TIMESTAMP=1691937205/*!*/;
35 BEGIN
36 /*!*/;
37 # at 464
38 # at 516
39 #230813 22:33:25 server id 3306 end_log_pos 567 CRC32 0xeeeacf88 Table_map: `testdb`.`test_t` mapped to number 115
40 # at 567
41 #230813 22:33:25 server id 3306 end_log_pos 607 CRC32 0x194fb475 Write_rows: table id 115 flags: STMT_END_F
42 ### INSERT INTO `testdb`.`test_t`
43 ### SET
44 ### @1=1
45 # at 607
46 #230813 22:33:25 server id 3306 end_log_pos 638 CRC32 0x5d7f5c93 Xid = 168
47 COMMIT/*!*/;
48 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
49 DELIMITER ;
50 # End of log file
51 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
52 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos7 binlog]#
#如上41~44行 --base64-output=decode-rows同时加一个v，信息稍微详细一点
mysqlbinlog --base64-output=decode-rows -v -v testdb-binlog.000001> 1.txt
[root@centos7 binlog]# mysqlbinlog --base64-output=decode-rows -v -v testdb-binlog.000001> 1.txt
[root@centos7 binlog]# cat -n 1.txt
1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
3 DELIMITER /*!*/;
4 # at 4
5 #230813 22:31:30 server id 3306 end_log_pos 123 CRC32 0x68ee453e Start: binlog v 4, server v 5.7.43-log created 230813 22:31:30 at startup
6 # Warning: this binlog is either in use or was not closed properly.
7 ROLLBACK/*!*/;
8 # at 123
9 #230813 22:31:30 server id 3306 end_log_pos 154 CRC32 0xcd2300de Previous-GTIDs
10 # [empty]
11 # at 154
12 #230813 22:33:22 server id 3306 end_log_pos 219 CRC32 0x43fb0e59 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
13 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
14 # at 219
15 #230813 22:33:22 server id 3306 end_log_pos 325 CRC32 0x3f787d12 Query thread_id=3 exec_time=0 error_code=0
16 use `testdb`/*!*/;
17 SET TIMESTAMP=1691937202/*!*/;
18 SET @@session.pseudo_thread_id=3/*!*/;
19 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
20 SET @@session.sql_mode=1436549120/*!*/;
21 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
22 /*!\C utf8 *//*!*/;
23 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
24 SET @@session.lc_time_names=0/*!*/;
25 SET @@session.collation_database=DEFAULT/*!*/;
26 create table test_t (id int)
27 /*!*/;
28 # at 325
29 #230813 22:33:25 server id 3306 end_log_pos 390 CRC32 0xfdb90f5a Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
30 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
31 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
32 # at 390
33 #230813 22:33:25 server id 3306 end_log_pos 464 CRC32 0x868ba03f Query thread_id=3 exec_time=0 error_code=0
34 SET TIMESTAMP=1691937205/*!*/;
35 BEGIN
36 /*!*/;
37 # at 464
38 #230813 22:33:25 server id 3306 end_log_pos 516 CRC32 0xf97e6695 Rows_query
39 # insert into test_t values(1)
40 # at 516
41 #230813 22:33:25 server id 3306 end_log_pos 567 CRC32 0xeeeacf88 Table_map: `testdb`.`test_t` mapped to number 115
42 # at 567
43 #230813 22:33:25 server id 3306 end_log_pos 607 CRC32 0x194fb475 Write_rows: table id 115 flags: STMT_END_F
44 ### INSERT INTO `testdb`.`test_t`
45 ### SET
46 ### @1=1 /* INT meta=0 nullable=1 is_null=0 */
47 # at 607
48 #230813 22:33:25 server id 3306 end_log_pos 638 CRC32 0x5d7f5c93 Xid = 168
49 COMMIT/*!*/;
50 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
51 DELIMITER ;
52 # End of log file
53 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
54 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos7 binlog]#
#如上43~46行 --base64-output=decode-rows，同时加了两个v，把更详细的信息打出来，包括具体执行的SQL语句
跳过前N 个条目显示
mysqlbinlog -o 20 testdb-binlog.000001 > 1.txt
#跳过前面20行记录，从21行记录开始显示
[root@centos7 binlog]# mysqlbinlog -o 20 testdb-binlog.000001 > 1.txt
[root@centos7 binlog]# cat -n 1.txt
1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
3 DELIMITER /*!*/;
4 # at 4
5 #230813 22:31:30 server id 3306 end_log_pos 123 CRC32 0x68ee453e Start: binlog v 4, server v 5.7.43-log created 230813 22:31:30 at startup
6 # Warning: this binlog is either in use or was not closed properly.
7 ROLLBACK/*!*/;
8 BINLOG '
9 QunYZA/qDAAAdwAAAHsAAAABAAQANS43LjQzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
10 AAAAAAAAAAAAAAAAAABC6dhkEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
11 AT5F7mg=
12 '/*!*/;
13 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
14 DELIMITER ;
15 # End of log file
16 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
17 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos7 binlog]#
保存到文件
mysqlbinlog -o 20 testdb-binlog.000001 > 1.txt
mysqlbinlog testdb-binlog.000001 -r 1.txt
mysqlbinlog --server-id=3306 testdb-binlog.000001 -r 1.txt
从一个特定的位置开始提取条目,从一个特定的位置结束提取条目
mysqlbinlog -j 386 testdb-binlog.000001
mysqlbinlog --start-position=386 testdb-binlog.000001
mysqlbinlog --stop-position=386 testdb-binlog.000001
mysqlbinlog --start-position=154 --stop-position=386 testdb-binlog.000001
从一个特定的时间开始提取条目,从一个特定的时间结束提取条目
mysqlbinlog --start-datetime='2023-08-13 14:11:38' testdb-binlog.000001
mysqlbinlog --stop-datetime='2023-08-13 14:16:41' testdb-binlog.000001
mysqlbinlog --start-datetime='2023-08-13 14:11:38' --stop-datetime='2023-08-13 14:17:00' testdb-binlog.000001
从远程获取二进制日志
mysqlbinlog -R -h 192.168.1.51 -p testdb-binlog.000001
1.7 MySQL 通过binlog 日志恢复数据-恢复误删除表
结合备份+binlog恢复数据
场景：
昨天晚上做了全备，今天上午用户不小心误删除某张表
解决方案：
不建议在原生产环境直接恢复，建议在另一台机器恢复全库+日志，然后导出删除的表，导入到生产环境。
解决思路：
早上创建了一张表，昨天晚上做了全备份，今天上午对表进行了更新，中午的时候把表误删除了吗，需要恢复这张表。
案例
第1步: 创建测试表
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2023-08-13 23:11:36 |
+---------------------+
1 row in set (0.00 sec)
use testdb
create table test_t (
`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,
primary key (`id`)
) engine=innodb default charset=utf8;
insert into testdb.test_t(`name`,`sex`,`age`) values
('test1','w',21),
('test2','m',22),
('test3','w',23),
('test4','m',24),
('test5','w',25);
mysql> select * from test_t;
+----+-------+-----+-----+
| id | name | sex | age |
+----+-------+-----+-----+
| 1 | test1 | w | 21 |
| 2 | test2 | m | 22 |
| 3 | test3 | w | 23 |
| 4 | test4 | m | 24 |
| 5 | test5 | w | 25 |
+----+-------+-----+-----+
5 rows in set (0.00 sec)
第2步: 做备份
[root@centos7 ~]# mysqldump -uroot -prootroot -F -R --all-databases > alldb_bak.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@centos7 ~]#
第3步: 上午的业务
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000003 | 154 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2023-08-13 23:14:05 |
+---------------------+
1 row in set (0.00 sec)
update testdb.test_t set name='test05' where id=5;
mysql> select * from test_t;
+----+--------+-----+-----+
| id | name | sex | age |
+----+--------+-----+-----+
| 1 | test1 | w | 21 |
| 2 | test2 | m | 22 |
| 3 | test3 | w | 23 |
| 4 | test4 | m | 24 |
| 5 | test05 | w | 25 |
+----+--------+-----+-----+
5 rows in set (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2023-08-13 23:17:06 |
+---------------------+
1 row in set (0.00 sec)
mysql> update testdb.test_t set name='test04' where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test_t;
+----+--------+-----+-----+
| id | name | sex | age |
+----+--------+-----+-----+
| 1 | test1 | w | 21 |
| 2 | test2 | m | 22 |
| 3 | test3 | w | 23 |
| 4 | test04 | m | 24 |
| 5 | test05 | w | 25 |
+----+--------+-----+-----+
5 rows in set (0.00 sec)
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000003 | 882 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
第4步: 中午的误删除
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2023-08-13 23:18:41 |
+---------------------+
1 row in set (0.00 sec)
mysql> drop table test_t;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from test_t;
ERROR 1146 (42S02): Table 'testdb.test_t' doesn't exist
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000003 | 1070 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
第5步: 在另一台机器恢复（当前案例在本地，生产不要在本地）
#将最新的二进制日志做一个备份，然后再分析
[root@centos7 binlog]# pwd
/mysql/log/3306/binlog
[root@centos7 binlog]# cp testdb-binlog.000003 testdb-binlog.000003.bak
[root@centos7 binlog]# ll -h
total 20K
-rw-r----- 1 mysql mysql 1.1K Aug 13 23:13 testdb-binlog.000001
-rw-r----- 1 mysql mysql 205 Aug 13 23:13 testdb-binlog.000002
-rw-r----- 1 mysql mysql 1.1K Aug 13 23:18 testdb-binlog.000003
-rw-r----- 1 root root 1.1K Aug 13 23:21 testdb-binlog.000003.bak
-rw-r----- 1 mysql mysql 132 Aug 13 23:13 testdb-binlog.index
[root@centos7 binlog]#
mysql> show binlog events in 'testdb-binlog.000003';
+----------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------+
| testdb-binlog.000003 | 4 | Format_desc | 3306 | 123 | Server ver: 5.7.43-log, Binlog ver: 4 |
| testdb-binlog.000003 | 123 | Previous_gtids | 3306 | 154 | |
| testdb-binlog.000003 | 154 | Anonymous_Gtid | 3306 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| testdb-binlog.000003 | 219 | Query | 3306 | 293 | BEGIN |
| testdb-binlog.000003 | 293 | Rows_query | 3306 | 366 | # update testdb.test_t set name='test05' where id=5 |
| testdb-binlog.000003 | 366 | Table_map | 3306 | 424 | table_id: 117 (testdb.test_t) |
| testdb-binlog.000003 | 424 | Update_rows | 3306 | 487 | table_id: 117 flags: STMT_END_F |
| testdb-binlog.000003 | 487 | Xid | 3306 | 518 | COMMIT /* xid=635 */ |
| testdb-binlog.000003 | 518 | Anonymous_Gtid | 3306 | 583 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| testdb-binlog.000003 | 583 | Query | 3306 | 657 | BEGIN |
| testdb-binlog.000003 | 657 | Rows_query | 3306 | 730 | # update testdb.test_t set name='test04' where id=4 |
| testdb-binlog.000003 | 730 | Table_map | 3306 | 788 | table_id: 117 (testdb.test_t) |
| testdb-binlog.000003 | 788 | Update_rows | 3306 | 851 | table_id: 117 flags: STMT_END_F |
| testdb-binlog.000003 | 851 | Xid | 3306 | 882 | COMMIT /* xid=638 */ |
| testdb-binlog.000003 | 882 | Anonymous_Gtid | 3306 | 947 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| testdb-binlog.000003 | 947 | Query | 3306 | 1070 | use `testdb`; DROP TABLE `test_t` /* generated by server */ |
+----------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------+
16 rows in set (0.01 sec)
#如上882~1070这段就是删除表的事务，恢复到882之前这个事务就可以了
#具体的时间可以使用mysqlbinlog工具根据pos查看时间点
[root@centos7 binlog]# mysqlbinlog testdb-binlog.000003 > 1.txt
[root@centos7 binlog]# cat -n 1.txt
1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
3 DELIMITER /*!*/;
4 # at 4
5 #230813 23:13:14 server id 3306 end_log_pos 123 CRC32 0x3fcae4d4 Start: binlog v 4, server v 5.7.43-log created 230813 23:13:14
6 # Warning: this binlog is either in use or was not closed properly.
7 BINLOG '
8 CvPYZA/qDAAAdwAAAHsAAAABAAQANS43LjQzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
9 AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
10 AdTkyj8=
11 '/*!*/;
12 # at 123
13 #230813 23:13:14 server id 3306 end_log_pos 154 CRC32 0x473340ea Previous-GTIDs
14 # [empty]
15 # at 154
16 #230813 23:16:36 server id 3306 end_log_pos 219 CRC32 0x90403097 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes
17 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
18 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
19 # at 219
20 #230813 23:16:36 server id 3306 end_log_pos 293 CRC32 0xa351b7f1 Query thread_id=4 exec_time=0 error_code=0
21 SET TIMESTAMP=1691939796/*!*/;
22 SET @@session.pseudo_thread_id=4/*!*/;
23 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
24 SET @@session.sql_mode=1436549120/*!*/;
25 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
26 /*!\C utf8 *//*!*/;
27 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
28 SET @@session.lc_time_names=0/*!*/;
29 SET @@session.collation_database=DEFAULT/*!*/;
30 BEGIN
31 /*!*/;
32 # at 293
33 # at 366
34 #230813 23:16:36 server id 3306 end_log_pos 424 CRC32 0x2fc3dbf3 Table_map: `testdb`.`test_t` mapped to number 117
35 # at 424
36 #230813 23:16:36 server id 3306 end_log_pos 487 CRC32 0x6b31d0ab Update_rows: table id 117 flags: STMT_END_F
37
38 BINLOG '
39 1PPYZBPqDAAAOgAAAKgBAAAAAHUAAAAAAAEABnRlc3RkYgAGdGVzdF90AAQDD/4BBDAA9wEA89vD
40 Lw==
41 1PPYZB/qDAAAPwAAAOcBAAAAAHUAAAAAAAEAAgAE///wBQAAAAV0ZXN0NQIZ8AUAAAAGdGVzdDA1
42 Ahmr0DFr
43 '/*!*/;
44 # at 487
45 #230813 23:16:36 server id 3306 end_log_pos 518 CRC32 0x01b34a63 Xid = 635
46 COMMIT/*!*/;
47 # at 518
48 #230813 23:17:23 server id 3306 end_log_pos 583 CRC32 0x353f4d3c Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
49 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
50 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
51 # at 583
52 #230813 23:17:23 server id 3306 end_log_pos 657 CRC32 0x2c6e0461 Query thread_id=4 exec_time=0 error_code=0
53 SET TIMESTAMP=1691939843/*!*/;
54 BEGIN
55 /*!*/;
56 # at 657
57 # at 730
58 #230813 23:17:23 server id 3306 end_log_pos 788 CRC32 0xc210cf79 Table_map: `testdb`.`test_t` mapped to number 117
59 # at 788
60 #230813 23:17:23 server id 3306 end_log_pos 851 CRC32 0x135bbcab Update_rows: table id 117 flags: STMT_END_F
61
62 BINLOG '
63 A/TYZBPqDAAAOgAAABQDAAAAAHUAAAAAAAEABnRlc3RkYgAGdGVzdF90AAQDD/4BBDAA9wEAec8Q
64 wg==
65 A/TYZB/qDAAAPwAAAFMDAAAAAHUAAAAAAAEAAgAE///wBAAAAAV0ZXN0NAEY8AQAAAAGdGVzdDA0
66 ARirvFsT
67 '/*!*/;
68 # at 851
69 #230813 23:17:23 server id 3306 end_log_pos 882 CRC32 0x0ff1cc57 Xid = 638
70 COMMIT/*!*/;
71 # at 882
72 #230813 23:18:55 server id 3306 end_log_pos 947 CRC32 0x81553f96 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no
73 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
74 # at 947
75 #230813 23:18:55 server id 3306 end_log_pos 1070 CRC32 0xc37e97a1 Query thread_id=4 exec_time=0 error_code=0
76 use `testdb`/*!*/;
77 SET TIMESTAMP=1691939935/*!*/;
78 DROP TABLE `test_t` /* generated by server */
79 /*!*/;
80 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
81 DELIMITER ;
82 # End of log file
83 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
84 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos7 binlog]#
#如上71行 pos：882 可以看出恢复到 2023230813 23:17:23 这个时间点就可以了
恢复方法：
另一台机器装一个数据库，直接恢复单个库
#删除数据库
drop database testdb;
#创建数据库
create database testdb default character set utf8; -- 如果不清楚条件，建议在原库查询
#根据备份恢复
mysql -uroot -prootroot -o testdb < alldb_bak.sql
#如下，恢复后，查不到上午更新的记录
[root@centos7 3306]# mysql -uroot -prootroot testdb -e 'select * from test_t'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+-----+-----+
| id | name | sex | age |
+----+-------+-----+-----+
| 1 | test1 | w | 21 |
| 2 | test2 | m | 22 |
| 3 | test3 | w | 23 |
| 4 | test4 | m | 24 |
| 5 | test5 | w | 25 |
+----+-------+-----+-----+
#通过binlog日志增量恢复表删除之前（也就是上午更新后的数据）
mysqlbinlog --stop-position=882 --database=testdb /mysql/log/3306/binlog/testdb-binlog.000003|mysql -uroot -prootroot testdb
[root@centos7 ~]# mysqlbinlog --stop-position=882 --database=testdb /mysql/log/3306/binlog/testdb-binlog.000003|mysql -uroot -prootroot testdb
mysql: [Warning] Using a password on the command line interface can be insecure.
WARNING: The option --database has been used. It may filter parts of transactions, but will include the GTIDs in any case. If you want to exclude or include transactions, you should use the options --exclude-gtids or --include-gtids, respectively, instead.
[root@centos7 ~]#
验证数据：
[root@centos7 ~]# mysql -uroot -prootroot testdb -e 'select * from test_t'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+--------+-----+-----+
| id | name | sex | age |
+----+--------+-----+-----+
| 1 | test1 | w | 21 |
| 2 | test2 | m | 22 |
| 3 | test3 | w | 23 |
| 4 | test04 | m | 24 |
| 5 | test05 | w | 25 |
+----+--------+-----+-----+
然后备份当前表到源库进行恢复
#恢复库导出表数据
mysqldump -uroot -prootroot testdb test_t > test_t.sql
[root@centos7 ~]# cat test_t.sql
-- MySQL dump 10.13 Distrib 5.7.43, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database: testdb
-- ------------------------------------------------------
-- Server version 5.7.43-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `test_t`
--
DROP TABLE IF EXISTS `test_t`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test_t` (
`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,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `test_t`
--
LOCK TABLES `test_t` WRITE;
/*!40000 ALTER TABLE `test_t` DISABLE KEYS */;
INSERT INTO `test_t` VALUES (1,'test1','w',21),(2,'test2','m',22),(3,'test3','w',23),(4,'test04','m',24),(5,'test05','w',25);
/*!40000 ALTER TABLE `test_t` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2023-08-13 23:51:00
[root@centos7 ~]#
#源库，恢复
mysql -uroot -p testdb < test_t.sql
1.8 MySQL 通过binlog 日志恢复数据-flashback 闪回
场景：
DBA/开发人员，有时候误删除/误更新，特别是批量误操作，这时候生产环境线上业务，影响非常大，这个时间需要快速的回滚。
传统方法：
MySQL 通过binlog 日志恢复数据-恢复误删除表，非常麻烦、费时费力、还需要停机维护
快速的方法：
闪回，通过binlog快速回滚，恢复速度快，不需要停机。（闪回主要是根据数据库的、表的、时间的去做一次过滤，根据它的位置做出一个准确的时间点或者闪回点，通过这个点还原回去，因为数据一直在往表里写，为了确保回滚的SQL不含其他数据，可以根据是不是同一个事务，或者说是找到误删除的行，或者说一些特征来判断我们是要恢复哪些数据，而且回滚的时候只是回滚某张表里面的内容或者和它相关的，要和业务沟通清楚，确定表是否有关联性，比如恢复这张表数据以后有可能导致其他地方的数据异常了）
mysql闪回工具有3类：
-
官方mysqlbinlog：解析出文本的二进制格式，然后通过命令或者正则进行匹配/替换，把语句拿到之后执行就可以恢复。
优点：快速定位，安装和使用都方便，支持离线。用脚本处理binlog的输出，再插回数据库。
缺点：通用性不好，对技术也有一定要求。
-
第三方的mysqlbinlog工具：比官方的工具多了一个闪回功能
-
开源的binlog2sql：兼容性比较好，使用难度小，使用简单，前提是MySQL必须开启。
线上环境的闪回：binlog2sql，离线解析：mysqlbinlog/mysqlbinlog_flashback
1.8.1 利用官方mysqlbinlog 闪回批量误删除操作
前提条件：
需要设置好三个参数
#这三个参数需要配置好
binlog_format=row
binlog_row_image=full
binlog_rows_query_log_events=on
创建测试数据
create database testdb;
use testdb
create table test_t (
`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,
primary key (`id`)
) engine=innodb default charset=utf8;
insert into testdb.test_t(`name`,`sex`,`age`) values
('test1','w',21),
('test2','m',22),
('test3','w',23),
('test4','m',24),
('test5','w',25);
select * from testdb.test_t;
mysql> select * from testdb.test_t;
+----+-------+-----+-----+
| id | name | sex | age |
+----+-------+-----+-----+
| 1 | test1 | w | 21 |
| 2 | test2 | m | 22 |
| 3 | test3 | w | 23 |
| 4 | test4 | m | 24 |
| 5 | test5 | w | 25 |
+----+-------+-----+-----+
5 rows in set (0.00 sec)
模拟误操作
#原计划操作
delete from testdb.test_t where id=2;
#实际操作
delete from testdb.test_t;
mysql> select * from testdb.test_t;
Empty set (0.00 sec)
开始恢复
#锁表，只能读不能写
lock tables testdb.test_t read;
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000001 | 1563 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
show binlog events in 'testdb-binlog.000001';
如上可以看出第30行就是delete的信息
解析binlog
[root@centos7 binlog]# pwd
/mysql/log/3306/binlog
[root@centos7 binlog]# mysqlbinlog --base64-output=decode-rows -v -v testdb-binlog.000001 > 1.txt
[root@centos7 binlog]#
[root@centos7 binlog]# cat -n 1.txt
1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
3 DELIMITER /*!*/;
4 # at 4
5 #230814 21:13:36 server id 3306 end_log_pos 123 CRC32 0xb1fa7209 Start: binlog v 4, server v 5.7.43-log created 230814 21:13:36 at startup
6 # Warning: this binlog is either in use or was not closed properly.
7 ROLLBACK/*!*/;
8 # at 123
9 #230814 21:13:36 server id 3306 end_log_pos 154 CRC32 0xcdcae2cc Previous-GTIDs
10 # [empty]
11 # at 154
12 #230814 21:16:03 server id 3306 end_log_pos 219 CRC32 0x68be5e7e Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
13 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
14 # at 219
15 #230814 21:16:03 server id 3306 end_log_pos 319 CRC32 0x5a05978d Query thread_id=2 exec_time=0 error_code=0
16 SET TIMESTAMP=1692018963/*!*/;
17 SET @@session.pseudo_thread_id=2/*!*/;
18 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
19 SET @@session.sql_mode=1436549120/*!*/;
20 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
21 /*!\C utf8 *//*!*/;
22 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
23 SET @@session.lc_time_names=0/*!*/;
24 SET @@session.collation_database=DEFAULT/*!*/;
25 create database testdb
26 /*!*/;
27 # at 319
28 #230814 21:16:20 server id 3306 end_log_pos 384 CRC32 0xf221e806 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
29 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
30 # at 384
31 #230814 21:16:20 server id 3306 end_log_pos 693 CRC32 0x2bff4609 Query thread_id=2 exec_time=0 error_code=0
32 use `testdb`/*!*/;
33 SET TIMESTAMP=1692018980/*!*/;
34 create table test_t (
35 `id` int(10) unsigned not null auto_increment,
36 `name` varchar(16) not null,
37 `sex` enum('m','w') not null default 'm',
38 `age` tinyint(3) unsigned not null,
39 primary key (`id`)
40 ) engine=innodb default charset=utf8
41 /*!*/;
42 # at 693
43 #230814 21:16:52 server id 3306 end_log_pos 758 CRC32 0x4a525dd6 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes
44 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
45 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
46 # at 758
47 #230814 21:16:52 server id 3306 end_log_pos 832 CRC32 0xaeaa2727 Query thread_id=2 exec_time=0 error_code=0
48 SET TIMESTAMP=1692019012/*!*/;
49 BEGIN
50 /*!*/;
51 # at 832
52 #230814 21:16:52 server id 3306 end_log_pos 997 CRC32 0x26b6b2d9 Rows_query
53 # insert into testdb.test_t(`name`,`sex`,`age`) values
54 # ('test1','w',21),
55 # ('test2','m',22),
56 # ('test3','w',23),
57 # ('test4','m',24),
58 # ('test5','w',25)
59 # at 997
60 #230814 21:16:52 server id 3306 end_log_pos 1055 CRC32 0xe67a5c79 Table_map: `testdb`.`test_t` mapped to number 109
61 # at 1055
62 #230814 21:16:52 server id 3306 end_log_pos 1155 CRC32 0xdfee8b1f Write_rows: table id 109 flags: STMT_END_F
63 ### INSERT INTO `testdb`.`test_t`
64 ### SET
65 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
66 ### @2='test1' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
67 ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
68 ### @4=21 /* TINYINT meta=0 nullable=0 is_null=0 */
69 ### INSERT INTO `testdb`.`test_t`
70 ### SET
71 ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
72 ### @2='test2' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
73 ### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
74 ### @4=22 /* TINYINT meta=0 nullable=0 is_null=0 */
75 ### INSERT INTO `testdb`.`test_t`
76 ### SET
77 ### @1=3 /* INT meta=0 nullable=0 is_null=0 */
78 ### @2='test3' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
79 ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
80 ### @4=23 /* TINYINT meta=0 nullable=0 is_null=0 */
81 ### INSERT INTO `testdb`.`test_t`
82 ### SET
83 ### @1=4 /* INT meta=0 nullable=0 is_null=0 */
84 ### @2='test4' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
85 ### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
86 ### @4=24 /* TINYINT meta=0 nullable=0 is_null=0 */
87 ### INSERT INTO `testdb`.`test_t`
88 ### SET
89 ### @1=5 /* INT meta=0 nullable=0 is_null=0 */
90 ### @2='test5' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
91 ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
92 ### @4=25 /* TINYINT meta=0 nullable=0 is_null=0 */
93 # at 1155
94 #230814 21:16:52 server id 3306 end_log_pos 1186 CRC32 0xc0f96fb7 Xid = 16
95 COMMIT/*!*/;
96 # at 1186
97 #230814 21:18:20 server id 3306 end_log_pos 1251 CRC32 0x2c8bac55 Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=yes
98 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
99 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
100 # at 1251
101 #230814 21:18:20 server id 3306 end_log_pos 1325 CRC32 0x0c6f3c8b Query thread_id=2 exec_time=0 error_code=0
102 SET TIMESTAMP=1692019100/*!*/;
103 BEGIN
104 /*!*/;
105 # at 1325
106 #230814 21:18:20 server id 3306 end_log_pos 1374 CRC32 0x4844b231 Rows_query
107 # delete from testdb.test_t
108 # at 1374
109 #230814 21:18:20 server id 3306 end_log_pos 1432 CRC32 0xaa189dc9 Table_map: `testdb`.`test_t` mapped to number 109
110 # at 1432
111 #230814 21:18:20 server id 3306 end_log_pos 1532 CRC32 0x772e936f Delete_rows: table id 109 flags: STMT_END_F
112 ### DELETE FROM `testdb`.`test_t`
113 ### WHERE
114 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
115 ### @2='test1' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
116 ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
117 ### @4=21 /* TINYINT meta=0 nullable=0 is_null=0 */
118 ### DELETE FROM `testdb`.`test_t`
119 ### WHERE
120 ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
121 ### @2='test2' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
122 ### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
123 ### @4=22 /* TINYINT meta=0 nullable=0 is_null=0 */
124 ### DELETE FROM `testdb`.`test_t`
125 ### WHERE
126 ### @1=3 /* INT meta=0 nullable=0 is_null=0 */
127 ### @2='test3' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
128 ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
129 ### @4=23 /* TINYINT meta=0 nullable=0 is_null=0 */
130 ### DELETE FROM `testdb`.`test_t`
131 ### WHERE
132 ### @1=4 /* INT meta=0 nullable=0 is_null=0 */
133 ### @2='test4' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
134 ### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
135 ### @4=24 /* TINYINT meta=0 nullable=0 is_null=0 */
136 ### DELETE FROM `testdb`.`test_t`
137 ### WHERE
138 ### @1=5 /* INT meta=0 nullable=0 is_null=0 */
139 ### @2='test5' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
140 ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
141 ### @4=25 /* TINYINT meta=0 nullable=0 is_null=0 */
142 # at 1532
143 #230814 21:18:20 server id 3306 end_log_pos 1563 CRC32 0xc4100f36 Xid = 18
144 COMMIT/*!*/;
145 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
146 DELIMITER ;
147 # End of log file
148 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
149 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos7 binlog]#
#如上112~141可以看出做了很多删除行的操作（有几条记录都有几次删除行动作）
使用正则过滤替换处理
#过滤delete部分的语句
[root@centos7 binlog]# sed -n '/### DELETE FROM `testdb`.`test_t`/,/COMMIT/p' 1.txt > test_t-tmp.txt
[root@centos7 binlog]# cat -n test_t-tmp.txt
1 ### DELETE FROM `testdb`.`test_t`
2 ### WHERE
3 ### @1=1 /* INT meta=0 nullable=0 is_null=0 */
4 ### @2='test1' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
5 ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
6 ### @4=21 /* TINYINT meta=0 nullable=0 is_null=0 */
7 ### DELETE FROM `testdb`.`test_t`
8 ### WHERE
9 ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
10 ### @2='test2' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
11 ### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
12 ### @4=22 /* TINYINT meta=0 nullable=0 is_null=0 */
13 ### DELETE FROM `testdb`.`test_t`
14 ### WHERE
15 ### @1=3 /* INT meta=0 nullable=0 is_null=0 */
16 ### @2='test3' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
17 ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
18 ### @4=23 /* TINYINT meta=0 nullable=0 is_null=0 */
19 ### DELETE FROM `testdb`.`test_t`
20 ### WHERE
21 ### @1=4 /* INT meta=0 nullable=0 is_null=0 */
22 ### @2='test4' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
23 ### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
24 ### @4=24 /* TINYINT meta=0 nullable=0 is_null=0 */
25 ### DELETE FROM `testdb`.`test_t`
26 ### WHERE
27 ### @1=5 /* INT meta=0 nullable=0 is_null=0 */
28 ### @2='test5' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
29 ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
30 ### @4=25 /* TINYINT meta=0 nullable=0 is_null=0 */
31 # at 1532
32 #230814 21:18:20 server id 3306 end_log_pos 1563 CRC32 0xc4100f36 Xid = 18
33 COMMIT/*!*/;
[root@centos7 binlog]#
#将delete修改为insert
cat test_t-tmp.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@4.*),/\1;/g' | sed 's/@[1-9]=//g' > recover_test_t.sql
[root@centos7 binlog]# cat test_t-tmp.txt | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/INSERT INTO/g;s/WHERE/SELECT/g;' | sed -r 's/(@4.*),/\1;/g' | sed 's/@[1-9]=//g' > recover_test_t.sql
[root@centos7 binlog]#
[root@centos7 binlog]# cat -n recover_test_t.sql
1 INSERT INTO `testdb`.`test_t`
2 SELECT
3 1 ,
4 'test1' ,
5 2 ,
6 21 ;
7 INSERT INTO `testdb`.`test_t`
8 SELECT
9 2 ,
10 'test2' ,
11 1 ,
12 22 ;
13 INSERT INTO `testdb`.`test_t`
14 SELECT
15 3 ,
16 'test3' ,
17 2 ,
18 23 ;
19 INSERT INTO `testdb`.`test_t`
20 SELECT
21 4 ,
22 'test4' ,
23 1 ,
24 24 ;
25 INSERT INTO `testdb`.`test_t`
26 SELECT
27 5 ,
28 'test5' ,
29 2 ,
30 25 ;
[root@centos7 binlog]#
执行操作
#先解锁表
unlock tables;
#赶紧加写锁，我可以写，别人不能写
lock tables testdb.test_t write;
mysql> source /mysql/log/3306/binlog/recover_test_t.sql
#解锁表
mysql> unlock tables;
#查询
mysql> select * from testdb.test_t;
+----+-------+-----+-----+
| id | name | sex | age |
+----+-------+-----+-----+
| 1 | test1 | w | 21 |
| 2 | test2 | m | 22 |
| 3 | test3 | w | 23 |
| 4 | test4 | m | 24 |
| 5 | test5 | w | 25 |
+----+-------+-----+-----+
5 rows in set (0.00 sec)
1.8.2 利用第三方mysqlbinlog 闪回误删除的表
工具下载地址
https://github.com/58daojia-dba/mysqlbinlog_flashback
#修改权限
chmod 777 mysqlbinlog_flashback
#查看帮助信息
[root@centos7 app]# ./mysqlbinlog_flashback --help
./mysqlbinlog_flashback Ver 3.4 for Linux at x86_64
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Dumps a MySQL binary log in a format usable for viewing or for piping to
the mysql command line client.
Usage: ./mysqlbinlog_flashback [options] log-files
-?, --help Display this help and exit.
--base64-output=name
Determine when the output statements should be
base64-encoded BINLOG statements: 'never' disables it and
works only for binlogs without row-based events;
'decode-rows' decodes row events into commented
pseudo-SQL statements if the --verbose option is also
given; 'auto' prints base64 only when necessary (i.e.,
for row-based events and format description events). If
no --base64-output[=name] option is given at all, the
default is 'auto'.
--bind-address=name IP address to bind to.
--character-sets-dir=name
Directory for character set files.
-d, --database=name List entries for just this database (local log only).
-#, --debug[=name] Output debug log.
--debug-check Check memory and open file usage at exit .
--debug-info Print some debug info at exit.
--default-auth=name Default authentication client-side plugin to use.
-D, --disable-log-bin
Disable binary log. This is useful, if you enabled
--to-last-log and are sending the output to the same
MySQL server. This way you could avoid an endless loop.
You would also like to use it when restoring after a
crash to avoid duplication of the statements you already
have. NOTE: you will need a SUPER privilege to use this
option.
-F, --force-if-open Force if binlog was not closed properly.
(Defaults to on; use --skip-force-if-open to disable.)
-f, --force-read Force reading unknown binlog events.
-H, --hexdump Augment output with hexadecimal and ASCII event dump.
-h, --host=name Get the binlog from server.
-l, --local-load=name
Prepare local temporary files for LOAD DATA INFILE in the
specified directory.
-o, --offset=# Skip the first N entries.
-p, --password[=name]
Password to connect to remote server.
--plugin-dir=name Directory for client-side plugins.
-P, --port=# Port number to use for connection or 0 for default to, in
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/services, built-in default (3306).
--protocol=name The protocol to use for connection (tcp, socket, pipe,
memory).
-R, --read-from-remote-server
Read binary logs from a MySQL server. This is an alias
for read-from-remote-master=BINLOG-DUMP-NON-GTIDS.
-B, --flashback Flashback data to start_postition or start_datetime.
--read-from-remote-master=name
Read binary logs from a MySQL server through the
COM_BINLOG_DUMP or COM_BINLOG_DUMP_GTID commands by
setting the option to either BINLOG-DUMP-NON-GTIDS or
BINLOG-DUMP-GTIDS, respectively. If
--read-from-remote-master=BINLOG-DUMP-GTIDS is combined
with --exclude-gtids, transactions can be filtered out on
the master avoiding unnecessary network traffic.
--raw Requires -R. Output raw binlog data instead of SQL
statements, output is to log files.
-r, --result-file=name
Direct output to a given file. With --raw this is a
prefix for the file names.
--secure-auth Refuse client connecting to server if it uses old
(pre-4.1.1) protocol.
(Defaults to on; use --skip-secure-auth to disable.)
--server-id=# Extract only binlog entries created by the server having
the given id.
--server-id-bits=# Set number of significant bits in server-id
--set-charset=name Add 'SET NAMES character_set' to the output.
-s, --short-form Just show regular queries: no extra info and no row-based
events. This is for testing only, and should not be used
in production systems. If you want to suppress
base64-output, consider using --base64-output=never
instead.
-S, --socket=name The socket file to use for connection.
--start-datetime=name
Start reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
-j, --start-position=#
Start reading the binlog at position N. Applies to the
first binlog passed on the command line.
--stop-datetime=name
Stop reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
--stop-never Wait for more data from the server instead of stopping at
the end of the last log. Implicitly sets --to-last-log
but instead of stopping at the end of the last log it
continues to wait till the server disconnects.
--stop-never-slave-server-id=#
The slave server_id used for --read-from-remote-server
--stop-never.
--connection-server-id=#
The slave server_id used for --read-from-remote-server.
--stop-position=# Stop reading the binlog at position N. Applies to the
last binlog passed on the command line.
-t, --to-last-log Requires -R. Will not stop at the end of the requested
binlog but rather continue printing until the end of the
last binlog of the MySQL server. If you send the output
to the same MySQL server, that may lead to an endless
loop.
-u, --user=name Connect to the remote server as username.
-v, --verbose Reconstruct pseudo-SQL statements out of row events. -v
-v adds comments on column data types.
-V, --version Print version and exit.
--open-files-limit=#
Used to reserve file descriptors for use by this program.
-c, --verify-binlog-checksum
Verify checksum binlog events.
--binlog-row-event-max-size=#
The maximum size of a row-based binary log event in
bytes. Rows will be grouped into events smaller than this
size if possible. This value must be a multiple of 256.
--skip-gtids Do not print Global Transaction Identifier information
(SET GTID_NEXT=... etc).
--include-gtids=name
Print events whose Global Transaction Identifiers were
provided.
--exclude-gtids=name
Print all events but those whose Global Transaction
Identifiers were provided.
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- ----------------------------------------
base64-output (No default value)
bind-address (No default value)
character-sets-dir (No default value)
database (No default value)
debug d:t:o,/tmp/mysqlbinlog.trace
debug-check FALSE
debug-info FALSE
default-auth (No default value)
disable-log-bin FALSE
force-if-open TRUE
force-read FALSE
hexdump FALSE
host (No default value)
local-load (No default value)
offset 0
plugin-dir (No default value)
port 0
read-from-remote-server FALSE
flashback FALSE
read-from-remote-master (No default value)
raw FALSE
result-file (No default value)
secure-auth TRUE
server-id 0
server-id-bits 32
set-charset (No default value)
short-form FALSE
socket (No default value)
start-datetime (No default value)
start-position 4
stop-datetime (No default value)
stop-never FALSE
stop-never-slave-server-id -1
connection-server-id -1
stop-position 18446744073709551615
to-last-log FALSE
user (No default value)
open-files-limit 64
verify-binlog-checksum FALSE
binlog-row-event-max-size 4294967040
skip-gtids FALSE
include-gtids (No default value)
exclude-gtids (No default value)
[root@centos7 app]#
#如上该工具比官方的工具对了这么一个参数
通过mysqlbinlog_flashback 闪回，选项-B
-B, --flashback Flashback data to start_postition or start_datetime.
binlog_format=row，只支持insert/delete/update，不支持ddl操作。可以针对单个event事件，也可以针对多个event事件。
测试
create table test_t1 (
`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,
primary key (`id`)
) engine=innodb default charset=utf8;
insert into testdb.test_t1 (`name`,`sex`,`age`) values
('test1','w',21),
('test2','m',22),
('test3','w',23),
('test4','m',24),
('test5','w',25);
误操作（连错数据库，以为这是测试库，结果把生产这张表做很多增删表）
insert into testdb.test_t1(`name`,`sex`,`age`) values('test100','w',25);
update test_t1 set name='test04' where id=4;
delete from test_t1 where id=3;
恢复过程
#先锁读
lock tables testdb.test_t1 read;
mysql> show master logs;
+----------------------+-----------+
| Log_name | File_size |
+----------------------+-----------+
| testdb-binlog.000001 | 5293 |
+----------------------+-----------+
1 row in set (0.00 sec)
show binlog events in 'testdb-binlog.000001';
#insert开始的pos为 4294
解析binlog
[root@centos7 binlog]# /mysql/app/mysqlbinlog_flashback -v -v --start-position=4294 --stop-position=5293 testdb-binlog.000001 > 1.txt
[root@centos7 binlog]# cat -n 1.txt
1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
2 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
3 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
4 DELIMITER /*!*/;
5 # at 4
6 #230814 21:13:36 server id 3306 end_log_pos 123 CRC32 0xb1fa7209 Start: binlog v 4, server v 5.7.43-log created 230814 21:13:36 at startup
7 # Warning: this binlog is either in use or was not closed properly.
8 ROLLBACK/*!*/;
9 BINLOG '
10 gCjaZA/qDAAAdwAAAHsAAAABAAQANS43LjQzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
11 AAAAAAAAAAAAAAAAAACAKNpkEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
12 AQly+rE=
13 '/*!*/;
14 # at 4294
15 #230814 21:48:52 server id 3306 end_log_pos 4368 CRC32 0x0c67a9c2 Query thread_id=3 exec_time=0 error_code=0
16 SET TIMESTAMP=1692020932/*!*/;
17 SET @@session.pseudo_thread_id=3/*!*/;
18 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
19 SET @@session.sql_mode=1436549120/*!*/;
20 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
21 /*!\C utf8 *//*!*/;
22 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
23 SET @@session.lc_time_names=0/*!*/;
24 SET @@session.collation_database=DEFAULT/*!*/;
25 BEGIN
26 /*!*/;
27 # at 4368
28 #230814 21:48:52 server id 3306 end_log_pos 4463 CRC32 0x3c6a9f0a Rows_query
29 # insert into testdb.test_t1(`name`,`sex`,`age`)
30 # values('test100','w',25)
31 # at 4463
32 #230814 21:48:52 server id 3306 end_log_pos 4522 CRC32 0x41b54e77 Table_map: `testdb`.`test_t1` mapped to number 110
33 # at 4522
34 #230814 21:48:52 server id 3306 end_log_pos 4572 CRC32 0x0383c901 Write_rows: table id 110 flags: STMT_END_F
35
36 BINLOG '
37 xDDaZB3qDAAAXwAAAG8RAACAAEdpbnNlcnQgaW50byB0ZXN0ZGIudGVzdF90MShgbmFtZWAsYHNl
38 eGAsYGFnZWApCnZhbHVlcygndGVzdDEwMCcsJ3cnLDI1KQqfajw=
39 xDDaZBPqDAAAOwAAAKoRAAAAAG4AAAAAAAEABnRlc3RkYgAHdGVzdF90MQAEAw/+AQQwAPcBAHdO
40 tUE=
41 xDDaZB7qDAAAMgAAANwRAAAAAG4AAAAAAAEAAgAE//AGAAAAB3Rlc3QxMDACGQHJgwM=
42 '/*!*/;
43 ### INSERT INTO `testdb`.`test_t1`
44 ### SET
45 ### @1=6 /* INT meta=0 nullable=0 is_null=0 */
46 ### @2='test100' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
47 ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
48 ### @4=25 /* TINYINT meta=0 nullable=0 is_null=0 */
49 # at 4572
50 #230814 21:48:52 server id 3306 end_log_pos 4603 CRC32 0x3a318f4a Xid = 47
51 COMMIT/*!*/;
52 # at 4603
53 #230814 21:48:57 server id 3306 end_log_pos 4668 CRC32 0x166dc78e GTID [commit=no]
54 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
55 # at 4668
56 #230814 21:48:57 server id 3306 end_log_pos 4742 CRC32 0x8aa54c34 Query thread_id=3 exec_time=0 error_code=0
57 SET TIMESTAMP=1692020937/*!*/;
58 BEGIN
59 /*!*/;
60 # at 4742
61 #230814 21:48:57 server id 3306 end_log_pos 4809 CRC32 0xbf61fbff Rows_query
62 # update test_t1 set name='test04' where id=4
63 # at 4809
64 #230814 21:48:57 server id 3306 end_log_pos 4868 CRC32 0xedb93318 Table_map: `testdb`.`test_t1` mapped to number 110
65 # at 4868
66 #230814 21:48:57 server id 3306 end_log_pos 4931 CRC32 0xbbb1e163 Update_rows: table id 110 flags: STMT_END_F
67
68 BINLOG '
69 yTDaZB3qDAAAQwAAAMkSAACAACt1cGRhdGUgdGVzdF90MSBzZXQgbmFtZT0ndGVzdDA0JyB3aGVy
70 ZSBpZD00//thvw==
71 yTDaZBPqDAAAOwAAAAQTAAAAAG4AAAAAAAEABnRlc3RkYgAHdGVzdF90MQAEAw/+AQQwAPcBABgz
72 ue0=
73 yTDaZB/qDAAAPwAAAEMTAAAAAG4AAAAAAAEAAgAE///wBAAAAAV0ZXN0NAEY8AQAAAAGdGVzdDA0
74 ARhj4bG7
75 '/*!*/;
76 ### UPDATE `testdb`.`test_t1`
77 ### WHERE
78 ### @1=4 /* INT meta=0 nullable=0 is_null=0 */
79 ### @2='test4' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
80 ### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
81 ### @4=24 /* TINYINT meta=0 nullable=0 is_null=0 */
82 ### SET
83 ### @1=4 /* INT meta=0 nullable=0 is_null=0 */
84 ### @2='test04' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
85 ### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
86 ### @4=24 /* TINYINT meta=0 nullable=0 is_null=0 */
87 # at 4931
88 #230814 21:48:57 server id 3306 end_log_pos 4962 CRC32 0x792e93c6 Xid = 48
89 COMMIT/*!*/;
90 # at 4962
91 #230814 21:49:01 server id 3306 end_log_pos 5027 CRC32 0x41a44941 GTID [commit=no]
92 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
93 # at 5027
94 #230814 21:49:01 server id 3306 end_log_pos 5101 CRC32 0xfac99bc5 Query thread_id=3 exec_time=0 error_code=0
95 SET TIMESTAMP=1692020941/*!*/;
96 BEGIN
97 /*!*/;
98 # at 5101
99 #230814 21:49:01 server id 3306 end_log_pos 5155 CRC32 0xe8e76677 Rows_query
100 # delete from test_t1 where id=3
101 # at 5155
102 #230814 21:49:01 server id 3306 end_log_pos 5214 CRC32 0x5f34f100 Table_map: `testdb`.`test_t1` mapped to number 110
103 # at 5214
104 #230814 21:49:01 server id 3306 end_log_pos 5262 CRC32 0x56737b4a Delete_rows: table id 110 flags: STMT_END_F
105
106 BINLOG '
107 zTDaZB3qDAAANgAAACMUAACAAB5kZWxldGUgZnJvbSB0ZXN0X3QxIHdoZXJlIGlkPTN3Zufo
108 zTDaZBPqDAAAOwAAAF4UAAAAAG4AAAAAAAEABnRlc3RkYgAHdGVzdF90MQAEAw/+AQQwAPcBAADx
109 NF8=
110 zTDaZCDqDAAAMAAAAI4UAAAAAG4AAAAAAAEAAgAE//ADAAAABXRlc3QzAhdKe3NW
111 '/*!*/;
112 ### DELETE FROM `testdb`.`test_t1`
113 ### WHERE
114 ### @1=3 /* INT meta=0 nullable=0 is_null=0 */
115 ### @2='test3' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
116 ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
117 ### @4=23 /* TINYINT meta=0 nullable=0 is_null=0 */
118 # at 5262
119 #230814 21:49:01 server id 3306 end_log_pos 5293 CRC32 0x3de852a0 Xid = 49
120 COMMIT/*!*/;
121 DELIMITER ;
122 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */;
123 DELIMITER ;
124 # End of log file
125 ROLLBACK /* added by mysqlbinlog */;
126 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
127 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos7 binlog]#
使用 -B选项
[root@centos7 binlog]# /mysql/app/mysqlbinlog_flashback -B -v -v --start-position=4294 --stop-position=5293 testdb-binlog.000001 > 1.txt
[root@centos7 binlog]# cat -n 1.txt
1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
2 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
3 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
4 DELIMITER /*!*/;
5 #230814 21:13:36 server id 3306 end_log_pos 123 CRC32 0xb1fa7209 Start: binlog v 4, server v 5.7.43-log created 230814 21:13:36 at startup
6 # Warning: this binlog is either in use or was not closed properly.
7 ROLLBACK/*!*/;
8 BINLOG '
9 gCjaZA/qDAAAdwAAAHsAAAABAAQANS43LjQzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
10 AAAAAAAAAAAAAAAAAACAKNpkEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
11 AQly+rE=
12 '/*!*/;
13 #230814 21:48:52 server id 3306 end_log_pos 4463 CRC32 0x3c6a9f0a Rows_query
14 # insert into testdb.test_t1(`name`,`sex`,`age`)
15 # values('test100','w',25)
16 #230814 21:48:52 server id 3306 end_log_pos 4522 CRC32 0x41b54e77 Table_map: `testdb`.`test_t1` mapped to number 110
17 #230814 21:48:52 server id 3306 end_log_pos 4603 CRC32 0x3a318f4a Xid = 47
18 COMMIT/*!*/;
19 #230814 21:48:57 server id 3306 end_log_pos 4668 CRC32 0x166dc78e GTID [commit=no]
20 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
21 #230814 21:48:57 server id 3306 end_log_pos 4809 CRC32 0xbf61fbff Rows_query
22 # update test_t1 set name='test04' where id=4
23 #230814 21:48:57 server id 3306 end_log_pos 4868 CRC32 0xedb93318 Table_map: `testdb`.`test_t1` mapped to number 110
24 #230814 21:48:57 server id 3306 end_log_pos 4962 CRC32 0x792e93c6 Xid = 48
25 COMMIT/*!*/;
26 #230814 21:49:01 server id 3306 end_log_pos 5027 CRC32 0x41a44941 GTID [commit=no]
27 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
28 #230814 21:49:01 server id 3306 end_log_pos 5155 CRC32 0xe8e76677 Rows_query
29 # delete from test_t1 where id=3
30 #230814 21:49:01 server id 3306 end_log_pos 5214 CRC32 0x5f34f100 Table_map: `testdb`.`test_t1` mapped to number 110
31 #230814 21:49:01 server id 3306 end_log_pos 5293 CRC32 0x3de852a0 Xid = 49
32 COMMIT/*!*/;
33 #230814 21:49:01 server id 3306 end_log_pos 5262 CRC32 0x56737b4a Delete_rows: table id 110 flags: STMT_END_F
34
35 BINLOG '
36 zTDaZB3qDAAANgAAACMUAACAAB5kZWxldGUgZnJvbSB0ZXN0X3QxIHdoZXJlIGlkPTN3Zufo
37 zTDaZBPqDAAAOwAAAF4UAAAAAG4AAAAAAAEABnRlc3RkYgAHdGVzdF90MQAEAw/+AQQwAPcBAADx
38 NF8=
39 zTDaZB7qDAAAMAAAAI4UAAAAAG4AAAAAAAEAAgAE//ADAAAABXRlc3QzAhdKe3NW
40 '/*!*/;
41 ### INSERT INTO `testdb`.`test_t1`
42 ### SET
43 ### @1=3 /* INT meta=0 nullable=0 is_null=0 */
44 ### @2='test3' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
45 ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
46 ### @4=23 /* TINYINT meta=0 nullable=0 is_null=0 */
47 #230814 21:48:57 server id 3306 end_log_pos 4931 CRC32 0xbbb1e163 Update_rows: table id 110 flags: STMT_END_F
48
49 BINLOG '
50 yTDaZB3qDAAAQwAAAMkSAACAACt1cGRhdGUgdGVzdF90MSBzZXQgbmFtZT0ndGVzdDA0JyB3aGVy
51 ZSBpZD00//thvw==
52 yTDaZBPqDAAAOwAAAAQTAAAAAG4AAAAAAAEABnRlc3RkYgAHdGVzdF90MQAEAw/+AQQwAPcBABgz
53 ue0=
54 yTDaZB/qDAAAPwAAAEMTAAAAAG4AAAAAAAEAAgAE///wBAAAAAZ0ZXN0MDQBGPAEAAAABXRlc3Q0
55 ARhj4bG7
56 '/*!*/;
57 ### UPDATE `testdb`.`test_t1`
58 ### WHERE
59 ### @1=4 /* INT meta=0 nullable=0 is_null=0 */
60 ### @2='test04' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
61 ### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
62 ### @4=24 /* TINYINT meta=0 nullable=0 is_null=0 */
63 ### SET
64 ### @1=4 /* INT meta=0 nullable=0 is_null=0 */
65 ### @2='test4' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
66 ### @3=1 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
67 ### @4=24 /* TINYINT meta=0 nullable=0 is_null=0 */
68 #230814 21:48:52 server id 3306 end_log_pos 4572 CRC32 0x0383c901 Write_rows: table id 110 flags: STMT_END_F
69
70 BINLOG '
71 xDDaZB3qDAAAXwAAAG8RAACAAEdpbnNlcnQgaW50byB0ZXN0ZGIudGVzdF90MShgbmFtZWAsYHNl
72 eGAsYGFnZWApCnZhbHVlcygndGVzdDEwMCcsJ3cnLDI1KQqfajw=
73 xDDaZBPqDAAAOwAAAKoRAAAAAG4AAAAAAAEABnRlc3RkYgAHdGVzdF90MQAEAw/+AQQwAPcBAHdO
74 tUE=
75 xDDaZCDqDAAAMgAAANwRAAAAAG4AAAAAAAEAAgAE//AGAAAAB3Rlc3QxMDACGQHJgwM=
76 '/*!*/;
77 ### DELETE FROM `testdb`.`test_t1`
78 ### WHERE
79 ### @1=6 /* INT meta=0 nullable=0 is_null=0 */
80 ### @2='test100' /* VARSTRING(48) meta=48 nullable=0 is_null=0 */
81 ### @3=2 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */
82 ### @4=25 /* TINYINT meta=0 nullable=0 is_null=0 */
83 DELIMITER ;
84 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog *//*!*/;
85 DELIMITER ;
86 # End of log file
87 ROLLBACK /* added by mysqlbinlog */;
88 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
89 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos7 binlog]#
#如上
14~15行记录原来的insert信息
22行记录原来的update信息
29行记录原来的delete信息
41~46将29行的delete变为了insert
57~67将22行的update再改回去了
77~82将14~15的insert变为了delete
#解锁表
mysql> unlock tables;
#注意87行是一个 回滚操作，需要手动改为commit，修改好后执行文件进行恢复
[root@centos7 binlog]# mysql -uroot -prootroot testdb < 1.txt
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@centos7 binlog]#
#检查
mysql> select * from testdb.test_t1;
+----+-------+-----+-----+
| id | name | sex | age |
+----+-------+-----+-----+
| 1 | test1 | w | 21 |
| 2 | test2 | m | 22 |
| 3 | test3 | w | 23 |
| 4 | test4 | m | 24 |
| 5 | test5 | w | 25 |
+----+-------+-----+-----+
5 rows in set (0.01 sec)
说明：
-B 反转，闪回，如果是insert ，就对应delete，如果是delete，对应insert，如果是update，就将set和where调换
1.8.3 利用开源binlog2sql 闪回误删除的表
提取SQL，生成回滚SQL
使用前提：binlog_format=row，binlog_row_image=full
下载安装：https://github.com/danfengcao/binlog2sql
1.8.3.1 安装步骤：
#文件结构
[root@centos7 app]# pwd
/mysql/app
[root@centos7 app]# tree binlog2sql
binlog2sql
|-- binlog2sql
| |-- binlog2sql.py
| |-- binlog2sql_util.py
| |-- binlog2sql_util.pyc
| `-- __init__.py
`-- binlog2sql_dependencies
|-- argparse-1.4.0-py2.py3-none-any.whl
|-- linecache2-1.0.0-py2.py3-none-any.whl
|-- mysql-replication-0.9.tar.gz
|-- pip-9.0.1.tar.gz
|-- PyMySQL-0.7.8-py2-none-any.whl
|-- setuptools-0.6c11.tar.gz
|-- six-1.10.0-py2.py3-none-any.whl
|-- traceback2-1.4.0-py2.py3-none-any.whl
|-- unittest2-1.1.0-py2.py3-none-any.whl
`-- wheel-0.24.0-py2.py3-none-any.whl
2 directories, 14 files
[root@centos7 app]# cd binlog2sql/binlog2sql_dependencies/
[root@centos7 binlog2sql_dependencies]# tar xvf setuptools-0.6c11.tar.gz
[root@centos7 binlog2sql_dependencies]# cd setuptools-0.6c11
[root@centos7 setuptools-0.6c11]# python setup.py install
......
Installed /usr/lib/python2.7/site-packages/setuptools-0.6c11-py2.7.egg
Processing dependencies for setuptools==0.6c11
Finished processing dependencies for setuptools==0.6c11
[root@centos7 setuptools-0.6c11]# cd ..
[root@centos7 binlog2sql_dependencies]# tar xvf pip-9.0.1.tar.gz
[root@centos7 binlog2sql_dependencies]# cd pip-9.0.1
[root@centos7 pip-9.0.1]# python setup.py install
......
Installed /usr/lib/python2.7/site-packages/pip-9.0.1-py2.7.egg
Processing dependencies for pip==9.0.1
Finished processing dependencies for pip==9.0.1
[root@centos7 pip-9.0.1]# cd ..
[root@centos7 binlog2sql_dependencies]# pip install *.whl mysql-replication-0.9.tar.gz
Processing ./mysql-replication-0.9.tar.gz
Processing ./argparse-1.4.0-py2.py3-none-any.whl
Processing ./linecache2-1.0.0-py2.py3-none-any.whl
Processing ./PyMySQL-0.7.8-py2-none-any.whl
Processing ./six-1.10.0-py2.py3-none-any.whl
Processing ./traceback2-1.4.0-py2.py3-none-any.whl
Processing ./unittest2-1.1.0-py2.py3-none-any.whl
Processing ./wheel-0.24.0-py2.py3-none-any.whl
Installing collected packages: argparse, linecache2, PyMySQL, six, traceback2, unittest2, wheel, mysql-replication
Found existing installation: six 1.9.0
Uninstalling six-1.9.0:
Successfully uninstalled six-1.9.0
Running setup.py install for mysql-replication ... done
Successfully installed PyMySQL-0.7.8 argparse-1.4.0 linecache2-1.0.0 mysql-replication-0.9 six-1.10.0 traceback2-1.4.0 unittest2-1.1.0 wheel-0.24.0
[root@centos7 binlog2sql_dependencies]#
#安装完成后，设置环境变量，别名：
alias binlog2sql='python /mysql/app/binlog2sql/binlog2sql/binlog2sql.py'
帮助信息
[root@centos7 ~]# binlog2sql --help
usage: binlog2sql.py [-h HOST] [-u USER] [-p PASSWORD] [-P PORT]
[--start-file STARTFILE] [--start-position STARTPOS]
[--stop-file ENDFILE] [--stop-position ENDPOS]
[--start-datetime STARTTIME] [--stop-datetime STOPTIME]
[--stop-never] [--help] [-d [DATABASES [DATABASES ...]]]
[-t [TABLES [TABLES ...]]] [-K] [-B]
Parse MySQL binlog to SQL you want
optional arguments:
--stop-never Wait for more data from the server. default: stop
replicate at the last binlog when you start binlog2sql
--help help infomation
-K, --no-primary-key Generate insert sql without primary key if exists
-B, --flashback Flashback data to start_postition of start_file
connect setting:
-h HOST, --host HOST Host the MySQL database server located
-u USER, --user USER MySQL Username to log in as
-p PASSWORD, --password PASSWORD
MySQL Password to use
-P PORT, --port PORT MySQL port to use
range filter:
--start-file STARTFILE
Start binlog file to be parsed
--start-position STARTPOS, --start-pos STARTPOS
Start position of the --start-file
--stop-file ENDFILE, --end-file ENDFILE
Stop binlog file to be parsed. default: '--start-file'
--stop-position ENDPOS, --end-pos ENDPOS
Stop position of --stop-file. default: latest position
of '--stop-file'
--start-datetime STARTTIME
Start reading the binlog at first event having a
datetime equal or posterior to the argument; the
argument must be a date and time in the local time
zone, in any format accepted by the MySQL server for
DATETIME and TIMESTAMP types, for example: 2004-12-25
11:25:56 (you should probably use quotes for your
shell to set it properly).
--stop-datetime STOPTIME
Stop reading the binlog at first event having a
datetime equal or posterior to the argument; the
argument must be a date and time in the local time
zone, in any format accepted by the MySQL server for
DATETIME and TIMESTAMP types, for example: 2004-12-25
11:25:56 (you should probably use quotes for your
shell to set it properly).
schema filter:
-d [DATABASES [DATABASES ...]], --databases [DATABASES [DATABASES ...]]
dbs you want to process
-t [TABLES [TABLES ...]], --tables [TABLES [TABLES ...]]
tables you want to process
[root@centos7 ~]#
1.8.3.2 案例：
#创建测试表
create table test_t2 (
`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,
primary key (`id`)
) engine=innodb default charset=utf8;
insert into testdb.test_t2(`name`,`sex`,`age`) values
('test1','w',21),
('test2','m',22),
('test3','w',23),
('test4','m',24),
('test5','w',25);
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2023-08-14 22:44:56 |
+---------------------+
1 row in set (0.00 sec)
误操作
mysql> delete from testdb.test_t2;
Query OK, 5 rows affected (0.01 sec)
mysql> select * from testdb.test_t2;
Empty set (0.00 sec)
恢复
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| testdb-binlog.000001 | 7606 | | | |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2023-08-14 22:46:43 |
+---------------------+
1 row in set (0.00 sec)
5293
show binlog events in 'testdb-binlog.000001' from 6357;
先找到已经被删除的数据：
方法1：按时间点
#2023-08-14 22:44:56 到 2023-08-14 22:46:43
binlog2sql -h192.168.1.10 -P3306 -uroot -proot -dtestdb -t test_t2 --start-file='testdb-binlog.000001' --stop-file='testdb-binlog.000001' --start-datetime='2023-08-14 22:44:56' --stop-datetime='2023-08-14 22:46:43'
[root@centos7 ~]# binlog2sql -h192.168.1.10 -P3306 -uroot -proot -dtestdb -t test_t2 --start-file='testdb-binlog.000001' --stop-file='testdb-binlog.000001' --start-datetime='2023-08-14 22:44:56' --stop-datetime='2023-08-14 22:46:43'
DELETE FROM `testdb`.`test_t2` WHERE `age`=21 AND `sex`='w' AND `id`=1 AND `name`='test1' LIMIT 1; #start 7227 end 7575 time 2023-08-14 22:45:53
DELETE FROM `testdb`.`test_t2` WHERE `age`=22 AND `sex`='m' AND `id`=2 AND `name`='test2' LIMIT 1; #start 7227 end 7575 time 2023-08-14 22:45:53
DELETE FROM `testdb`.`test_t2` WHERE `age`=23 AND `sex`='w' AND `id`=3 AND `name`='test3' LIMIT 1; #start 7227 end 7575 time 2023-08-14 22:45:53
DELETE FROM `testdb`.`test_t2` WHERE `age`=24 AND `sex`='m' AND `id`=4 AND `name`='test4' LIMIT 1; #start 7227 end 7575 time 2023-08-14 22:45:53
DELETE FROM `testdb`.`test_t2` WHERE `age`=25 AND `sex`='w' AND `id`=5 AND `name`='test5' LIMIT 1; #start 7227 end 7575 time 2023-08-14 22:45:53
[root@centos7 ~]#
方法2：按pos
binlog2sql -h192.168.1.10 -P3306 -uroot -proot -dtestdb -t test_t2 --start-file='testdb-binlog.000001' --stop-file='testdb-binlog.000001' --start-position=7292 --stop-position=7606
[root@centos7 ~]# binlog2sql -h192.168.1.10 -P3306 -uroot -proot -dtestdb -t test_t2 --start-file='testdb-binlog.000001' --stop-file='testdb-binlog.000001' --start-position=7292 --stop-position=7606
DELETE FROM `testdb`.`test_t2` WHERE `age`=21 AND `sex`='w' AND `id`=1 AND `name`='test1' LIMIT 1; #start 7292 end 7575 time 2023-08-14 22:45:53
DELETE FROM `testdb`.`test_t2` WHERE `age`=22 AND `sex`='m' AND `id`=2 AND `name`='test2' LIMIT 1; #start 7292 end 7575 time 2023-08-14 22:45:53
DELETE FROM `testdb`.`test_t2` WHERE `age`=23 AND `sex`='w' AND `id`=3 AND `name`='test3' LIMIT 1; #start 7292 end 7575 time 2023-08-14 22:45:53
DELETE FROM `testdb`.`test_t2` WHERE `age`=24 AND `sex`='m' AND `id`=4 AND `name`='test4' LIMIT 1; #start 7292 end 7575 time 2023-08-14 22:45:53
DELETE FROM `testdb`.`test_t2` WHERE `age`=25 AND `sex`='w' AND `id`=5 AND `name`='test5' LIMIT 1; #start 7292 end 7575 time 2023-08-14 22:45:53
[root@centos7 ~]#
通过闪回恢复数据
方法1：按时间点
binlog2sql -h192.168.1.10 -P3306 -uroot -proot -dtestdb -t test_t2 --start-file='testdb-binlog.000001' --stop-file='testdb-binlog.000001' --start-datetime='2023-08-14 22:44:56' --stop-datetime='2023-08-14 22:46:43' --flashback > test_t2.txt
[root@centos7 ~]# binlog2sql -h192.168.1.10 -P3306 -uroot -proot -dtestdb -t test_t2 --start-file='testdb-binlog.000001' --stop-file='testdb-binlog.000001' --start-datetime='2023-08-14 22:44:56' --stop-datetime='2023-08-14 22:46:43' --flashback > test_t2.txt
[root@centos7 ~]# cat test_t2.txt
INSERT INTO `testdb`.`test_t2`(`age`, `sex`, `id`, `name`) VALUES (25, 'w', 5, 'test5'); #start 7227 end 7575 time 2023-08-14 22:45:53
INSERT INTO `testdb`.`test_t2`(`age`, `sex`, `id`, `name`) VALUES (24, 'm', 4, 'test4'); #start 7227 end 7575 time 2023-08-14 22:45:53
INSERT INTO `testdb`.`test_t2`(`age`, `sex`, `id`, `name`) VALUES (23, 'w', 3, 'test3'); #start 7227 end 7575 time 2023-08-14 22:45:53
INSERT INTO `testdb`.`test_t2`(`age`, `sex`, `id`, `name`) VALUES (22, 'm', 2, 'test2'); #start 7227 end 7575 time 2023-08-14 22:45:53
INSERT INTO `testdb`.`test_t2`(`age`, `sex`, `id`, `name`) VALUES (21, 'w', 1, 'test1'); #start 7227 end 7575 time 2023-08-14 22:45:53
[root@centos7 ~]#
方法2：按日志位置
binlog2sql -h192.168.1.10 -P3306 -uroot -proot -dtestdb -t test_t2 --start-file='testdb-binlog.000001' --stop-file='testdb-binlog.000001' --start-position=7292 --stop-position=7606 --flashback > test_t2.txt
[root@centos7 ~]# binlog2sql -h192.168.1.10 -P3306 -uroot -proot -dtestdb -t test_t2 --start-file='testdb-binlog.000001' --stop-file='testdb-binlog.000001' --start-position=7292 --stop-position=7606 --flashback > test_t2.txt
[root@centos7 ~]# cat test_t2.txt
INSERT INTO `testdb`.`test_t2`(`age`, `sex`, `id`, `name`) VALUES (25, 'w', 5, 'test5'); #start 7292 end 7575 time 2023-08-14 22:45:53
INSERT INTO `testdb`.`test_t2`(`age`, `sex`, `id`, `name`) VALUES (24, 'm', 4, 'test4'); #start 7292 end 7575 time 2023-08-14 22:45:53
INSERT INTO `testdb`.`test_t2`(`age`, `sex`, `id`, `name`) VALUES (23, 'w', 3, 'test3'); #start 7292 end 7575 time 2023-08-14 22:45:53
INSERT INTO `testdb`.`test_t2`(`age`, `sex`, `id`, `name`) VALUES (22, 'm', 2, 'test2'); #start 7292 end 7575 time 2023-08-14 22:45:53
INSERT INTO `testdb`.`test_t2`(`age`, `sex`, `id`, `name`) VALUES (21, 'w', 1, 'test1'); #start 7292 end 7575 time 2023-08-14 22:45:53
[root@centos7 ~]#
恢复
mysql -uroot -prootroot testdb < test_t2.txt
#查看数据
mysql> select * from testdb.test_t2;
+----+-------+-----+-----+
| id | name | sex | age |
+----+-------+-----+-----+
| 1 | test1 | w | 21 |
| 2 | test2 | m | 22 |
| 3 | test3 | w | 23 |
| 4 | test4 | m | 24 |
| 5 | test5 | w | 25 |
+----+-------+-----+-----+
5 rows in set (0.00 sec)
1.8.4 binlog_rollback
下载工具
https://github.com/GoDannyLai/binlog_rollback/releases/download/V2.1/binlog_rollback-V2.1-linux64.zip
#帮助信息
[root@centos7 app]# ./binlog_rollback --help
binlog_rollback V2.1 By laijunshou@gmail.com
parse mysql binlog to generate analysis report, redo or rollback sql.
two work mode:
read binlog from master, work as a fake slave: ./binlog_rollback -m repl opts...
read binlog from local filesystem: ./binlog_rollback -m file opts... mysql-bin.000010
usage example:
generate redo sql and analysis report:
./binlog_rollback -m repl -w 2sql -M mysql -t 4 -mid 3331 -H 127.0.0.1 -P 3306 -u xxx -p xxx -dbs db1,db2 -tbs tb1,tb2 -sbin mysql-bin.000556 -spos 107 -ebin mysql-bin.000559 -epos 4 -e -f -r 20 -k -b 100 -l 10 -o /home/apps/tmp -dj tbs_all_def.json
generate rollback sql and analysis report:
./binlog_rollback -m file -w rollback -M mysql -t 4 -H 127.0.0.1 -P 3306 -u xxx -p xxx -dbs db1,db2 -tbs tb1,tb2 -tbs tb1,tb2 -sdt "2017-09-28 13:00:00" -edt "2017-09-28 16:00:00" -e -f -r 20 -k -b 100 -l 10 -o /home/apps/tmp -dj tbs_all_def.json /apps/dbdata/mysqldata_3306/log/mysql-bin.000556
only generate analysis report:
./binlog_rollback -m repl -w stats -M mysql -H 127.0.0.1 -P 3306 -u xxx -p xxx -dbs db1,db2 -tbs tb1,tb2 -sbin mysql-bin.000556 -spos 107 -i 20 -b 100 -l 10 -o /home/apps/tmp
suported options:
-C works with -w='stats', keep analyzing transations to last binlog for -m=file, and keep analyzing for -m=repl
-H string
master host, DONOT need to specify when -w=stats. if mode is file, it can be slave or other mysql contains same schema and table structure, not only master. default 127.0.0.1 (default "127.0.0.1")
-I for insert statement when -wtype=2sql, ignore primary key
-M string
valid options are: mysql,mariadb. server of binlog, mysql or mariadb, default mysql (default "mysql")
-P uint
master port, default 3306. DONOT need to specify when -w=stats (default 3306)
-S string
mysql socket file
-U prefer to use unique key instead of primary key to build where condition for delete/update sql
-a Works with -w=2sql|rollback. for update sql, include unchanged columns. for update and delete, use all columns to build where condition.
default false, this is, use changed columns to build set part, use primary/unique key to build where condition
-b int
transaction with affected rows greater or equal to this value is considerated as big transaction. Valid values range from 10 to 30000, default 500 (default 500)
-d Works with -w=2sql|rollback. Prefix table name with database name in sql, ex: insert into db1.tb1 (x1, x1) values (y1, y1). Default true (default true)
-dbs string
only parse database which match any of these regular expressions. The regular expression should be in lower case because database name is translated into lower case and then matched against it.
Multi regular expressions is seperated by comma, default parse all databases. Useless when -w=stats
-dj string
dump table structure to this file. default tblDef.json (default "tblDef.json")
-e Works with -w=2sql|rollback. Print database/table/datetime/binlogposition...info on the line before sql, default false
-ebin string
binlog file to stop reading
-edt string
Stop reading the binlog at first event having a datetime equal or posterior to the argument, it should be like this: "2004-12-25 11:25:56"
-epos uint
Stop reading the binlog at position
-f Works with -w=2sql|rollback. one file for one table if true, else one file for all tables. default false. Attention, always one file for one binlog
-i int
works with -w='stats', print stats info each PrintInterval. Valid values range from 1 to 600, default 30 (default 30)
-ies string
for sql which is error to parsed and matched by this regular expression, just print error info, skip it and continue parsing, otherwise stop parsing and exit.
The regular expression should be in lower case, because sql is translated into lower case and then matched against it. (default "^create definer.+trigger")
-k Works with -w=2sql|rollback. wrap result statements with 'begin...commit|rollback'
-l int
transaction with duration greater or equal to this value is considerated as long transaction. Valid values range from 1 to 3600, default 300 (default 300)
-m string
valid options are: repl,file. repl: as a slave to get binlogs from master. file: get binlogs from local filesystem. default file (default "file")
-mid uint
works with -m=repl, this program replicates from master as slave to read binlogs. Must set this server id unique from other slaves, default 3320 (default 3320)
-o string
result output dir, default current work dir. Attension, result files could be large, set it to a dir with large free space
-oj
Only use table structure from -rj, do not get or merge table struct from mysql
-ors
for mysql>=5.6.2 and binlog_rows_query_log_events=on, if set, output original sql. default false
-p string
mysql user password. DONOT need to specify when -w=stats
-r int
Works with -w=2sql|rollback. rows for each insert sql. Valid values range from 1 to 500, default 30 (default 30)
-rj string
Works with -w=2sql|rollback, read table structure from this file and merge from mysql
-sbin string
binlog file to start reading
-sdt string
Start reading the binlog at first event having a datetime equal or posterior to the argument, it should be like this: "2004-12-25 11:25:56"
-spos uint
start reading the binlog at position
-sql string
valid options are: insert,update,delete. only parse these types of sql, comma seperated, valid types are: insert, update, delete; default is all(insert,update,delete)
-stsql
when -w=2sql, also parse plain sql and write into result file even if binlog_format is not row. default false
-t uint
Works with -w=2sql|rollback. threads to run, default 4 (default 2)
-tbs string
only parse table which match any of these regular expressions.The regular expression should be in lower case because database name is translated into lower case and then matched against it.
Multi regular expressions is seperated by comma, default parse all tables. Useless when -w=stats
-tl string
time location to parse timestamp/datetime column in binlog, such as Asia/Shanghai. default Local (default "Local")
-u string
mysql user. DONOT need to specify when -w=stats
-v print version
-w string
valid options are: tbldef,stats,2sql,rollback. tbldef: only get table definition structure; 2sql: convert binlog to sqls, rollback: generate rollback sqls, stats: analyze transactions. default: stats (default "stats")
[root@centos7 app]#
参数说明：
- -m：file: get binlogs from local filesystem
- -w：rollback: generate rollback sqls
- -M：database type mysql or mariadb
- -t：threads to run
- -H：host
- -P：port
- -u：user
- -p：password
- -dbs：database
- -tbs：tablename
- -sdt：start time
- -edt：end time
- -sql：解析的sql类型，insert,update,delete
- -d：Prefix table name with database name in sql
- -e：Print database/table/datetime/binlogposition…info on the line before sql
- -f：one file for one table if true
- -r：rows for each insert sql
- -b：transaction with affected rows greater or equal to this value is considerated as big transaction
- -l：transaction with duration greater or equal to this value is considerated as long transaction
- -o：result output dir
- -dj：dump table structure to this file
案例
create database testdb;
use testdb
create table test_t (id int,name varchar(100));
insert into test_t values(1,'test1');
insert into test_t values(2,'test2');
insert into test_t values(3,'test3');
insert into test_t values(4,'test4');
insert into test_t values(5,'test5');
insert into test_t values(6,'test6');
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2023-08-16 20:21:01 |
+---------------------+
1 row in set (0.00 sec)
delete from test_t;
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2023-08-16 20:21:19 |
+---------------------+
1 row in set (0.00 sec)
show binlog events in 'testdb-binlog.000001';
mysql> show binlog events in 'testdb-binlog.000001';
+----------------------+------+----------------+-----------+-------------+--------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------------+------+----------------+-----------+-------------+--------------------------------------------------------------+
| testdb-binlog.000001 | 4 | Format_desc | 3306 | 123 | Server ver: 5.7.43-log, Binlog ver: 4 |
| testdb-binlog.000001 | 123 | Previous_gtids | 3306 | 154 | |
| testdb-binlog.000001 | 154 | Anonymous_Gtid | 3306 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| testdb-binlog.000001 | 219 | Query | 3306 | 319 | create database testdb |
| testdb-binlog.000001 | 319 | Anonymous_Gtid | 3306 | 384 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| testdb-binlog.000001 | 384 | Query | 3306 | 508 | use `testdb`; create table test_t (id int,name varchar(100)) |
| testdb-binlog.000001 | 508 | Anonymous_Gtid | 3306 | 573 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| testdb-binlog.000001 | 573 | Query | 3306 | 647 | BEGIN |
| testdb-binlog.000001 | 647 | Rows_query | 3306 | 707 | # insert into test_t values(1,'test1') |
| testdb-binlog.000001 | 707 | Table_map | 3306 | 761 | table_id: 109 (testdb.test_t) |
| testdb-binlog.000001 | 761 | Write_rows | 3306 | 808 | table_id: 109 flags: STMT_END_F |
| testdb-binlog.000001 | 808 | Xid | 3306 | 839 | COMMIT /* xid=11 */ |
| testdb-binlog.000001 | 839 | Anonymous_Gtid | 3306 | 904 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| testdb-binlog.000001 | 904 | Query | 3306 | 978 | BEGIN |
| testdb-binlog.000001 | 978 | Rows_query | 3306 | 1038 | # insert into test_t values(2,'test2') |
| testdb-binlog.000001 | 1038 | Table_map | 3306 | 1092 | table_id: 109 (testdb.test_t) |
| testdb-binlog.000001 | 1092 | Write_rows | 3306 | 1139 | table_id: 109 flags: STMT_END_F |
| testdb-binlog.000001 | 1139 | Xid | 3306 | 1170 | COMMIT /* xid=12 */ |
| testdb-binlog.000001 | 1170 | Anonymous_Gtid | 3306 | 1235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| testdb-binlog.000001 | 1235 | Query | 3306 | 1309 | BEGIN |
| testdb-binlog.000001 | 1309 | Rows_query | 3306 | 1369 | # insert into test_t values(3,'test3') |
| testdb-binlog.000001 | 1369 | Table_map | 3306 | 1423 | table_id: 109 (testdb.test_t) |
| testdb-binlog.000001 | 1423 | Write_rows | 3306 | 1470 | table_id: 109 flags: STMT_END_F |
| testdb-binlog.000001 | 1470 | Xid | 3306 | 1501 | COMMIT /* xid=13 */ |
| testdb-binlog.000001 | 1501 | Anonymous_Gtid | 3306 | 1566 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| testdb-binlog.000001 | 1566 | Query | 3306 | 1640 | BEGIN |
| testdb-binlog.000001 | 1640 | Rows_query | 3306 | 1700 | # insert into test_t values(4,'test4') |
| testdb-binlog.000001 | 1700 | Table_map | 3306 | 1754 | table_id: 109 (testdb.test_t) |
| testdb-binlog.000001 | 1754 | Write_rows | 3306 | 1801 | table_id: 109 flags: STMT_END_F |
| testdb-binlog.000001 | 1801 | Xid | 3306 | 1832 | COMMIT /* xid=14 */ |
| testdb-binlog.000001 | 1832 | Anonymous_Gtid | 3306 | 1897 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| testdb-binlog.000001 | 1897 | Query | 3306 | 1971 | BEGIN |
| testdb-binlog.000001 | 1971 | Rows_query | 3306 | 2031 | # insert into test_t values(5,'test5') |
| testdb-binlog.000001 | 2031 | Table_map | 3306 | 2085 | table_id: 109 (testdb.test_t) |
| testdb-binlog.000001 | 2085 | Write_rows | 3306 | 2132 | table_id: 109 flags: STMT_END_F |
| testdb-binlog.000001 | 2132 | Xid | 3306 | 2163 | COMMIT /* xid=15 */ |
| testdb-binlog.000001 | 2163 | Anonymous_Gtid | 3306 | 2228 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| testdb-binlog.000001 | 2228 | Query | 3306 | 2302 | BEGIN |
| testdb-binlog.000001 | 2302 | Rows_query | 3306 | 2362 | # insert into test_t values(6,'test6') |
| testdb-binlog.000001 | 2362 | Table_map | 3306 | 2416 | table_id: 109 (testdb.test_t) |
| testdb-binlog.000001 | 2416 | Write_rows | 3306 | 2463 | table_id: 109 flags: STMT_END_F |
| testdb-binlog.000001 | 2463 | Xid | 3306 | 2494 | COMMIT /* xid=16 */ |
| testdb-binlog.000001 | 2494 | Anonymous_Gtid | 3306 | 2559 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| testdb-binlog.000001 | 2559 | Query | 3306 | 2633 | BEGIN |
| testdb-binlog.000001 | 2633 | Rows_query | 3306 | 2675 | # delete from test_t |
| testdb-binlog.000001 | 2675 | Table_map | 3306 | 2729 | table_id: 109 (testdb.test_t) |
| testdb-binlog.000001 | 2729 | Delete_rows | 3306 | 2836 | table_id: 109 flags: STMT_END_F |
| testdb-binlog.000001 | 2836 | Xid | 3306 | 2867 | COMMIT /* xid=19 */ |
+----------------------+------+----------------+-----------+-------------+--------------------------------------------------------------+
48 rows in set (0.00 sec)
#恢复：
/mysql/app/binlog_rollback -m file -w rollback -M mysql -t 4 -H 192.168.1.10 -P 3306 -u root -p 'root' -dbs testdb -tbs test_t -sdt "2023-08-16 20:21:01" -edt "2023-08-16 20:21:19" -sql delete -d -e -f -r 20 -b 100 -l 10 -o /tmp/20230816 -dj tbs_all_def.json testdb-binlog.000001
[root@centos7 binlog]# /mysql/app/binlog_rollback -m file -w rollback -M mysql -t 4 -H 192.168.1.10 -P 3306 -u root -p 'root' -dbs testdb -tbs test_t -sdt "2023-08-16 20:21:01" -edt "2023-08-16 20:21:19" -sql delete -d -e -f -r 20 -b 100 -l 10 -o /tmp/20230816 -dj tbs_all_def.json testdb-binlog.000001
time="2023-08-16_20:28:37" level=info errcode=0 errmsg="start to get table structure from mysql"
time="2023-08-16_20:28:37" level=info errcode=0 errmsg="geting target table names from mysql"
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="geting table fields from mysql"
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="geting primary/unique keys from mysql"
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="successfully get table structure from mysql"
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="table definition has been dumped to /tmp/20230816/tbs_all_def.json"
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="start to parse binlog from local files"
time="2023-08-16_20:28:38" level=info errcode=0
start to parse testdb-binlog.000001 4
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="start thread 1 to generate redo/rollback sql"
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="start thread to analyze statistics from binlog"
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="start thread to write redo/rollback sql into file"
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="start thread 2 to generate redo/rollback sql"
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="start thread 3 to generate redo/rollback sql"
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="start thread 4 to generate redo/rollback sql"
time="2023-08-16_20:28:38" level=warning errcode=0
testdb-binlog.000002 not exists nor a file
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="finish parsing binlog from local files"
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="exit thread 2 to generate redo/rollback sql"
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="exit thread 4 to generate redo/rollback sql"
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="exit thread 3 to generate redo/rollback sql"
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="exit thread to analyze statistics from binlog"
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="exit thread 1 to generate redo/rollback sql"
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="finish writing rollback sql into tmp files, start to revert content order of tmp files"
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="start thread 1 to revert rollback sql files"
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="start to revert tmp file /tmp/20230816/.testdb.test_t.rollback.1.sql into /tmp/20230816/testdb.test_t.rollback.1.sql"
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="finish reverting tmp file /tmp/20230816/.testdb.test_t.rollback.1.sql into /tmp/20230816/testdb.test_t.rollback.1.sql"
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="exit thread 1 to revert rollback sql files"
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="finish reverting content order of tmp files"
time="2023-08-16_20:28:38" level=info errcode=0 errmsg="exit thread to write redo/rollback sql into file"
[root@centos7 binlog]#
#查看恢复后的文件结构
[root@centos7 ~]# ll /tmp/20230816
total 20
-rw-r--r-- 1 root root 107 Aug 16 20:28 big_long_trx.txt
-rw-r--r-- 1 root root 291 Aug 16 20:28 binlog_stats.txt
-rw-r--r-- 1 root root 64 Aug 16 20:28 ddl_info.txt
-rw-r--r-- 1 root root 403 Aug 16 20:28 tbs_all_def.json
-rw-r--r-- 1 root root 244 Aug 16 20:28 testdb.test_t.rollback.1.sql
[root@centos7 ~]#
[root@centos7 ~]# cat /tmp/20230816/big_long_trx.txt
binlog starttime stoptime startpos stoppos rows duration tables
[root@centos7 ~]# cat /tmp/20230816/binlog_stats.txt
binlog starttime stoptime startpos stoppos inserts updates deletes database table
testdb-binlog.000001 2023-08-16_20:21:13 2023-08-16_20:21:13 2675 2836 0 0 6 testdb test_t
[root@centos7 ~]# cat /tmp/20230816/ddl_info.txt
datetime binlog startpos stoppos sql
[root@centos7 ~]# cat /tmp/20230816/tbs_all_def.json
{
"testdb.test_t": {
"_/0/0": {
"database": "testdb",
"table": "test_t",
"columns": [
{
"column_name": "id",
"column_type": "int"
},
{
"column_name": "name",
"column_type": "varchar"
}
],
"primary_key": null,
"unique_keys": null,
"ddl_info": {
"binlog": "_",
"start_position": 0,
"stop_position": 0,
"ddl_sql": ""
}
}
}
}[root@centos7 ~]# cat /tmp/20230816/testdb.test_t.rollback.1.sql
INSERT INTO `testdb`.`test_t` (`id`,`name`) VALUES (1,'test1'), (2,'test2'), (3,'test3'), (4,'test4'), (5,'test5'), (6,'test6');
# datetime=2023-08-16_20:21:13 database=testdb table=test_t binlog=testdb-binlog.000001 startpos=2675 stoppos=2836
[root@centos7 ~]#
1.9 防止误删sql_safe_updates 参数
sql_safe_updates：控制权限，防止误删除。
#开启这个功能
#1、修改参数（不能加入到my.cnf 里面）
set global sql_safe_updates=1;
#2、登录的时候添加选项，对当前会话有效
mysql -uroot -prootroot --safe-updates
#测试
mysql> delete from testdb.test_t2;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
mysql>
1.10 binlog 日志突然爆增怎么分析?
场景：
日志突然增大，分析为什么？
针对binlog 日志对表的增删改统计分析：
mysqlbinlog --no-defaults --base64-output=decode-rows -vv testdb-binlog.000001| awk '/UPDATE|INSERT|DELETE/{gsub("###","");gsub("INSERT.*INTO","INSERT");gsub("DELETE.*FROM","DELETE");count[$1" "$2]++}END{for(i in count)print i,"\t",count[i]}' |sort -k3nr|head -n 10
[root@centos7 binlog]# mysqlbinlog --no-defaults --base64-output=decode-rows -vv testdb-binlog.000001| awk '/UPDATE|INSERT|DELETE/{gsub("###","");gsub("INSERT.*INTO","INSERT");gsub("DELETE.*FROM","DELETE");count[$1" "$2]++}END{for(i in count)print i,"\t",count[i]}' |sort -k3nr|head -n 10
# INSERT 10
INSERT `testdb`.`test_t` 10
INSERT `testdb`.`test_t2` 10
INSERT `testdb`.`test_t1` 7
DELETE `testdb`.`test_t2` 5
DELETE `testdb`.`test_t` 5
DELETE `testdb`.`test_t1` 2
UPDATE `testdb`.`test_t1` 2
[root@centos7 binlog]#
其他分析binlog命令
mysqlbinlog -vv mysql-bin.000039 | \
grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter" | \
cut -c1-100 | tr '[A-Z]' '[a-z]' | \
sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//" | sed -e "s/ where .*$//" | \
sort | uniq -c | sort -nr
扫一扫
1045
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
