SpringBoot數(shù)據(jù)庫恢復的兩種方法mysqldump和mysqlbinlog
更新時間：2024年01月22日 14:59:50 作者：風流 少年
binlog用來實現(xiàn)主從復制,也常用來誤刪數(shù)據(jù)庫找回丟失的記錄,本文主要介紹了SpringBoot數(shù)據(jù)庫恢復的兩種方法mysqldump和mysqlbinlog,具有一定的參考價值,感興趣的可以了解一下
一：什么是bin log
MySQL的所有的數(shù)據(jù)庫改變操作（除了select等）都會記錄到一種二進制文件中，這種文件就是bin log。
bin log 用來實現(xiàn)主從復制，也常用來誤刪數(shù)據(jù)庫找回丟失的記錄。
一般恢復從兩種緯度來進行恢復：
- 一部分從mysqldump中恢復備份到之前的狀態(tài)。
- 另一部分在恢復到備份之前的基礎上再從bin log中找出需要恢復的數(shù)據(jù)。
二：查看bin log配置
- log_bin ：是否開啟bin log，默認值為
ON表示開啟。 - log_bin_basename ：配置bin log文件所在的目錄（
/usr/local/mysql/data）以及文件名前綴（mysql-bin）。 - bin log文件名是從
mysql-bin.000001開始的。log_bin_index：配置索引對應的bin log文件的絕對路徑，文件中記錄所有bin log文件名。/usr/local/mysql/data/mysql-bin.index。
三：刪除bin log文件
為了測試方便可以先刪除之前的bin log文件。
注意：最好是自己本地開發(fā)環(huán)境來執(zhí)行以下命令，千萬不要在測試環(huán)境或者其它共用環(huán)境刪除bin log。
# 用來查看當前最新的bin log文件及對應的結束位置 show master status; # 查看所有bin log文件 show binary logs; # 生成一個新的bin log文件（推薦：自己玩的時候可以先生成一個新的bin log文件，所有新操作都記錄在新生成的文件中） flush binary logs; # 刪除所有binlog日志，新日志編號從頭000001開始（建議自己玩的時候可以使用） reset master; # 刪除mysql-bin.000001之前的所有bin log purge master logs to 'mysql-bin.000001'; # 刪除指定時間之前的數(shù)據(jù) purge master logs before '2022-09-01 23:59:59';
四：準備測試數(shù)據(jù)
執(zhí)行SQL時不要一下子全部執(zhí)行，為了后續(xù)方便演示基于時間范圍的操作，需要一條一條的執(zhí)行，最好每條命令間隔1秒以上執(zhí)行。
CREATE DATABASE `db1`;
use db1;
CREATE TABLE `tbl_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL COMMENT '用戶名',
`status` tinyint(4) DEFAULT NULL COMMENT '狀態(tài)(0:關閉，1：打開)',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into tbl_user(username, status) values('monday', 0);
insert into tbl_user(username, status) values('vbirdbest', 1);
update tbl_user set status = 1 where username = 'monday';
delete from tbl_user where username = 'vbirdbest';
drop database db1;
五：查看bin log
- bin log 需要關注的字段：
- Log_name：bin log 文件名。
Pos：開始位置點。End_log_pos：結束位置點。- Event_type：事件類型（Query：DML語句，Write_rows：insert語句，Update_rows：update語句，Delete_rows：delete語句）。
方式一：show binlog events
# 查看當前最新的bin log文件 mysql> show master status; # 查看bin文件基本信息, Pos表示位置position，如904位置是insert一條數(shù)據(jù) mysql> show binlog events in 'mysql-bin.000001';
方式二：mysqlbinlog命令
mysqlbinlog方式和show binlog events方式差不多，其中 at 表示開始位置Pos(position)，end_log_pos表示結束位置點。
- bin log中創(chuàng)建數(shù)據(jù)庫create database、創(chuàng)建表create table、刪除數(shù)據(jù)庫drop database 都是直接記錄的SQL語句，
- 插入insert、更新update、刪除delete 都是編碼之后的人類不可讀的內(nèi)容存在。所以需要
--base64-output=DECODE-ROWS -vv解碼。 - 每個SQL操作都會記錄
操作時間，#220903 17:08:44。
# 如果報權限問題切換root用戶 mysqlbinlog: File '/usr/local/mysql/data/mysql-bin.000001' not found (Errcode: 13 - Permission denied) ~ sudo su # 解碼：將insert/update/delete等解碼成SQL語句 mysqlbinlog --no-defaults --skip-gtids --base64-output=DECODE-ROWS -vv /usr/local/mysql/data/mysql-bin.000001
六：恢復數(shù)據(jù)mysqlbinlog
6.1 mysqlbinlog命令常用參數(shù)
--no-defaults：如果不報錯可以不使用該參數(shù)。mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'。--skip-gtids=true：gtid是一種全局唯一id。設置為true表示導出的sql中不包含gtid相關的設置。--base64-output=DECODE-ROWS -vv：bin log內(nèi)容經(jīng)過編碼了，需要解碼之后人類才可讀。--start-position=801：恢復的開始位置。注意：開始位置一般取BEGIN的位置，取靠下的位置可能導不出來想要的log。--stop-position=1590：恢復的結束位置。注意：是不包含停止位置的。--start-datetime="2022-01-01 00:00:00"：指定開始時間之后的bin log。如果不指定表示從頭開始。--stop-datetime="2022-09-01 23:59:59"：指定結束時間之前的bin log。如果不指定表示一直到結尾。--database=db1：bin log 文件會記錄所有數(shù)據(jù)庫的變更，所以一般我們需要指定數(shù)據(jù)庫對應的bin log。
6.2 查看最新的bin log文件
# 刪除所有bin log文件，從mysql-bin.000001開始 mysql> reset master; mysql> show master status;
6.3 模擬數(shù)據(jù)庫誤刪操作
mysql> CREATE DATABASE `db1`;
mysql> use db1;
mysql> CREATE TABLE `tbl_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL COMMENT '用戶名',
`status` tinyint(4) DEFAULT NULL COMMENT '狀態(tài)(0:關閉，1：打開)',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
mysql> insert into tbl_user(username, status) values('monday', 0);
mysql> insert into tbl_user(username, status) values('vbirdbest', 1);
# mysql備份 https://blog.csdn.net/zhou920786312/article/details/123354503
# 單事務導出包括存儲過程、觸發(fā)器、事件
~ mysqldump -uroot -p --single-transaction --triggers --routines --events --databases db1 > db1-20220904.dump
mysql> insert into tbl_user(username, status) values('modely', 1);
# 模擬誤操作，清空了表
mysql> delete from tbl_user;
6.4 從備份中恢復
~ mysql -uroot -p < alldb1.dump
七：從bin log中恢復
總共有3條數(shù)據(jù)，已經(jīng)有2條從備份中恢復了，剩下1條我們從bin log中找出來恢復。
7.1 方式一：基于時間范圍
7.1.1 先查看一下所有的內(nèi)容，確認時間范圍
~ sudo su ~ /usr/local/mysql/bin/mysqlbinlog --no-defaults \ --base64-output=DECODE-ROWS -vv --skip-gtids=true \ --database=db1 /usr/local/mysql/data/mysql-bin.000001
7.1.2 根據(jù)時間范圍確認過濾出來的日志
/usr/local/mysql/bin/mysqlbinlog \ --no-defaults \ --start-datetime="2022-09-04 18:12:17" \ --stop-datetime="2022-09-04 18:12:52" \ --base64-output=DECODE-ROWS -vv \ --skip-gtids=true \ --database=db1 /usr/local/mysql/data/mysql-bin.000001
7.1.3 確認好后導出.sql文件
# 導出sql文件 /usr/local/mysql/bin/mysqlbinlog \ --no-defaults \ --start-datetime="2022-09-04 18:12:17" \ --stop-datetime="2022-09-04 18:12:52" \ --base64-output=DECODE-ROWS -vv \ --skip-gtids=true \ --database=db1 /usr/local/mysql/data/mysql-bin.000001 \ > /Users/mengday/binlog.sql
7.1.4 從.sql文件中恢復數(shù)據(jù)
# 2. 方式一：然后登錄mysql source這個文件 ~ mysql -uroot -p mysql> source /Users/mengday/binlog.sql; # 2. 方式二 mysql -uroot -p < /Users/mengday/binlog.sql;
通過 source binlog.sql 或者 mysql -uroot -p < binlog.sql 都沒有將insert語句插入進去，不知道什么原因。通過另一種形式也沒有執(zhí)行成功！?。?/p>
/usr/local/mysql/bin/mysqlbinlog \ --no-defaults \ --start-datetime="2022-09-04 18:12:17" \ --stop-datetime="2022-09-04 18:12:52" \ --base64-output=DECODE-ROWS -vv \ --skip-gtids=true \ --database=db1 /usr/local/mysql/data/mysql-bin.000001 \ | mysql -uroot -p
7.2 方式二：基于位置(推薦)
因為在同一秒中可能操作多條SQL，如果在這一秒中有一部分需要恢復有一部分不需要恢復那么使用居于時間范圍的就不準確，使用基于位置的就更加準確。
7.2.1 基于位置過濾日志
/usr/local/mysql/bin/mysqlbinlog \ --no-defaults \ --start-position=841 \ --stop-position=948 \ --base64-output=DECODE-ROWS -vv \ --skip-gtids=true \ --database=db1 /usr/local/mysql/data/mysql-bin.000001 \ > /Users/mengday/binlog2.sql
7.2.2 恢復數(shù)據(jù)
source binlog2.sql;
同樣不出意外的insert語句并沒有執(zhí)行。
7.3 多文件恢復
# 跨bin log文件 mysqlbinlog --skip-gtids=true \ --start-position=802 mysql-bin.000001 \ --stop-position=726 mysql-bin.000002 \ |mysql -uroot -proot # 從mysql-bin.000001 802位置開始到mysql-bin.000002結束 mysqlbinlog --skip-gtids=true \ --start-position=802 mysql-bin.000001 mysql-bin.000002 \ |mysql -uroot -proot
注意：如果有多個mysql-bin文件，文件中的位置position不是唯一的，是重復利用的。所以當操作多個文件時要小心。例如 at 4 可能在多個bin log中出現(xiàn)。需要操作多個文件時最好分開一個一個單獨操作。
八：binlog2sql 工具
既然通過source不能直接執(zhí)行bin log sql語句，那么我們就把bin log sql語句轉(zhuǎn)化成正常的sql語句自己手動執(zhí)行。
Python工具 binlog2sql：https://github.com/danfengcao/binlog2sql
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql # 注意：如果本地python環(huán)境沖突的話，最好為當前項目創(chuàng)建一個虛擬環(huán)境，否則很容易報ModuleNotFoundError: No module named 'pymysqlreplication' python3 -m venv env source env/bin/activate pip3 install -r requirements.txt # 查詢所有sql語句，從而找到想要恢復是sql語句 python3 binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root123' -ddb1 -ttbl_user --start-file='mysql-bin.000001' # 通過起始位置進一步確認是否是想要的sql python3 binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root123' -ddb1 -ttbl_user --start-file='mysql-bin.000001' --start-position=705 --stop-position=948 # -B參數(shù)生成對應的回滾sql，如insert回滾sql為delete python3 binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root123' -ddb1 -ttbl_user --start-file='mysql-bin.000001' --start-position=705 --stop-position=948 > insert.sql | cat # 拿到這個SQL我直接執(zhí)行，我就不信恢復不過來 INSERT INTO `db1`.`tbl_user`(`id`, `username`, `status`) VALUES (6, 'modely', 1); #start 705 end 948 time 2022-09-04 18:12:17
??????????????????至此葵花寶典已經(jīng)連成。??????????????????
到此這篇關于SpringBoot數(shù)據(jù)庫恢復的兩種方法mysqldump和mysqlbinlog的文章就介紹到這了,更多相關SpringBoot數(shù)據(jù)庫恢復內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:
- springboot項目數(shù)據(jù)庫配置類DatabaseConfig示例詳解
- SpringBoot自動初始化數(shù)據(jù)庫的方法分享
- Spring?boot數(shù)據(jù)庫依賴詳解
- Spring Boot 如何使用Liquibase 進行數(shù)據(jù)庫遷移(操作方法)
- SpringBoot配置主從數(shù)據(jù)庫實現(xiàn)讀寫分離
- 如何在Spring?Boot中使用MyBatis訪問數(shù)據(jù)庫
- springboot如何從數(shù)據(jù)庫獲取數(shù)據(jù),用echarts顯示(數(shù)據(jù)可視化)
- SpringBoot整合H2數(shù)據(jù)庫的操作方法
相關文章
這篇文章主要為大家詳細介紹了Java實現(xiàn)人機對戰(zhàn)猜拳游戲，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2021-07-07
這篇文章主要為大家詳細介紹了java實現(xiàn)撲克牌發(fā)牌器，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2020-04-04
這篇文章主要介紹了spring springMVC中常用注解,本文給大家介紹的非常詳細，需要的朋友可以參考下2018-05-05
springboot 返回json格式數(shù)據(jù)時間格式配置方式
這篇文章主要介紹了springboot 返回json格式數(shù)據(jù)時間格式配置方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2021-11-11
這篇文章主要介紹了Jmeter實現(xiàn)Base64編碼,大家都知道Jmeter實現(xiàn)Base64編碼有兩種方式，本文通過圖文并茂的形式把每種方法給大家介紹的非常詳細，需要的朋友可以參考下2022-01-01
SpringBoot基于RabbitMQ實現(xiàn)消息延時隊列的方案
在很多的業(yè)務場景中,延時隊列可以實現(xiàn)很多功能,此類業(yè)務中,一般上是非實時的,需要延遲處理的,需要進行重試補償?shù)?本文給大家介紹了SpringBoot基于RabbitMQ實現(xiàn)消息延遲隊列的方案,文中有詳細的代碼講解,需要的朋友可以參考下2024-04-04
這篇文章主要給大家介紹了springmvc如何處理接受http請求,文中通過代碼示例給大家講解的非常詳細,對大家的學習或工作有一定的幫助,需要的朋友可以參考下2024-02-02
這篇文章主要介紹了Java編程中接口的運用,接口是Java面向?qū)ο蟮睦^承特性的體現(xiàn),需要的朋友可以參考下2015-10-10
這篇文章主要介紹了java多線程編程實例，分享了幾則多線程的實例代碼，具有一定參考價值，加深多線程編程的理解還是很有幫助的，需要的朋友可以參考下2021-07-07
最近需要做?個把多個pdf報告合并成?個以?便預覽的需求,下面這篇文章主要給大家介紹了關于java操作PDF文件方法之轉(zhuǎn)換、合成、切分的相關資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2024-01-01