MySQL的備份與恢復詳解

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

讓客戶滿意是我們工作的目標，不斷超越客戶的期望值來自于我們對這個行業(yè)的熱愛。我們立志把好的技術通過有效、簡單的方式提供給客戶，將通過不懈努力成為客戶在信息化領域值得信任、有價值的長期合作伙伴，公司提供的服務項目有：域名注冊、網站空間、營銷軟件、網站建設、七星關區(qū)網站維護、網站推廣。

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

需要注意的是，不是備份就萬事大吉了，最好確認備份是否可用，所以備份之后的恢復測試是非常有必要的。同時備份時間也要靈活調整，如：

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

數(shù)據(jù)庫備份類型可以從兩個角度來看待：

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

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

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

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

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

③、溫備份：數(shù)據(jù)庫鎖定表格（不可寫入，但可讀?。┑臓顟B(tài)下進行備份；

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

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

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

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

來幾個備份實例：

1、物理冷備份與恢復：


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

2、mysqldump備份與恢復：

1）備份某一個表：

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

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

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

3）備份mysql庫：

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

4）恢復mysql庫：

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

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

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

3、MySQL增量備份與恢復：

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

要進行MySQL的增量備份，那么首先需要開啟二進制日志功能：

[root@localhost ~]# mkdir /usr/local/mysql/logs      #創(chuàng)建專門存放二進制日志文件的目錄
[root@localhost ~]# chown mysql:mysql /usr/local/mysql/logs           #更改目錄歸屬，使其擁有寫入權限
[root@localhost ~]# vim /etc/my.cnf        #編輯MySQL的主配文件
[mysqld]
log-bin=/usr/local/mysql/logs/mysql-bin            #寫入該行，指定二進制日志的存放位置
[root@localhost ~]# systemctl restart mysqld             #重啟服務
[root@localhost ~]# ls -l /usr/local/mysql/logs                #然后即可看到目錄下自動生成的日志文件
-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)在所有對數(shù)據(jù)庫的修改，都將記錄mysql-bin.000001文件中，當執(zhí)行“mysqladmin -u root -p flush-logs”刷新二進制日志后，將會繼續(xù)生成一個名為mysql-bin.000002的文件，之后所有的更改又將存在mysql-bin.000002文件中，以此類推.......

接下來對下面test庫中的user_info這個表進行備份：

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

1）先進行一次完全備份：

[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）刷新二進制文件：

[root@localhost ~]# mysqladmin -u root -p flush-logs             #刷新日志文件
Enter password: 
[root@localhost ~]# ls -l /usr/local/mysql/logs/           查看，每次刷新后，都會多出一個日志文件，
之后所有的修改，都將保存在這個日志文件中
-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ù)庫中又多了兩條數(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)在這個表被誤刪了，那么可以這樣恢復：

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

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

在恢復增量備份時，--no-defaults選項必須要有，否則會報錯：

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

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

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

........................
# at 199       #199即為一個動作的位置，接下來就是這個動作的時間
#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開始恢復，該日志文件353之前的數(shù)據(jù)不會恢復；

以上選項可更改為下面類型：

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

基于時間點的恢復：

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

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

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

網頁題目：MySQL的備份與恢復詳解
網址分享：http://bm7419.com/article48/igcjep.html

成都網站建設公司_創(chuàng)新互聯(lián)，為您提供虛擬主機、品牌網站建設、Google、網站改版、全網營銷推廣、定制開發(fā)

聲明：本網站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內容