第一种方法:
第一步 准备工作
- 刷新日志
mysql> flush logs;
Query OK, 0 rows affected (0.08 sec)
- 查看使用的二进制文件
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000015 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
- 建库建表
mysql> create database binlog charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> use binlog;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.08 sec)
mysql> insert t1 select 1;
Query OK, 1 row affected (0.08 sec)
Records: 1 Duplicates: 0 Warnings: 0
- 提交事务
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
第二步 截取
- 查看二进制事件
mysql> show binlog events in 'mysql-bin.000015';
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
| mysql-bin.000015 | 4 | Format_desc | 9 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 |
| mysql-bin.000015 | 123 | Previous_gtids | 9 | 154 | |
| mysql-bin.000015 | 154 | Anonymous_Gtid | 9 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000015 | 219 | Query | 9 | 335 | create database binlog charset utf8mb4 |
| mysql-bin.000015 | 335 | Anonymous_Gtid | 9 | 400 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000015 | 400 | Query | 9 | 502 | use `binlog`; create table t1 (id int) |
| mysql-bin.000015 | 502 | Anonymous_Gtid | 9 | 567 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000015 | 567 | Query | 9 | 641 | BEGIN |
| mysql-bin.000015 | 641 | Table_map | 9 | 688 | table_id: 122 (binlog.t1) |
| mysql-bin.000015 | 688 | Write_rows | 9 | 728 | table_id: 122 flags: STMT_END_F |
| mysql-bin.000015 | 728 | Xid | 9 | 759 | COMMIT /* xid=900083 */ |
+------------------+-----+----------------+-----------+-------------+----------------------------------------+
11 rows in set (0.00 sec)
- 查看二进制内容
进入二进制日志所在地
[root@localhost 3309]# mysqlbinlog mysql-bin.000015
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200405 14:00:01 server id 9 end_log_pos 123 CRC32 0x5f1988f3 Start: binlog v 4, server v 5.7.24-log created 200405 14:00:01
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
4XOJXg8JAAAAdwAAAHsAAAABAAQANS43LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AfOIGV8=
'/*!*/;
# at 123
#200405 14:00:01 server id 9 end_log_pos 154 CRC32 0x2be9d359 Previous-GTIDs
# [empty]
# at 154
#200405 14:00:22 server id 9 end_log_pos 219 CRC32 0x278301f7 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#200405 14:00:22 server id 9 end_log_pos 335 CRC32 0x4cbdd935 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1586066422/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database binlog charset utf8mb4
/*!*/;
# at 335
#200405 14:00:51 server id 9 end_log_pos 400 CRC32 0xa2c2e05d Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 400
#200405 14:00:51 server id 9 end_log_pos 502 CRC32 0x3e3a1aa7 Query thread_id=4 exec_time=0 error_code=0
use `binlog`/*!*/;
SET TIMESTAMP=1586066451/*!*/;
create table t1 (id int)
/*!*/;
# at 502
#200405 14:05:31 server id 9 end_log_pos 567 CRC32 0x75e17dda Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 567
#200405 14:01:00 server id 9 end_log_pos 641 CRC32 0x021655fb Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1586066460/*!*/;
BEGIN
/*!*/;
# at 641
#200405 14:01:00 server id 9 end_log_pos 688 CRC32 0xcf6aa15f Table_map: `binlog`.`t1` mapped to number 122
# at 688
#200405 14:01:00 server id 9 end_log_pos 728 CRC32 0xa57bcdbb Write_rows: table id 122 flags: STMT_END_F
BINLOG '
HHSJXhMJAAAALwAAALACAAAAAHoAAAAAAAEABmJpbmxvZwACdDEAAQMAAV+has8=
HHSJXh4JAAAAKAAAANgCAAAAAHoAAAAAAAEAAgAB//4BAAAAu817pQ==
'/*!*/;
# at 728
#200405 14:05:31 server id 9 end_log_pos 759 CRC32 0x103cfe62 Xid = 900083
COMMIT/*!*/;
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*/;
可以过滤set
[root@localhost 3309]# mysqlbinlog mysql-bin.000015 |grep -v '^SET'
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200405 14:00:01 server id 9 end_log_pos 123 CRC32 0x5f1988f3 Start: binlog v 4, server v 5.7.24-log created 200405 14:00:01
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
4XOJXg8JAAAAdwAAAHsAAAABAAQANS43LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AfOIGV8=
'/*!*/;
# at 123
#200405 14:00:01 server id 9 end_log_pos 154 CRC32 0x2be9d359 Previous-GTIDs
# [empty]
# at 154
#200405 14:00:22 server id 9 end_log_pos 219 CRC32 0x278301f7 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
# at 219
#200405 14:00:22 server id 9 end_log_pos 335 CRC32 0x4cbdd935 Query thread_id=4 exec_time=0 error_code=0
/*!\C utf8 *//*!*/;
create database binlog charset utf8mb4
/*!*/;
# at 335
#200405 14:00:51 server id 9 end_log_pos 400 CRC32 0xa2c2e05d Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
# at 400
#200405 14:00:51 server id 9 end_log_pos 502 CRC32 0x3e3a1aa7 Query thread_id=4 exec_time=0 error_code=0
use `binlog`/*!*/;
create table t1 (id int)
/*!*/;
# at 502
#200405 14:05:31 server id 9 end_log_pos 567 CRC32 0x75e17dda Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# at 567
#200405 14:01:00 server id 9 end_log_pos 641 CRC32 0x021655fb Query thread_id=4 exec_time=0 error_code=0
BEGIN
/*!*/;
# at 641
#200405 14:01:00 server id 9 end_log_pos 688 CRC32 0xcf6aa15f Table_map: `binlog`.`t1` mapped to number 122
# at 688
#200405 14:01:00 server id 9 end_log_pos 728 CRC32 0xa57bcdbb Write_rows: table id 122 flags: STMT_END_F
BINLOG '
HHSJXhMJAAAALwAAALACAAAAAHoAAAAAAAEABmJpbmxvZwACdDEAAQMAAV+has8=
HHSJXh4JAAAAKAAAANgCAAAAAHoAAAAAAAEAAgAB//4BAAAAu817pQ==
'/*!*/;
# at 728
#200405 14:05:31 server id 9 end_log_pos 759 CRC32 0x103cfe62 Xid = 900083
COMMIT/*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/
查看可以勉强看懂的
[root@localhost 3309]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000015
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200405 14:00:01 server id 9 end_log_pos 123 CRC32 0x5f1988f3 Start: binlog v 4, server v 5.7.24-log created 200405 14:00:01
# Warning: this binlog is either in use or was not closed properly.
# at 123
#200405 14:00:01 server id 9 end_log_pos 154 CRC32 0x2be9d359 Previous-GTIDs
# [empty]
# at 154
#200405 14:00:22 server id 9 end_log_pos 219 CRC32 0x278301f7 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#200405 14:00:22 server id 9 end_log_pos 335 CRC32 0x4cbdd935 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1586066422/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database binlog charset utf8mb4
/*!*/;
# at 335
#200405 14:00:51 server id 9 end_log_pos 400 CRC32 0xa2c2e05d Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 400
#200405 14:00:51 server id 9 end_log_pos 502 CRC32 0x3e3a1aa7 Query thread_id=4 exec_time=0 error_code=0
use `binlog`/*!*/;
SET TIMESTAMP=1586066451/*!*/;
create table t1 (id int)
/*!*/;
# at 502
#200405 14:05:31 server id 9 end_log_pos 567 CRC32 0x75e17dda Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 567
#200405 14:01:00 server id 9 end_log_pos 641 CRC32 0x021655fb Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1586066460/*!*/;
BEGIN
/*!*/;
# at 641
#200405 14:01:00 server id 9 end_log_pos 688 CRC32 0xcf6aa15f Table_map: `binlog`.`t1` mapped to number 122
# at 688
#200405 14:01:00 server id 9 end_log_pos 728 CRC32 0xa57bcdbb Write_rows: table id 122 flags: STMT_END_F
### INSERT INTO `binlog`.`t1`
### SET
### @1=1 /* INT meta=0 nullable=1 is_null=0 */
# at 728
#200405 14:05:31 server id 9 end_log_pos 759 CRC32 0x103cfe62 Xid = 900083
COMMIT/*!*/;
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@localhost 3309]# mysqlbinlog --start-position=400 --stop-position=502 mysql-bin.000015
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200405 14:00:01 server id 9 end_log_pos 123 CRC32 0x5f1988f3 Start: binlog v 4, server v 5.7.24-log created 200405 14:00:01
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
4XOJXg8JAAAAdwAAAHsAAAABAAQANS43LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AfOIGV8=
'/*!*/;
# at 400
#200405 14:00:51 server id 9 end_log_pos 502 CRC32 0x3e3a1aa7 Query thread_id=4 exec_time=0 error_code=0
use `binlog`/*!*/;
SET TIMESTAMP=1586066451/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table t1 (id int)
/*!*/;
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@localhost 3309]# mysqlbinlog --start-position=219 --stop-position=759 mysql-bin.000015 >/tmp/a.sql
第三步 模拟故障
- 删除数据库
mysql> drop database binlog ;
Query OK, 1 row affected (0.02 sec)
- 恢复
mysql> source /tmp/a1.sql;
第二种方法:(gtid)
第一步:开启GTID
gtid-mode=on
enforce-gtid-consistency=ture
第二步 准备库和表
mysql> create table test1(id int);
mysql> insert test1 select 1;
mysql> drop table test1;
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000017 | 798 | | | 43d43f5c-69b5-11ea-972e-000c29c23ae9:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
mysql> show binlog events in 'mysql-bin.000017';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000017 | 4 | Format_desc | 9 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 |
| mysql-bin.000017 | 123 | Previous_gtids | 9 | 194 | 43d43f5c-69b5-11ea-972e-000c29c23ae9:1 |
| mysql-bin.000017 | 194 | Gtid | 9 | 259 | SET @@SESSION.GTID_NEXT= '43d43f5c-69b5-11ea-972e-000c29c23ae9:2' |
| mysql-bin.000017 | 259 | Query | 9 | 359 | use `test`; create table test1(id int) |
| mysql-bin.000017 | 359 | Gtid | 9 | 424 | SET @@SESSION.GTID_NEXT= '43d43f5c-69b5-11ea-972e-000c29c23ae9:3' |
| mysql-bin.000017 | 424 | Query | 9 | 496 | BEGIN |
| mysql-bin.000017 | 496 | Table_map | 9 | 544 | table_id: 117 (test.test1) |
| mysql-bin.000017 | 544 | Write_rows | 9 | 584 | table_id: 117 flags: STMT_END_F |
| mysql-bin.000017 | 584 | Xid | 9 | 615 | COMMIT /* xid=19 */ |
| mysql-bin.000017 | 615 | Gtid | 9 | 680 | SET @@SESSION.GTID_NEXT= '43d43f5c-69b5-11ea-972e-000c29c23ae9:4' |
| mysql-bin.000017 | 680 | Query | 9 | 798 | use `test`; DROP TABLE `test1` /* generated by server */ |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
第三步 截取
[root@localhost 3309]# mysqlbinlog --skip-gtids --include-gtids='43d43f5c-69b5-11ea-972e-000c29c23ae9:1-3' mysql-bin.000017 >/tmp/ssgtid.sql
第四步 恢复
mysql> set sql_log_bin=0;
mysql> source /tmp/ssgtid.sql;
mysql> set sql_log_bin=1;