Mysql資料備份和還原

Mysql資料備份和還原
一、Mysql完整備份和恢復
1、備份的作用和mysql產生故障的原因
1）備份的作用
防止發生故障損失降到最低
故障後能夠快速恢復丟失資料保證資料庫系統或者資料穩定執行
2）mysql故障的型別
程式錯誤：MySQL程式故障導致資料無法使用
人為誤操作：資料庫管理員誤刪除資料
運算錯誤：資料庫程式邏輯故障
磁碟故障：硬碟壞道導致資料丟失
災難不可抗力的自然災害：地震海嘯使用異地備份緩解
2、資料庫備份的型別
1）冷備份
關閉資料庫服務
直接複製mysql資料目錄進行資料備份
2）熱備份
不停止資料庫服務實時動態備份資料
3）溫備份
備份資料設定資料庫允許讀取不允許寫入資料備份成為溫和備份
3、備份策略
1）完整備份
一般第一次備份使用完整備份
完整備份對資料庫和資料庫邏輯檔案以及日誌統一備份
備份時間長佔用磁碟空間大
2）差異備份
完整備份後執行差異備份
差異備份只備份完整備份後修改的資料
3）增量備份
做完完整備份和差異備份使用後使用
增量備份備份差異備份修改的資料
備份速度快
佔用磁碟空間小
方便資料恢復
4、常見的備份工具
1）冷備份工具
tar
cp
2）專用備份工具備份
mysqldump：MySQL系統自帶備份工具
mysqlhotcopy：對資料庫引擎進行備份，工具使用perl語言
3）二進位制日誌
通過二進位制日誌備份工具備份資料
資料故障從二進位制日誌中恢復
一般增量備份使用
4）第三工具
xtrbackup：是一個二進位制檔案工具，只能備份innodb/xtradb資料檔案
innodbackupex：使用perl語言編寫的備份指令碼，可以備份innodb/xtradb以及mysiam引擎
xbstream：允許將檔案轉換為xbstream格式進行資料備份
二、資料庫的完全備份和恢復
1、冷備份
1）停止mysql服務
[root@centos01 ~]# systemctl stop mysql

2）備份mysql資料
[root@centos01 ~]# mkdir /backup
[root@centos01 ~]# tar zcf /backup/mysql_backup-$(date +%F).tar.gz /usr/local/mysql/data/

3）模擬資料丟失
[root@centos01 ~]# mv /usr/local/mysql/data/ /opt/

4）恢復丟失資料
[root@centos01 ~]# mkdir restore
[root@centos01 ~]# tar zxf /backup/mysql_backup-2020-06-22.tar.gz -C ./restore/
[root@centos01 ~]# mv ./restore/usr/local/mysql/data/ /usr/local/mysql/



5）啟動mysql服務
[root@centos01 ~]# systemctl start mysqld

2、使用mysqldump備份資料
1）備份benet資料庫中的student表
[root@centos01 ~]# mysqldump -uroot -p benet student > benet_student_table.sql
mysql> mysql> create table benet.student (姓名 char(6),性別 char(3),電話 char(11),primary key(電話));
[root@centos01 ~]# cat benet_student_table.sql

2）備份資料庫中的資料，備份benet資料庫
[root@centos01 ~]# mysqldump -uroot -p --database benet > benet_database.sql

3）備份所有的資料庫
[root@centos01 ~]# mysqldump -uroot -p --opt --all-databases > all_databases

3、恢復備份的資料
1）恢復benet資料庫中的student表到accp資料庫中
[root@centos01 ~]# mysql -uroot -ppwd@123
mysql> create database accp;
[root@centos01 ~]# mysql -uroot -p accp < ./benet_student_table.sql
[root@centos01 ~]# mysql -uroot -ppwd@123 -e ‘select * from accp.student;’
2）恢復備份的資料庫
[root@centos01 ~]# mysql -uroot -ppwd@123 -e ‘create database benet;’
[root@centos01 ~]# mysql -uroot -p benet < ./benet_database.sql
三、配置增量備份恢復
1、增量備份的作用和優勢
1）增量備份的作用
方便進行資料備份和恢復
2）增量備份的優勢
備份和恢復資料速度快
恢復資料可靠性強
恢復資料不容易舊資料覆蓋新資料
2、配置二進位制日誌
1）開啟mysql二進位制日誌功能
[root@centos01 ~]# vim /etc/my.cnf
49 log-bin=mysql-bin
2）二進位制日誌儲存的預設位置
/usr/local/mysql/data/
3）檢視二進位制日誌
[root@centos01 ~]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000005
4）二進位制日誌切割，生成新的二進位制日誌
[root@centos01 ~]# mysqladmin -uroot -ppwd@123 flush-logs
3、使用二進位制日誌恢復誤刪除資料
1）備份二進位制日誌
[root@centos01 ~]# cp /usr/local/mysql/data/mysql-bin.000005 ./
2）模擬刪除benet資料庫所有資料丟失
[root@centos01 ~]# mysql -uroot -ppwd@123 -e ‘drop database benet;’
3）從備份的二進位制日誌中恢復誤刪除的資料
[root@centos01 ~]# mysqlbinlog --no-defaults mysql-bin.000004 | mysql -uroot -ppwd@123
4）檢視恢復的資料
[root@centos01 ~]# mysql -uroot -ppwd@123 -e ‘select * from benet.student;’
4、使用二進位制日誌基於位置的恢復
1）模擬資料丟失
[root@centos01 ~]# mysql -uroot -ppwd@123 -e ‘drop database benet;’
2）恢復到備份二benet資料庫狀態
[root@centos01 ~]# mysql -uroot -ppwd@123 -e ‘create database benet;’
[root@centos01 ~]# mysql -uroot -p benet < ./benet_database.sql
3）通過備份的二進位制日誌恢復最新的資料
[root@centos01 ~]# mysqlbinlog --stop-position=‘1289’ ./mysql-bin.000004 | mysql -uroot -ppwd@123
4）檢視恢復的資料
[root@centos01 ~]# mysql -uroot -ppwd@123 -e ‘select * from benet.student;’
5）從特定的二進位制日誌開始恢復
[root@centos01 ~]# mysqlbinlog --start-position=‘1176’ ./mysql-bin.000004 | mysql -uroot -ppwd@123
首次插入資料
建立benet資料庫student表

錄入資料

首次錄入資料進行完全備份

錄入新的資料並進行增量備份

切割日誌，生成新的二進位制日誌


備份二進位制日誌到root

模擬刪除benet資料庫所有資料丟失

先恢復完全備份


恢復增量備份

檢視恢復的資料

使用二進位制日誌基於位置的恢復
模擬資料丟失

恢復到備份二benet資料庫狀態


通過備份的二進位制日誌恢復最新的資料


檢視恢復的資料

從特定的二進位制日誌開始恢復
[root@centos01 ~]# mysqlbinlog --start-position=‘1176’ ./mysql-bin.000004 | mysql -uroot -ppwd@123

5、基於時間點恢復資料
1）基於開始恢復到日誌結束時間
[root@centos01 ~]# mysqlbinlog --start-datetime=‘2020-06-23 15:38:21’ ./mysql-bin.000004 | mysql -uroot -ppwd@123

2）基於結束時間點恢復
[root@centos01 ~]# mysqlbinlog --stop-datetime=‘2020-06-23 15:38:35’ ./mysql-bin.000004 | mysql -uroot -ppwd@123

3）基於開始時間和結束時間之間的資料進行恢復
[root@centos01 ~]# mysqlbinlog --start-datetime=‘2020-06-23 15:38:21’ --stop-datetime=‘2020-06-23 15:38:35’ ./mysql-bin.000004 | mysql -uroot -ppwd@123