當前位置：首頁 > 數據庫 > Mysql > 詳細頁面

Mysql中二進制日志操作方法說明

時間：2023-03-19來源：系統城裝機大師作者：佚名

二進制日志

二進制日志中以“事件”的形式記錄了數據庫中數據的變化情況，對于MySQL數據庫的災難恢復起著重要的作用。

開啟二進制日志

可以在 my.cnf 文件或者 my.ini 文件中進行如下配置來開啟二進制日志。

1
2
3
4
5
6
7 [mysqld]
log_bin = /data/mysql/log/bin_log/mysql-bin
binlog_format= mixed
binlog_cache_size=32m
max_binlog_cache_size=64m
max_binlog_size=512m
expire_logs_days = 10

各項配置說明如下：

log_bin：表示開啟二進制日志。如果沒有為此項賦值，則 MySQL 會在 DATADIR 選項指定的目錄（MySQL的數據存放目錄）下創建二進制文件。

binlog_format：二進制文件的格式。取值可以是STATEMENT、ROW和MIXED。

STATEMENT

記錄SQL語句。日志文件小，節約IO，但是對一些系統函數不能準確復制或不能復制，如now()、uuid()等

ROW

記錄表的行更改情況，可以為數據庫的恢復、復制帶來更好的可靠性，但是二進制文件的大小相較于STATEMENT會有所增加

MIXED

STATEMENT和ROW模式的混合。默認采用STATEMENT格式進行二進制日志文件的記錄，但是在一些情況下會使用ROW格式。

業內目前推薦使用的是 ROW 模式，準確性高，雖然說文件大，但是現在有SSD和萬兆光纖網絡，這些磁盤IO和網絡IO都是可以接受的。

binlog_cache_size：二進制日志的緩存大小。
max_binlog_cache_size：二進制日志的最大緩存大小。
max_binlog_size：單個二進制日志文件的最大大小，當文件大小超過此選項配置的值時，會發生日志滾動，重新生成一個新的二進制文件。
expire_logs_days：二進制日志的過期時間。如果配置了此選項，則 MySQL 會自動清理過期的二進制日志。此選項的默認值為 0 ，表示 MySQL 不會清理過期日志。

配置完成后，重啟 MySQL 才能使配置生效。此時，會在 /data/mysql/log/bin_log 目錄下生成 MySQL 的二進制文件。

重啟之后，可以看到我們配置的二進制日志的相關信息。

1	`show variables` `like` `'%log_bin%';`

±--------------------------------±-----------------------------------------+

| Variable_name | Value |

±--------------------------------±-----------------------------------------+

| log_bin | ON |

| log_bin_basename | D:\mysql-8.0.28-winx64\data\binlog |

| log_bin_index | D:\mysql-8.0.28-winx64\data\binlog.index |

| log_bin_trust_function_creators | ON |

| log_bin_use_v1_row_events | OFF |

| sql_log_bin | ON |

±--------------------------------±-----------------------------------------+

6 rows in set

查看二進制日志

二進制日志文件不能以純文本文件的形式來查看，可以使用 MySQL 的 mysqlbinlog 命令進行查看。接下來簡單介紹一下查看 MySQL 二進制日志的步驟。

注：我提前創建的測試表

1
2
3
4
5 CREATE TABLE `test1` (
`id` int NOT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

（1）向 test1 表中插入兩條測試數據。

1 2	`INSERT` `INTO` `test1 (id,name)` `VALUES(5,"趙山河");` `INSERT` `INTO` `test1 (id,name)` `VALUES(6,"跟著學編程");`

（2）使用 mysqlbinlog 命令查看二進制日志

mysqlbinlog --no-defaults /data/mysql/log/bin_log/mysql-bin.000001

二進制日志中記錄了向 test1 數據表中插入數據的 SQL 語句。

注意：查看 /data/mysql/log/bin_log 目錄下生成的 MySQL 二進制文件時，發現有一個 mysql-bin.index 文件，這個文件不記錄二進制內容，其中記錄的是當前目錄下存在的所有二進制文件的完整路徑?？梢砸约兾谋疚募男问絹聿榭?mysql-bin.index 文件。

cat /data/mysql/log/bin_log/mysql-bin.index

刪除二進制日志

MySQL中除了通過配置二進制日志的過期時間，由 MySQL 自動刪除過期的二進制日志外，還提供了3種安全的手動刪除二進制日志的方法。

在正式介紹手動刪除 MySQL 二進制日志的方法之前，先對 MySQL 進行多次重啟操作，使 MySQL 能夠生成多個二進制日志文件，以便進行刪除測試。

多次重啟MySQL后，再次查看 /data/mysql/log/bin_log 目錄下的文件。

1．根據編號刪除二進制日志

根據編號刪除二進制日志，語法格式如下：

1	`PURGE {` `BINARY` `\| MASTER } LOGS` `TO` `'log_name'`

在MySQL命令行執行此語法格式的SQL語句，會刪除比指定文件名編號小的所有二進制日志文件。例如，刪除比mysql-bin.000003文件編號小的所有二進制日志文件。

1 2	`mysql> PURGE MASTER LOGS` `TO` `'mysql-bin.000003';` `Query OK, 0` `rows` `affected (0.01 sec)`

SQL語句執行成功，查看/data/mysql/log/bin_log目錄下的文件。

1
2
3
4
5
6
7
8
9
10
11 [root@binghe150 ~]# ll /data/mysql/log/bin_log
total 36
-rw-r----- 1 mysql mysql 178 Jan 17 16:20 mysql-bin.000003
-rw-r----- 1 mysql mysql 178 Jan 17 16:20 mysql-bin.000004
-rw-r----- 1 mysql mysql 178 Jan 17 16:20 mysql-bin.000005
-rw-r----- 1 mysql mysql 178 Jan 17 16:20 mysql-bin.000006
-rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000007
-rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000008
-rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000009
-rw-r----- 1 mysql mysql 155 Jan 17 16:21 mysql-bin.000010
-rw-r----- 1 mysql mysql 328 Jan 17 16:29 mysql-bin.index

發現mysql-bin.000001文件和mysql-bin.000002文件被刪除了。說明根據編號刪除二進制日志時，只會刪除比當前指定的文件編號小的二進制日志文件，不會刪除當前指定的二進制日志文件。

2．根據時間刪除二進制日志

根據時間刪除二進制日志，語法格式如下：

1	`PURGE {` `BINARY` `\| MASTER } LOGS BEFORE datetime_expr`

執行此語法格式的SQL語句時，MySQL會刪除指定時間以前的二進制日志。

例如，刪除“2020-01-17 16:21:00”之前的二進制日志文件。

1 2	`mysql> PURGE MASTER LOGS BEFORE` `'2020-01-17 16:21:00';` `Query OK, 0` `rows` `affected (0.00 sec)`

SQL語句執行成功，查看/data/mysql/log/bin_log目錄下的文件。

1
2
3
4
5
6
7 [root@binghe150 ~]# ll /data/mysql/log/bin_log
total 20
-rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000007
-rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000008
-rw-r----- 1 mysql mysql 178 Jan 17 16:21 mysql-bin.000009
-rw-r----- 1 mysql mysql 155 Jan 17 16:21 mysql-bin.000010
-rw-r----- 1 mysql mysql 164 Jan 17 16:37 mysql-bin.index

“2020-01-17 16:21:00”之前的二進制日志文件已經被刪除了，但不會刪除“2020-01-17 16:21:00”時間點的二進制日志文件。

3．刪除所有二進制日志

在MySQL命令行執行如下命令即可刪除所有二進制日志文件。

1 2	`mysql> RESET MASTER;` `Query OK, 0` `rows` `affected (0.01 sec)`

SQL語句執行成功，再次查看/data/mysql/log/bin_log目錄下的文件。

1
2
3
4 [root@binghe150 ~]# ll /data/mysql/log/bin_log
total 8
-rw-r----- 1 mysql mysql 155 Jan 17 16:41 mysql-bin.000001
-rw-r----- 1 mysql mysql 41 Jan 17 16:41 mysql-bin.index

此時/data/mysql/log/bin_log目錄下的所有二進制文件已經被刪除，并且二進制文件重新從000001開始編號。