MySQL通過Binlog恢復刪除的表

haoge0205發表於2015-08-14
MySql
檢視log-bin是否開啟：
mysql> show variables like '%log%bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+
3 rows in set (0.00 sec)


用sakila資料庫測試：
mysql> use sakila;
Database changed


檢視錶內容：
mysql> select * from yoon;
+----+------+
| id | name |
+----+------+
|  1 | yoon |
|  7 | aaa  |
+----+------+
2 rows in set (0.00 sec)


檢視日誌資訊：
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000025 |      932 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


mysql> drop table yoon;
Query OK, 0 rows affected (0.00 sec)


重新整理日誌：
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)


mysql> select * from yoon;
ERROR 1146 (42S02): Table 'sakila.yoon' doesn't exist


mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000026 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)




[root@yoon data]# ls
ibdata1  ib_logfile0  ib_logfile2  mysql-bin.000025  mysql-bin.index     rocover.sql  test
ibdata2  ib_logfile1  mysql        mysql-bin.000026  performance_schema  sakila


[root@yoon data]# mysqlbinlog mysql-bin.000025 | grep --ignore-case DROP -A3 -B4
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
DROP TABLE `yoon` /* generated by server */
/*!*/;
# at 215
#150814  3:34:55 server id 360360  end_log_pos 379      Query   thread_id=1     exec_time=3215  error_code=0
--
COMMIT/*!*/;
# at 932
#150814  4:42:00 server id 360360  end_log_pos 1040     Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1439541720/*!*/;
DROP TABLE `yoon` /* generated by server */
/*!*/;
# at 1040
#150814  4:42:10 server id 360360  end_log_pos 1083     Rotate to mysql-bin.000026  pos: 4


mysql> select from_unixtime('1439541720');
+-----------------------------+
| from_unixtime('1439541720') |
+-----------------------------+
| 2015-08-14 04:42:00         |
+-----------------------------+
1 row in set (0.00 sec)


###如果從上次備份重新整理binlog，到發現表被刪掉的過程中產生了多個binlog，則要按照binlog產生的順序，
那麼恢復的次序應該是按照binglog的產生的序號，從小到大依次恢復###


[root@yoon data]# mysqlbinlog -d sakila --stop-datetime='2015-08-14 04:42:00' mysql-bin.000025 > recover_sakila.sql


[root@yoon data]# mysql -uroot -p < recover_sakila.sql 
Enter password: 


mysql> select * from yoon;
+----+------+
| id | name |
+----+------+
|  1 | yoon |
|  7 | aaa  |
+----+------+
2 rows in set (0.00 sec)

過濾方法：（因為測試中只有一個表，而生產環境中就會有多張表）
[root@yoon data]# more recover_sakila.sql | grep --ignore-case -E 'insert|update|select|delete' -A2 -B2 | grep yoon

如果表名包含yoon_log,yoon_order,只想匯出yoon表的話，+個-w
[root@yoon data]# more recover_sakila.sql | grep --ignore-case -E 'insert' -A2 -B2 | grep -w yoon > yoon.sql
insert into yoon(name) values ('yoon')
insert into yoon(name) values ('aaa')


[root@yoon data]# more recover_sakila.sql | grep --ignore-case -E 'create' -A2 -B2 | grep yoon                           
create table yoon (id int(11) unsigned NOT NULL AUTO_INCREMENT,name varchar(20),PRIMARY KEY (`id`))

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28939273/viewspace-1771728/，如需轉載，請註明出處，否則將追究法律責任。

相關文章