1.1 Backing up the database Thunder
Mysqldump-s/tmp/mysql3316.sock--single-transaction--master-data=2 Thunder >thunder_full_2015112.sql
1.2 Perform a TRUNCATE TABLE operation and insert into table
(work) [Email protected]: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) [email protected]:mysql3316.sock [(None)]>truncate table thunder.tb1; query ok, 0 rows affected (0.02 SEC) (work) [Email protected]:mysql3316.sock [(None)]>insert into thunder.tb1 (name) values (' like '); query ok, 1 row affected (0.00 SEC) (work) [Email protected]:mysql3316.sock [(None)]>insert into thunder.tb1 (name) values (' lik '); query ok, 1 row affected (0.00 SEC) (work) [Email protected]:mysql3316.sock [(None)]>insert into thunder.tb1 (name) values (' Li '); query ok, 1 row affected (0.00 SEC) (work) [Email protected]:mysql3316.sock [(None)]>insert into thunder.tb1 (name) values (' l '); query ok, 1 row affected (0.00 SEC) (work) [Email protected]: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 Viewing the Binlog location of the backup and finding out the location of the mis-action statement
[[email protected] mysql3316]# grep master thunder_full_2015112.sql--change MASTER to Master_log_file= ' Mysql-bin.000003 ', master_log_pos=25191; [Email protected] ~]# mysql-s/tmp/mysql3316.sock-e "show Binlog events in ' mysql-bin.000003 '" |grep-i truncatemysql-b in.000003 1011 Query 1163316 1091 truncate mysql.dbmysql-bin.000003 25239 Query 1163316 25328 TRUNCATE TABLE THUNDER.TB1
1.4 Use Mysqlbinlog command to find related records in Binlog
[[email protected] mysql3316]# mysqlbinlog -v --base64-output=decode-rows /data/ Mysql/mysql3316/logs/mysql-bin.000003 >3.sql [[email protected] 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/*!*/; by View before backup Master status position immediately after the truncate command, and then insert, so do not need to perform binlog incremental recovery, only need to restore the insert data, if there is data, after the import of the backup will 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 location in the data backup file,--stop-position is the location before the error operation
。
1.5 Create a new database thued and restore the previous backup of the Thunder Library
[Email protected] mysql3316]# mysql-s/tmp/mysql3316.sock thued<thunder_full_2015112.sql
1.6 Insert the newly added data in the THUED.TB1 and view
(work) [EMAIL&NBSP;PROTECTED]:MYSQL3316.SOCK&NBSP;[THUED]>INSERT&NBSP;INTO&NBSP;TB1 (name) select name from thunder.tb1; query ok, 4 rows affected (0.00 sec) records: 4 duplicates: 0 warnings: 0 (work) [email protected]: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&NBSP;SEC)
1.7 Delete thunder.tb1, rename thued.tb1 to Thunder.tb1
(work) [email protected]:mysql3316.sock [thued]>drop table thunder.tb1; query ok, 0 rows affected (0.00&NBSP;SEC) (work) [Email protected]:mysql3316.sock [thued]>rename table thued.tb1 to thunder.tb1; query ok, 0 rows affected (0.00&NBSP;SEC) (work) [Email protected]: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&NBSP;SEC)
Thai-Yue MySQL DBA college phase Two
This article is from the "Thundermeng" blog, make sure to keep this source http://thundermeng.blog.51cto.com/9414441/1715746
MySQL TRUNCATE table after the recovery idea finishing (if there is backup and open Binlog)