Mysql中二進制日志操作方法有哪些

這篇文章主要介紹了Mysql中二進制日志操作方法有哪些的相關知識，內容詳細易懂，操作簡單快捷，具有一定借鑒價值，相信大家閱讀完這篇Mysql中二進制日志操作方法有哪些文章都會有所收獲，下面我們一起來看看吧。

二進制日志

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

開啟二進制日志

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

[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 的二進制文件。

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

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 二進制日志的步驟。

注：我提前創建的測試表

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

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

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．根據編號刪除二進制日志

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

PURGE { BINARY | MASTER } LOGS TO 'log_name'

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

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

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

[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．根據時間刪除二進制日志

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

PURGE { BINARY | MASTER } LOGS BEFORE datetime_expr

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

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

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目錄下的文件。

[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命令行執行如下命令即可刪除所有二進制日志文件。

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

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

[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開始編號。

臨時開啟與關閉二進制

在 Mysql 命令行執行如下命令暫時關閉二進制日志：

mysql> SET sql_log_bin = 0;
Query OK, 0 rows affected (0.00 sec)

暫時開啟二進制日志，則需要在MySQL命令行執行如下命令：

mysql> SET sql_log_bin = 1;

Query OK, 0 rows affected (0.00 sec)

關于“Mysql中二進制日志操作方法有哪些”這篇文章的內容就介紹到這里，感謝各位的閱讀！相信大家對“Mysql中二進制日志操作方法有哪些”知識都有一定的了解，大家如果還想學習更多知識，歡迎關注億速云行業資訊頻道。