Recover MySQL data to the latest node after loss

Incremental backup point-------------Error Delete point------------------------------"Recover data

The data continues to be written

Simulation

1.full Back (3 rows) 3pm the day before the backup

2,insert 3 rows 5pm next day new data

3,delete from table 7pm error operation

4,insert 3 Rows 8pm continuously new data

5,recovery databases 9pm Recovery data

/mysqldump-uroot-p db1 >/root/mysqlbackup/t2.sql Backup

/ETC/INIT.D/MYSQLD Restart simulation generates a new binary log

INSERT into T2 values (4,40), (5,50), (6,60); New data

Delete from T2; Wrong operation

INSERT into T2 values (7,70), (8,80), (9,90); Keep adding data

Start recovery

Mysql-uroot-p DB1 </root/mysqlbackup/t2.sql

Skip the Delete record below to recover other position records

# at 526

#150512 7:50:58 Server ID 1 end_log_pos 640 CRC32 0x4f6549a3 Querythread_id=1exec_time=0 error_code=0

SET timestamp=1431388258/*!*/;

INSERT into T2 values (4,40), (5,50), (6,60)

/*!*/;

# at 640

#150512 7:50:58 Server ID 1 end_log_pos 671 CRC32 0x253a2239 Xid = 60

commit/*!*/;

# at 671

#150512 7:52:11 Server ID 1 end_log_pos 748 CRC32 0x7617cce8 Querythread_id=1exec_time=0 error_code=0

SET timestamp=1431388331/*!*/;

BEGIN

/*!*/;

# at 748

#150512 7:52:11 Server ID 1 end_log_pos 834 CRC32 0x112802bc Querythread_id=1exec_time=0 error_code=0

SET timestamp=1431388331/*!*/;

Delete from T2

/*!*/;

# at 834

#150512 7:52:11 Server ID 1 end_log_pos 865 CRC32 0x8895c353 Xid = 62

commit/*!*/;

# at 865

#150512 7:52:38 Server ID 1 end_log_pos 942 CRC32 0xa34076dd Querythread_id=1exec_time=0 error_code=0

SET timestamp=1431388358/*!*/;

BEGIN

/*!*/;

# at 942

#150512 7:52:38 Server ID 1 end_log_pos 1056 CRC32 0xa1c9c171 Querythread_id=1Exec_tim E=0error_code=0

SET timestamp=1431388358/*!*/;

INSERT into T2 values (7,70), (8,80), (9,90)

/*!*/;

# at 1056

#150512 7:52:38 Server ID 1 end_log_pos 1087 CRC32 0x73a98385 Xid = 64

commit/*!*/;

Bin/mysqlbinlog--start-position=526--stop-position=640 data/mysql-bin.000004 | Mysql-uroot-p

Bin/mysqlbinlog--start-position=942--stop-position=1056 data/mysql-bin.000004 | Mysql-uroot-p

This article is from the "Crazy_sir" blog, make sure to keep this source http://douya.blog.51cto.com/6173221/1651285

Recover MySQL data to the latest node after loss