MySQL 誤操作 資料恢複

binlog介紹

1. binlog,即二進制日志,它記錄了資料庫上的所有改變.
2. SQL語句執行結束時,将在binlog的末尾寫入一條記錄,同時通知語句解析器,語句執行完畢.
3. binlog格式

• 基于語句,無法保證所有語句都在從庫執行成功,比如update … limit 1;
• 基于行,将每一次改動記為binlog中的一行.在執行一個特别複雜的update或者delete操作時,基于行的格式會有優勢.

是否啟用了日志

mysql>show variables like ‘log_bin’;

目前的日志

mysql> show master status;

看二進制日志檔案用mysqlbinlog

shell>mysqlbinlog -v mail-bin.000001

或者shell>mysqlbinlog -v mail-bin.000001 | tail

binglog的類型

binlog的格式有三種，這也反應了mysql的複制技術：基于SQL語句的複制(statement-based replication, SBR)，基于行的複制(row-based replication, RBR)，混合模式複制(mixed-based replication, MBR)。相應地，binlog的格式也有三種：STATEMENT，ROW，MIXED。

mysql>showvariables like ‘binlog_format’    #檢視binlog的格式

mysqlbinlog 恢複

不同的mysql mysqlbinlog版本不一樣，需要擷取到程序自身的mysqlbinlog

1. 隻檢視第一個binlog檔案的内容

   show binlog events;

2. 檢視指定binlog檔案的内容

   show binlog events in 'mysql-bin.000002'

   ;
3. 檢視目前正在寫入的binlog檔案（目前資料庫使用的binlog）

   show master status

4. 擷取binlog檔案清單

   show binary logs

5. 檢視所有binlog日志清單

   show master logs;

6. 清空binlog日志檔案

   reset master

7. 重新整理 log 日志，重新生成一個新編号的 binlog 日志檔案

   flush logs;

導出 binlog

mysqlbinlog -v binlog.000003 > /home/ryan_binlog.sql           

記得要加上 -v，不然導出時，無法看到修改的SQL語句，也就無法進行定位，

-v 選項可以從行事件中重建僞 SQL 語句。

導入 binlog，恢複資料

基于開始/結束時間

mysqlbinlog binlog.000003 --start-datetime=2021-01-20 10:00:00 --stop-datetime=2021-01-23 10:00:00 -v | mysql -uroot -p           

基于 pos 位點

mysqlbinlog binlog.000003 --start-position=528 --stop-position=1778 -v | mysql -uroot -p           

或者也可以使用下面的工具生成復原 SQL 進行恢複：

https://github.com/zhengjianhong001/binlog2sql

https://dev.mysql.com/doc/internals/en/binary-log.html

https://www.jianshu.com/p/c16686b35807

http://www.ttlsa.com/mysql/super-useful-mysqlbinlog-command/