mysql 5.7 binlog无法导入数据
将命令输出到文件中:
mysqlbinlog /app/data/mysql-bin.000001 > binlog.sql
测试删除数据库:
[[email protected] ~]# mysql -uroot -p'123456'
mysql> select * from testdb.t1;
+------+
| id |
+------+
| 10 |
| 11 |
| 12 |
+------+
[[email protected] ~]#
mysql> drop database testdb;
Query OK, 1 row affected (0.03 sec)
查看数据库并导入binlog:
[[email protected] ~]# mysql -uroot -p'123456' -e "select * from testdb.t1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1146 (42S02) at line 1: Table 'testdb.t1' doesn't exist
[[email protected] ~]#
[[email protected] ~]# mysql -uroot -p'123456' < binlog.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[[email protected] ~]# mysql -uroot -p'123456' -e "select * from testdb.t1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1146 (42S02) at line 1: Table 'testdb.t1' doesn't exist
查看文件内容:
create database testdb
/*!*/;
# at 324
#190113 11:33:30 server id 1 end_log_pos 389 CRC32 0x7744b794 GTID last_committed=1 sequence_number=2
SET @@SESSION.GTID_NEXT= '86cba1e7-14c5-11e9-9319-fa163eaab8a4:2'/*!*/;
看到这里大概就知道原因了,因为数据库启用了gtid所以导出的时候默认也带了gtid。
解析后的binlog文件中每个事物开始前,都执行了SET @@SESSION.GTID_NEXT=操作来执行下一个要执行的GTID。但是这些GTID都已经存在数据库的Executed_Gtid_Set中(因为这些GTID都之前已经在实例上执行过),所以我们执行解析后的binlog文件时,所有的事物都被忽略(已经存在于Executed_Gtid_Set集合中的GTID会跳过)。
因此,在使用gtid的mysql上使用mysqlbinlog解析数据来恢复数据库的话,在执行mysqlbinlog命令时加上–skip-gtids=true参数,解析的sql中就不会包含gtid信息了。
再次执行命令
mysqlbinlog /app/data/mysql-bin.000001 --skip-gtids=true > binlog.sql-3
对比两次输出结果:
vimdiff binlog.sql binlog.sql-3
测试跳过gtid,导入:
[[email protected] ~]# mysql -uroot -p'123456' < binlog.sql-3
mysql: [Warning] Using a password on the command line interface can be insecure.
[[email protected] ~]#
[[email protected] ~]# mysql -uroot -p'123456' -e "select * from testdb.t1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id |
+------+
| 10 |
| 11 |
| 12 |
+------+
至此,数据成功导入。