一、binlog日志基本概念

   binlog是MySQL sever層維護的一種二進制日志，binlog是記錄所有數(shù)據(jù)庫表結構變更（例如CREATE、ALTER TABLE、DROP等）以及表數(shù)據(jù)修改（INSERT、UPDATE、DELETE、TRUNCATE等）的二進制日志。不會記錄SELECT和SHOW這類操作，因為這類操作對數(shù)據(jù)本身并沒有修改。

作用主要有：

• 主從復制：在MySQL的Master節(jié)點開啟binlog，Master把它的二進制日志傳遞給slaves并回放來達到master-slave數(shù)據(jù)一致的目的。
• 數(shù)據(jù)恢復：通過mysqlbinlog工具來恢復數(shù)據(jù)

二、開啟binlog日志記錄

2.1、查看binlog日志記錄啟用狀態(tài)

MySQL安裝完成后，MySQL5.7版本binlog默認是不開啟的，MySQL8默認開啟binlog，登錄MySQL后，可以通過SHOW VARIABLES LIKE '%log_bin%';命令查看是否開啟binlog。

# 登錄 mysql
mysql -h127.0.0.1 -P3306 -uroot -p123456

# 查看是否開啟binlog
mysql> SHOW VARIABLES LIKE '%log_bin%';

log_bin 的Value如果為ON代表開啟，如果為OFF代表關閉，我這里使用的是MySQL8.0默認是開啟的，如果沒有開啟可以通過下面方式開啟：

2.2、開啟配置binlog日志

修改MySQL配置文件，linux中配置文件為my.conf，window下為my.ini，下面以centos為例演示：

編輯配置文件

# 在centos中mysql的配置文件一般都在/etc/mysql目錄下，如果不在可以通過 find / -name "my.cnf" 查找
vi /etc/mysql/my.cnf

添加配置

# 服務ID
server-id=1
# binlog 配置 只要配置了log_bin地址 就會開啟
log_bin = /var/lib/mysql/mysql_bin
# 日志存儲天數(shù) 默認0 永久保存
# 如果數(shù)據(jù)庫會定期歸檔，建議設置一個存儲時間不需要一直存儲binlog日志，理論上只需要存儲歸檔之后的日志
expire_logs_days = 30
# binlog最大值
max_binlog_size = 1024M
# 規(guī)定binlog的格式，binlog有三種格式statement、row、mixad，默認使用statement，建議使用row格式
binlog_format = ROW
# 在提交n次事務后，進行binlog的落盤，0為不進行強行的刷新操作，而是由文件系統(tǒng)控制刷新日志文件，如果是在線交易和賬有關的數(shù)據(jù)建議設置成1，如果是其他數(shù)據(jù)可以保持為0即可
sync_binlog = 1

重啟MySQL服務使配置生效

systemctl restart mysqld

登錄MySQL查看配置是否生效

mysql> SHOW VARIABLES LIKE '%log_bin%';

• log_bin為ON代表MySQL已經(jīng)開啟binlog日志記錄
• log_bin_basename配置了binlog的文件路徑及文件前綴名
• log_bin_index配置了binlog索引文件的路徑

查看日志列表

mysql> SHOW MASTER LOGS;

根據(jù)log_bin_basename的路徑查看binlog具體文件

ls -l /var/lib/mysql/mysql_bin.*

三、制作測試數(shù)據(jù)（可以先不執(zhí)行，這里是為后續(xù)數(shù)據(jù)恢復做準備，先看數(shù)據(jù)恢復流程）

登錄MySQL后創(chuàng)建庫、創(chuàng)建表、在插入一些數(shù)據(jù)

登錄數(shù)據(jù)庫

mysql -h127.0.0.1 -P3306 -uroot -p123456

創(chuàng)建庫

CREATE DATABASE binlog_test_db;

切換到自己創(chuàng)建的數(shù)據(jù)庫

USE binlog_test_db;

創(chuàng)建表

DROP TABLE IF EXISTS `binlog_test_table`;
CREATE TABLE `binlog_test_table`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `nick_name` varchar(255) DEFAULT NULL,
  `create_time` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB ;

插入數(shù)據(jù)

INSERT INTO `binlog_test_table` VALUES (1, 'Alia', NOW());
INSERT INTO `binlog_test_table` VALUES (2, 'Kerwin', NOW());
INSERT INTO `binlog_test_table` VALUES (3, 'Hilaria', NOW());
INSERT INTO `binlog_test_table` VALUES (4, 'Coco', NOW());

查看創(chuàng)建的庫

mysql> SHOW DATABASES;

查看創(chuàng)建的表和數(shù)據(jù)

# 切換到binlog_test_db庫
mysql> USE binlog_test_db;
# 查看庫中所有的表
mysql> SHOW TABLES;
# 查看表中的數(shù)據(jù)
mysql> SELECT * FROM binlog_test_table;

四、使用binlog日志恢復數(shù)據(jù)

當數(shù)據(jù)庫發(fā)生變化時，binlog會記錄數(shù)據(jù)庫中的所有變化，需要恢復的時候可以通過mysqlbinlog工具，根據(jù)binlog中的開始位置和結束位置或者開始時間和結束時間還原本部分操作，結束位置或者結束時間一般是數(shù)據(jù)被破壞或刪除之前的位置。

4.1、前置準備

自己測試的話先要確保有一個干凈的環(huán)境，避免出現(xiàn)一些奇怪問題導致浪費時間，這里先做一些前置準備，也就是說自己的binlog文件中最好只有當前測試的數(shù)據(jù)記錄，避免出現(xiàn)干擾。

1、登錄MySQL執(zhí)行下面命令刷新log日志，自此刻開始產生一個新編號的binlog日志文件

# 注：每當mysqld服務重啟時，會自動執(zhí)行此命令，刷新binlog日志；在mysqldump備份數(shù)據(jù)時加 -F 選項也會刷新binlog日志
mysql> FLUSH LOGS;

2、查看當前的binlog文件

ls -l /var/lib/mysql/mysql_bin.*

我這里在刷新binlog之前是mysql_bin.000003，刷新后新生成了一個mysql_bin.000004，后續(xù)日志都會記錄在這個新的文件中。

4.2、恢復全部數(shù)據(jù)

在恢復數(shù)據(jù)前首先要制作好測試數(shù)據(jù)，恢復全部數(shù)據(jù)就是將一個完整的binlog都執(zhí)行，比如我們在制作測試數(shù)據(jù)時創(chuàng)建庫、創(chuàng)建表、插入數(shù)據(jù)等操作都會記錄在binlog中，使用這個binlog恢復數(shù)據(jù)等于將我們上面的操作又執(zhí)行了一次。

數(shù)據(jù)恢復演示流程：

1、執(zhí)行4.1前置準備中的操作，生成一個新的binlog文件，假設我這里生成新的文件叫做mysql_bin.000004，后續(xù)數(shù)據(jù)的操作日志都會記錄在這個日志文件中。

2、準備數(shù)據(jù)，直接執(zhí)行本文第三點制作測試數(shù)據(jù)中的SQL即可。

3、將mysql_bin.000004這個binlog日志歸檔，因為我們需要通過mysql_bin.000004這個日志恢復全部數(shù)據(jù)，所以要在刪除數(shù)據(jù)庫之前歸檔，如果刪除數(shù)據(jù)庫語句也被記錄在mysql_bin.000004中，那么執(zhí)行完mysql_bin.000004庫又會被刪除了。

# 登錄數(shù)據(jù)庫執(zhí)行刷新log日志，自此刻開始產生一個新編號的binlog日志文件
mysql> FLUSH LOGS;

4、刪除數(shù)據(jù)庫

mysql> DROP DATABASE binlog_test_db;

這里可以看到binlog_test_db庫已經(jīng)被刪除了。

5、通過mysqlbinlog執(zhí)行恢復全部

# mysqlbinlog是MySQL自帶的一個工具，一般在安裝MySQL時mysqlbinlog也會被放入可執(zhí)行目錄下，可以直接執(zhí)行
# 如果不能直接使用mysqlbinlog命令，可以通過 find / -name "mysqlbinlog" 查找一下這個工具在什么位置
# 然后指定全路徑執(zhí)行即可：如 /usr/bin/mysqlbinlog --no-defaults ...
mysqlbinlog --no-defaults /var/lib/mysql/mysql_bin.000004 | mysql -h127.0.0.1 -P3306 -uroot -p123456

6、查看數(shù)據(jù)恢復情況

# 查看全部庫
mysql> SHOW DATABASES;
# 切換到binlog_test_db庫
mysql> USE binlog_test_db;
# 查看庫中所有的表
mysql> SHOW TABLES;
# 查看表中的數(shù)據(jù)
mysql> SELECT * FROM binlog_test_table;

到這里可以看到數(shù)據(jù)已經(jīng)全部恢復，但是還存在幾個問題：

1、如果存在多個binlog文件如何恢復全部數(shù)據(jù) 存在多個binlog文件依次恢復即可，必須按照日志序號來恢復，假設有三個日志文件序號是000001、000002、000003，那么第一個恢復的一定是000001，第二個恢復000002，最后恢復000003。

2、如果我們在同一個日志文件中也進行了刪庫操作怎么恢復 全部恢復原理就是將之前做過的操作全做一次，有刪庫操作自然也會在操作一次，這樣肯定是違背我們的需求的，解決方法其實就是不去執(zhí)行某個節(jié)點后的操作就行，比如刪庫操作是在節(jié)點5執(zhí)行，那么我們只恢復到節(jié)點4即可，在后續(xù)會詳細說明。

4.3、通過指定位置區(qū)間恢復

和恢復全部數(shù)據(jù)不同的是可以指定一個位置區(qū)間恢復數(shù)據(jù)，這樣如果刪除庫操作和數(shù)據(jù)插入操作都在同一個日志文件中也可以進行數(shù)據(jù)恢復，而且很靈活。

數(shù)據(jù)恢復演示流程：

1、執(zhí)行4.1前置準備中的操作，生成一個新的binlog文件，假設我這里生成新的文件叫做mysql_bin.000006，后續(xù)數(shù)據(jù)的操作日志都會記錄在這個日志文件中。

2、準備數(shù)據(jù)，直接執(zhí)行本文第三點制作測試數(shù)據(jù)中的SQL即可。

3、刪除數(shù)據(jù)庫

mysql> DROP DATABASE binlog_test_db;

這里可以看到binlog_test_db庫已經(jīng)被刪除了。

4、將mysql_bin.000006這個binlog日志歸檔，為了防止恢復數(shù)據(jù)后影響最新業(yè)務，需要執(zhí)行flush logs，產生一個新的binlog文件，此時舊的binlog文件不會再有寫入。

# 登錄數(shù)據(jù)庫執(zhí)行刷新log日志，自此刻開始產生一個新編號的binlog日志文件
mysql> FLUSH LOGS;

5、通過mysqlbinlog將binlog轉為sql，以方便查詢具體位置

mysqlbinlog --set-charset=utf-8 /var/lib/mysql/mysql_bin.000006>binlog_000006.sql

6、查看生成的binlog_000006.sql，確定需要恢復的起始位置結束位置還有起始時間和結束時間

# 打開 binlog_000006.sql 文件，搜索自己的創(chuàng)建庫語句確認起始位置和時間，在搜索刪除庫語句確認結束位置和時間
vi binlog_000006.sql

確認開始位置為232，開始時間為 23年9月27日 17:17:23

確認結束位置為2220，結束時間為 23年9月27日 17:18:36

7、通過mysqlbinlog執(zhí)行恢復操作

# 通過位置區(qū)間恢復 
mysqlbinlog --no-defaults /var/lib/mysql/mysql_bin.000006 --start-position=232 --stop-position=2220 | mysql -h127.0.0.1 -P3306 -uroot -p123456

• /var/lib/mysql/mysql_bin.000006 ： 要操作binlog文件
• –start-position=232 ：數(shù)據(jù)恢復的起始位置
• –stop-position=2220 ：數(shù)據(jù)恢復的結束位置
• mysql -h127.0.0.1 -P3306 -uroot -p123456 ： 數(shù)據(jù)恢復需要登錄數(shù)據(jù)庫

總結

到此這篇關于MySQL開啟配置binlog及通過binlog恢復數(shù)據(jù)的文章就介紹到這了,更多相關MySQL開啟配置binlog及恢復數(shù)據(jù)內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論