Mysql binlog備份資料及恢復資料，學會這個，我在也不怕刪庫跑路啦~

陳彥斌發表於2020-11-23
MySql

導讀

　　我一直都主張，技多不壓身(沒有學不會的技術，只有不學習的人)，多學一項技能，未來就少求人一次。網上經常聽到xxx刪庫跑路，萬一真的遇到了，相信通過今天的學習，也能將資料再恢復回來~~~

　　當然啦，備份資料/還原資料也是挺重要的，可以看我另一篇：點我直達

　　如果感覺這樣還不安全，可以考慮授予使用者許可權：點我直達

介紹

　　記錄著mysql資料庫中的一些增刪改操作(沒有查詢)

功能

  1. 資料複製(主從複製)
  2. 資料恢復

注意事項

　　開啟二進位制日誌會有效能的消耗！！！

檢視二進位制日誌是否開啟

檢視是否開啟：show variables like 'log_bin%';

 

開啟二進位制日誌

　　修改my.cnf檔案

[mysqld]
datadir=/usr/local/mysql/data
port = 3306
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
symbolic-links=0
max_connections=400
innodb_file_per_table=1
#表名大小寫不明感，敏感為
lower_case_table_names=1
# skip-grant-tables
log-bin=/usr/local/mysql/log_bin_data/mysql-bin
server-id=1
切換：cd /usr/local/mysql/
建立目錄(用於存放二進位制日誌)：mkdir log_bin_data

注意：
1、log_bin_data必須要有mysql許可權
2、修改許可權：chown -R mysql:mysql /usr/local/mysql/log_bin_data

修改my.cnf： vim /usr/local/mysql/my.cnf

在[mysqld]下新增
log-bin=/usr/local/mysql/log_bin_data/mysql-bin
server-id=1


修改完之後，一定要重啟mysql服務：service mysql restart

 

 

　　此時繼續檢視二進位制日誌是否開啟，現在可以看到已經開啟

檢視binlog日誌列表

show master logs;

 

 

重新整理二進位制日誌

　　重新整理/重啟mysql服務，都會在加一個binlog日誌檔案

flush logs;

重置(清空)日誌檔案

　　有時候感覺日誌檔案沒啥用，還佔用空間，可以使用下面命令重置日誌檔案

登入mysql：mysql -uroot -proot
刪除binlog：reset master

二進位制備份

　　備份好二進位制日誌檔案之後，往表中插入一條記錄，然後將資料庫給刪掉，等會使用二進位制日誌恢復資料庫

描述：備份本地db2庫，並壓縮存放到/mysql_data_back下

切換：cd /usr/local/mysql/bin

語法：./mysqldump -u使用者 -p密碼 -F --databases 資料庫 | gzip > 路徑+檔名

備份：./mysqldump -uroot -proot -F --databases db2 | gzip > /mysql_data_back/db2.sql.gz


新增一條記錄：
INSERT INTO `db2`.`nba_player`(`id`, `countryEn`, `teamName`, `birthDay`, `country`, `teamCityEn`, `code`, `displayAffiliation`, `displayName`, `schoolType`, `teamConference`, `teamConferenceEn`, `weight`, `teamCity`, `playYear`, `jerseyNo`, `teamNameEn`, `draft`, `displayNameEn`, `birthDayStr`, `heightValue`, `position`, `age`, `playerId`) VALUES (999999, 'Croatia', '快船', 858661200000, '克羅埃西亞', 'LA', 'ivica_zubac', 'Croatia', '伊維察 祖巴茨', '', '西部', 'Western', '108.9 公斤', '洛杉磯', 3, '40', 'Clippers', 2016, 'Ivica Zubac', '1997-03-18', 2.16, '中鋒', 22, '1627826');

剛才的所有操作，都會記錄在binlog日誌檔案中！

 

binlog恢復資料

　　剛才我們備份資料庫之前，新增一條記錄，id=9999，恢復回去之後，如果有這條記錄，說明我們恢復成功啦

檢視binlog裡的日誌

語法：./mysqlbinlog binlog日誌檔案

切換：/usr/local/mysql/bin

檢視binlog日誌 :./mysqlbinlog /usr/local/mysql/log_bin_data/mysql-bin.000002

　　如果檢視binlog日誌報錯，解決方法

新增：--no-defaults引數
./mysqlbinlog --no-defaults /usr/local/mysql/log_bin_data/mysql-bin.000002

將binlog日誌轉換普通檔案

語法：./mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -v binlog日誌檔案 > 匯出檔案路徑

./mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -v /usr/local/mysql/log_bin_data/mysql-bin.000002 > /mysql_data_back/bin-log.sql

恢復資料方式

根據binlog日誌的時間

根據binlog的位置

 

找到刪除庫的行

./mysqlbinlog --no-defaults ../log_bin_data/mysql-bin.000002 | cat -n |grep -iw 'drop'


./mysqlbinlog --no-defaults ../log_bin_data/mysql-bin.000002 | cat -n | sed -n '行數1,行數2p'

./mysqlbinlog --no-defaults ../log_bin_data/mysql-bin.000002 | cat -n | sed -n '59,70p'

開始恢復資料

先恢復資料庫

　　先將原先備份的資料庫給備份回來

切換： cd /mysql_data_back

解壓： gunzip -d db2.sql.gz 

恢復資料：mysql -uroot -proot -h 127.0.0.1 < /mysql_data_back/db2.sql

　　此時資料庫已經恢復回來，但是新增的那條記錄沒有恢復回來(id=9999)

恢復binlog

 

語法：./mysqlbinlog --no-defaults --set-charset=utf8 --stop-position="停止位置" binlog日誌 | mysql -u使用者 -p密碼
./mysqlbinlog --no-defaults --set-charset=utf8 --stop-position="761" /usr/local/mysql/log_bin_data/mysql-bin.000002 | mysql -uroot -proot

恢復某一段記錄
./mysqlbinlog --no-defaults --set-charset=utf8 --start-position="開始座標" --stop-position="結束座標" | mysql -uroot -proot

備註

　　本次案例，我先備份一次資料庫，然後新增一條記錄，再把資料庫給刪了，我先恢復資料庫(恢復的資料庫中是沒新增的這條記錄)，通過binlog將新增的記錄，回滾到資料庫中！

搞定

相關文章