MySQL 增量備份

增量備份的特點

        增量備份的優點是沒有重復數據，備份量不大，時間短。缺點也很明顯，需要上次完全備份及完全備份之后所有的增量備份才能恢復，而且對所有增量進行逐個反推恢復，操作較為繁瑣。

       MySQL 沒有提供直接的增量備份方法，但是可以通過MySQL  的二進制間接實現增量備份。二進制日志對備份的意義如下：

（1）二進制日志保存了所有更新或者可能更新數據庫的操作。

（2）二進制日志在啟動MySQL 服務器后開始記錄，并在文件到達 max_binlog_size 所設置的大小或者接收到 flush logs 命令后重新創建新的日志文件。

（3）只需要訂時執行 flush logs 方法重新創建新的日志，生成二進制文件序列，并及時把這些日志保存到安全的地方就完成了一個時間段的增量備份。

在數據庫school 中的表 info  的基礎上進行增量備份操作

mysql> select * from info;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | tom  | 89.00 |
|  2 | lili | 92.00 |
+----+------+-------+

一 增量備份

1.實現增量備份，首先要開啟二進制功能。在mysql 配置文件中添加 log-bin=mysql-bin   語句，然后重新啟動服務

[root@bogon data]# vim /etc/my.cnf

[mysqld]

log-bin=mysql-bin         //開啟二進制功能

[root@localhost ~]# systemctl restart mysqld.service      //重啟 mysql 服務，生成二進制文件
[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# ls
auto.cnf        ib_logfile0  mysql             performance_schema
ib_buffer_pool  ib_logfile1  mysql-bin.000001  school             //二進制文件序列
ibdata1         ibtmp1       mysql-bin.index   sys

2.使用 mysqldump 命令，對數據庫 school 中的表 info 進行完全備份

[root@localhost data]# mysqldump -uroot -p school info > /opt/info.sql
Enter password:
[root@localhost data]# cd /opt/
[root@localhost opt]# ls
info.sql  mysql-5.7.17  rh                           //表info 備份文件

3.向 info 表中插入數據，執行flush-logs 操作，生成新的二進制增量備份文件

[root@localhost data]# mysql -uroot –p              //進入mysql數據庫
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> use school;            //進入庫 school
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into info (name,score) values ('test01',88);        //插入信息
Query OK, 1 row affected (0.37 sec)

mysql> insert into info (name,score) values ('test02',76);
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | tom    | 89.00 |
|  2 | lili   | 92.00 |
|  3 | test01 | 88.00 |
|  4 | test02 | 76.00 |
+----+--------+-------+

mysql> quit
Bye
[root@localhost data]# mysqladmin -uroot -p flush-logs              //進行增量備份
Enter password:
[root@localhost data]# ls
auto.cnf        ib_logfile0  mysql             mysql-bin.index     sys
ib_buffer_pool  ib_logfile1  mysql-bin.000001  performance_schema
ibdata1         ibtmp1       mysql-bin.000002  school                    //生成新的二進制增量備份文件

4.雖然生成新的二進制增量備份文件  mysql-bin.000002   但此時數據變化保存在編號 000001 中，而000002 是一個空的日志文件，使用 mysqlbinlog 命令查看二進制文件內容。

[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v  mysql-bin.000001        //查看編號000001 的日志文件

# at 348                                                                                     //不會生成亂碼
#180904 16:45:20 server id 1  end_log_pos 398 CRC32 0x398bd3e5     Write_rows: table id 118 flags: STMT_END_F
### INSERT INTO `school`.`info`
### SET
###   @1=3                                              //表info 中插入的信息
###   @2='test01'
###   @3=88.00
# at 398
#180904 16:45:20 server id 1  end_log_pos 429 CRC32 0xd2d905fe     Xid = 63
COMMIT/*!*/;

# at 623
#180904 16:45:32 server id 1  end_log_pos 673 CRC32 0x4164c313     Write_rows: table id 118 flags: STMT_END_F
### INSERT INTO `school`.`info`
### SET
###   @1=4
###   @2='test02'
###   @3=76.00
# at 673
#180904 16:45:32 server id 1  end_log_pos 704 CRC32 0x8287a8b1     Xid = 64
COMMIT/*!*/;

二。增量備份恢復

增量恢復比完全恢復操作更加繁瑣，每個增量備份都是單獨的個體，數據不重復，需要控制的更加準確。

完全備份之后丟失所有數據的恢復步驟

         當完全備份和增量備份之后，所有數據丟失，需要把完全備份和所有增量備份文件逐個恢復，這里演示對庫 school 中的info 表的恢復操作。

（1）模擬數據庫 school 中表info 數據丟失，使用drop 刪除info 表

[root@localhost ~]# mysql -uroot -p
Enter password:

mysql> use school;

mysql> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | tom    | 89.00 |
|  2 | lili   | 92.00 |
|  3 | test01 | 88.00 |
|  4 | test02 | 76.00 |
+----+--------+-------+
4 rows in set (0.00 sec)

mysql> drop table info;                  //刪除info 表
Query OK, 0 rows affected (0.01 sec)

mysql> select * from info;
ERROR 1146 (42S02): Table 'school.info' doesn't exist

（2）先使用mysql 命令進行完全備份恢復的操作。

[root@localhost ~]# mysql -uroot -p school < /opt/info.sql       //恢復school 庫中 info 表，備份文件為/opt/info.sql
Enter password:
[root@localhost ~]# mysql -uroot -p -e 'use school;show tables;select * from info;'     //不進入數據庫查看info 表的恢復情況
Enter password:
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | tom  | 89.00 |                     //完全備份操作已經恢復
|  2 | lili | 92.00 |
+----+------+-------+

（3）被刪除的表中數據又可以查詢出來，說明完全恢復是成功的。接下來使用二進制文件進行增量恢復操作，需要注意的是恢復的順序，要恢復最先生成的二進制文件，然后依次執行

[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# ls
auto.cnf        ib_logfile0  mysql             mysql-bin.000003    school
ib_buffer_pool  ib_logfile1  mysql-bin.000001  mysql-bin.index     sys
ibdata1         ibtmp1       mysql-bin.000002  performance_schema
[root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000001 | mysql -uroot –p         //使用二進制文件恢復
Enter password:
[root@localhost data]# mysql -uroot -p -e 'use school;select * from info;'     //查看恢復情況
Enter password:
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | tom    | 89.00 |
|  2 | lili   | 92.00 |
|  3 | test01 | 88.00 |
|  4 | test02 | 76.00 |
+----+--------+-------+

三。基于時間點與位置的恢復

        利用二進制日志可實現基于時間點與位置的恢復，例如由于誤操作刪除了一張表，這是完全恢復是沒有用的，因為日志里面還存在誤操作的語句，我們需要的是恢復到誤操作前的狀態，然后跳過操作語句，在恢復后面操作的語句。

       假定需要往數據庫中插入兩條數據，但由于誤操作，兩條語句中間刪除了一條數據，而這條數據是不應該刪除的。

由于誤操作，在添加完 test04 后 將  ‘tom’ 給誤刪除，有添加了test 05.

（1）基于時間點的恢復，就是講某個起始時間的二進制日志導入數據庫中，從而跳過某個發生錯誤的時間點實現數據恢復。

使用 mysqlbinlog   加上 --stop-datetime   選項，表示在哪個時間點結束，后面誤操作的語句不執行。

使用 mysqlbinlog  加上  --start-datetime   選項，表示執行后面的語句，結合使用跳過誤操作語句，完成恢復。需要注意的是，二進制文件中保存的日期格式需要調整為 “ –“  分割。

mysql> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  2 | lili   | 92.00 |
|  3 | test01 | 88.00 |
|  4 | test02 | 76.00 |
|  5 | test04 | 80.00 |
|  6 | test05 | 90.00 |
+----+--------+-------+
5 rows in set (0.00 sec)

[root@localhost data]# ls                     //有錯誤操作的增量備份的二進制文件
auto.cnf        ib_logfile0  mysql             mysql-bin.000003  performance_schema
ib_buffer_pool  ib_logfile1  mysql-bin.000001  mysql-bin.000004  school
ibdata1         ibtmp1       mysql-bin.000002  mysql-bin.index   sys

[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v  mysql-bin.000003                  //查看增量備份二進制文件

# at 2272        //位置點，上一次可以執行的位置
#180904 20:26:49 server id 1  end_log_pos 2322 CRC32 0x50dbd722     Write_rows: table id 222 flags: STMT_END_F   //時間點
### INSERT INTO `school`.`info`       //正確操作語句
### SET
###   @1=5
###   @2='test04'
###   @3=80.00
# at 2322
#180904 20:26:49 server id 1  end_log_pos 2353 CRC32 0x9ffdf83c     Xid = 93
COMMIT/*!*/;

# at 2547     
#180904 20:27:19 server id 1  end_log_pos 2594 CRC32 0xaed6d12c     Delete_rows: table id 222 flags: STMT_END_F
### DELETE FROM `school`.`info`         //誤操作語句
### WHERE
###   @1=1
###   @2='tom'
###   @3=89.00
# at 2594                         // 下一次可以被執行的位置
#180904 20:27:19 server id 1  end_log_pos 2625 CRC32 0x84e5fdb3     Xid = 94
COMMIT/*!*/;

# at 2819
#180904 20:32:27 server id 1  end_log_pos 2869 CRC32 0x5babb6b2     Write_rows: table id 222 flags: STMT_END_F
### INSERT INTO `school`.`info`       //正確操作語句
### SET
###   @1=6
###   @2='test05'
###   @3=90.00
# at 2869
#180904 20:32:27 server id 1  end_log_pos 2900 CRC32 0x73283b1b     Xid = 95
COMMIT/*!*/;

（2）刪除info 表，先進性完全備份恢復，在按順序進行增量備份恢復

mysql> use school;                       //進入school 數據庫
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table info;                 //刪除info 表
Query OK, 0 rows affected (0.35 sec)

mysql> quit
Bye
[root@localhost data]# mysql -uroot -p school < /opt/info.sql           //先進行完全備份
Enter password:
[root@localhost data]# mysql -uroot -p -e 'use school;show tables;select * from info;'Enter password:       //查看完全備份info 表
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | tom  | 89.00 |
|  2 | lili | 92.00 |
+----+------+-------+

[root@localhost data]#  mysqlbinlog --no-defaults mysql-bin.000001|mysql -uroot –pabc123        //進行第一次增量備份恢復
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost data]# mysql -uroot -p -e 'use school;show tables;select * from info;'
Enter password:
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | tom    | 89.00 |
|  2 | lili   | 92.00 |
|  3 | test01 | 88.00 |
|  4 | test02 | 76.00 |

                                                                                                                         //刪除‘tom’語句錯誤操作時間

[root@localhost data]# mysqlbinlog --no-defaults --stop-datetime='2018-09-04 20:27:19' /usr/local/mysql/data/mysql-bin.000003 | mysql -uroot -p
Enter password: 
[root@localhost data]# mysql -uroot -p -e 'use school;show tables;select * from info;'
Enter password:
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | tom    | 89.00 |
|  2 | lili   | 92.00 |
|  3 | test01 | 88.00 |
|  4 | test02 | 76.00 |
|  5 | test04 | 80.00 |
+----+--------+-------+

                                                                                               //下一次正確操作時間點

[root@localhost data]# mysqlbinlog --no-defaults --start-datetime='2018-09-04 20:32:27 ' /usr/local/mysql/data/mysql-bin.000003 | mysql -uroot -p
Enter password:

[root@localhost data]# mysql -uroot -p -e 'use school;show tables;select * from info;'Enter password:
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | tom    | 89.00 |
|  2 | lili   | 92.00 |
|  3 | test01 | 88.00 |                               //info 表恢復正確
|  4 | test02 | 76.00 |
|  5 | test04 | 80.00 |
|  6 | test05 | 90.00 |
+----+--------+-----