MySQL binlog恢複

標籤：AC   xid   sed   llb   mysq   exec   gtid   connect   amp   

mysql binlog恢複資料，有時候可能不小心delete了資料，一下子捉急了，怎麼辦？  binlog來恢複（前提是你開啟了binlog），怎麼開啟呢？

在my.cnf檔案中添加如下

[mysqld]
log_bin = mysql_bin

重啟服務即可

接下來講講怎麼恢複，刪除資料後，馬上查看當前的資料庫處於哪個binlog檔案：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 | 1016 | | | |
+------------------+----------+--------------+------------------+-------------------+

接下來查看binlog檔案在哪

find / -name mysql_bin.000001

去到binlog的目錄下，執行mysqlbinlog mysql_bin.000001（這裡我的檔案比較小，檔案大的話加上過濾或者分頁）

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @[email protected]@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180409 20:56:53 server id 1 end_log_pos 120 CRC32 0xf20fd50f Start: binlog v 4, server v 5.6.39-log created 180409 20:56:53 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG ‘
FWPLWg8BAAAAdAAAAHgAAAABAAQANS42LjM5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAVY8taEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQ/V
D/I=
‘/*!*/;
# at 120
#180409 20:58:03 server id 1 end_log_pos 199 CRC32 0x2c3cc333 Querythread_id=2exec_time=0error_code=0
SET TIMESTAMP=1523278683/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 199
#180409 20:58:03 server id 1 end_log_pos 313 CRC32 0xd0d61192 Querythread_id=2exec_time=0error_code=0
use `test`/*!*/;
SET TIMESTAMP=1523278683/*!*/;
insert into by_year values(‘2019-10-10‘)
/*!*/;
# at 313
#180409 20:58:03 server id 1 end_log_pos 344 CRC32 0x614b6da7 Xid = 14
COMMIT/*!*/;
# at 344
#180409 20:58:20 server id 1 end_log_pos 423 CRC32 0x7610d8d6 Querythread_id=2exec_time=0error_code=0
SET TIMESTAMP=1523278700/*!*/;
BEGIN
/*!*/;
# at 423
#180409 20:58:20 server id 1 end_log_pos 537 CRC32 0xea1fbbcb Querythread_id=2exec_time=0error_code=0
SET TIMESTAMP=1523278700/*!*/;
delete from by_year where d=‘2019-10-10‘  （刪除語句）
/*!*/;
# at 537
#180409 20:58:20 server id 1 end_log_pos 568 CRC32 0xacbe35a7 Xid = 15
COMMIT/*!*/;

很明顯，這時候只需要把前面插入的語句恢複即可

找到對應的時間點，將binlog匯出到sql檔案中

mysqlbinlog --start-datetime=‘2018-04-09 20:58:00‘ --stop-datetime=‘2018-04-09 20:58:18‘  mysql_bin.000001 > tmp.sql

再將sql匯入即可

 

MySQL binlog恢複