一、binlog概述

binlog是Mysql sever層維護(hù)的一種二進(jìn)制日志,與innodb引擎中的redo/undolog是完全不同的日志；

其主要是用來(lái)記錄對(duì)mysql數(shù)據(jù)更新或潛在發(fā)生更新的SQL語(yǔ)句，并以"事務(wù)"的形式保存在磁盤中；

作用主要有：

復(fù)制：MySQL Replication在Master端開(kāi)啟binlog，Master把它的二進(jìn)制日志傳遞給slaves并回放來(lái)達(dá)到master-slave數(shù)據(jù)一致的目的

數(shù)據(jù)恢復(fù)：通過(guò)mysqlbinlog工具恢復(fù)數(shù)據(jù)

增量備份：

二、開(kāi)啟binlog日志：

??vi編輯打開(kāi)mysql配置文件

??# vi /etc/my.cnf

??在[mysqld] 區(qū)塊

??設(shè)置/添加 log-bin=mysql-bin ?確認(rèn)是打開(kāi)狀態(tài)(值 mysql-bin 是日志的基本 名或前綴名)；

??重啟mysqld服務(wù)使配置生效

??#pkill mysqld

??#mysqld_safe --defaults-file=/etc/my.cnf &

??mysql> show variables like 'log_bin%';

??+---------------------------------+---------------------------------------+

??| Variable_name ??????????????????| Value ????????????????????????????????|

??+---------------------------------+---------------------------------------+

??| log_bin ????????????????????????| ON ???????????????????????????????????|

??| log_bin_basename ???????????????| /u01/mysql3308/binlog/mysql-bin ??????|

??| log_bin_index ??????????????????| /u01/mysql3308/binlog/mysql-bin.index |

??| log_bin_trust_function_creators | OFF ??????????????????????????????????|

??| log_bin_use_v1_row_events ??????| OFF ??????????????????????????????????|

??+---------------------------------+---------------------------------------+

三、常用binlog日志操作命令

???1.查看所有binlog日志列表

?????mysql> show master logs;

???2.查看master狀態(tài)，即最后(最新)一個(gè)binlog日志的編號(hào)名稱，及其最后一個(gè)操作事件pos結(jié)束點(diǎn)(Position)值

?????mysql> show master status;

???3.刷新log日志，自此刻開(kāi)始產(chǎn)生一個(gè)新編號(hào)的binlog日志文件

?????mysql> flush logs;

?????注：每當(dāng)mysqld服務(wù)重啟時(shí)，會(huì)自動(dòng)執(zhí)行此命令，刷新binlog日志；在mysqldump備份數(shù)據(jù)時(shí)加 -F 選項(xiàng)也會(huì)刷新binlog日志；

???4.刪除二進(jìn)制日志

?????a.mysql> reset master; ?重置(清空)所有binlog日志

?????b.purge master logs to 'mysql-bin.000006'; (刪除mysql-bin.000006之前的二進(jìn)制日志文件)

?????c.purge master logs before '2019-08-10 04:07:00'(刪除該日期之前的日志)

?????d.在my.cnf 配置文件中[mysqld]中添加:

???????expire_logs_day=3設(shè)置日志的過(guò)期天數(shù),過(guò)了指定的天數(shù),會(huì)自動(dòng)刪除

四、mysqlbinlog工具介紹

1、mybinlog常用參數(shù)介紹

??mysqlbinlog ?

???--base64-output ?控制解析日志文件編碼輸出參數(shù)如下：

?????auto：默認(rèn)常規(guī)輸出

?????never：不處理row格式日志

?????decode-rows：解碼處理，通常與-V 一起使用

??-v 重組偽SQL語(yǔ)句輸出，指定兩次-v，輸出信息中還包括列的數(shù)據(jù)類型信息。

??-d 只處理與指定數(shù)據(jù)庫(kù)相關(guān)的日志

??--start-datetime ?指定分析起始的時(shí)間點(diǎn)

??--stop-datetime 指定分析結(jié)束的時(shí)間點(diǎn) ?（可以做精確的時(shí)間點(diǎn)恢復(fù)）

??-j，--start-position：指定分析起始事件位置（#at 后面的值）

??--stopt-position：指定分析結(jié)束事件位置 （可以做基于位置點(diǎn)恢復(fù)）

??對(duì)于 mysqlbinlog的詳細(xì)信息，參見(jiàn)mysql手冊(cè)8.6節(jié)，“mysqlbinlog：用于處理二進(jìn)制日志文件的實(shí)用工具”。

??要想從二進(jìn)制日志恢復(fù)數(shù)據(jù)，你需要知道當(dāng)前二進(jìn)制日志文件的路徑和文件名。一般可以從選項(xiàng)文 件(即my.cnf or my.ini，取決于你的系統(tǒng))中找到路徑。

??如果未包含在選項(xiàng)文件中，當(dāng)服務(wù)器啟動(dòng)時(shí)，可以在命令行中以選項(xiàng)的形式給出。啟用二進(jìn)制日志的選項(xiàng)為--log-bin。

??要想確定當(dāng)前的二進(jìn)制日志文件的文件名，輸入下面的MySQL語(yǔ)句：

??SHOW BINLOG EVENTS G (查看初始的二進(jìn)制文件信息)

??你還可以從命令行輸入下面的內(nèi)容：

??mysql --user=root -pmy_pwd -e 'SHOW BINLOG EVENTS G'，將密碼my_pwd替換為服務(wù)器的root密碼。

??指定恢復(fù)時(shí)間

??mysqlbinlog語(yǔ)句中通過(guò)--start-datetime和--stop-datetime選項(xiàng)指定DATETIME格式的起止時(shí)間

??舉例說(shuō)明，假設(shè)在今天上午10:00(今天是2019年4月20日)，執(zhí)行SQL語(yǔ)句來(lái)刪除一個(gè)大表。要想恢復(fù)表和數(shù)據(jù)，你可以恢復(fù)前晚上的備份，并輸入：

??mysqlbinlog --stop-datetime="2019-04-20 9:59:59″ /u01/mysql3308/binlog/mysql-bin.000001 | mysql -u root -pmypwd

??該命令將恢復(fù)截止到在--stop-datetime選項(xiàng)中以DATETIME格式給出的日期和時(shí)間的所有數(shù)據(jù)。如果你沒(méi)有檢測(cè)到幾個(gè)小時(shí)后輸入的錯(cuò)誤的SQL語(yǔ)句，

??可能你想要恢復(fù)后面發(fā)生的活動(dòng)。根據(jù)這些，你可以用起使日期和時(shí)間再次運(yùn)行mysqlbinlog：

??mysqlbinlog –start-datetime=”2019-04-20 10:01:00″ /u01/mysql3308/binlog/mysql-bin.000001 | mysql -u root -pmypwd

??在該行中，從上午10:01登錄的SQL語(yǔ)句將運(yùn)行。組合執(zhí)行前夜的轉(zhuǎn)儲(chǔ)文件和mysqlbinlog的兩行可以將所有數(shù)據(jù)恢復(fù)到上午10:00前一秒鐘。

??指定恢復(fù)位置

??也可以不指定日期和時(shí)間，而使用mysqlbinlog的選項(xiàng)–start-position和–stop-position來(lái)指定日志位置。它們的作用與起止日選項(xiàng)相同，不同的是給出了從日志起的位置號(hào)。

??使用日志位置是更準(zhǔn)確的 恢復(fù)方法，特別是當(dāng)由于破壞性SQL語(yǔ)句同時(shí)發(fā)生許多事務(wù)的時(shí)候。要想確定位置號(hào)，可以運(yùn)行mysqlbinlog尋找執(zhí)行了不期望的事務(wù)的時(shí)間范圍，

??但應(yīng)將結(jié)果重新指向文本文件以便進(jìn)行檢查。操作方法為：

??mysqlbinlog --start-datetime="2019-04-20 9:55:00″ --stop-datetime="2005-04-20 10:05:00″/u01/mysql3308/binlog/mysql-bin.000001 > /tmp/mysql_restore.sql

??該命令將在/tmp目錄創(chuàng)建小的文本文件，將顯示執(zhí)行了錯(cuò)誤的SQL語(yǔ)句時(shí)的SQL語(yǔ)句。你可以用文本編輯器打開(kāi)該文件，尋找你不要想重復(fù)的語(yǔ)句。

??如果二進(jìn)制日志中的位置號(hào)用于停止和繼續(xù)恢復(fù)操作，應(yīng)進(jìn)行注釋。用log_pos加一個(gè)數(shù)字來(lái)標(biāo)記位置。使用位置號(hào)恢復(fù)了以前的備份文件后，你應(yīng)從命令行輸入下面內(nèi)容：

??mysqlbinlog –stop-position="368312″ /u01/mysql3308/binlog/mysql-bin.000001 | mysql -u root -pmypwd

??mysqlbinlog –start-position="368315″/u01/mysql3308/binlog/mysql-bin.000001 | mysql -u root -pmypwd

??上面的第1行將恢復(fù)到停止位置為止的所有事務(wù)。下一行將恢復(fù)從給定的起始位置直到二進(jìn)制日志結(jié)束的所有事務(wù)。

??因?yàn)閙ysqlbinlog的輸出包括每個(gè)SQL語(yǔ)句記錄之前的SET TIMESTAMP語(yǔ)句，恢復(fù)的數(shù)據(jù)和相關(guān)MySQL日志將反應(yīng)事務(wù)執(zhí)行的原時(shí)間

2、詳細(xì)信息輸出用例

??mysqlbinlog --base64-output=decode-rows -v -v mysql-bin.000009

??查看binlog文件中的內(nèi)容(--no--defaults 參數(shù)是為解決my.cnf 無(wú)法識(shí)別default-character-set=utf8mb4)

??mysqlbinlog --no-defaults mysql-bin.000009 ?

??分析結(jié)果輸出到sql文件

??mysqlbinlog ?mysql-bin.000009 ?> /backup/binlog/inc_000009.sql

??mysqlbinlog --stop-datetime="2020-04-10 10:25:20" mysql-bin.000008

??從開(kāi)頭顯示到20-10-26 10:25:20為止,此時(shí)間要在文件的損壞時(shí)間之前,提前1秒即可!

??mysqlbinlog --stop-datetime="2020-10-26 10:25:20" mysql-bin.000001 | mysql -uroot -p123 ?

??根據(jù)截至日期來(lái)恢復(fù)數(shù)據(jù)庫(kù)

??mysqlbinlog --no-defaults --start-datetime="2020-04-07 16:50:01" --stop-datetime="2020-04-07 16:58:51" ?mysql-bin.000002

??顯示這個(gè)之間的日志

??mysqlbinlog --start-position=469 --stop-position=562 mysql-bin.000001 |mysql -uroot -p123

??根據(jù)位置點(diǎn)來(lái)恢復(fù)數(shù)據(jù)庫(kù),位置點(diǎn)建議取實(shí)際開(kāi)始和結(jié)束的點(diǎn)而不是解析出來(lái)的位置點(diǎn)

3、mysqlbinlog 使用技巧

??技巧1 :

??在下面你將看到 mysqlbinlog --stop-datetime="2019-04-20 9:59:59″ /u01/mysql3308/binlog/mysql-bin.000001 | mysql -u root -pmypwd 類似的語(yǔ)句，

??但是它一次只能操作一個(gè)日志文件，如果你變通一下變成這樣 ?

??mysqlbinlog --stop-date="2019-04-20 9:59:59″/u01/mysql3308/binlog/mysql-bin.0* | mysql -u root -pmypwd ?

??那么它基本上就會(huì)表示出的所有的日志文件了，這樣可解決你忘記在哪一個(gè)日志文件中的問(wèn)題，當(dāng)然你也可以用這種寫法更完美,

??mysqlbinlog –stop-datetime="2019-04-20 9:59:59″/u01/mysql3308/binlog/mysql-bin.[0-9]* | mysql -u root -pmypwd ?

??看到[0-9]這個(gè)東東了吧，它表示以數(shù)字開(kāi)頭的任何字符，方便吧！

??技巧2:

??你可以通過(guò)--one-database 參數(shù)選擇性的恢復(fù)單個(gè)數(shù)據(jù)庫(kù)，example在下面，爽吧。

??mysqlbinlog --stop-datetime="2019-04-20 9:59:59″ /u01/mysql3308/binlog/mysql-bin.000001 | mysql -u root -pmypwd --one-database db_test

??技巧3:

??mysqlbinlog --start-datetimetime="2019-04-20 9:55:00″/u01/mysql3308/binlog/mysql-bin.0 > /home/db/tt.sql ?

??類似的語(yǔ)句將日志導(dǎo)成了ASCII文本文件，那么你就可以直接在phpmyadmin或者其它什么亂七糟八的的客戶端里執(zhí)行這個(gè)文件文件就行了[source *.sql]，

??因?yàn)樗旧砭褪且粋€(gè)標(biāo)準(zhǔn)的sql文件，比如想讓文件里面的某些語(yǔ)句不執(zhí)行，OK，it’s easy,找到它們刪除即可，然后再放進(jìn)去執(zhí)行就OK滴啦！這個(gè)可是灰?；页５乃瑔?。。。。。。

??技巧4:

??我來(lái)給大家講一下，下面這條語(yǔ)句都做了什么

??mysqlbinlog --stop-datetime="2019-04-20 9:59:59″ /u01/mysql3308/binlog/mysql-bin.000001 | mysql -u root -pmypwd --one-database db_test

??這是把mysql-bin.000001這個(gè)二進(jìn)制文件里的內(nèi)容轉(zhuǎn)換成ASCII文件(也就是sql語(yǔ)句)，直接通過(guò)管道操作符”|”傳輸給 mysql這個(gè)程序，然后過(guò)濾掉其它數(shù)據(jù)庫(kù)的語(yǔ)句，只在db_test里執(zhí)行。

五、binlog 恢復(fù)案例

五、binlog 恢復(fù)案例

??1、庫(kù)被刪除

???drop database test;

???恢復(fù)方式：

???獲取數(shù)據(jù)庫(kù)全備份--恢復(fù)備份--使用二進(jìn)制日志恢復(fù)

??1.1、恢復(fù)數(shù)據(jù)庫(kù)全備份

???myloader -u root -p root -S /u01/mysql3308/data/mysql.sock -B test -o -d /u01/mysql3308/backup/mysqldumper_test

??1.2、獲取刪除數(shù)據(jù)庫(kù)位置點(diǎn)

???mysqlbinlog --base64-output=decode-rows -v -v -d test /u01/mysql3308/binlog/mysql-bin.00000* | grep -i 'DROP DATABASE test' -A3 -B4

???---------------

??SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

??# at 14134

??#230208 10:45:58 server id 1 ?end_log_pos 14238 CRC32 0xad4dd6bf ???????Query ??thread_id=27 ???exec_time=0 ????error_code=0 ??????Xid = 767

??SET TIMESTAMP=1675824358/*!*/;

??drop database test

??/*!*/;

??SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

??DELIMITER ;

??--------------

??或者查看最近的binlog

??show binlog events in '/u01/mysql3308/binlog/mysql-bin.000002';

??| mysql-bin.000002 | 13736 | Write_rows ????| ????????1 | ??????13817 | table_id: 234 flags: STMT_END_F ??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????|

??| mysql-bin.000002 | 13817 | Xid ???????????| ????????1 | ??????13848 | COMMIT /* xid=764 */ ?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????|

??| mysql-bin.000002 | 13848 | Anonymous_Gtid | ????????1 | ??????13925 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' ?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????|

??| mysql-bin.000002 | 13925 | Query ?????????| ????????1 | ??????14057 | use `test`; DROP TABLE IF EXISTS `t_dept` /* generated by server */ ??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????|

??| mysql-bin.000002 | 14057 | Anonymous_Gtid | ????????1 | ??????14134 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' ?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????|

??| mysql-bin.000002 | 14134 | Query ?????????| ????????1 | ??????14238 | drop database test /* xid=767 */ ?

??1.3、獲取備份數(shù)據(jù)庫(kù)位置點(diǎn)

??more metadata ?

??SHOW MASTER STATUS:

?????????Log: mysql-bin.000002

?????????Pos: 11625

?????????GTID:

??1.4、執(zhí)行備份點(diǎn)到刪除點(diǎn)恢復(fù)

??mysqlbinlog --start-position=11625 --stop-position=14057 /u01/mysql3308/binlog/mysql-bin.000002| mysql -uroot -proot -S /u01/mysql3308/data/mysql.sock --one-database test

?2、表被刪除

??drop table tt9;

??恢復(fù)方式：

??獲取備份-恢復(fù)備份-使用二進(jìn)制日志恢復(fù)

?2.1、恢復(fù)表結(jié)構(gòu)（從全備份里獲取）

??sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `tt9`/!d;q' test_full.sql > tt9.sql

?2.2、獲取表的數(shù)據(jù)：

??grep -i 'INSERT INTO ??tt9??' test_full.sql >> tt9.sql

?2.3、恢復(fù)：

??mysql -usystem -p -S /usr/local/mysql/data/mysql.sock test < tt9.sql

??如果是mydumper備份

??直接執(zhí)行恢復(fù)，/u01/mysql/backup 下只包括要恢復(fù)的tt9 表文件

??myloader -u root -p root ?-B test -o -d /u01/mysql/backup

??確認(rèn)對(duì)象已經(jīng)恢復(fù)

?2.4、從binlog里恢復(fù)備份點(diǎn)到當(dāng)前點(diǎn)變化的數(shù)據(jù)

??獲取刪除點(diǎn)pos

??mysqlbinlog ??--no-defaults --base64-output=decode-rows -v -v ?-d test ?/usr/local/mysql/binlog/mysql-bin.00000* ??| grep ?-i 'DROP TABLE `tt9`' ?-A3 -B4

??-----------------------------------------------------------------------------------

??#200729 17:17:08 server id 1 ?end_log_pos 11778 CRC32 0xff8f558f ???????Anonymous_GTID ?last_committed=18 ??????sequence_number=19rbr_only=yes

??--

??SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

??# at 13819

??#200729 17:19:56 server id 1 ?end_log_pos 13935 CRC32 0xe89e1574 ???????Query ??thread_id=1449 ?exec_time=0 ????error_code=0

??SET TIMESTAMP=1596014396/*!*/;

??DROP TABLE `tt9` /* generated by server */

??/*!*/;

??# at 13935

??#200729 17:25:03 server id 1 ?end_log_pos 14000 CRC32 0xb30bcfa7 ???????Anonymous_GTID ?last_committed=26 ??????sequence_number=27rbr_only=no

??SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

??# at 14000

??------------------------------------------------------------------------------------

??獲取到刪除點(diǎn)pos: 13819

?2.5、獲取備份點(diǎn)到刪除點(diǎn)的binlog日志

??假如從上次備份，到發(fā)現(xiàn)表被刪除，共有兩個(gè)binlog文件，分別是mysql-bin.000003、mysql-bin.000004

??則按照binlog序號(hào)從小到大的排列，恢復(fù)的順序應(yīng)該是：（執(zhí)行兩個(gè)v 獲取偽列信息）

??mysqlbinlog ??--no-defaults ?--base64-output=decode-rows -v -v -d test ??/usr/local/mysql/binlog/mysql-bin.000003 ?> recover_tt9.sql

??mysqlbinlog ??--no-defaults ?--base64-output=decode-rows -v -v -d test ?--stop-position=13819 ?/usr/local/mysql/binlog/mysql-bin.000004 ?>> recover_tt9.sql

??注意：MySQL中binlog參數(shù):binlog_rows_query_log_events 開(kāi)啟后才能記錄完整的sql語(yǔ)句，不然就是變量形式。

??由于恢復(fù)的文件recover_tt9.sql中包含了整個(gè)test數(shù)據(jù)庫(kù)的所有表，我們只要恢復(fù)指定的表tt9，還要對(duì)恢復(fù)出來(lái)的sql進(jìn)行過(guò)濾。

??cat recover_tt9.sql | grep ?-A2 -B2 -i -E 'insert|update|delete|replace|alter' | grep -A2 -B2 tt9 ?> tt9.sql

??####cat recover_tt9.sql | grep --ignore-case -E 'insert|update|select|delete' -A2 -B2 | grep tt9 >tt9.sql

??過(guò)濾后，更改注釋及不要的，保存sql執(zhí)行恢復(fù)。

??注意：獲取實(shí)際執(zhí)行的語(yǔ)句，偽列信息需要?jiǎng)h除

?3、對(duì)象被更新

??獲取更新點(diǎn)

??分析binlog中關(guān)于test表的所有DML 操作輸出到文本文件

??mysqlbinlog ??--no-defaults ?--base64-output=decode-rows -v -v -d test ?--start-position=13910 ?/usr/local/mysql/binlog/mysql-bin.000007 ?>> recover_tt9.sql

??截取tt9 表的操作

??cat recover_tt9.sql | grep ?-A2 -B2 -i ?'update' | grep -A2 -B2 tt9 ?> tt9.sql

??編輯文件，做反向操作，然后執(zhí)行恢復(fù)

?4、對(duì)象新增加

??分析binlog中關(guān)于test表的所有DML 操作輸出到文本文件(也可以只查詢insert操作)

??mysqlbinlog ??--no-defaults ?--base64-output=decode-rows -v -v -d test ?--start-position=13910 ?/usr/local/mysql/binlog/mysql-bin.000007 ?>> recover_tt9.sql

??截取tt9 表的操作

??cat recover_tt9.sql | grep ?-A2 -B2 -i ?'insert' | grep -A2 -B2 tt9 ?> tt9.sql

??編輯文件，做反向(delete)操作，然后執(zhí)行恢復(fù)

?5、對(duì)象被刪除

??分析binlog中關(guān)于test表的所有DML 操作輸出到文本文件(也可以只查詢insert操作)

??mysqlbinlog ??--no-defaults ?--base64-output=decode-rows -v -v -d test ?--start-position=13910 ?/usr/local/mysql/binlog/mysql-bin.000007 ?>> recover_tt9.sql

??截取tt9 表的操作

??cat recover_tt9.sql | grep ?-A2 -B2 -i ?'delete' | grep -A2 -B2 tt9 ?> tt9.sql文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-651386.html

到了這里，關(guān)于mysql binlog 日志詳解及恢復(fù)的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！