MySQL 增量備份

增量備份的特點(diǎn)

        增量備份的優(yōu)點(diǎn)是沒有重復(fù)數(shù)據(jù)，備份量不大，時(shí)間短。缺點(diǎn)也很明顯，需要上次完全備份及完全備份之后所有的增量備份才能恢復(fù)，而且對所有增量進(jìn)行逐個反推恢復(fù)，操作較為繁瑣。

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

（1）二進(jìn)制日志保存了所有更新或者可能更新數(shù)據(jù)庫的操作。

（2）二進(jìn)制日志在啟動MySQL 服務(wù)器后開始記錄，并在文件到達(dá) max_binlog_size 所設(shè)置的大小或者接收到 flush logs 命令后重新創(chuàng)建新的日志文件。

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

在數(shù)據(jù)庫school 中的表 info  的基礎(chǔ)上進(jìn)行增量備份操作

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

一 增量備份

1.實(shí)現(xiàn)增量備份，首先要開啟二進(jìn)制功能。在mysql 配置文件中添加 log-bin=mysql-bin   語句，然后重新啟動服務(wù)

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

[mysqld]

log-bin=mysql-bin         //開啟二進(jìn)制功能

[root@localhost ~]# systemctl restart mysqld.service      //重啟 mysql 服務(wù)，生成二進(jìn)制文件
[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             //二進(jìn)制文件序列
ibdata1         ibtmp1       mysql-bin.index   sys

2.使用 mysqldump 命令，對數(shù)據(jù)庫 school 中的表 info 進(jìn)行完全備份

[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 表中插入數(shù)據(jù)，執(zhí)行flush-logs 操作，生成新的二進(jìn)制增量備份文件

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

mysql> use school;            //進(jìn)入庫 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              //進(jìn)行增量備份
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                    //生成新的二進(jìn)制增量備份文件

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

[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/*!*/;

二。增量備份恢復(fù)

增量恢復(fù)比完全恢復(fù)操作更加繁瑣，每個增量備份都是單獨(dú)的個體，數(shù)據(jù)不重復(fù)，需要控制的更加準(zhǔn)確。

完全備份之后丟失所有數(shù)據(jù)的恢復(fù)步驟

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

（1）模擬數(shù)據(jù)庫 school 中表info 數(shù)據(jù)丟失，使用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 命令進(jìn)行完全備份恢復(fù)的操作。

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

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

[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         //使用二進(jìn)制文件恢復(fù)
Enter password:
[root@localhost data]# mysql -uroot -p -e 'use school;select * from info;'     //查看恢復(fù)情況
Enter password:
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | tom    | 89.00 |
|  2 | lili   | 92.00 |
|  3 | test01 | 88.00 |
|  4 | test02 | 76.00 |
+----+--------+-------+

三。基于時(shí)間點(diǎn)與位置的恢復(fù)

        利用二進(jìn)制日志可實(shí)現(xiàn)基于時(shí)間點(diǎn)與位置的恢復(fù)，例如由于誤操作刪除了一張表，這是完全恢復(fù)是沒有用的，因?yàn)槿罩纠锩孢€存在誤操作的語句，我們需要的是恢復(fù)到誤操作前的狀態(tài)，然后跳過操作語句，在恢復(fù)后面操作的語句。

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

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

（1）基于時(shí)間點(diǎn)的恢復(fù)，就是講某個起始時(shí)間的二進(jìn)制日志導(dǎo)入數(shù)據(jù)庫中，從而跳過某個發(fā)生錯誤的時(shí)間點(diǎn)實(shí)現(xiàn)數(shù)據(jù)恢復(fù)。

使用 mysqlbinlog   加上 --stop-datetime   選項(xiàng)，表示在哪個時(shí)間點(diǎn)結(jié)束，后面誤操作的語句不執(zhí)行。

使用 mysqlbinlog  加上  --start-datetime   選項(xiàng)，表示執(zhí)行后面的語句，結(jié)合使用跳過誤操作語句，完成恢復(fù)。需要注意的是，二進(jìn)制文件中保存的日期格式需要調(diào)整為 “ –“  分割。

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                     //有錯誤操作的增量備份的二進(jìn)制文件
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                  //查看增量備份二進(jìn)制文件

# at 2272        //位置點(diǎn)，上一次可以執(zhí)行的位置
#180904 20:26:49 server id 1  end_log_pos 2322 CRC32 0x50dbd722     Write_rows: table id 222 flags: STMT_END_F   //時(shí)間點(diǎn)
### 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                         // 下一次可以被執(zhí)行的位置
#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 表，先進(jìn)性完全備份恢復(fù)，在按順序進(jìn)行增量備份恢復(fù)

mysql> use school;                       //進(jìn)入school 數(shù)據(jù)庫
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           //先進(jìn)行完全備份
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        //進(jìn)行第一次增量備份恢復(fù)
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’語句錯誤操作時(shí)間

[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 |
+----+--------+-------+

                                                                                               //下一次正確操作時(shí)間點(diǎn)

[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 表恢復(fù)正確
|  4 | test02 | 76.00 |
|  5 | test04 | 80.00 |
|  6 | test05 | 90.00 |
+----+--------+-----