超詳細MySql備份策略

標籤：cte   恢複   flush   systemctl   資料丟失   collate   格式   類比   記錄檔   

資料備份的重要性

在生產環境中,資料的安全性是至關重要的,任何資料的丟失都可能產生嚴重的後果。重要性可以體現為以下幾點：
1.提高系統的高可用性和災難可恢複性，在資料庫崩潰時，沒有Database Backup就沒法找到資料
2.使用資料備份還原資料庫，是資料庫崩潰時提供資料恢複最小代價的最優方案，重新添加資料，代價太大
3.沒有資料就沒有一切，Database Backup是一種防災難的強力手段

造成資料丟失的原因

程式錯誤
人為錯誤
電腦失敗
磁碟失敗
災難(如火災、地震)和偷竊

資料備份的分類1、從物理與邏輯的角度，備份可分為物理備份和邏輯備份

邏輯備份：對資料庫邏輯組件(如表等資料庫物件)的備份。注意備份之前要鎖表

物理備份：對資料庫作業系統的物理檔案(如資料檔案、記錄檔等)的備份
物理備份又可以分為離線備份(冷備份)和聯機備份(熱備份)
冷備份：是在關閉資料庫的時候進行的（打壓縮包）
熱備份：資料庫處於運行狀態，這種備份方法依賴於資料庫的記錄檔。運行狀態下，大多數操作都會記錄在日誌中，通過日誌回放的方式恢複，只需要備份日誌

2、從資料庫的備份策略角度，備份可分為完全備份、差異備份和增量備份

完全備份：每次對資料進行完整的備份
差異備份：備份那些自從上次完全備份之後被修改過的檔案。注意這裡只針對上次完全備份，中間不管會不會再次出現備份
增量備份：只有那些在上次完全備份或者增量備份後修改的檔案才會被備份。注意這裡是基於上一次備份，上一次備份方式不管是什麼，將多出來的進行備份

實驗環境

• 系統內容：CentOS7.4
• 伺服器IP地址：192.168.100.71
• yum掛載目錄：/mnt/sr0
• 相關源碼資訊：mysql-5.7.17

命令步驟一、使用tar命令打包檔案夾備份1、安裝xz軟體包

[[email protected]_1 ~]# yum -y install xz #使用xz壓縮格式，壓縮率較大

2、備份資料目錄

[[email protected]_1 ~]# tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/ #將mysql資料目錄進行備份，至/opt/目錄下，以目前時間作為尾碼，防止檔案名稱重複

3、查看備份資料

[[email protected]_1 ~]# ls /opt/

4、查看備份前後資料大小

5、恢複備份資料

[[email protected]_1 ~]# tar Jxvf /opt/mysql-2018-08-30.tar.xz /usr/local/mysql/data/

6、引入周期性計劃任務

[[email protected]_1 ~]# crontab -e #編輯周期性計劃任務

[[email protected]_1 ~]# crontab -l

二、使用mysqldump工具備份1、建立測試資料

mysql> create database school default character set utf8 collate utf8_general_ci; #建立資料庫
mysql> use school; #進入school資料庫
#建立"student"表並添加資料
mysql> create table student(Sid int not null primary key auto_increment,Sname char(10),Saddress varchar(50),Sscore decimal(5,2));
mysql> insert into student(Sname,Saddress,Sscore) values(‘huamanlou‘,‘nanjing‘,99); #添加資料
mysql> insert into student(Sname,Saddress,Sscore) values(‘zhanzhao‘,‘kaifeng‘,95);
mysql> insert into student(Sname,Saddress,Sscore) values(‘xiaoqi‘,‘shanghai‘,91);
#建立"teache"表並添加資料
mysql> create table teacher(Tid int not null primary key auto_increment,Tname varchar(20));
mysql> insert into teacher(Tname) values(‘Mr Zhang‘);
mysql> insert into teacher(Tname) values(‘Mr Wang‘);
mysql> insert into teacher(Tname) values(‘Mrs Liu‘);

2、mysqldump命令對單個庫進行完全備份

[[email protected]_1 ~]# mysqldump -uroot -p school > /opt/school.sql #對"school"庫進行備份
[[email protected]_1 ~]# vim /opt/school.sql #查看備份sql檔案

3、mysqldump命令對多個庫進行完全備份

[[email protected]_1 ~]# mysqldump -u root -p --databases mysql school > /opt/mysql-school-mysql.sql
[[email protected]_1 ~]# vim /opt/mysql-school-mysql.sql #查看備份sql檔案

4、對所有庫進行完全備份

[[email protected]_1 ~]# mysqldump -u root -p --opt --all-databases > /opt/all-data.sql
[[email protected]_1 ~]# vim /opt/all-data.sql

5、指定表或表結構

[[email protected]_1 ~]# mysqldump -u root -p school student > /opt/school_student.sql
[[email protected]_1 ~]# vim /opt/school_student.sql

[[email protected]_1 ~]# mysqldump -u root -p -d school student > /opt/school_stu.sql
[[email protected]_1 ~]# vim /opt/school_stu.sql

三、恢複資料庫操作1、使用source恢複資料庫的操作

[[email protected]_1 ~]# mysqldump -u root -p --databases school > /opt/mysql_schooldb.sql #備份資料
[[email protected]_1 ~]# mysql -u root -p
mysql> drop database school;
mysql> show databases;

mysql> source /opt/mysql_school.sql #匯入school;
mysql> select from school.student;
mysql> select from school.teacher;

2、使用mysql命令恢複資料

[[email protected]_1 ~]# mysqldump -u root -p school student > /opt/school_student.sql #備份"student"表資料
[[email protected]_1 ~]# mysql -u root -p
mysql> drop table school.student;
mysql> desc school.student;
mysql> quit

[[email protected]_1 ~]# mysql -u root -p school < /opt/school_student.sql #重新匯入資料
[[email protected]_1 ~]# mysql -u root -p
mysql> select * from school.student;

四、資料庫增量備份恢複1、開啟Mysql二進位日誌功能

[[email protected]_1 ~]# vim /etc/my.cnf

在mysld標籤下添加：

[mysqld]
log_bin=mysql-bin

[[email protected]_1 ~]# systemctl restart mysqld.service #重啟服務

[[email protected]_1 ~]# cd /usr/local/mysql/data/
[roo[email protected]_1 data]# ls

2、建立相應測試資料

[[email protected]_1 ~]# mysql -u root -p
mysql> drop database school; #刪除之前的"school"庫
mysql> create database school;
mysql> use school;
mysql> create table student(id int not null primary key auto_increment , name varchar(10),score decimal(5,2));
mysql> insert into student (name,score) values(‘Tom‘,99); #添加以下兩條資料
mysql> insert into student (name,score) values(‘Bob‘,95);
mysql> quit

3、對"school"庫進行完整備份

[[email protected]_1 ~]# mysqldump -uroot -p --databases school > /opt/school.sql

4、查看二進位日誌

[[email protected]_1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000001

5、截斷日誌

[[email protected]_1 ~]# mysqladmin -uroot -p flush-logs
[[email protected]_1 ~]# ls /usr/local/mysql/data/

[[email protected]_1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000002

6、插入測試資料

[[email protected]_1 ~]# mysql -uroot -p
mysql> insert into school.student(name,score) values(‘t01‘,88);
mysql> insert into school.student(name,score) values(‘t02‘,89);
mysql> quit

7、查看二進位日誌

[[email protected]_1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000002

8、再次截斷日誌

[[email protected]_1 ~]# mysqladmin -uroot -p flush-logs
[[email protected]_1 ~]# ls /usr/local/mysql/data/

9、類比故障還原資料

[[email protected]_1 ~]# mysql -uroot -p
mysql> drop database school; #刪除資料庫
mysql> quit

[[email protected]_1 ~]# mysql -u root -p </opt/school.sql #匯入資料
mysql> select * from school.student;

[[email protected]_1 ~]# cd /usr/local/mysql/data/
[[email protected]_1 data]# mysqlbinlog --no-defaults mysql-bin.000002 | mysql -u root -p #匯入增量資料
[[email protected]_1 ~]# mysql -u root -p
mysql> select * from school.student;

五、基於時間點恢複資料1、重新匯入"school"庫完全備份

[[email protected]_1 ~]# mysql -u root -p </opt/school.sql
[[email protected]_1 ~]# mysql -u root -p

2、截斷日誌

[[email protected]_1 ~]# mysqladmin -uroot -p flush-logs
[[email protected]_1 ~]# ls /usr/local/mysql/data/

3、添加資料類比故障

[[email protected]_1 ~]# mysql -uroot -p
mysql> insert into school.student(name,score) values(‘user01‘,79);
mysql> delete from school.student where name=‘Bob‘; #注意這條sql語句類比誤刪除
mysql> insert into school.student(name,score) values(‘user02‘,84);
mysql> insert into school.student(name,score) values(‘user03‘,83);

mysql> select * from school.student;

4、再次截斷日誌

[[email protected]_1 ~]# mysqladmin -uroot -p flush-logs
[[email protected]_1 ~]# ls /usr/local/mysql/data/

5、刪庫類比故障

[[email protected]_1 ~]# mysql -u root -p
mysql> drop database school;
mysql> quit

[[email protected]_1 ~]# mysql -u root -p </opt/school.sql #匯入完全備份資料

6、恢複資料

[[email protected]_1 ~]# cd /usr/local/mysql/data/
[[email protected]_1 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000004

[[email protected]_1 data]# mysqlbinlog --no-defaults --stop-datetime=‘2018-09-03 18:52:11‘ mysql-bin.000004 | mysql -uroot -p123

#"stop-datetime"代表的是錯誤時間點，從二進位記錄檔開頭進行載入，一直到錯誤時間點結束

[[email protected]_1 data]# mysqlbinlog --no-defaults --start-datetime=‘2018-09-03 18:52:17‘ mysql-bin.000004 | mysql -uroot -p123

#"start-datetime"代表的是正確操作時間點，從哪個時間點再進行開始

六、基於位置恢複資料1、刪庫類比故障

[[email protected]_1 ~]# mysql -u root -p
mysql> drop database school;
mysql> quit

[[email protected]_1 ~]# mysql -u root -p </opt/school.sql #匯入完全備份資料

2、恢複資料

[[email protected]_1 ~]# cd /usr/local/mysql/data/
[[email protected]_1 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000004

[[email protected]_1 data]# mysqlbinlog --no-defaults --stop-position=‘559‘ mysql-bin.000004 | mysql -uroot -p123

#"stop-position"代表的是錯誤位置，從二進位記錄檔開頭進行載入，一直到錯誤位置結束

[[email protected]_1 data]# mysqlbinlog --no-defaults --start-position=‘664‘ mysql-bin.000004 | mysql -uroot -p123

#"start-position"代表的是正確操作位置，從哪個位置再進行開始

超詳細MySql備份策略