實際線上的場景比較復雜,當時涉及了truncate, delete 兩個操作,經確認丟數據差不多7萬多行,等停下來時,差不多又有共計1萬多行數據寫入。 這裡為了簡單說明,只拿弄一個簡單的業務場景舉例。
測試環境: Percona-Server-5.6.16
日志格式: mixed 沒起用gtid
表結構如下:
CREATE TABLE `tb_wubx` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 CREATE TABLE `tb_wubx` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
基於某個時間點有一個備份或是有全量的binlog是能恢復數據的一個唯一保證。 例如我們的備份就是一個表結構創建語句,binlog pos相關信息: mysql-bin.000004 , 4,然後進行了如下:
–t1時間 程序寫入:
insert into tb_wubx(name) values(‘張三'),(‘李四'); insert into tb_wubx(name) values(‘隔壁老王');
–t2時間 某個人員失誤
truncate table tb_wubx;
–t3時間 程序寫入
insert into tb_wubx(name) values(‘老趙'); update tb_wubx set name='老趙趙' where id=1;
現在表裡的數據情況:
mysql>select * from tb_wubx; +----+-----------+ | id | name | +----+-----------+ | 1 | 老趙趙 | +----+-----------+ 1 row in set (0.00 sec) mysql>select * from tb_wubx; +----+-----------+ | id | name | +----+-----------+ | 1 | 老趙趙 | +----+-----------+ 1 row in set (0.00 sec)
可以見truncate table操作後,表的自增id又變更為從1開始,原來寫入的數據應該是:
+—-+———–+ | id | name | +—-+———–+ | 1 | 張三 | +—-+———–+ | 2 | 李四 | +—-+———–+ | 3 | 隔壁老王 | +—-+———–+
如果沒生truncate table操作,實際的數據應該為:
+—-+———–+ | id | name | +—-+———–+ | 1 | 張三 | +—-+———–+ | 2 | 李四 | +—-+———–+ | 3 | 隔壁老王 | +—-+———–+ | 4 | 老趙趙 | +—-+———–+
而且線上的恢復那個表時和序序開發人員了解才知道,原來那個id和緩存及其它地方有依賴,因為id亂了,也會造成程序錯亂。這個時間修復id在程序層錯亂的事,留給開發人員了關建是給他們講明白恢復的結果是什麼樣,我們的關建任務是把數據恢復出來。好,接下來的工作是開始從binlog中恢復數據。
利用: show binary logs; 查看當的log文件分布, 然後利用show binlog events in ‘binary log文件'; 查看log文件的內容,目的是找到truncate發生的日志位置。
另外因為基於備份(由log的啟始位置)或是從量log, 如果基於備份有log的起始位置,我們需要處理的log文件是啟始位置到發生truncate的日值(後面的數據處理不了,會發生主建沖突的錯誤造成truncate後的數據不能恢復),
如果是全量日志,需要從創建完mysql後庫後的日志去處理到當前的發生truncate的位置(後面數據會因為主建沖突寫不進去)
恢復准備工作,創建一個庫用於恢復數據,這裡創建了一個re_wubx, 及原結構的表: tb_wubx (相當於恢復了備份,過程省略)
mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 143 | | mysql-bin.000002 | 261 | | mysql-bin.000003 | 562 | | mysql-bin.000004 | 1144 | +------------------+-----------+ 4 rows in set (0.00 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 143 | | mysql-bin.000002 | 261 | | mysql-bin.000003 | 562 | | mysql-bin.000004 | 1144 | +------------------+-----------+ 4 rows in set (0.00 sec)
我這裡有一個備份文件就是那個創建表的sql語句,位置是mysql-bin.000004 , 4
在這個案例裡我只用cover住mysql-bin.000004這個文件。
mysql>show binlog events in 'mysql-bin.000004'; +------------------+------+-------------+-----------+-------------+----------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+-------------+-----------+-------------+----------------------------------------------------+ | mysql-bin.000004 | 4 | Format_desc | 753306 | 120 | Server ver: 5.6.16-64.2-rel64.2-log, Binlog ver: 4 | | mysql-bin.000004 | 120 | Query | 753306 | 209 | use `wubx`; truncate table tb_wubx | | mysql-bin.000004 | 209 | Query | 753306 | 281 | BEGIN | | mysql-bin.000004 | 281 | Table_map | 753306 | 334 | table_id: 91 (wubx.tb_wubx) | | mysql-bin.000004 | 334 | Write_rows | 753306 | 393 | table_id: 91 flags: STMT_END_F | | mysql-bin.000004 | 393 | Xid | 753306 | 424 | COMMIT /* xid=1073 */ | | mysql-bin.000004 | 424 | Query | 753306 | 496 | BEGIN | | mysql-bin.000004 | 496 | Table_map | 753306 | 549 | table_id: 91 (wubx.tb_wubx) | | mysql-bin.000004 | 549 | Write_rows | 753306 | 602 | table_id: 91 flags: STMT_END_F | | mysql-bin.000004 | 602 | Xid | 753306 | 633 | COMMIT /* xid=1074 */ | | mysql-bin.000004 | 633 | Query | 753306 | 722 | use `wubx`; truncate table tb_wubx | | mysql-bin.000004 | 722 | Query | 753306 | 794 | BEGIN | | mysql-bin.000004 | 794 | Table_map | 753306 | 847 | table_id: 92 (wubx.tb_wubx) | | mysql-bin.000004 | 847 | Write_rows | 753306 | 894 | table_id: 92 flags: STMT_END_F | | mysql-bin.000004 | 894 | Xid | 753306 | 925 | COMMIT /* xid=1081 */ | | mysql-bin.000004 | 925 | Query | 753306 | 997 | BEGIN | | mysql-bin.000004 | 997 | Table_map | 753306 | 1050 | table_id: 92 (wubx.tb_wubx) | | mysql-bin.000004 | 1050 | Update_rows | 753306 | 1113 | table_id: 92 flags: STMT_END_F | | mysql-bin.000004 | 1113 | Xid | 753306 | 1144 | COMMIT /* xid=1084 */ | +------------------+------+-------------+-----------+-------------+----------------------------------------------------+ 19 rows in set (0.00 sec) mysql>show binlog events in 'mysql-bin.000004'; +------------------+------+-------------+-----------+-------------+----------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+-------------+-----------+-------------+----------------------------------------------------+ | mysql-bin.000004 | 4 | Format_desc | 753306 | 120 | Server ver: 5.6.16-64.2-rel64.2-log, Binlog ver: 4 | | mysql-bin.000004 | 120 | Query | 753306 | 209 | use `wubx`; truncate table tb_wubx | | mysql-bin.000004 | 209 | Query | 753306 | 281 | BEGIN | | mysql-bin.000004 | 281 | Table_map | 753306 | 334 | table_id: 91 (wubx.tb_wubx) | | mysql-bin.000004 | 334 | Write_rows | 753306 | 393 | table_id: 91 flags: STMT_END_F | | mysql-bin.000004 | 393 | Xid | 753306 | 424 | COMMIT /* xid=1073 */ | | mysql-bin.000004 | 424 | Query | 753306 | 496 | BEGIN | | mysql-bin.000004 | 496 | Table_map | 753306 | 549 | table_id: 91 (wubx.tb_wubx) | | mysql-bin.000004 | 549 | Write_rows | 753306 | 602 | table_id: 91 flags: STMT_END_F | | mysql-bin.000004 | 602 | Xid | 753306 | 633 | COMMIT /* xid=1074 */ | | mysql-bin.000004 | 633 | Query | 753306 | 722 | use `wubx`; truncate table tb_wubx | | mysql-bin.000004 | 722 | Query | 753306 | 794 | BEGIN | | mysql-bin.000004 | 794 | Table_map | 753306 | 847 | table_id: 92 (wubx.tb_wubx) | | mysql-bin.000004 | 847 | Write_rows | 753306 | 894 | table_id: 92 flags: STMT_END_F | | mysql-bin.000004 | 894 | Xid | 753306 | 925 | COMMIT /* xid=1081 */ | | mysql-bin.000004 | 925 | Query | 753306 | 997 | BEGIN | | mysql-bin.000004 | 997 | Table_map | 753306 | 1050 | table_id: 92 (wubx.tb_wubx) | | mysql-bin.000004 | 1050 | Update_rows | 753306 | 1113 | table_id: 92 flags: STMT_END_F | | mysql-bin.000004 | 1113 | Xid | 753306 | 1144 | COMMIT /* xid=1084 */ | +------------------+------+-------------+-----------+-------------+----------------------------------------------------+ 19 rows in set (0.00 sec)
看到這個表剛開始就發生一次truncate, 那其實也可以說明我就恢復剛開始那個truncate到後來那個誤操作的truncate table的語句之間的數據就是丟失的數據。
這個恢復可以從mysql-bin.000004 pos: 4到mysql-bin.000004 pos: 633 即:
mysqlbinlog --rewrite-db='wubx->re_wubx' --start-position=4 --stop-position=633 mysql-bin.000004 |mysql -S /tmp/mysql.sock re_wubx mysqlbinlog --rewrite-db='wubx->re_wubx' --start-position=4 --stop-position=633 mysql-bin.000004 |mysql -S /tmp/mysql.sock re_wubx
恢復結果如下:
mysql -S /tmp/mysql.sock re_wubx; mysql>select count(*) from tb_wubx; +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.02 sec) mysql>select * from tb_wubx; +----+--------------+ | id | name | +----+--------------+ | 1 | 張三 | | 2 | 李四 | | 3 | 隔壁老王 | +----+--------------+ 3 rows in set (0.00 sec) mysql>insert into tb_wubx(name) select name from wubx.tb_wubx; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> rename table wubx.tb_wubx to wubx.bak_tb_wubx; Query OK, 0 rows affected (0.04 sec) mysql> rename table re_wubx.tb_wubx to wubx.tb_wubx; Query OK, 0 rows affected (0.03 sec) mysql> select * from wubx.tb_wubx; +----+--------------+ | id | name | +----+--------------+ | 1 | 張三 | | 2 | 李四 | | 3 | 隔壁老王 | | 4 | 老趙趙 | +----+--------------+ 4 rows in set (0.00 sec) mysql -S /tmp/mysql.sock re_wubx; mysql>select count(*) from tb_wubx; +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.02 sec) mysql>select * from tb_wubx; +----+--------------+ | id | name | +----+--------------+ | 1 | 張三 | | 2 | 李四 | | 3 | 隔壁老王 | +----+--------------+ 3 rows in set (0.00 sec) mysql>insert into tb_wubx(name) select name from wubx.tb_wubx; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> rename table wubx.tb_wubx to wubx.bak_tb_wubx; Query OK, 0 rows affected (0.04 sec) mysql> rename table re_wubx.tb_wubx to wubx.tb_wubx; Query OK, 0 rows affected (0.03 sec) mysql> select * from wubx.tb_wubx; +----+--------------+ | id | name | +----+--------------+ | 1 | 張三 | | 2 | 李四 | | 3 | 隔壁老王 | | 4 | 老趙趙 | +----+--------------+ 4 rows in set (0.00 sec)
恢復完成。