Mysql中二進(jìn)制日志操作方法有哪些

這篇文章主要介紹了Mysql中二進(jìn)制日志操作方法有哪些的相關(guān)知識(shí)，內(nèi)容詳細(xì)易懂，操作簡(jiǎn)單快捷，具有一定借鑒價(jià)值，相信大家閱讀完這篇Mysql中二進(jìn)制日志操作方法有哪些文章都會(huì)有所收獲，下面我們一起來(lái)看看吧。

二進(jìn)制日志

二進(jìn)制日志中以“事件”的形式記錄了數(shù)據(jù)庫(kù)中數(shù)據(jù)的變化情況，對(duì)于MySQL數(shù)據(jù)庫(kù)的災(zāi)難恢復(fù)起著重要的作用。

開啟二進(jìn)制日志

可以在 my.cnf 文件或者 my.ini 文件中進(jìn)行如下配置來(lái)開啟二進(jìn)制日志。

[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

各項(xiàng)配置說(shuō)明如下：

log_bin：表示開啟二進(jìn)制日志。如果沒(méi)有為此項(xiàng)賦值，則 MySQL 會(huì)在 DATADIR 選項(xiàng)指定的目錄（MySQL的數(shù)據(jù)存放目錄）下創(chuàng)建二進(jìn)制文件。

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

STATEMENT

記錄SQL語(yǔ)句。日志文件小，節(jié)約IO，但是對(duì)一些系統(tǒng)函數(shù)不能準(zhǔn)確復(fù)制或不能復(fù)制，如now()、uuid()等

ROW

記錄表的行更改情況，可以為數(shù)據(jù)庫(kù)的恢復(fù)、復(fù)制帶來(lái)更好的可靠性，但是二進(jìn)制文件的大小相較于STATEMENT會(huì)有所增加

MIXED

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

業(yè)內(nèi)目前推薦使用的是 ROW 模式，準(zhǔn)確性高，雖然說(shuō)文件大，但是現(xiàn)在有SSD和萬(wàn)兆光纖網(wǎng)絡(luò)，這些磁盤IO和網(wǎng)絡(luò)IO都是可以接受的。

• binlog_cache_size：二進(jìn)制日志的緩存大小。

• max_binlog_cache_size：二進(jìn)制日志的最大緩存大小。

• max_binlog_size：?jiǎn)蝹€(gè)二進(jìn)制日志文件的最大大小，當(dāng)文件大小超過(guò)此選項(xiàng)配置的值時(shí)，會(huì)發(fā)生日志滾動(dòng)，重新生成一個(gè)新的二進(jìn)制文件。

• expire_logs_days：二進(jìn)制日志的過(guò)期時(shí)間。如果配置了此選項(xiàng)，則 MySQL 會(huì)自動(dòng)清理過(guò)期的二進(jìn)制日志。此選項(xiàng)的默認(rèn)值為 0 ，表示 MySQL 不會(huì)清理過(guò)期日志。

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

重啟之后，可以看到我們配置的二進(jìn)制日志的相關(guān)信息。

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

查看二進(jìn)制日志

二進(jìn)制日志文件不能以純文本文件的形式來(lái)查看，可以使用 MySQL 的 mysqlbinlog 命令進(jìn)行查看。接下來(lái)簡(jiǎn)單介紹一下查看 MySQL 二進(jìn)制日志的步驟。

注：我提前創(chuàng)建的測(cè)試表

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

（1）向 test1 表中插入兩條測(cè)試數(shù)據(jù)。

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

（2）使用 mysqlbinlog 命令查看二進(jìn)制日志

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

二進(jìn)制日志中記錄了向 test1 數(shù)據(jù)表中插入數(shù)據(jù)的 SQL 語(yǔ)句。

注意：查看 /data/mysql/log/bin_log 目錄下生成的 MySQL 二進(jìn)制文件時(shí)，發(fā)現(xiàn)有一個(gè) mysql-bin.index 文件，這個(gè)文件不記錄二進(jìn)制內(nèi)容，其中記錄的是當(dāng)前目錄下存在的所有二進(jìn)制文件的完整路徑?？梢砸约兾谋疚募男问絹?lái)查看 mysql-bin.index 文件。

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

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

MySQL中除了通過(guò)配置二進(jìn)制日志的過(guò)期時(shí)間，由 MySQL 自動(dòng)刪除過(guò)期的二進(jìn)制日志外，還提供了3種安全的手動(dòng)刪除二進(jìn)制日志的方法。

在正式介紹手動(dòng)刪除 MySQL 二進(jìn)制日志的方法之前，先對(duì) MySQL 進(jìn)行多次重啟操作，使 MySQL 能夠生成多個(gè)二進(jìn)制日志文件，以便進(jìn)行刪除測(cè)試。

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

1．根據(jù)編號(hào)刪除二進(jìn)制日志

根據(jù)編號(hào)刪除二進(jìn)制日志，語(yǔ)法格式如下：

PURGE { BINARY | MASTER } LOGS TO 'log_name'

在MySQL命令行執(zhí)行此語(yǔ)法格式的SQL語(yǔ)句，會(huì)刪除比指定文件名編號(hào)小的所有二進(jìn)制日志文件。例如，刪除比mysql-bin.000003文件編號(hào)小的所有二進(jìn)制日志文件。

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

SQL語(yǔ)句執(zhí)行成功，查看/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

發(fā)現(xiàn)mysql-bin.000001文件和mysql-bin.000002文件被刪除了。說(shuō)明根據(jù)編號(hào)刪除二進(jìn)制日志時(shí)，只會(huì)刪除比當(dāng)前指定的文件編號(hào)小的二進(jìn)制日志文件，不會(huì)刪除當(dāng)前指定的二進(jìn)制日志文件。

2．根據(jù)時(shí)間刪除二進(jìn)制日志

根據(jù)時(shí)間刪除二進(jìn)制日志，語(yǔ)法格式如下：

PURGE { BINARY | MASTER } LOGS BEFORE datetime_expr

執(zhí)行此語(yǔ)法格式的SQL語(yǔ)句時(shí)，MySQL會(huì)刪除指定時(shí)間以前的二進(jìn)制日志。

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

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

SQL語(yǔ)句執(zhí)行成功，查看/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”之前的二進(jìn)制日志文件已經(jīng)被刪除了，但不會(huì)刪除“2020-01-17 16:21:00”時(shí)間點(diǎn)的二進(jìn)制日志文件。

3．刪除所有二進(jìn)制日志

在MySQL命令行執(zhí)行如下命令即可刪除所有二進(jìn)制日志文件。

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

SQL語(yǔ)句執(zhí)行成功，再次查看/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

此時(shí)/data/mysql/log/bin_log目錄下的所有二進(jìn)制文件已經(jīng)被刪除，并且二進(jìn)制文件重新從000001開始編號(hào)。

臨時(shí)開啟與關(guān)閉二進(jìn)制

在 Mysql 命令行執(zhí)行如下命令暫時(shí)關(guān)閉二進(jìn)制日志：

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

暫時(shí)開啟二進(jìn)制日志，則需要在MySQL命令行執(zhí)行如下命令：

mysql> SET sql_log_bin = 1;

Query OK, 0 rows affected (0.00 sec)

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