本文的操作環(huán)境是 macOS Mojave 10.14.1, mysql 5.7.19
二進(jìn)制日志的作用
二進(jìn)制日志主要記錄 MySQL 數(shù)據(jù)庫的變化。它會記錄所有更改表數(shù)據(jù)和表結(jié)構(gòu)的事件，比如 update望门、delete 等善绎，它也會記錄語句執(zhí)行所用的時間祥楣。為了保證事件的準(zhǔn)確性检访，會記錄事件的長度眶拉。它不會記錄 select罗晕、show 語句的執(zhí)行济欢。使用二進(jìn)制日志主要有兩種用途：
- 主從備份赠堵。主庫上的二進(jìn)制日志記錄主庫的所有變更，主庫會把日志包含的事件發(fā)給從庫法褥，從庫執(zhí)行這些事件顾腊，從而與主庫的數(shù)據(jù)保持一致；
- 從二進(jìn)制日志恢復(fù)數(shù)據(jù)庫挖胃。
Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order.
在 commit 執(zhí)行之前杂靶，寫入二進(jìn)制日志。
配置方式
默認(rèn)情況下酱鸭，二進(jìn)制日志是關(guān)閉的吗垮，可以通過修改 MySQL 的配置文件來啟動和設(shè)置二進(jìn)制日志。
查看 MySQL 配置文件的路徑：
shell> mysql --help | grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
如果是用 brew 安裝的 mysql凹髓，配置文件默認(rèn)在 /usr/local/etc/my.cnf
在配置文件的 [mysqld] 下面添加：
# 由于 mysql 的 bug烁登，必須配置 server-id，可以是任意值
server-id=11
# 開啟二進(jìn)制日志蔚舀，日志文件名稱是 mysql-bin
log-bin=mysql-bin
# 設(shè)置過期時間饵沧，10天后自動刪除舊的日志。默認(rèn)是 0赌躺，表示永不刪除
expire_logs_days = 10
# 設(shè)置每個日志文件的大小狼牺，超過這個值，重新創(chuàng)建新的文件礼患。最小值是4096B是钥，最大值是 1GB，默認(rèn)是1GB
max_binlog_size = 100M
注意：必須配置 server-id缅叠，否則啟動 MySQL 會報錯悄泥，官方文檔有說明：
If you specify the
--log-binoption without also specifying a--server-id, the server is not allowed to start. (Bug #11763963, Bug #56739)
日志文件默認(rèn)保存在datadir目錄下，也可以設(shè)置為其他路徑肤粱，比如 log-bin=/usr/local/var/mysql/mysql-bin弹囚。即使在文件名稱后面添加后綴，mysql 也會忽略领曼，mysql 會添加數(shù)字后綴鸥鹉。
也可以直接配置為 log-bin，不加任何參數(shù)悯森。此時日志文件的默認(rèn)名稱是 host_name-bin宋舷，host_name 是你的主機(jī)名稱绪撵。
除了二進(jìn)制日志瓢姻，mysql 還會創(chuàng)建一個 mysql-bin.index 文件，該文件保存所有的二進(jìn)制日志的文件名稱音诈』眉睿可以直接使用 cat 命令查看绎狭，不要手動修改這個文件。
另外褥傍，每次重新啟動 mysql儡嘶，也會創(chuàng)建新的二進(jìn)制日志。
當(dāng)前客戶端可以暫時關(guān)閉二進(jìn)制日志： SET sql_log_bin=OFF恍风。
查看方法
使用 show variables like 'log_bin%'; 查詢二進(jìn)制日志設(shè)置：
使用 show binary logs; 查看二進(jìn)制日志文件個數(shù)及文件名稱：
使用 show master status 查看當(dāng)前正在使用的日志：
使用 SHOW SLAVE STATUS 可以在從庫上查看它在讀取哪個日志蹦狂；
使用 mysqlbinlog 查看二進(jìn)制日志的內(nèi)容，紅色方框里面是該文件的創(chuàng)建時間：
二進(jìn)制日志的格式
有三種格式：row-based logging（ROW）朋贬，statement-based logging（STATEMENT）凯楔，mixed-base logging（MIXED）。
There are several reasons why a client might want to set binary logging on a per-session basis:
- A session that makes many small changes to the database might want to use row-based logging.
- A session that performs updates that match many rows in the
WHEREclause might want to use statement-based logging because it will be more efficient to log a few statements than many rows. - Some statements require a lot of execution time on the master, but result in just a few rows being modified. It might therefore be beneficial to replicate them using row-based logging.
官方文檔有這樣兩句話：
Statement may not be safe to log in statement format.
You can avoid these issues by using MySQL's row-based replication instead.
查看日志格式：
mysql> select @@session.binlog_format;
mysql> select @@global.binlog_format;
改變?nèi)罩靖袷剑?/p>
全局改變：
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';
客戶端改變：
mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';
下面這張圖锦募，上面的日志是 ROW 格式摆屯，選中的日志是 STATEMENT 格式：
刪除二進(jìn)制日志
使用 reset master; 刪除所有的二進(jìn)制日志文件。MySQL 會重新創(chuàng)建二進(jìn)制日志糠亩，新的日志文件擴(kuò)展名從 000001 開始編號虐骑。
MySQL 還提供了刪除指定日志的方法：
purge master logs to 'log_name' 刪除比 log_name 編號小的日志，比如 purge master logs to 'mysql-bin.000003';赎线，編號小于 000003 的日志會被刪除廷没。
purge master logs before 'datatime' 刪除 datatime 之前的日志，日期格式是 YYYY-MM-DD hh:mm:ss垂寥。
如果日志文件正在用于主從備份腕柜，它不會被刪除。
使用二進(jìn)制日志恢復(fù)數(shù)據(jù)庫
在使用 mysql 的全量備份恢復(fù)數(shù)據(jù)庫之后矫废，可以再使用二進(jìn)制日志恢復(fù)到指定時間點盏缤。比如當(dāng)前時間是11點多，我不小心把一個重要的庫刪除了蓖扑。幸好每天凌晨兩點做了數(shù)據(jù)庫全量備份唉铜，這時我就可以先恢復(fù)數(shù)據(jù)庫到兩點，然后再使用二進(jìn)制日志恢復(fù)到刪除之前的數(shù)據(jù)庫律杠。
直接使用日志文件恢復(fù)數(shù)據(jù)庫：
shell> mysqlbinlog binlog_files | mysql -u root -p
可以把日志文件導(dǎo)出文本潭流，然后編輯文本，刪除一些不需要執(zhí)行的語句柜去，然后再恢復(fù)數(shù)據(jù)庫：
shell> mysqlbinlog binlog_files > tmpfile
shell> ... edit tmpfile ...
shell> mysql -u root -p < tmpfile
如果有多個日志文件灰嫉，在一個連接中使用它們：
shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p
恢復(fù)到指定時間點：
shell> mysqlbinlog --stop-datetime="2018-10-25 11:00:00" \
/var/log/mysql/bin.123456 | mysql -u root -p
從指定時間點恢復(fù)：
shell> mysqlbinlog --start-datetime="2018-10-25 10:01:00" \
/var/log/mysql/bin.123456 | mysql -u root -p
根據(jù)位置恢復(fù)：
shell> mysqlbinlog --stop-position=368312 /var/log/mysql/bin.123456 \
| mysql -u root -p
shell> mysqlbinlog --start-position=368315 /var/log/mysql/bin.123456 \
| mysql -u root -p
位置信息可以從日志文件的 log_pos 獲取。
