MySQL binlog 导入

1. 转换为SQL

mysqlbinlog -v  --base64-output=decode-rows --skip-gtids=true -d db1  mysql-bin.003513 > db1.sql

 

-d: 指定数据库

--skip-gtids:  跳过gtids

转换后的文件内容样例:

BEGIN
/*!*/;
# at 28780088
#200730 17:04:29 server id 3304445132  end_log_pos 28780173 CRC32 0x85c63a2f    Table_map: `test_master`.`item_stock_io_record` mapped to number 5964
# at 28780173
#200730 17:04:29 server id 3304445132  end_log_pos 28780307 CRC32 0x91f48ce0    Write_rows: table id 5964 flags: STMT_END_F
### INSERT INTO `test_master`.`item_stock_io_record`
### SET
###   @1='000001739eec131700007d09'
###   @2='0000016bcfa5566300000e6a'
###   @3=1000
###   @4=0
###   @5=0
###   @6=1596099138327
###   @7=1596099138327
###   @8=0
###   @9=0
# at 28780307
#200730 17:04:29 server id 3304445132  end_log_pos 28780338 CRC32 0x750e5180    Xid = 5452960571
COMMIT/*!*/;
# at 28780338
# at 28780403
#200730 17:04:29 server id 3304445132  end_log_pos 28780487 CRC32 0x04ead87d    Query   thread_id=11643976      exec_time=0     error_code=0
SET TIMESTAMP=1596099869/*!*/;

 

2. 导入数据库

mysql -f -h192.168.0.101 -udbuser1 -ptest123 db1 < db1.sql

-f: 忽略SQL错误