本文的操作環(huán)境是 macOS Mojave 10.14.1, mysql 5.7.19

二進(jìn)制日志的作用

二進(jìn)制日志主要記錄 MySQL 數(shù)據(jù)庫的變化。它會(huì)記錄所有更改表數(shù)據(jù)和表結(jié)構(gòu)的事件，比如 update、delete 等，它也會(huì)記錄語句執(zhí)行所用的時(shí)間。為了保證事件的準(zhǔn)確性，會(huì)記錄事件的長度。它不會(huì)記錄 select、show 語句的執(zhí)行。使用二進(jìn)制日志主要有兩種用途：

1. 主從備份。主庫上的二進(jìn)制日志記錄主庫的所有變更，主庫會(huì)把日志包含的事件發(fā)給從庫，從庫執(zhí)行這些事件，從而與主庫的數(shù)據(jù)保持一致；
2. 從二進(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 的配置文件來啟動(dòng)和設(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è)置過期時(shí)間，10天后自動(dòng)刪除舊的日志。默認(rèn)是 0，表示永不刪除
expire_logs_days = 10
# 設(shè)置每個(gè)日志文件的大小，超過這個(gè)值，重新創(chuàng)建新的文件。最小值是4096B，最大值是 1GB，默認(rèn)是1GB
max_binlog_size = 100M

注意：必須配置 server-id，否則啟動(dòng) MySQL 會(huì)報(bào)錯(cuò)，官方文檔有說明：

If you specify the --log-bin option 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 也會(huì)忽略，mysql 會(huì)添加數(shù)字后綴。

也可以直接配置為 log-bin，不加任何參數(shù)。此時(shí)日志文件的默認(rèn)名稱是 host_name-bin，host_name 是你的主機(jī)名稱。

除了二進(jìn)制日志，mysql 還會(huì)創(chuàng)建一個(gè) mysql-bin.index 文件，該文件保存所有的二進(jìn)制日志的文件名稱?？梢灾苯邮褂?cat 命令查看，不要手動(dòng)修改這個(gè)文件。

另外，每次重新啟動(dòng) mysql，也會(huì)創(chuàng)建新的二進(jìn)制日志。

當(dāng)前客戶端可以暫時(shí)關(guān)閉二進(jìn)制日志： SET sql_log_bin=OFF。

查看方法

使用 show variables like 'log_bin%'; 查詢二進(jìn)制日志設(shè)置：

image

使用 show binary logs; 查看二進(jìn)制日志文件個(gè)數(shù)及文件名稱：

image

使用 show master status 查看當(dāng)前正在使用的日志：

image

使用 SHOW SLAVE STATUS 可以在從庫上查看它在讀取哪個(gè)日志；

使用 mysqlbinlog 查看二進(jìn)制日志的內(nèi)容，紅色方框里面是該文件的創(chuàng)建時(shí)間：

image

二進(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 WHERE clause 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 格式：

image

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

使用 reset master; 刪除所有的二進(jìn)制日志文件。MySQL 會(huì)重新創(chuàng)建二進(jìn)制日志，新的日志文件擴(kuò)展名從 000001 開始編號(hào)。

MySQL 還提供了刪除指定日志的方法：

purge master logs to 'log_name' 刪除比 log_name 編號(hào)小的日志，比如 purge master logs to 'mysql-bin.000003';，編號(hào)小于 000003 的日志會(huì)被刪除。

purge master logs before 'datatime' 刪除 datatime 之前的日志，日期格式是 YYYY-MM-DD hh:mm:ss。

如果日志文件正在用于主從備份，它不會(huì)被刪除。

使用二進(jìn)制日志恢復(fù)數(shù)據(jù)庫

在使用 mysql 的全量備份恢復(fù)數(shù)據(jù)庫之后，可以再使用二進(jìn)制日志恢復(fù)到指定時(shí)間點(diǎn)。比如當(dāng)前時(shí)間是11點(diǎn)多，我不小心把一個(gè)重要的庫刪除了。幸好每天凌晨兩點(diǎn)做了數(shù)據(jù)庫全量備份，這時(shí)我就可以先恢復(fù)數(shù)據(jù)庫到兩點(diǎn)，然后再使用二進(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

如果有多個(gè)日志文件，在一個(gè)連接中使用它們：

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

恢復(fù)到指定時(shí)間點(diǎn)：

shell> mysqlbinlog --stop-datetime="2018-10-25 11:00:00" \
         /var/log/mysql/bin.123456 | mysql -u root -p

從指定時(shí)間點(diǎn)恢復(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 獲取。

參考

1. MySQL 二進(jìn)制日志簡介

2. 二進(jìn)制日志所有參數(shù)

3. mysqlbinlog 的使用方法

4. 使用二進(jìn)制日志恢復(fù)數(shù)據(jù)庫