mysql truncate表恢复_MySQL之truncate表后恢复思路整理（前提是有备份且开启binlog）...

1.1对数据库thunder进行备份mysqldump -S /tmp/mysql3316.sock  --single-transaction --master-data=2 thunder >thunder_full_2015112.sql

1.2进行truncate table操作并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查看备份时binlog位置并找出误操作语句的位置[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 truncate

mysql-bin.000003        1011    Query   1163316 1091    truncate mysql.db

mysql-bin.000003        25239   Query   1163316 25328   truncate table thunder.tb1

1.4用mysqlbinlog命令在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=0

SET 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=0

SET 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 = 4442

COMMIT/*!*/;

经查看在备份前master status位置后紧跟truncate命令，而后是insert，所以不需要执行binlog增量恢复,只需要把insert的数据还原，此处如果有数据，在导入备份后还需要执行这条语句

mysqlbinlog  --start-position=18311 --stop-position=19557 mysql-bin.000003 |mysql -S /tmp/mysql3316.sock thunder

其中--start-position为数据备份文件中的位置，--stop-position为误操作前的位置

。

1.5新建一个数据库thued，并还原之前thunder库的备份[root@mysqlnode1 mysql3316]# mysql -S /tmp/mysql3316.sock thued

1.6在thued.tb1中插入新增加的数据，并查看(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删除thunder.tb1，把thued.tb1改名成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)

泰岳MySQL DBA 学院 二期