MySQL通過binlog恢復數據
任何成熟軟件都會有一套成熟的日志系統，當軟件出現問題時，這些日志就是查詢問題來源的寶庫。同樣，mysql也不例外，也會有一系列日志記錄mysql的運行狀態。
mysql主要有以下幾種日志：
錯誤日志：記錄mysql運行過程中的錯誤信息 一般查詢日志：記錄mysql正在運行的語句，包括查詢、修改、更新等的每條sql 慢查詢日志：記錄查詢比較耗時的SQL語句 binlog日志：記錄數據修改記錄，包括創建表、數據更新等這些日志均需要在my.cnf文件進行配置，如果不知道mysql的配置文件路徑，可以使用mysql命令進行查找，
mysql --verbose --help|grep -A 1 ’Default options’ #該命令會羅列出my.cnf順序查找的路徑。binlog日志
binlog就是binary log，二進制日志文件，記錄所有數據庫更新語句，包括表更新和記錄更新，即數據操縱語言(DML)，binlog主要用于數據恢復和配置主從復制等；
數據恢復：當數據庫誤刪或者發生不可描述的事情時，可以通過binlog恢復到某個時間點的數據。主從復制：當有數據庫更新之后，主庫通過binlog記錄并通知從庫進行更新，從而保證主從數據庫數據一致；
mysql按照功能分為服務層模塊和存儲引擎層模塊，服務層負責客戶端連接、SQL語句處理優化等操作，存儲引擎層負責數據的存儲和查詢；binlog屬于服務層模塊的日志，即引擎無關性，所有數據引擎的數據更改都會記錄binlog日志。當數據庫發生崩潰時，如果使用InnoDB引擎，binlog日志還可以檢驗InnoDB的redo日志的commit情況。
binlog日志開啟日志開啟方式：1、添加配置
log_bin=ONlog_bin_basename=/path/bin-loglog_bin_index=/path/bin-log.index
2、僅僅設置log-bin參數
log-bin=/path/bin-log
當開啟binlog日志之后，mysql會創建一個 log_bin_index指定的 .index 文件和多個二進制日志文件，index中按順序記錄了mysql使用的所有binlog文件。binlog日志則會以指定的名稱(或默認值) 加自增的數字作為后綴，ex：bin-log.000001，當發生下述三種情況時，binlog日志便會進行重建:
文件大小達到max_binlog_size參數的值 執行 flush logs命令 重啟mysql服務
binlog 日志格式通過參數binlog_format參數的值，可以設置binlog的格式，可選值有 statement、row、mixed * statement格式：記錄數據庫執行的原始SQL語句 * row格式：記錄具體的行的修改，這個為目前默認值 * mixed格式：因為上邊兩種格式各有優缺點，所以就出現了mixed格式
binlog日志查看工具：mysqlbinlog因為binlog是二進制文件，不能像其他文件一樣，直接打開查看。但mysql提供了binlog查看工具mysqlbinlog，可以解析二進制文件。當然不同格式的日志解析結果是不一樣的； 1. statement格式日志，執行mysqlbinlog /path/bin-log.000001，可以直接看到原始執行的SQL語句 2. row格式日志，則可讀性沒有那么好，但仍可通過參數使文檔更加可讀 mysqlbinlog -v /path/bin-log.000001
mysqlbinlog兩對非常重要的參數 1. --start-datetime --stop-datetime 解析某一個時間段內的binlog； 2. --start-position --stop-position 解析在兩個position之間的binlog；
使用binlog恢復數據使用binlog恢復數據，本質上就是通過binlog找到所有DML操作，去掉錯誤的SQL語句，然后重走一遍長征路，就可以將數據恢復；
線下實操1.創建數據表并插入初始值
CREATE TABLE `users` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `age` int(8) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `users` (`id`, `name`, `age`)VALUES (null, ’姓名一’, 5);
2.找到上一次全量備份的數據庫和binlog的position(ps：當然也可以通過時間進行恢復)。此處以目前狀態作為備份的初始值，
mysqldump -uroot -p T > /path/xxx.sql; # 備份數據庫 show master status; # 查看當前的position位置，此時值為154
3.插入多條記錄
INSERT INTO `users` (`id`, `name`, `age`) VALUES (null, ’姓名二’, 13), (null, ’姓名三’, 14), (null, ’姓名四’, 15), (null, ’姓名五’, 16), (null, ’姓名六’, 17);
4.進行誤操作，并且在誤操作之后又插入幾條數據
update users set age = 5； INSERT INTO `users` (`id`, `name`, `age`) VALUES (null, ’姓名七’, 16), (null, ’姓名八’, 18);
5.發現誤操作之后，進行數據恢復，首先停止mysql對外的服務，利用備份數據恢復到上次數據；
6.通過mysqlbinlog命令對二進制文件進行分析，分析發現
誤操作發生在position為706位置，且上次正常操作的結束位置在513 在1152到結尾位置有正常執行的SQL執行
7.通過mysqlbinlog命令從binlog日志中導出可執行的SQL文件，并將數據導入到mysql
mysqlbinlog --start-position=154 --stop-position=513 bin-log.000001 > /path/bak.sql; mysql -uroot -p < /path/bak.sql;
8.跳過錯誤的更新語句，再通過步驟7的邏輯把后續正常語句重新跑一遍，完成數據恢復工作
小結無論什么時間，數據庫發生崩潰都會令人愁眉緊鎖，心煩意亂。binlog可以說是在各種情況下，數據庫崩潰、數據丟失之后的一粒后悔藥，本文通過線下環境，簡單的對數據庫進行了一次數據恢復實驗，如有不對，還請指教
以上就是MySQL通過 binlog 恢復數據的詳細內容，更多關于MySQL binlog 恢復數據的資料請關注好吧啦網其它相關文章！
相關文章：
網公網安備