首頁 > 軟體

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:二進位制檔案的格式。取值可以是STATEMENTROWMIXED

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!


IT145.com E-mail:sddin#qq.com