Mysql中二進位制紀錄檔操作方法說明

2023-03-19 06:04:03

二進位制紀錄檔

二進位制紀錄檔中以“事件”的形式記錄了資料庫中資料的變化情況，對於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-winx64databinlog |

| log_bin_index | D:mysql-8.0.28-winx64databinlog.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二進位制紀錄檔內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！