程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL之truncate表後恢復思路整理(前提是有備份且開啟binlog)

MySQL之truncate表後恢復思路整理(前提是有備份且開啟binlog)

編輯:MySQL綜合教程

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<thunder_full_2015112.sql   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)

 


  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved