另外因为基于备份（由log的启始位置）或是从量log, 如果基于备份有log的起始位置，我们需要处理的log文件是启始位置到发生truncate的日值（后面的数据处理不了，会发生主建冲突的错误造成truncate后的数据不能恢复），
如果是全量日志，需要从创建完mysql后库后的日志去处理到当前的发生truncate的位置（后面数据会因为主建冲突写不进去）
恢复准备工作，创建一个库用于恢复数据，这里创建了一个re_wubx, 及原结构的表: tb_wubx （相当于恢复了备份，过程省略）
| mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 143 | | mysql-bin.000002 | 261 | | mysql-bin.000003 | 562 | | mysql-bin.000004 | 1144 | +------------------+-----------+ 4 rows in set (0.00 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 143 | | mysql-bin.000002 | 261 | | mysql-bin.000003 | 562 | | mysql-bin.000004 | 1144 | +------------------+-----------+ 4 rows in set (0.00 sec) |
我这里有一个备份文件就是那个创建表的sql语句，位置是mysql-bin.000004 , 4
在这个案例里我只用cover住mysql-bin.000004这个文件。
| mysql>show binlog events in 'mysql-bin.000004'; +------------------+------+-------------+-----------+-------------+----------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+-------------+-----------+-------------+----------------------------------------------------+ | mysql-bin.000004 | 4 | Format_desc | 753306 | 120 | Server ver: 5.6.16-64.2-rel64.2-log, Binlog ver: 4 | | mysql-bin.000004 | 120 | Query | 753306 | 209 | use `wubx`; truncate table tb_wubx | | mysql-bin.000004 | 209 | Query | 753306 | 281 | BEGIN | | mysql-bin.000004 | 281 | Table_map | 753306 | 334 | table_id: 91 (wubx.tb_wubx) | | mysql-bin.000004 | 334 | Write_rows | 753306 | 393 | table_id: 91 flags: STMT_END_F | | mysql-bin.000004 | 393 | Xid | 753306 | 424 | COMMIT /* xid=1073 */ | | mysql-bin.000004 | 424 | Query | 753306 | 496 | BEGIN | | mysql-bin.000004 | 496 | Table_map | 753306 | 549 | table_id: 91 (wubx.tb_wubx) | | mysql-bin.000004 | 549 | Write_rows | 753306 | 602 | table_id: 91 flags: STMT_END_F | | mysql-bin.000004 | 602 | Xid | 753306 | 633 | COMMIT /* xid=1074 */ | | mysql-bin.000004 | 633 | Query | 753306 | 722 | use `wubx`; truncate table tb_wubx | | mysql-bin.000004 | 722 | Query | 753306 | 794 | BEGIN | | mysql-bin.000004 | 794 | Table_map | 753306 | 847 | table_id: 92 (wubx.tb_wubx) | | mysql-bin.000004 | 847 | Write_rows | 753306 | 894 | table_id: 92 flags: STMT_END_F | | mysql-bin.000004 | 894 | Xid | 753306 | 925 | COMMIT /* xid=1081 */ | | mysql-bin.000004 | 925 | Query | 753306 | 997 | BEGIN | | mysql-bin.000004 | 997 | Table_map | 753306 | 1050 | table_id: 92 (wubx.tb_wubx) | | mysql-bin.000004 | 1050 | Update_rows | 753306 | 1113 | table_id: 92 flags: STMT_END_F | | mysql-bin.000004 | 1113 | Xid | 753306 | 1144 | COMMIT /* xid=1084 */ | +------------------+------+-------------+-----------+-------------+----------------------------------------------------+ 19 rows in set (0.00 sec) mysql>show binlog events in 'mysql-bin.000004'; +------------------+------+-------------+-----------+-------------+----------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+-------------+-----------+-------------+----------------------------------------------------+ | mysql-bin.000004 | 4 | Format_desc | 753306 | 120 | Server ver: 5.6.16-64.2-rel64.2-log, Binlog ver: 4 | | mysql-bin.000004 | 120 | Query | 753306 | 209 | use `wubx`; truncate table tb_wubx | | mysql-bin.000004 | 209 | Query | 753306 | 281 | BEGIN | | mysql-bin.000004 | 281 | Table_map | 753306 | 334 | table_id: 91 (wubx.tb_wubx) | | mysql-bin.000004 | 334 | Write_rows | 753306 | 393 | table_id: 91 flags: STMT_END_F | | mysql-bin.000004 | 393 | Xid | 753306 | 424 | COMMIT /* xid=1073 */ | | mysql-bin.000004 | 424 | Query | 753306 | 496 | BEGIN | | mysql-bin.000004 | 496 | Table_map | 753306 | 549 | table_id: 91 (wubx.tb_wubx) | | mysql-bin.000004 | 549 | Write_rows | 753306 | 602 | table_id: 91 flags: STMT_END_F | | mysql-bin.000004 | 602 | Xid | 753306 | 633 | COMMIT /* xid=1074 */ | | mysql-bin.000004 | 633 | Query | 753306 | 722 | use `wubx`; truncate table tb_wubx | | mysql-bin.000004 | 722 | Query | 753306 | 794 | BEGIN | | mysql-bin.000004 | 794 | Table_map | 753306 | 847 | table_id: 92 (wubx.tb_wubx) | | mysql-bin.000004 | 847 | Write_rows | 753306 | 894 | table_id: 92 flags: STMT_END_F | | mysql-bin.000004 | 894 | Xid | 753306 | 925 | COMMIT /* xid=1081 */ | | mysql-bin.000004 | 925 | Query | 753306 | 997 | BEGIN | | mysql-bin.000004 | 997 | Table_map | 753306 | 1050 | table_id: 92 (wubx.tb_wubx) | | mysql-bin.000004 | 1050 | Update_rows | 753306 | 1113 | table_id: 92 flags: STMT_END_F | | mysql-bin.000004 | 1113 | Xid | 753306 | 1144 | COMMIT /* xid=1084 */ | +------------------+------+-------------+-----------+-------------+----------------------------------------------------+ 19 rows in set (0.00 sec) |
