# MySQL增量備份與恢複

標籤：name   host   開始   output   重複數   rac   show   間接   flags   

增量備份概念：

備份自上一次備份之後增加或改變的檔案或內容

增量備份的特點：

優點：沒有重複資料，備份量不大，時間短。

缺點：需要上次完全備份及完全備份之後所有的增量備份才能恢複，而且對所有增量備份進行逐個反推恢複，操作較為繁瑣。

mysql沒有直接提供的增量備份方法，但是可以通過MySQL的二進位日誌（binary logs）間接實現增量備份。二進位日誌對備份的意義如下：

（1）二進位日誌儲存了所有更新或者可能更新資料庫的操作。

（2）二進位日誌在啟動MySQL伺服器後開始記錄，並在檔案到達max_binlog_size所設定的大小或者接收到flush-logs命令後重新建立新的記錄檔。

（3） 只需要定時執行fulsh-logs方法重新建立新的日誌，產生二進位檔案序列，並及時把這些日誌儲存到安全的地方就完成了一個時間段的增量備份。

增量備份

1.要進行MySQL增量備份，首先要開啟二進位日誌功能。開啟二進位日誌方法：

（1）在MySQL設定檔的[mysqld]項中加入log-binfilepath項，如log-bin=mysql-bin，然後重啟mysqld服務。

[[email protected] ~]# vim /etc/my.cnf[mysqld]user = mysqlbasedir = /usr/local/mysqldatadir = /usr/local/mysql/dataport = 3306character_set_server=utf8log-bin=mysql-bin            //添加項//pid-file = /usr/local/mysql/mysqld.pidsocket = /usr/local/mysql/mysql.sockserver-id = 1[[email protected] ~]# systemctl restart mysqld.service     //重啟MySQL服務//

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

[[email protected] data]# mysqladmin -uroot -p flush-logs    //增量備份//[[email protected] data]# lsauto.cnf        ibdata1      ib_logfile1  kgc    mysql-bin.000001  performance_schemaib_buffer_pool  ib_logfile0  ibtmp1       mysql  mysql-bin.index   sys

增量恢複

當資料發生錯誤時，應根據實際 情況選擇使用完全備份恢複，還是增量恢複。增量恢複的情境是：

（1）人為的SQL語句破壞了資料庫。

（2）在進行下一次全備之前發生系統故障導致資料庫資料丟失。

（3）在主從架構中，主庫資料發生了故障。

1.丟失了完全備份之後更改的資料恢複步驟

（1）首先進入MySQL資料庫中，建立school庫和info表。

[[email protected] data]# mysql -uroot -p   //登入mysql資料庫//mysql> create database school;     //建立新的資料庫//Query OK, 1 row affected (0.01 sec)mysql> show databases;        //查看資料庫//+--------------------+| Database           |+--------------------+| information_schema || kgc                || mysql              || performance_schema || school             || sys                |+--------------------+6 rows in set (0.00 sec)mysql> use school;        //進入資料庫//

（2）向資料庫插入1條資料，退出MySQL資料庫。使用mysqldump完全備份school庫。

mysql> create table info(name varchar(10),score decimal(5,2)); //建立info表//mysqlmysql> show tables;   //查看錶資訊//+------------------+| Tables_in_school |+------------------+| info             |+------------------+1 row in set (0.00 sec)mysql> insert into info(name,score) values (‘tom‘,88);  //插入資料//mysql> select * from info;  //查看資料記錄//+------+-------+| name | score |+------+-------+| tom  | 88.00 |+------+-------+1 row in set (0.00 sec)[[email protected] data]# mysqldump -uroot -p school > /opt/school.sql //完全備份school庫//[[email protected] data]# ls /opt/school.sql

（3）使用flush-log產生新的二進位檔案，用以儲存之後的資料庫動作陳述式。

[[email protected] data]# mysqladmin -uroot -p flush-logs[[email protected] data]# lsauto.cnf        ibdata1      ib_logfile1  kgc    mysql-bin.000001  mysql-bin.index     schoolib_buffer_pool  ib_logfile0  ibtmp1       mysql  mysql-bin.000002  performance_schema  sys

（4）再次向資料庫插入1條資料，使用flush-log產生新的二進位檔案，用以儲存之後的資料庫動作陳述式。

mysql> insert into info(name,score) values (‘abc01‘,77);  //插入資料//Query OK, 1 row affected (0.03 sec)mysql> select * from info;+-------+-------+| name  | score |+-------+-------+| tom   | 88.00 || abc01 | 77.00 |+-------+-------+2 rows in set (0.00 sec)[[email protected] data]# mysqladmin -uroot -p flush-logs  //增量備份//[[email protected] data]# lsauto.cnf        ibdata1      ib_logfile1  kgc    mysql-bin.000001  mysql-bin.000003  performance_schema  sysib_buffer_pool  ib_logfile0  ibtmp1       mysql  mysql-bin.000002  mysql-bin.index

（6）使用delete刪除插入的1條資料，也就是假設完全備份後的資料丟失了。

mysql> delete from info where name=‘abc01‘;   //刪除資料//mysql> select * from info;        //查看資料記錄//+------+-------+| name | score |+------+-------+| tom  | 88.00 |+------+-------+

（7）使用二進位檔案進行增量恢複。

[[email protected] data]# mysqlbinlog --no-defaults mysql-bin.000002 | mysql -u root -p //增量恢複//[[email protected] data]# mysql -uroot -p    //登入MySQL資料庫//mysql> use school;             //進入school庫//mysql> select * from info;    //查看資料記錄//+-------+-------+| name  | score |+-------+-------+| tom   | 88.00 || abc01 | 77.00 |+-------+-------+

2.基於時間點與位置的恢複

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

假設需要往資料庫中插入兩條資料，但由於誤操作，兩條插入語句中間刪除了一條資料，而這條資料是不應該刪除的。

mysql> insert into info(name,score) values (‘test01‘,77); //插入資料test01//mysql> delete from info where name=‘tom‘;                //誤刪除tom資料//mysql> insert into info(name,score) values (‘test02‘,77);    //插入資料test02//mysql> select * from info;      //查看資料記錄//+--------+-------+| name   | score |+--------+-------+| jack   | 88.00 || test01 | 77.00 || test02 | 77.00 |+--------+-------+3 rows in set (0.00 sec)

（1）進行增量備份，編號為000002的二進位檔案中儲存了正確的插入語句，同時也儲存了不應該執行的刪除語句。

[[email protected] data]# mysqladmin -uroot -p flush-logs  //增量備份//[[email protected] data]# lsauto.cnf        ib_logfile1  mysql-bin.000001  performance_schemaib_buffer_pool  ibtmp1       mysql-bin.000002  schoolibdata1         kgc          mysql-bin.000003  sysib_logfile0     mysql        mysql-bin.index

（2）查看編號為000002的二進位檔案，尋找誤操作的位置和正確操作的位置，便於進行恢複。

[[email protected] data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002   //查看二進位檔案# at 617     //位置標記//#180704 17:12:01 server id 1  end_log_pos 660 CRC32 0xe2f72a72  Delete_rows: table id 118 flags: STMT_END_F   //誤操作的起始時間//### DELETE FROM `school`.`info`   //誤刪的資料//### WHERE###   @1=‘tom‘###   @2=88.00# at 884   //位置標記//#180704 17:12:08 server id 1  end_log_pos 930 CRC32 0xd17eb525  Write_rows: table id 118 flags: STMT_END_F  //操作正確的起始時間//### INSERT INTO `school`.`info`     //插入的資料//### SET###   @1=‘test02‘###   @2=77.00

（3）登入Mysql資料庫把原有誤刪資料的info表刪除，使用mysql恢複school庫。

mysql> drop table info;   //刪除info表//mysql> show tables;Empty set (0.00 sec)[[email protected] opt]# mysql -u root -p school < /opt/school.sql  //恢複school庫//[[email protected] opt]# mysql -uroot -pmysql> show tables;+------------------+| Tables_in_school |+------------------+| info             |+------------------+1 row in set (0.00 sec)mysql> select * from info;+------+-------+| name | score |+------+-------+| tom  | 88.00 |                  //資料tom恢複//| jack | 88.00 |+------+-------+2 rows in set (0.00 sec)

（4）基於時間點的恢複

使用mysqlbinlong加上--stop-datetime選項，表示在哪個時間點結束，後面誤動作陳述式不執行，--start-datetime選項表示執行後面的語句，結合使用它們就可以跳過誤操作的語句，完成恢複工作。需要注意的是，二進位檔案中儲存的日期格式需要調整為用“-”分割。

[[email protected] data]# mysqlbinlog --no-defaults --stop-datetime=‘2018-07-04 17:12:01‘ /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p[[email protected] opt]# mysql -u root -pmysql> use school;mysql> select * from info;+--------+-------+| name   | score |+--------+-------+| tom    | 88.00 || jack   | 88.00 || test01 | 77.00 |        //test01資料恢複//+--------+-------+3 rows in set (0.00 sec)[[email protected] data]# mysqlbinlog --no-defaults --start-datetime=‘2018-07-04 17:12:08‘ /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -pmysql> select * from info;+--------+-------+| name   | score |+--------+-------+| tom    | 88.00 || jack   | 88.00 || test01 | 77.00 || test02 | 77.00 |       //test02資料恢複//+--------+-------+4 rows in set (0.00 sec)

（5）基於位置的恢複

就是使用基於時間點的恢複，可能會出現在一個時間點裡既同時存在正確的操作又存在錯誤的操作，基於位置是一種更為準確的恢複方式。

①刪除test01和test02資料

mysql> delete from info where name=‘test01‘;mysql> delete from info where name=‘test02‘;mysql> select * from info;+------+-------+| name | score |+------+-------+| tom  | 88.00 || jack | 88.00 |+------+-------+2 rows in set (0.00 sec)

②開啟編號000002的二進位檔案，找到誤操作的位置。

# at 563    //上一次正確動作節點//#180704 17:12:01 server id 1  end_log_pos 617 CRC32 0x5b03a315  Table_map: `school`.`info` mapped to number 118# at 617   //誤操作//#180704 17:12:01 server id 1  end_log_pos 660 CRC32 0xe2f72a72  Delete_rows: table id 118 flags: STMT_END_F### DELETE FROM `school`.`info`### WHERE###   @1=‘tom‘###   @2=88.00# at 660   //下一次正確動作節點//#180704 17:12:01 server id 1  end_log_pos 691 CRC32 0x1230c738  Xid = 37COMMIT/*!*/;

③第一條插入語句後面的位置是563，--stop-position設定為563，表示執行第一條插入語句後結束。第二條插入語句後面的位置是660，--start-position設定為660，表示執行第二條插入語句，跳過了誤操作的刪除語句，達到了恢複資料的目的。

[[email protected] opt]# mysqlbinlog --no-defaults --stop-position=‘563‘ /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p[[email protected] opt]# mysqlbinlog --no-defaults --start-position=‘660‘ /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p[[email protected] opt]# mysql -uroot -p   //登入mysql資料庫//mysql> use school;      //進入school庫//mysql> select * from info;     //查看資料記錄//+--------+-------+| name   | score |+--------+-------+| tom    | 88.00 || jack   | 88.00 || test01 | 77.00 || test02 | 77.00 |+--------+-------+4 rows in set (0.00 sec)

# MySQL增量備份與恢複