MySQL的備份與恢復(fù)詳解

在公司中備份的策略并不是千篇一律的，而是根據(jù)每個(gè)企業(yè) 的實(shí)際生產(chǎn)環(huán)境與業(yè)務(wù)需求制定合適的備份策略。無(wú)論是選擇完全備份還是增量備份，都需要考慮它們的優(yōu)缺點(diǎn)，是否適合當(dāng)前的生產(chǎn)環(huán)境。同時(shí)為了保證恢復(fù)的完整性，建議開(kāi)啟二進(jìn)制日志功能，二進(jìn)制日志文件給恢復(fù)工作帶來(lái)了很大的靈活性，可以基于時(shí)間點(diǎn)或位置進(jìn)行恢復(fù)，考慮到數(shù)據(jù)庫(kù)性能，可以將二進(jìn)制日志文件保存在其他安全的硬盤中。

在進(jìn)行熱備時(shí)，備份操作和應(yīng)用服務(wù)在同時(shí)運(yùn)行，這樣十分消耗系統(tǒng)資源，導(dǎo)致數(shù)據(jù)庫(kù)服務(wù)性能下降，這就要求我們選擇一個(gè)合適的時(shí)間（一般在應(yīng)用負(fù)擔(dān)很小的時(shí)候）再來(lái)進(jìn)行備份操作。

需要注意的是，不是備份就萬(wàn)事大吉了，最好確認(rèn)備份是否可用，所以備份之后的恢復(fù)測(cè)試是非常有必要的。同時(shí)備份時(shí)間也要靈活調(diào)整，如：

1. 數(shù)據(jù)更新頻繁，則應(yīng)該頻繁地備份。
2. 數(shù)據(jù)的重要性，在有適當(dāng)更新時(shí)進(jìn)行備份。
3. 在數(shù)據(jù)庫(kù)壓力小的時(shí)間段進(jìn)行備份，如一周一次完全備份，每天進(jìn)行增量備份。
4. 中小公司，完全備份一般一天一次即可。
5. 大公司可每周進(jìn)行一次完全備份，每天進(jìn)行一次增量備份。
6. 盡量為企業(yè)實(shí)現(xiàn)主從復(fù)制架構(gòu)，以增加數(shù)據(jù)的可用性。

數(shù)據(jù)庫(kù)備份類型可以從兩個(gè)角度來(lái)看待：

1、從物理與邏輯的角度：

物理備份是對(duì)數(shù)據(jù)庫(kù)操作系統(tǒng)的物理文件（如數(shù)據(jù)文件、日志文件等）的備份。這種類型的備份適用于在出現(xiàn)問(wèn)題時(shí)需要快速恢復(fù)的大型重要數(shù)據(jù)庫(kù)。

物理備份有可以分為以下幾種類型：

①、冷備份：在數(shù)據(jù)庫(kù)關(guān)閉狀態(tài)下進(jìn)行備份操作；

②、熱備份：在數(shù)據(jù)庫(kù)處于運(yùn)行狀態(tài)時(shí)進(jìn)行備份操作，該備份方法依賴數(shù)據(jù)庫(kù)的日志文件；

③、溫備份：數(shù)據(jù)庫(kù)鎖定表格（不可寫入，但可讀取）的狀態(tài)下進(jìn)行備份；

邏輯備份是對(duì)數(shù)據(jù)庫(kù)邏輯組件（如表等數(shù)據(jù)庫(kù)對(duì)象）的備份，表示為邏輯數(shù)據(jù)庫(kù)結(jié)構(gòu)（create database、create table語(yǔ)句）和內(nèi)容（insert語(yǔ)句或分隔文本文件）的信息。這種類型的備份使用于可以編輯數(shù)據(jù)值或表結(jié)構(gòu)較小的數(shù)據(jù)量，或者在不同的機(jī)器體系上重新創(chuàng)建數(shù)據(jù)。

2、從數(shù)據(jù)庫(kù)的備份策略角度：

從數(shù)據(jù)庫(kù)的備份策略角度，數(shù)據(jù)庫(kù)的備份可分為完全備份、差異備份和增量備份。其中呢，完整備份是實(shí)現(xiàn)差異、增量備份的基礎(chǔ)。

1. 完整備份：每次對(duì)數(shù)據(jù)進(jìn)行完整的備份，即對(duì)整個(gè)數(shù)據(jù)庫(kù)的備份。備份與恢復(fù)的操作非常簡(jiǎn)單，但是數(shù)據(jù)存在大量的重復(fù)，會(huì)占用大量的磁盤空間，備份的時(shí)間也很長(zhǎng)。
2. 差異備份：備份那些自從上次完全備份之后被修改過(guò)的所有文件，備份的時(shí)間點(diǎn)是從上次完整備份起，備份數(shù)據(jù)會(huì)越來(lái)越大，恢復(fù)數(shù)據(jù)時(shí)，只需恢復(fù)上次的完全備份和最近的一次差異備份。
3. 增量備份：只有在那些在上次完全備份或增量備份后被修改的文件才會(huì)被備份，以上次完整備份或上次增量備份的時(shí)間為時(shí)間點(diǎn)，僅僅備份這之間的數(shù)據(jù)變化，因而備份的數(shù)據(jù)量也小，占用空間小，備份速度快，但恢復(fù)時(shí)，需要從上一次的完整備份開(kāi)始到最后一次增量備份之間的所有增量依次恢復(fù)，一旦中間的數(shù)據(jù)發(fā)生損壞，將導(dǎo)致數(shù)據(jù)的丟失。

來(lái)幾個(gè)備份實(shí)例：

1、物理冷備份與恢復(fù)：

[root@localhost ~]# tar zcf /backup/mysql_$(date +%F).tar.gz /usr/local/mysql/data  #直接tar打包數(shù)據(jù)庫(kù)文件
[root@localhost ~]# cd /backup/
[root@localhost backup]# ls
mysql_2019-05-16.tar.gz              #備份后的文件
[root@localhost backup]# 

2、mysqldump備份與恢復(fù)：

1）備份某一個(gè)表：

[root@localhost backup]# mysqldump -u root -p mysql user > mysql-user.sql          #備份mysql庫(kù)中的user表
Enter password:              #輸入密碼
[root@localhost backup]# ls                  #查看備份文件
mysql-user.sql

2）恢復(fù)mysql數(shù)據(jù)庫(kù)中的user表（注意路徑問(wèn)題）：

[root@localhost backup]# mysql -u root -p mysql < mysql-user.sql 
Enter password: 

3）備份mysql庫(kù)：

[root@localhost backup]# mysqldump -u root -p --databases mysql > mysql.sql            #備份mysql庫(kù)
Enter password:              #輸入密碼
[root@localhost backup]# ls             #查看備份文件
mysql.sql

4）恢復(fù)mysql庫(kù)：

[root@localhost backup]# mysql -u root -p < mysql.sql 
Enter password: 

5）備份所有的庫(kù)（當(dāng)導(dǎo)出的數(shù)據(jù)量較大時(shí)，可以添加“--opt”選項(xiàng)以優(yōu)化執(zhí)行速度）：

[root@localhost backup]# mysqldump -u root -p --opt --all-databases > all-data.sql         #備份所有庫(kù)
Enter password:            #輸入密碼
[root@localhost backup]# ls              #查看備份文件
all-data.sql

3、MySQL增量備份與恢復(fù)：

與完全備份不同，增量備份沒(méi)有重復(fù)數(shù)據(jù)，備份量不大，時(shí)間段，但其恢復(fù)比較麻煩，需要上次完全備份及完全備份之后的所有增量備份之后才能恢復(fù)，而且要對(duì)所有增量備份逐個(gè)反推恢復(fù)。MySQL沒(méi)有提供直接的增量備份辦法，所以一般是通過(guò)MySQL提供的二進(jìn)制日志來(lái)間接實(shí)現(xiàn)增量備份。

要進(jìn)行MySQL的增量備份，那么首先需要開(kāi)啟二進(jìn)制日志功能：

[root@localhost ~]# mkdir /usr/local/mysql/logs      #創(chuàng)建專門存放二進(jìn)制日志文件的目錄
[root@localhost ~]# chown mysql:mysql /usr/local/mysql/logs           #更改目錄歸屬，使其擁有寫入權(quán)限
[root@localhost ~]# vim /etc/my.cnf        #編輯MySQL的主配文件
[mysqld]
log-bin=/usr/local/mysql/logs/mysql-bin            #寫入該行，指定二進(jìn)制日志的存放位置
[root@localhost ~]# systemctl restart mysqld             #重啟服務(wù)
[root@localhost ~]# ls -l /usr/local/mysql/logs                #然后即可看到目錄下自動(dòng)生成的日志文件
-rw-rw----. 1 mysql mysql 120 5月  16 14:16 mysql-bin.000001
-rw-rw----. 1 mysql mysql  39 5月  16 14:16 mysql-bin.index

現(xiàn)在所有對(duì)數(shù)據(jù)庫(kù)的修改，都將記錄mysql-bin.000001文件中，當(dāng)執(zhí)行“mysqladmin -u root -p flush-logs”刷新二進(jìn)制日志后，將會(huì)繼續(xù)生成一個(gè)名為mysql-bin.000002的文件，之后所有的更改又將存在mysql-bin.000002文件中，以此類推.......

接下來(lái)對(duì)下面test庫(kù)中的user_info這個(gè)表進(jìn)行備份：

mysql> select * from user_info;
+---------+----------+---------+
| gonghao | xingming | xingbie |
+---------+----------+---------+
|       1 | zhangsan | nan     |
|       2 | lisi     | nan     |
|       3 | wangwu   | nan     |
+---------+----------+---------+

1）先進(jìn)行一次完全備份：

[root@localhost ~]# mysqldump -u root -p test user_info >/backup/user_info_$(date +%F).sql;    #完全備份
Enter password:                 #輸入密碼
[root@localhost ~]# ls /backup/              #查看備份生成的文件
user_info_2019-05-16.sql

2）刷新二進(jìn)制文件：

[root@localhost ~]# mysqladmin -u root -p flush-logs             #刷新日志文件
Enter password: 
[root@localhost ~]# ls -l /usr/local/mysql/logs/           查看，每次刷新后，都會(huì)多出一個(gè)日志文件，
之后所有的修改，都將保存在這個(gè)日志文件中
-rw-rw----. 1 mysql mysql 1007 5月  16 14:36 mysql-bin.000001
-rw-rw----. 1 mysql mysql  120 5月  16 14:36 mysql-bin.000002
-rw-rw----. 1 mysql mysql   78 5月  16 14:36 mysql-bin.index

3）現(xiàn)在數(shù)據(jù)庫(kù)中又多了兩條數(shù)據(jù)：

mysql> select * from user_info;
+---------+----------+---------+
| gonghao | xingming | xingbie |
+---------+----------+---------+
|       1 | zhangsan | nan     |
|       2 | lisi     | nan     |
|       3 | wangwu   | nan     |
|       4 | zhaoliu  | nan     |
|       5 | sunqi    | nan     |
+---------+----------+---------+

4）如果現(xiàn)在這個(gè)表被誤刪了，那么可以這樣恢復(fù)：

[root@localhost ~]# mysqladmin -u root -p flush-logs    #刷新日志
Enter password: 

[root@localhost ~]# mysql -u root -p test < /backup/user_info_2019-05-16.sql          #先恢復(fù)完全備份
Enter password: 
[root@localhost ~]# mysql -u root -p -e ' select * from test.user_info;'             #完全備份的內(nèi)容恢復(fù)成功
Enter password: 
+---------+----------+---------+
| gonghao | xingming | xingbie |
+---------+----------+---------+
|       1 | zhangsan | nan     |
|       2 | lisi     | nan     |
|       3 | wangwu   | nan     |
+---------+----------+---------+

在恢復(fù)增量備份時(shí)，--no-defaults選項(xiàng)必須要有，否則會(huì)報(bào)錯(cuò)：

[root@localhost ~]# mysqlbinlog --no-defaults /usr/local/mysql/logs/mysql-bin.000002 | mysql -u root -p    #恢復(fù)增量備份
Enter password: 
[root@localhost ~]# mysql -u root -p -e ' select * from test.user_info;'         #查看是否恢復(fù)成功
Enter password: 
+---------+----------+---------+
| gonghao | xingming | xingbie |
+---------+----------+---------+
|       1 | zhangsan | nan     |
|       2 | lisi     | nan     |
|       3 | wangwu   | nan     |
|       4 | zhaoliu  | nan     |
|       5 | sunqi    | nan     |
+---------+----------+---------+

恢復(fù)成功了，那么還有基于位置、時(shí)間的恢復(fù)，命令格式如下：

[root@localhost ~]# mysqlbinlog --no-defaults /usr/local/mysql/logs/mysql-bin.000002    #使用工具查看日志文件

........................
# at 199       #199即為一個(gè)動(dòng)作的位置，接下來(lái)就是這個(gè)動(dòng)作的時(shí)間
#190516 14:39:47 server id 1  end_log_pos 322 CRC32 0x5339b5bd  Query   thread_id=5 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1557988787/*!*/;
insert into user_info values('4','zhaoliu','nan')
/*!*/;
# at 322
#190516 14:39:47 server id 1  end_log_pos 353 CRC32 0x873df67e  Xid = 54
COMMIT/*!*/;               #該字樣表示為提交
# at 353
#190516 14:39:57 server id 1  end_log_pos 432 CRC32 0xcd2543f7  Query   thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1557988797/*!*/;
BEGIN
/*!*/;
# at 432
#190516 14:39:57 server id 1  end_log_pos 553 CRC32 0xc1300526  Query   thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1557988797/*!*/;
insert into user_info values('5','sunqi','nan')
/*!*/;
# at 553
#190516 14:39:57 server id 1  end_log_pos 584 CRC32 0xebb496fb  Xid = 55
COMMIT/*!*/;

......................

[root@localhost ~]# mysqlbinlog --no-defaults --start-position='353' /usr/local/mysql/logs/mysql-bin.000002 | mysql -u root -p      
Enter password: 

--start-position='353'：表示為從位置353開(kāi)始恢復(fù)，該日志文件353之前的數(shù)據(jù)不會(huì)恢復(fù)；

以上選項(xiàng)可更改為下面類型：

--stop-position='353'：表示恢復(fù)到位置353就停止，該日志文件353之后的數(shù)據(jù)不會(huì)恢復(fù)；

基于時(shí)間點(diǎn)的恢復(fù)：

--start-datetime='2019-05-16 14:39:47'：表示恢復(fù)該時(shí)間之后的數(shù)據(jù)；

--stop-datetime='2019-05-16 14:39:47'：表示僅恢復(fù)該時(shí)間之前的數(shù)據(jù)；

切記，所有類型的增量恢復(fù)之前，都必須先執(zhí)行最近一次的完全恢復(fù)。