一、binlog日志基本概念
?? binlog是MySQL sever層維護的一種二進制日志，binlog是記錄所有數據庫表結構變更（例如CREATE、ALTER TABLE、DROP等）以及表數據修改（INSERT、UPDATE、DELETE、TRUNCATE等）的二進制日志。不會記錄SELECT和SHOW這類操作，因為這類操作對數據本身并沒有修改。
作用主要有：
- 主從復制：在MySQL的Master節(jié)點開啟binlog，Master把它的二進制日志傳遞給slaves并回放來達到master-slave數據一致的目的。
- 數據恢復：通過mysqlbinlog工具來恢復數據
二、開啟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
# 日志存儲天數 默認0 永久保存
# 如果數據庫會定期歸檔，建議設置一個存儲時間不需要一直存儲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)控制刷新日志文件，如果是在線交易和賬有關的數據建議設置成1，如果是其他數據可以保持為0即可
sync_binlog = 1
- 重啟MySQL服務使配置生效
systemctl restart mysqld
- 登錄MySQL查看配置是否生效
mysql> SHOW VARIABLES LIKE '%log_bin%';
log_bin為ON代表MySQL已經開啟binlog日志記錄
log_bin_basename配置了binlog的文件路徑及文件前綴名
log_bin_index配置了binlog索引文件的路徑
- 查看日志列表
mysql> SHOW MASTER LOGS;
- 根據log_bin_basename的路徑查看binlog具體文件
ls -l /var/lib/mysql/mysql_bin.*
三、制作測試數據（可以先不執(zhí)行，這里是為后續(xù)數據恢復做準備，先看數據恢復流程）
登錄MySQL后創(chuàng)建庫、創(chuàng)建表、在插入一些數據
- 登錄數據庫
mysql -h127.0.0.1 -P3306 -uroot -p123456
- 創(chuàng)建庫
CREATE DATABASE binlog_test_db;
- 切換到自己創(chuàng)建的數據庫
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 ;
- 插入數據
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)建的表和數據
# 切換到binlog_test_db庫
mysql> USE binlog_test_db;
# 查看庫中所有的表
mysql> SHOW TABLES;
# 查看表中的數據
mysql> SELECT * FROM binlog_test_table;
四、使用binlog日志恢復數據
當數據庫發(fā)生變化時，binlog會記錄數據庫中的所有變化，需要恢復的時候可以通過mysqlbinlog工具，根據binlog中的開始位置和結束位置或者開始時間和結束時間還原本部分操作，結束位置或者結束時間一般是數據被破壞或刪除之前的位置。
4.1、前置準備
自己測試的話先要確保有一個干凈的環(huán)境，避免出現一些奇怪問題導致浪費時間，這里先做一些前置準備，也就是說自己的binlog文件中最好只有當前測試的數據記錄，避免出現干擾。
-
1、登錄MySQL執(zhí)行下面命令刷新log日志，自此刻開始產生一個新編號的binlog日志文件
# 注：每當mysqld服務重啟時，會自動執(zhí)行此命令，刷新binlog日志；在mysqldump備份數據時加 -F 選項也會刷新binlog日志 mysql> FLUSH LOGS; -
2、查看當前的binlog文件
ls -l /var/lib/mysql/mysql_bin.*
我這里在刷新binlog之前是mysql_bin.000003，刷新后新生成了一個mysql_bin.000004，后續(xù)日志都會記錄在這個新的文件中。
4.2、恢復全部數據
在恢復數據前首先要制作好測試數據，恢復全部數據就是將一個完整的binlog都執(zhí)行，比如我們在制作測試數據時創(chuàng)建庫、創(chuàng)建表、插入數據等操作都會記錄在binlog中，使用這個binlog恢復數據等于將我們上面的操作又執(zhí)行了一次。
數據恢復演示流程：
-
1、執(zhí)行4.1前置準備中的操作，生成一個新的binlog文件，假設我這里生成新的文件叫做mysql_bin.000004，后續(xù)數據的操作日志都會記錄在這個日志文件中。
-
2、準備數據，直接執(zhí)行本文第三點制作測試數據中的SQL即可。
-
3、將mysql_bin.000004這個binlog日志歸檔，因為我們需要通過mysql_bin.000004這個日志恢復全部數據，所以要在刪除數據庫之前歸檔，如果刪除數據庫語句也被記錄在mysql_bin.000004中，那么執(zhí)行完mysql_bin.000004庫又會被刪除了。
# 登錄數據庫執(zhí)行刷新log日志，自此刻開始產生一個新編號的binlog日志文件 mysql> FLUSH LOGS;
-
4、刪除數據庫
mysql> DROP DATABASE binlog_test_db;
這里可以看到binlog_test_db庫已經被刪除了。 -
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、查看數據恢復情況
# 查看全部庫 mysql> SHOW DATABASES; # 切換到binlog_test_db庫 mysql> USE binlog_test_db; # 查看庫中所有的表 mysql> SHOW TABLES; # 查看表中的數據 mysql> SELECT * FROM binlog_test_table;
到這里可以看到數據已經全部恢復，但是還存在幾個問題：
- 1、如果存在多個binlog文件如何恢復全部數據
- 存在多個binlog文件依次恢復即可，必須按照日志序號來恢復，假設有三個日志文件序號是000001、000002、000003，那么第一個恢復的一定是000001，第二個恢復000002，最后恢復000003。
- 2、如果我們在同一個日志文件中也進行了刪庫操作怎么恢復
- 全部恢復原理就是將之前做過的操作全做一次，有刪庫操作自然也會在操作一次，這樣肯定是違背我們的需求的，解決方法其實就是不去執(zhí)行某個節(jié)點后的操作就行，比如刪庫操作是在節(jié)點5執(zhí)行，那么我們只恢復到節(jié)點4即可，在后續(xù)會詳細說明。
4.3、通過指定位置區(qū)間恢復
和恢復全部數據不同的是可以指定一個位置區(qū)間恢復數據，這樣如果刪除庫操作和數據插入操作都在同一個日志文件中也可以進行數據恢復，而且很靈活。
數據恢復演示流程：
-
1、執(zhí)行4.1前置準備中的操作，生成一個新的binlog文件，假設我這里生成新的文件叫做mysql_bin.000006，后續(xù)數據的操作日志都會記錄在這個日志文件中。
-
2、準備數據，直接執(zhí)行本文第三點制作測試數據中的SQL即可。
-
3、刪除數據庫
mysql> DROP DATABASE binlog_test_db;
這里可以看到binlog_test_db庫已經被刪除了。 -
4、將mysql_bin.000006這個binlog日志歸檔，為了防止恢復數據后影響最新業(yè)務，需要執(zhí)行flush logs，產生一個新的binlog文件，此時舊的binlog文件不會再有寫入。
# 登錄數據庫執(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 ：數據恢復的起始位置
–stop-position=2220 ：數據恢復的結束位置
mysql -h127.0.0.1 -P3306 -uroot -p123456 ： 數據恢復需要登錄數據庫文章來源：http://www.zghlxwxcb.cn/news/detail-768953.html
文章來源地址http://www.zghlxwxcb.cn/news/detail-768953.html
到了這里，關于MySQL 開啟配置binlog以及通過binlog恢復數據的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網！
