一、binlog日志基本概念

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

作用主要有：

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

二、開啟binlog日志記錄

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

MySQL安裝完成后，MySQL5.7版本binlog默認(rèn)是不開啟的，MySQL8默認(rèn)開啟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代表關(guān)閉，我這里使用的是MySQL8.0默認(rèn)是開啟的，如果沒有開啟可以通過下面方式開啟：

2.2、開啟配置binlog日志

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

編輯配置文件

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

添加配置

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

重啟MySQL服務(wù)使配置生效

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.*

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

登錄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日志恢復(fù)數(shù)據(jù)

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

4.1、前置準(zhǔn)備

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

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

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

2、查看當(dāng)前的binlog文件

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

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

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

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

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

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

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

3、將mysql_bin.000004這個(gè)binlog日志歸檔，因?yàn)槲覀冃枰ㄟ^mysql_bin.000004這個(gè)日志恢復(fù)全部數(shù)據(jù)，所以要在刪除數(shù)據(jù)庫之前歸檔，如果刪除數(shù)據(jù)庫語句也被記錄在mysql_bin.000004中，那么執(zhí)行完mysql_bin.000004庫又會(huì)被刪除了。

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

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

mysql> DROP DATABASE binlog_test_db;

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

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

# mysqlbinlog是MySQL自帶的一個(gè)工具，一般在安裝MySQL時(shí)mysqlbinlog也會(huì)被放入可執(zhí)行目錄下，可以直接執(zhí)行
# 如果不能直接使用mysqlbinlog命令，可以通過 find / -name "mysqlbinlog" 查找一下這個(gè)工具在什么位置
# 然后指定全路徑執(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ù)恢復(fù)情況

# 查看全部庫
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)全部恢復(fù)，但是還存在幾個(gè)問題：

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

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

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

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

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

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

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

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

mysql> DROP DATABASE binlog_test_db;

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

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

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

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

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

6、查看生成的binlog_000006.sql，確定需要恢復(fù)的起始位置結(jié)束位置還有起始時(shí)間和結(jié)束時(shí)間

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

確認(rèn)開始位置為232，開始時(shí)間為 23年9月27日 17:17:23

確認(rèn)結(jié)束位置為2220，結(jié)束時(shí)間為 23年9月27日 17:18:36

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

# 通過位置區(qū)間恢復(fù) 
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ù)恢復(fù)的起始位置
• –stop-position=2220 ：數(shù)據(jù)恢復(fù)的結(jié)束位置
• mysql -h127.0.0.1 -P3306 -uroot -p123456 ： 數(shù)據(jù)恢復(fù)需要登錄數(shù)據(jù)庫

總結(jié)

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

最新評(píng)論