MySQL完全備份、增量備份與恢複

標籤：資料操作   mysql   位置   需要   mysql增量備份   output   速度   ica   技術分享   

在生產環境中，資料的安全性是至關重要的，任何資料的丟失都可能產生嚴重的後果。所以資料庫需要備份。這裡以新版mysql5.7為例。

一、Database Backup分類：

1. 從物理與邏輯的角度，備份可分為物理備份和邏輯備份。

   （1）物理備份：對資料庫作業系統的物理檔案（如資料檔案、記錄檔等）的備份。又可以分為冷備份和熱備份。

   冷備份：關閉資料庫時進行的備份操作

   熱備份：在資料庫運行狀態中進行備份操作，這種備份方法依賴於資料庫的記錄檔。

   （2）邏輯備份：對資料庫邏輯組件（如表等資料庫物件）的備份。

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

   （1）完全備份：每次對資料庫進行完整的備份。可以備份單個資料庫，多個資料庫，所有資料庫，也可以備份資料庫中的單個表，多個表。

   （2）差異備份：備份那些自從上次完全備份之後被修改過的檔案，只備份資料庫部分內容，但是儲存和恢複速度快。

   （3）增量備份：只有那些在上次完全備份或者增量備份後被修改的檔案才會被備份。

二、完全備份與恢複

• 備份：

  使用mysqldump工具可以靈活的控製備份的內容，比如某幾個表或庫都可以單獨備份。

1. 對單個庫進行完全備份。命令格式如下：

mysqldump -u使用者名稱 -p[密碼] [選項] [資料庫名] > /備份路徑/備份檔案名

2. 對多個庫進行完全備份。命令格式如下：

mysqldump -u使用者名稱 -p[密碼] [選項] --databases 庫名1 庫名2 ... > /備份路徑/備份檔案名

3. 對所有庫進行完全備份。命令格式如下：

mysqldump -u使用者名稱 -p[密碼] --opt --all-databases > /備份路徑/備份檔案名

4. 對錶結構進行完全備份。命令格式如下：

mysqldump -u使用者名稱 -p[密碼] -d 資料庫名 表名 > /備份路徑/備份檔案名

5. 對錶進行完全備份。命令格式如下：

mysqldump -u使用者名稱 -p[密碼] 資料庫名 表名 > /備份路徑/備份檔案名

下面演練完全備份的實驗。

完全備份單個庫：

[[email protected] ~]# mysqldump -uroot -pabc123 school > /opt/school-$(date +%F).sql [[email protected] ~]# ls /opt/school-2018-07-19.sql

完全備份單個表：

[[email protected] ~]# mysqldump -uroot -pabc123 school info > /opt/school_info-$(date +%F).sql[[email protected] ~]# ls /opt/school_info-2018-07-19.sql

• 恢複：

1. 登入mysql，使用source命令恢複庫。命令格式如下：

source 庫備份指令碼的路徑

2. 在不登入MySQL的情況下，使用mysql命令直接恢複整庫。命令格式如下：

mysql -u使用者名稱 -p[密碼] < 庫備份指令碼的路徑

下面演練完全備份後恢複的實驗。

恢複單個庫：

假設資料庫損壞，刪除資料庫school。

[[email protected] ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.mysql> drop database school;Query OK, 1 row affected (0.02 sec)mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || sys                |+--------------------+4 rows in set (0.00 sec)

1）使用source命令恢複。

注意：先建立一個同名的資料庫，再進入資料庫，用surce命令恢複,否則會報錯。

mysql> create database school;Query OK, 1 row affected (0.01 sec)mysql> use school;Database changedmysql> source /opt/school-2018-07-19.sqlQuery OK, 0 rows affected (0.01 sec)......Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0...Query OK, 0 rows affected (0.00 sec)

2）使用mysql命令恢複。

再一次刪掉資料庫school。

注意：在恢複操作之前要建立一個同名的資料庫，否則會報錯。

mysql> create database school;Query OK, 1 row affected (0.00 sec)mysql> exitBye[[email protected] ~]# mysql -uroot -pabc123 school < /opt/school-2018-07-19.sql 

兩種方法恢複完成後查看資料庫恢複成功。

恢複單個表：

假設資料損毀，刪除資料庫school中的表info。

mysql> use school;Database changedmysql> select * from info;+----------+-------+| name     | score |+----------+-------+| zhangsan | 75.00 || lisi     | 85.00 |+----------+-------+2 rows in set (0.00 sec)mysql> drop table info;Query OK, 0 rows affected (0.01 sec)mysql> show tables;Empty set (0.00 sec)

1）使用source命令恢複。
```**
mysql> use school;
Database changed
mysql> source /opt/school_info-2018-07-19.sql
Query OK, 0 rows affected (0.00 sec)
...
...
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
...
Query OK, 0 rows affected (0.00 sec)

**2）使用mysql命令恢複。**再一次刪除表info,然後進行恢複操作。

[[email protected] ~]# mysql -uroot -pabc123 school < /opt/school_info-2018-07-19.sql

兩種方法恢複完成後查看錶info恢複成功。

mysql> use school;
Database changed

![](http://i2.51cto.com/images/blog/201807/20/1588a19b3803b9990f26037d1b00f695.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)### 三、增量備份與恢複MySQL沒有提供直接的增量辦法，但是可以通過對MySQL的二進位日誌間接實現增量備份。二進位日誌儲存了所有更新或者可能更新資料庫的操作。**特點：**- 沒有重複資料，備份量不大，時間短- 需要上次完全備份及完全備份之後所有的增量備份才能恢複，而且要進行逐個反推恢複，操作繁瑣。### 1.實現增量備份**（1）要進行MySQL增量備份，首先要開啟二進位日誌功能。**在mysql的設定檔的[mysqld]選項中加入log-bin=mysql-bin，然後重啟服務。

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin

systemctl restart mysqld.service

**（2）使用mysqldump完全備份school庫。**

[[email protected] ~]# mysqldump -uroot -pabc123 school > /opt/school-$(date +%F).sql
[[email protected] ~]# ls /opt/
school-2018-07-19.sql

**（3）使用mysqladmin的選項flush-logs產生新的二進位檔案，這樣在插入新的資料後，新的二進位檔案對應的就是資料庫的變化的內容。**

[[email protected] ~]# cd /usr/local/mysql/data/
[[email protected] data]# ls ./ | grep mysql.bin.
mysql-bin.000001
mysql-bin.index
[[email protected] data]# mysqladmin -uroot -pabc123 flush-logs //進行日誌分割
[[email protected] data]# ls ./ | grep mysql.bin.
mysql-bin.000001
mysql-bin.000002 //產生新的二進位檔案mysql-bin.000002
mysql-bin.index

**（4）插入一條新的資料，以類比資料的增加或變更。**

mysql> use school;
Database changed
mysql> insert into info (name,score) values (‘tom‘,‘65‘);
Query OK, 1 row affected (0.01 sec)

mysql> exit
Bye

此時的資料庫變化儲存在編號2 的二進位檔案中，使用mysqlbinlog命令可以查看二進位檔案的內容，裡面儲存了插入資料的語句。

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

![](http://i2.51cto.com/images/blog/201807/20/9b11b20c1100ca761b5c52812f900b56.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)**（5）再次執行flush- logs操作產生新的二進位檔案，而新的二進位檔案會儲存之後的資料操作。**

[[email protected] data]# mysqladmin -uroot -pabc123 flush-logs
[[email protected] data]# ls ./ | grep mysql.bin.*
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003 //產生新的二進位檔案mysql-bin.000003
mysql-bin.index

**（6）再次向資料庫插入一條資料。**

mysql> use school;
Database changed
mysql> insert into info (name,score) values (‘lucy‘,‘70‘);
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye

**（7）最後再執行一次flush-logs操作，確保之前的二進位檔案不再發生變化。**

[[email protected] data]# mysqladmin -uroot -pabc123 flush-logs
[[email protected] data]# ls ./ | grep mysql.bin.*
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004 //產生新的二進位檔案，儲存之後的資料動作陳述式
mysql-bin.index

### 2.丟失完全備份之後更改的資料的恢複**（1）使用delete刪除插入的兩條資料，也就是假設完全備份後的資料丟失了。**

mysql> use school;
Database changed
mysql> delete from info where name=‘tom‘;
Query OK, 1 row affected (0.01 sec)

mysql> delete from info where name=‘lucy‘;
Query OK, 1 row affected (0.00 sec)

**（2）使用二進位檔案恢複時需要注意的是恢複順序，要先恢複最先產生的二進位檔案，然後依次執行。**

[[email protected] ~]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000002|mysql -uroot -pabc123
[[email protected] ~]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000003|mysql -uroot -pabc123

查看資料庫內容，完全備份之後新插入的兩條資料找回來了，說明資料恢複成功。![](http://i2.51cto.com/images/blog/201807/20/0dee22e223324510b85d3324146a163c.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)### 3. 完全備份之後丟失所有資料的恢複當完全備份和增量備份之後，所有資料丟失，需要把完全備份和所有增量備份檔案逐個恢複。**（1）執行flush-logs操作分割日誌，插入一條資料。**

[[email protected] data]# mysqladmin -uroot -pabc123 flush-logs
[[email protected] data]# ls ./ | grep mysql.bin.*
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005 //產生新的二進位檔案，儲存之後的資料動作陳述式
mysql-bin.index

mysql> use school;
Database changed
mysql> insert into info (name,score) values (‘jack‘,‘55‘);
Query OK, 1 row affected (0.01 sec)

mysql> exit
Bye

這條插入語句儲存在mysql-bin.000005二進位檔案中。**（2）再執行一次flush-logs操作，插入一條資料，之後還要再執行一次flush-logs操作，確保插入資料儲存在二進位檔案中，不再改變。**

[[email protected] data]# mysqladmin -uroot -pabc123 flush-logs
[[email protected] data]# ls ./ | grep mysql.bin.*
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005
mysql-bin.000006 //產生新的二進位檔案，儲存之後的資料動作陳述式
mysql-bin.index

mysql> use school;
Database changed
mysql> insert into info (name,score) values (‘xixi‘,‘45‘);
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye

[[email protected] data]# mysqladmin -uroot -pabc123 flush-logs
[[email protected] data]# ls ./ | grep mysql.bin.*
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005
mysql-bin.000006
mysql-bin.000007 //產生新的二進位檔案，儲存之後的資料動作陳述式
mysql-bin.index

這條插入語句儲存在mysql-bin.000006二進位檔案中。**（3）使用drop刪除表info，也就是假設完全備份前info的資料和完全備份後的資料都丟失了。**

mysql> use school;
Database changed
mysql> drop table info;
Query OK, 0 rows affected (0.03 sec)

**（4）先使用mysql命令進行完全備份的恢複操作。**

[[email protected] data]# mysql -uroot -pabc123 school < /opt/school-2018-07-19.sql

[[email protected] data]# mysql -uroot -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> use school;
Database changed
mysql> select * from info;
+----------+-------+
| name | score |
+----------+-------+
| zhangsan | 75.00 |
| lisi | 85.00 |
| wangwu | 95.00 |
+----------+-------+
3 rows in set (0.00 sec)

被刪除的表的資料又可以查詢出來**（5）使用二進位檔案恢複時需要注意的是恢複順序，要先恢複最先產生的二進位檔案，然後依次執行。**

[[email protected] ~]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000005|mysql -uroot -pabc123
[[email protected] ~]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000006|mysql -uroot -pabc123

可以看到資料全部恢複出來了。![](http://i2.51cto.com/images/blog/201807/20/bfb0ffea3a82cccfa08e261c9afa3a70.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)### 4. 基於時間點與位置恢複利用二進位日誌可實現基於時間點與位置的恢複，例如由於誤操作刪除了一張表，這時完全恢複時沒有用的，因為日誌裡面還存在誤操作的語句。我們需要的是恢複到誤操作前的狀態，然後跳過誤操作的語句，再恢複後面操作的語句。

mysql> use school;
Database changed
mysql> select * from info;
+----------+-------+
| name | score |
+----------+-------+
| zhangsan | 75.00 |
| lisi | 85.00 |
| wangwu | 95.00 |
+----------+-------+
3 rows in set (0.00 sec)

**首先對資料庫進行備份。**

[[email protected] ~]# mysqldump -uroot -pabc123 school > /opt/school-$(date +%F).sql
[[email protected] ~]# ls /opt/
school-2018-07-20.sql

### （1）基於時間點恢複將某個起始時間的二進位日誌匯入資料庫中，從而跳過某個發生錯誤的時間點實現資料的恢複。**1）執行flush-logs操作產生新的二進位檔案。**

[[email protected] data]# mysqladmin -uroot -pabc123 flush-logs
[[email protected] data]# ls ./ | grep mysql.bin.*
mysql-bin.0000020
//產生新的二進位檔案，儲存之後的資料動作陳述式

**2）插入兩條資料，但由於誤操作，兩條插入語句中間刪除了一條資料，而這條資料是不應該刪除的。為了確保資料儲存在二進位檔案中不改變，執行一次flush-logs操作。**

mysql> use school;
Database changed
mysql> insert into info (name,score) values (‘coco‘,‘65‘);
Query OK, 1 row affected (0.00 sec)

mysql> delete from info where name=‘lisi‘;
Query OK, 1 row affected (0.06 sec)

mysql> insert into info (name,score) values (‘momo‘,‘85‘);
Query OK, 1 row affected (0.00 sec)

[[email protected] data]# mysqladmin -uroot -pabc123 flush-logs
[[email protected] data]# ls ./ | grep mysql.bin.*
mysql-bin.0000021
//產生新的二進位檔案，儲存之後的資料動作陳述式

**3）假設資料損毀，刪除表info。**

mysql> use school;
Database changed
mysql> drop table info;
Query OK, 0 rows affected (0.01 sec)

**4）使用mysql命令進行完全備份的恢複操作。**

[[email protected] data]# mysql -uroot -pabc123 school < /opt/school-2018-07-20.sql

[[email protected] data]# mysql -uroot -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> use school;
Database changed
mysql> select * from info;
+----------+-------+
| name | score |
+----------+-------+
| zhangsan | 75.00 |
| lisi | 85.00 |
| wangwu | 95.00 |
+----------+-------+
3 rows in set (0.00 sec)

**5）查看資料動作陳述式儲存的二進位檔案mysql-bin.0000020。**

[[email protected] data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000020

![](http://i2.51cto.com/images/blog/201807/20/9c7d1ac0c81dcc4197240ff136940303.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)![](http://i2.51cto.com/images/blog/201807/20/c84877044810d07204269ecbcf09d028.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)**6）根據二進位檔案的資料動作陳述式的時間點進行完全備份後增量備份的恢複操作。**

[[email protected] data]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000020 --stop-datetime=‘18-07-20 0:36:18‘|mysql -uroot -pabc123

[[email protected] data]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000020 --start-datetime=‘18-07-20 0:36:41‘|mysql -uroot -pabc123

![](http://i2.51cto.com/images/blog/201807/20/e40517abd97187c550991bfc9c4ed48b.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)### （2）基於位置恢複使用基於時間點的恢複可能會出現在一個時間點裡既同時存在正確的操作又存在錯誤的操作，基於位置是一種更為精確的恢複方式。**假設資料損毀，刪除表info。****

mysql> use school;
Database changed
mysql> drop table info;
Query OK, 0 rows affected (0.01 sec)

**使用mysql命令進行完全備份的恢複操作。**

[[email protected] data]# mysql -uroot -pabc123 school < /opt/school-2018-07-20.sql

[[email protected] data]# mysql -uroot -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> use school;
Database changed
mysql> select * from info;
+----------+-------+
| name | score |
+----------+-------+
| zhangsan | 75.00 |
| lisi | 85.00 |
| wangwu | 95.00 |
+----------+-------+
3 rows in set (0.00 sec)

**1）執行flush-logs操作產生新的二進位檔案。**

[[email protected] data]# mysqladmin -uroot -pabc123 flush-logs
[[email protected] data]# ls ./ | grep mysql.bin.*
mysql-bin.0000024
//產生新的二進位檔案，儲存之後的資料動作陳述式

**2）插入兩條資料，但由於誤操作，兩條插入語句中間刪除了一條資料，而這條資料是不應該刪除的。為了確保資料儲存在二進位檔案中不改變，執行一次flush-logs操作。**

mysql> use school;
Database changed
mysql> insert into info (name,score) values (‘lili‘,‘65‘);
Query OK, 1 row affected (0.00 sec)

mysql> delete from info where name=‘wangwu‘;
Query OK, 1 row affected (0.06 sec)

mysql> insert into info (name,score) values (‘mimi‘,‘85‘);
Query OK, 1 row affected (0.00 sec)

[[email protected] data]# mysqladmin -uroot -pabc123 flush-logs
[[email protected] data]# ls ./ | grep mysql.bin.*
mysql-bin.0000025
//產生新的二進位檔案，儲存之後的資料動作陳述式

**3）查看資料動作陳述式儲存的二進位檔案mysql-bin.0000024。**

[[email protected] data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000024

![](http://i2.51cto.com/images/blog/201807/20/ad6b00341d9e36b32326aa6efa65147f.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)![](http://i2.51cto.com/images/blog/201807/20/e22b9ff257eb8008315ab9b84bb00544.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)**4）根據二進位檔案的資料動作陳述式的時間點進行完全備份後增量備份的恢複操作。**

[[email protected] data]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000024 --stop-position=‘615‘|mysql -uroot -pabc123

[[email protected] data]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000024 --start-position=‘661‘|mysql -uroot -pabc123

![](http://i2.51cto.com/images/blog/201807/20/d648d8193f1177b6cc6ebde51fb818ff.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)

MySQL完全備份、增量備份與恢複