How to restore the MySQL truncate table (provided that binlog is backed up and enabled)

How to restore the MySQL truncate table (provided that binlog is backed up and enabled)
1.1 back up database thunder mysqldump-S/tmp/mysql3316.sock -- single-transaction -- master-data = 2 thunder> thunder_full_2015112. SQL 1.2 for truncate table operation and insert into table

(work)root@localhost:mysql3316.sock [(none)]>select * from thunder.tb1;+----+---------+| id | name    |+----+---------+|  1 | test    ||  4 | thun    ||  5 | thunder ||  6 | thun    ||  7 | thun    ||  8 | thun    ||  9 | test    |+----+---------+7 rows in set (0.00 sec)(work)root@localhost:mysql3316.sock [(none)]>truncate table thunder.tb1;Query OK, 0 rows affected (0.02 sec)(work)root@localhost:mysql3316.sock [(none)]>insert into thunder.tb1(name) values('like');  Query OK, 1 row affected (0.00 sec)(work)root@localhost:mysql3316.sock [(none)]>insert into thunder.tb1(name) values('lik');Query OK, 1 row affected (0.00 sec)(work)root@localhost:mysql3316.sock [(none)]>insert into thunder.tb1(name) values('li');Query OK, 1 row affected (0.00 sec)(work)root@localhost:mysql3316.sock [(none)]>insert into thunder.tb1(name) values('l');Query OK, 1 row affected (0.00 sec)(work)root@localhost:mysql3316.sock [(none)]>select * from thunder.tb1;+----+---------+| id | name    |+----+---------+|  1 | test    ||  4 | thun    ||  5 | thunder ||  6 | thun    ||  7 | thun    ||  8 | thun    ||  9 | test    || 10 | like    || 11 | lik     || 12 | li      || 13 | l       |+----+---------+11 rows in set (0.00 sec)

 

1.3 view the binlog location during Backup and find the location of the misoperation statement

[root@mysqlnode1 mysql3316]# grep MASTER thunder_full_2015112.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=25191;[root@mysqlnode1 ~]# mysql -S /tmp/mysql3316.sock -e "show binlog events in 'mysql-bin.000003'" |grep -i truncatemysql-bin.000003        1011    Query   1163316 1091    truncate mysql.dbmysql-bin.000003        25239   Query   1163316 25328   truncate table thunder.tb1

1.4 Use the mysqlbinlog command to find related records in binlog

[root@mysqlnode1 mysql3316]# mysqlbinlog -v --base64-output=decode-rows /data/mysql/mysql3316/logs/mysql-bin.000003 >3.sql [root@mysqlnode1 mysql3316]# vim 3.sql # at 25191#151122 19:59:50 server id 1163316  end_log_pos 25239 CRC32 0xac936e4e  GTID [commit=yes]SET @@SESSION.GTID_NEXT= 'c009ae77-8def-11e5-ab11-000c29ea831c:78'/*!*/;# at 25239#151122 19:59:50 server id 1163316  end_log_pos 25328 CRC32 0xd020ddc8  Query   thread_id=175   exec_time=0     error_code=0SET TIMESTAMP=1448193590/*!*/;truncate table thunder.tb1/*!*/;# at 25328#151122 20:00:50 server id 1163316  end_log_pos 25376 CRC32 0xa12c299c  GTID [commit=yes]SET @@SESSION.GTID_NEXT= 'c009ae77-8def-11e5-ab11-000c29ea831c:79'/*!*/;# at 25376#151122 20:00:50 server id 1163316  end_log_pos 25444 CRC32 0xaa7072db  Query   thread_id=175   exec_time=0     error_code=0SET TIMESTAMP=1448193650/*!*/;BEGIN/*!*/;# at 25444#151122 20:00:50 server id 1163316  end_log_pos 25496 CRC32 0xd45864c2  Table_map: `thunder`.`tb1` mapped to number 267# at 25496#151122 20:00:50 server id 1163316  end_log_pos 25541 CRC32 0x71343558  Write_rows: table id 267 flags: STMT_END_F### INSERT INTO `thunder`.`tb1`### SET###   @1=1###   @2='like'# at 25541#151122 20:00:50 server id 1163316  end_log_pos 25572 CRC32 0xd860159a  Xid = 4442COMMIT/*!*/;

 

After checking that the master status is followed by the truncate command before the backup, and then the insert command, you do not need to perform binlog incremental recovery. You only need to restore the insert data. If there is data here, after importing the backup, you also need to execute this statement mysqlbinlog -- start-position = 18311 -- stop-position = 19557 mysql-bin.000003 | mysql-S/tmp/mysql3316.sock thunder where -- start-position is the data backup file Location, -- stop-position: the position before the misoperation. 1.5 create a database thued and restore the backup of the previous thunder database [root @ mysqlnode1 mysql3316] # mysql-S/tmp/mysql3316.sock thued <thunder_full_2015112. SQL 1.6 in thued. insert new data in tb1 and view

(work)root@localhost:mysql3316.sock [thued]>insert into tb1(name) select name from thunder.tb1;Query OK, 4 rows affected (0.00 sec)Records: 4  Duplicates: 0  Warnings: 0(work)root@localhost:mysql3316.sock [thued]>select * from tb1;                                 +----+---------+| id | name    |+----+---------+|  1 | test    ||  4 | thun    ||  5 | thunder ||  6 | thun    ||  7 | thun    ||  8 | thun    ||  9 | test    || 10 | like    || 11 | lik     || 12 | li      || 13 | l       |+----+---------+11 rows in set (0.00 sec)

 

1.7 Delete thunder. tb1 and change thued. tb1 to thunder. tb1.

(work)root@localhost:mysql3316.sock [thued]>drop table thunder.tb1;Query OK, 0 rows affected (0.00 sec)(work)root@localhost:mysql3316.sock [thued]>rename table thued.tb1 to thunder.tb1;Query OK, 0 rows affected (0.00 sec)(work)root@localhost:mysql3316.sock [thued]>select * from thunder.tb1;+----+---------+| id | name    |+----+---------+|  1 | test    ||  4 | thun    ||  5 | thunder ||  6 | thun    ||  7 | thun    ||  8 | thun    ||  9 | test    || 10 | like    || 11 | lik     || 12 | li      || 13 | l       |+----+---------+11 rows in set (0.00 sec)