MySQL應該如何備份與恢復

本文主要給大家簡單講講MySQL應該如何備份與恢復，相關專業術語大家可以上網查查或者找一些相關書籍補充一下，這里就不涉獵了，我們就直奔主題吧，希望MySQL應該如何備份與恢復這篇文章可以給大家帶來一些實際幫助。

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

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

數據更新頻繁，則應該頻繁地備份。
數據的重要性，在有適當更新時進行備份。
在數據庫壓力小的時間段進行備份，如一周一次完全備份，每天進行增量備份。
中小公司，完全備份一般一天一次即可。
大公司可每周進行一次完全備份，每天進行一次增量備份。
盡量為企業實現主從復制架構，以增加數據的可用性。
數據庫備份類型可以從兩個角度來看待：

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

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

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

①、冷備份：在數據庫關閉狀態下進行備份操作；

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

③、溫備份：數據庫鎖定表格（不可寫入，但可讀取）的狀態下進行備份；

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

2、從數據庫的備份策略角度：

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

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

[root@mysql /]# systemctl stop mysqld        #先停掉服務
[root@mysql /]# mkdir /backup            # 創建一個備份目錄
[root@mysql /]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/         # 將整個數據庫文件夾打包備份，（date +%F）當前日期
[root@mysql /]# ls -l /backup/      # 查看備份文件
total 732         # 總用量
-rw-r--r-- 1 root root 746839 Aug  2 14:48 mysql_all-2019-08-02.tar.gz     # 備份文件

來模擬數據庫文件丟失：

[root@mysql /]# mkdir /diushi
[root@mysql /]# mv /usr/local/mysql/data/ /diushi/         # 將數據庫存放目錄移動到另一個目錄

恢復數據庫：

[root@mysql /]# mkdir /restore/
[root@mysql /]# tar zxf /backup/mysql_all-2019-08-02.tar.gz -C   /restore/     # 要先將備份文件釋放到一個空目錄中，然后將需要的恢復到原位置
[root@mysql /]# mv /restore/usr/local/mysql/data/   /usr/local/mysql/         # 將數據庫目錄恢復到原位置
[root@mysql /]# systemctl restart mysqld             # 重啟服務驗證

2、mysqldump 備份與恢復：
備份數據庫
備份指定庫中的表：

mysqldump    [選項]    庫名   表名  表名2   ……  > /備份路徑/備份文件名
[root@mysql /]# mysqldump -u root -p test user   >   /backup/user-table.sql          # 將test庫中的user表備份到backup目錄中
Enter password:                   # 輸入密碼

備份一個或多個完整的庫：

mysqldump   [選項]   --databases      庫名1   庫名2   ……  >  /備份路徑/備份文件名
[root@mysql /]# mysqldump -u root -p  --databases  test mysql    >   /backup/databases.sql            # 將 test 和 mysql 庫備份到backup中
Enter password:                       # 輸入密碼

備份 MySQL 中的所有庫：

mysqldump   [選項]     --all-databases   >    /備份路徑/備份文件名
[root@mysql /]# mysqldump -u root -p   --opt   --all-databases   >   all-data.sql          # --opt：優化執行速度
Enter password:          # 輸入密碼

[root@mysql /]# ls backup/            # 查看備份文件
all-data.sql      databases.sql       user-table.sql

恢復數據庫：
恢復庫中的表

mysql     [選項]   庫名   <   /備份路徑/備份文件名
[root@mysql /]# mysql -u root -p  test   <   /backup/user-table.sql    
Enter password: 
[root@mysql /]# mysql -u root -p -e ' show  tables  from  test;'           // 驗證導入結果
Enter password: 
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+

恢復單個或多個庫：

[root@mysql /]# mysql -u root -p -e ' drop database test;'     // 刪除 test 數據庫，模擬故障
Enter password: 
[root@mysql /]# mysql -u root -p -e ' show databases;'        // 驗證 test 數據庫是否存在
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
[root@mysql /]# mysql -u root -p   <   /backup/databases.sql     // 執行導入恢復操作
Enter password: 
[root@mysql /]# mysql -u root -p -e ' show databases;'       // 確認恢復后結果
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

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

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

[root@mysql /]# mkdir /usr/local/mysql/logs            # 創建一個存放二進制日志文件的目錄
[root@mysql /]# cd /usr/local/mysql/                       
[root@mysql mysql]# chown mysql:mysql logs/       # 設置目錄歸屬，使其能夠寫入
[root@mysql /]# vim /etc/my.cnf                # 編寫配置文件
[mysqld]
log-bin=/usr/local/mysql/logs/mysql-bin
[root@mysql /]# systemctl restart mysqld          # 重啟服務，使配置生效
[root@mysql /]# ll /usr/local/mysql/logs/mysql-bin.*         # 目錄下自動生成日志文件
-rw-rw---- 1 mysql mysql 120 Aug  2 17:04 /usr/local/mysql/logs/mysql-bin.000001
-rw-rw---- 1 mysql mysql  39 Aug  2 17:04 /usr/local/mysql/logs/mysql-bin.index

現在所有對數據庫的修改，都將記錄mysql-bin.000001文件中，當執行“mysqladmin -u root -p flush-logs”刷新二進制日志后，將會繼續生成一個名為mysql-bin.000002的文件，之后所有的更改又將存在mysql-bin.000002文件中，以此類推，每刷新一次，就會生成一個新文件！
首先我們在表中先錄入一些信息，然后進行一次完整備份：

mysql> select * from user_info；
+------+----------+----------+
| id   | xingming | nianling |
+------+----------+----------+
| 001  | zhangsan |       20 |
| 002  | lisi     |       25 |
| 003  | wangwu   |       20 |
+------+----------+----------+

[root@mysql /]# mkdir /mysql_bak                # 創建一個備份存放位置
[root@mysql /]# mysqldump -u root -p   test user_info   > /mysql_bak/test_userinfo$(date +%F).sql           # 進行完整備份
Enter password: 
[root@mysql /]# ls /mysql_bak/               # 驗證備份結果
test_userinfo2019-08-02.sql
[root@mysql /]# mysqladmin -u root -p flush-logs            # 刷新日志文件
Enter password: 
[root@mysql /]# ll /usr/local/mysql/logs/mysql-bin.*         # 生成新的日志文件000002
-rw-rw---- 1 mysql mysql 1192 Aug  2 17:18 /usr/local/mysql/logs/mysql-bin.000001
-rw-rw---- 1 mysql mysql  120 Aug  2 17:18 /usr/local/mysql/logs/mysql-bin.000002
-rw-rw---- 1 mysql mysql   78 Aug  2 17:18 /usr/local/mysql/logs/mysql-bin.index

繼續錄入新的數據，并進行增量備份：

mysql> select * from user_info;
+------+----------+----------+
| id   | xingming | nianling |
+------+----------+----------+
| 001  | zhangsan |       20 |
| 002  | lisi     |       25 |
| 003  | wangwu   |       20 |
| 004  | zhaoliu  |       20 |
| 005  | sunqi    |       30 |
+------+----------+----------+

[root@mysql /]# mysqladmin -u root -p flush-logs            # 刷新日志文件，這樣在000002中只有兩條數據的操作
Enter password: 
[root@mysql /]# cp /usr/local/mysql/logs/mysql-bin.000002 /mysql_bak/      　＃ 將日志文件復制到備份目錄中

模擬user_info 這個表被誤刪除了，恢復：

[root@mysql /]# mysql -u root -p test   <   /mysql_bak/test_userinfo2019-08-02.sql    # 先恢復完整備份
Enter password: 
[root@mysql /]# mysql -u root -p -e ' select * from test.user_info;'      # 查看一下確認，恢復成功
Enter password: 
+------+----------+----------+
| id   | xingming | nianling |
+------+----------+----------+
| 001  | zhangsan |       20 |
| 002  | lisi     |       25 |
| 003  | wangwu   |       20 |
+------+----------+----------+
[root@mysql /]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 | mysql -u root -p                              # 恢復增量備份，--no-defaults 選項必須要有
[root@mysql /]# mysql -u root -p -e ' select * from test.user_info;'       # 確認，增量備份恢復成功
Enter password: 
+------+----------+----------+
| id   | xingming | nianling |
+------+----------+----------+
| 001  | zhangsan |       20 |
| 002  | lisi     |       25 |
| 003  | wangwu   |       20 |
| 004  | zhaoliu  |       20 |
| 005  | sunqi    |       30 |
+------+----------+----------+

下來就是基于位置恢復和基于時間點恢復了，這兩種恢復是有很大的相同之處的，想要實現，必需先查看二進制日志文件來確認恢復的位置或時間點。

[root@mysql /]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 
……      // 省略部分內容
#at 199               # 這一行就是操作ID號了
#190802 17:21:40 server id 1  end_log_pos 346 CRC32 0xc61c38c9  Query   thread_id=4 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1564737700/*!*/;
insert into user_info (id,xingming,nianling) values('004','zhaoliu','20')
/*!*/;
#at 346
#190802 17:21:40 server id 1  end_log_pos 377 CRC32 0xea2c7707  Xid = 50
COMMIT/*!*/;                   # 操作確認標記  謹記一條操作在此才算結束
#at 377
#190802 17:22:09 server id 1  end_log_pos 456 CRC32 0x6265a2a6  Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1564737729/*!*/;
BEGIN
/*!*/;
#at 456
#190802 17:22:09 server id 1  end_log_pos 601 CRC32 0x3727aeb7  Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1564737729/*!*/;
insert into user_info (id,xingming,nianling) values('005','sunqi','30')
/*!*/;
#at 601
#190802 17:22:09 server id 1  end_log_pos 632 CRC32 0x17c4779a  Xid = 51
COMMIT/*!*/;
#at 632
#190802 17:24:05 server id 1  end_log_pos 679 CRC32 0x9c698f03  Rotate to mysql-bin.000003  pos: 4
DELIMITER ;
#End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@mysql /]# mysqlbinlog --no-defaults --stop-position='456' /mysql_bak/mysql-bin.000002 | mysql -u root -p               # 恢復操作ID‘456’ 之前的操作
Enter password: 

--start-position='456'：表示為從操作456開始恢復，該日志文件456之前的數據不會恢復；
以上選項可更改為下面類型：
--stop-position='456'：表示恢復到操作456就停止，該日志文件456之后的數據不會恢復；
基于時間點的恢復：
--start-datetime='2019-08-2 17:22:09'：表示恢復該時間之后的數據；
--stop-datetime='2019-08-2 17:22:09'：表示僅恢復該時間之前的數據；

MySQL應該如何備份與恢復就先給大家講到這里，對于其它相關問題大家想要了解的可以持續關注我們的行業資訊。我們的板塊內容每天都會捕捉一些行業新聞及專業知識分享給大家的。