MySQL TRUNCATE table after the recovery idea finishing (if there is backup and open Binlog)

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 PROTECTED]: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) [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 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 SEC) (work) [Email protected]:mysql3316.sock  [thued]>rename table thued.tb1 to thunder.tb1; query ok, 0 rows affected  (0.00 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 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)