MySQL增量備份與恢復(fù)

[TOC]

創(chuàng)新互聯(lián)專(zhuān)注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于成都網(wǎng)站設(shè)計(jì)、做網(wǎng)站、新晃網(wǎng)絡(luò)推廣、微信小程序、新晃網(wǎng)絡(luò)營(yíng)銷(xiāo)、新晃企業(yè)策劃、新晃品牌公關(guān)、搜索引擎seo、人物專(zhuān)訪、企業(yè)宣傳片、企業(yè)代運(yùn)營(yíng)等，從售前售中售后，我們都將竭誠(chéng)為您服務(wù)，您的肯定，是我們最大的嘉獎(jiǎng)；創(chuàng)新互聯(lián)為所有大學(xué)生創(chuàng)業(yè)者提供新晃建站搭建服務(wù)，24小時(shí)服務(wù)熱線：028-86922220，官方網(wǎng)址：jinyejixie.com

一、前言

? 上篇文章（MySQL完全備份與恢復(fù)概念及操作）中我們?cè)敿?xì)介紹了MySQL完全備份與恢復(fù)的概念以及詳細(xì)的命令操作進(jìn)行驗(yàn)證，并且也講述了一些增量備份的概念。而本文將就MySQL的增量備份及恢復(fù)作進(jìn)一步介紹與實(shí)例操作。

二、再談MySQL增量備份

2.1問(wèn)題引出與解決

? 首先我們思考一個(gè)問(wèn)題：既然有了完全備份和差異備份，為什么還需要增量備份呢？

? 上篇文章中我們談到可以使用tar命令將數(shù)據(jù)庫(kù)的數(shù)據(jù)目錄中的目錄和文件進(jìn)行xz格式的打包從而進(jìn)行數(shù)據(jù)庫(kù)的變相備份，也可以使用MySQL數(shù)據(jù)庫(kù)自帶的mysqldump工具進(jìn)行完全備份。

? 但是我們會(huì)發(fā)現(xiàn)這樣一個(gè)問(wèn)題：當(dāng)數(shù)據(jù)庫(kù)系統(tǒng)中的數(shù)據(jù)庫(kù)包含的數(shù)據(jù)表越來(lái)越多（在生產(chǎn)環(huán)境中，尤其是大型企業(yè)，數(shù)據(jù)表中必然會(huì)有大量的數(shù)據(jù)），那么我們繼續(xù)使用tar壓縮解壓恢復(fù)或者說(shuō)進(jìn)行完全備份操作來(lái)備份數(shù)據(jù)庫(kù)的數(shù)據(jù)時(shí)，就會(huì)造成諸多問(wèn)題，比如：備份時(shí)間加長(zhǎng)，備份冗余數(shù)據(jù)龐大，服務(wù)器存儲(chǔ)資源占用大，占用網(wǎng)絡(luò)帶寬，備份過(guò)程中出現(xiàn)網(wǎng)絡(luò)癱瘓，服務(wù)器宕機(jī)等問(wèn)題時(shí)就會(huì)造成無(wú)法承擔(dān)的災(zāi)難，而差異備份也是如此，畢竟差異備份的參考對(duì)象僅僅是完全備份的內(nèi)容。

? 因此，我們需要考慮如何在節(jié)約各種成本的情況下，兼顧安全與性能，得出一個(gè)相對(duì)更好的方法來(lái)備份數(shù)據(jù)，而這個(gè)方法就是進(jìn)行“增量備份”，其余差異備份的差別在于參考對(duì)象，可以參考上面博文中畫(huà)出的比較三者區(qū)別的表格案例，看了之后您一定會(huì)理解。

2.2進(jìn)一步理解增量備份及其優(yōu)缺點(diǎn)，考慮其適用場(chǎng)景

? 個(gè)人比較喜歡使用身邊生活中的例子來(lái)理解各種知識(shí)點(diǎn)，那么就舉一個(gè)例子來(lái)加深大家對(duì)“增量備份”的理解。

? 在我們使用計(jì)算機(jī)的時(shí)候必定會(huì)存儲(chǔ)一些文件，細(xì)心的人更加會(huì)在另一個(gè)終端或者說(shuō)使用U盤(pán)進(jìn)行一次備份，防止原來(lái)的丟失或出錯(cuò)，而一個(gè)細(xì)心且有條理的人會(huì)過(guò)一段時(shí)間將新產(chǎn)生的文件再次進(jìn)行備份，而不是全盤(pán)重新進(jìn)行備份，因?yàn)檫@樣的人是希望提高效率并且節(jié)約時(shí)間，尤其在工作數(shù)據(jù)量大的情況下，之后循環(huán)此類(lèi)的操作。

? 而我們所說(shuō)的“增量備份”就是這樣一種“細(xì)心而又條理的人”。

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

? MySQL沒(méi)有提供直接的增量備份方法，但是可以通過(guò)MySQL的二進(jìn)制日志文件（binary logs）簡(jiǎn)接實(shí)現(xiàn)增量備份。二進(jìn)制日志對(duì)備份的意義如下：

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

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

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

2.2.2增量備份的優(yōu)點(diǎn)

增量備份沒(méi)有冗余數(shù)據(jù)，備份量少；
時(shí)間短，效率高，成本低；

2.2.3增量備份的缺點(diǎn)

? 備份具備優(yōu)勢(shì)的同時(shí)，其恢復(fù)數(shù)據(jù)的要求必然嚴(yán)苛，這就是所謂“收之桑榆，失之東隅”了。增量備份的恢復(fù)就需要依賴(lài)于最初的完全備份以及截至目前的所有的增量備份才可以完成恢復(fù)數(shù)據(jù)庫(kù)數(shù)據(jù)的操作：對(duì)所有的增量備份進(jìn)行逐個(gè)反推，操作非常繁瑣。

2.3適用場(chǎng)景

? 一般，在數(shù)據(jù)庫(kù)系統(tǒng)建立后可預(yù)測(cè)未來(lái)數(shù)據(jù)量非常龐大的場(chǎng)景中比較適合適用增量備份；數(shù)據(jù)庫(kù)中的數(shù)據(jù)更新迭代速度快內(nèi)容多的情況也適用增量備份，可以使用周期計(jì)劃任務(wù)等其他方法來(lái)進(jìn)行備份數(shù)據(jù)。

三、增量備份與模擬數(shù)據(jù)恢復(fù)操作

? 上文中談到MySQL并沒(méi)有給出直接的增量備份的方法，而是依賴(lài)二進(jìn)制日志文件簡(jiǎn)接實(shí)現(xiàn)增量備份。因此我們首先需要先開(kāi)啟二進(jìn)制日志功能，開(kāi)啟方法如下：

? 修改配置文件，當(dāng)初我們?cè)谑止ぞ幾g安裝MySQL時(shí)，修改過(guò)/etc/my.cnf文件，開(kāi)啟二進(jìn)制日志功能也是需要更改配置文件，并重啟服務(wù)。

? 在配置文件的mysqld目錄下添加如下一行：

log-bin=mysql-bin

? 我們重啟服務(wù)之后就可以才data目錄下看到mysql-bin.000001的一個(gè)文件

[root@localhost data]# systemctl restart mysqld.service 
[root@localhost data]# ls
auto.cnf  student  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  ibtmp1  mysql  mysql-bin.000001  mysql-bin.index  performance_schema  sys
[root@localhost data]#

? 因?yàn)椴町悅浞莺驮隽總浞荻际腔谕耆珎浞莸幕A(chǔ)上執(zhí)行操作的，因此我們需要先進(jìn)行一次完全備份操作，并模擬增加數(shù)據(jù)、誤操作刪除了數(shù)據(jù)的操作，最后依賴(lài)上述類(lèi)型的二進(jìn)制日志文件進(jìn)行必要的數(shù)據(jù)恢復(fù)。

? 具體的操作過(guò)程如下：

1）首先是進(jìn)行完全備份并查看文件

[root@localhost data]# mysqldump -uroot -p student > /opt/student.sql
Enter password: 
[root@localhost data]# ls /opt/
student.sql  mysql-5.7.17  rh

使用mysqladmin命令刷新日志文件

[root@localhost data]# mysqladmin -uroot -p flush-logs
Enter password: 
[root@localhost data]# ls
auto.cnf        ibdata1      ib_logfile1  mysql             mysql-bin.000002  mysql-bin.index     student
ib_buffer_pool  ib_logfile0  ibtmp1       mysql-bin.000001  mysql-bin.000003  performance_schema  sys

我們之后對(duì)數(shù)據(jù)的操作會(huì)以編碼形式存放到mysql-bin.000003文件中

2）插入新的數(shù)據(jù)

? 先了解一下當(dāng)前表的結(jié)構(gòu)內(nèi)容

mysql> use student;
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> select * from info;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | zhsan | 85.00 |
|  2 | lisi  | 78.00 |
+----+-------+-------+
2 rows in set (0.00 sec)

mysql> desc info;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(3)       | NO   | PRI | NULL    | auto_increment |
| name  | varchar(6)   | YES  |     | NULL    |                |
| score | decimal(5,2) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

插入新的數(shù)據(jù)

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

3）誤刪數(shù)據(jù)

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

4）再次插入數(shù)據(jù)

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

mysql> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  2 | lisi   | 78.00 |
|  3 | wangwu | 88.00 |
|  4 | sswu   | 98.00 |
+----+--------+-------+
3 rows in set (0.00 sec)

5）首先我們來(lái)查看一下二進(jìn)制日志的相關(guān)內(nèi)容

mysqlbinlog --no-defaults mysql-bin.000002

我們所操作的SQL語(yǔ)句就在該文件的BINLOG下,舉例：

# at 350
#200108 19:11:26 server id 1  end_log_pos 400 CRC32 0x39a71a8c  Write_rows: table id 219 flags: STMT_END_F

BINLOG '
3rgVXhMBAAAAOAAAAF4BAAAAANsAAAAAAAEAB3N0dWRlbnQABGluZm8AAwMP9gQSAAUCBvwGAEA=
3rgVXh5BAAAAMgAAAJABAAAAANsAAAAAAAEAAgAD//gDAAAABndhbmd3dYBYAIwapzk=
'/*!*/;

但是這樣的顯示并不友好，我們可以使用如下的命令來(lái)進(jìn)行查看

[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000003 /opt/1.txt
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200108 19:07:19 server id 1  end_log_pos 123 CRC32 0x64c66a17  Start: binlog v 4, server v 5.7.17-log created 200108 19:07:19
# at 123
#200108 19:07:19 server id 1  end_log_pos 154 CRC32 0xb0f35ef5  Previous-GTIDs
# [empty]
# at 154
#200108 19:11:26 server id 1  end_log_pos 219 CRC32 0x93232076  Anonymous_GTID  last_committed=0        sequence_number=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#200108 19:11:26 server id 1  end_log_pos 294 CRC32 0x9f329e2e  Query   thread_id=8     exec_time=0     error_code=0
SET TIMESTAMP=1578481886/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1437073414/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 294
#200108 19:11:26 server id 1  end_log_pos 350 CRC32 0x400006fc  Table_map: `student`.`info` mapped to number 219
# at 350
#200108 19:11:26 server id 1  end_log_pos 400 CRC32 0x39a71a8c  Write_rows: table id 219 flags: STMT_END_F
### INSERT INTO `student`.`info`
### SET
###   @1=3
###   @2='wangwu'
###   @3=88.00
# at 400
#200108 19:11:26 server id 1  end_log_pos 431 CRC32 0xdf332cf6  Xid = 53
COMMIT/*!*/;
# at 431
#200108 19:11:54 server id 1  end_log_pos 496 CRC32 0xe47ccb34  Anonymous_GTID  last_committed=1        sequence_number=2
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 496
#200108 19:11:54 server id 1  end_log_pos 571 CRC32 0xd8019486  Query   thread_id=8     exec_time=0     error_code=0
SET TIMESTAMP=1578481914/*!*/;
BEGIN
/*!*/;
# at 571
#200108 19:11:54 server id 1  end_log_pos 627 CRC32 0xf435652f  Table_map: `student`.`info` mapped to number 219
# at 627
#200108 19:11:54 server id 1  end_log_pos 676 CRC32 0xa514f9cc  Delete_rows: table id 219 flags: STMT_END_F
### DELETE FROM `student`.`info`
### WHERE
###   @1=1
###   @2='zhsan'
###   @3=85.00
# at 676
#200108 19:11:54 server id 1  end_log_pos 707 CRC32 0x97f66430  Xid = 54
COMMIT/*!*/;
# at 707
#200108 19:12:04 server id 1  end_log_pos 772 CRC32 0x4a6ce3e0  Anonymous_GTID  last_committed=2        sequence_number=3
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 772
#200108 19:12:04 server id 1  end_log_pos 847 CRC32 0xe4524691  Query   thread_id=8     exec_time=0     error_code=0
SET TIMESTAMP=1578481924/*!*/;
BEGIN
/*!*/;
# at 847
#200108 19:12:04 server id 1  end_log_pos 903 CRC32 0x868e76af  Table_map: `student`.`info` mapped to number 219
# at 903
#200108 19:12:04 server id 1  end_log_pos 951 CRC32 0x5f906c6d  Write_rows: table id 219 flags: STMT_END_F
### INSERT INTO `student`.`info`
### SET
###   @1=4
###   @2='sswu'
###   @3=98.00
# at 951
#200108 19:12:04 server id 1  end_log_pos 982 CRC32 0x2315c471  Xid = 55
COMMIT/*!*/;
# at 982
#200108 19:20:26 server id 1  end_log_pos 1047 CRC32 0x22e3dd74         Anonymous_GTID  last_committed=3        sequence_number=4
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1047
#200108 19:20:26 server id 1  end_log_pos 1170 CRC32 0x0835dab9         Query   thread_id=9     exec_time=0     error_code=0
use `student`/*!*/;
SET TIMESTAMP=1578482426/*!*/;
DROP TABLE "info" /* generated by server */
/*!*/;
# at 1170
#200108 19:20:43 server id 1  end_log_pos 1235 CRC32 0xceae6cad         Anonymous_GTID  last_committed=4        sequence_number=5
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1235
#200108 19:20:43 server id 1  end_log_pos 1368 CRC32 0x77138dd6         Query   thread_id=9     exec_time=0     error_code=0
SET TIMESTAMP=1578482443/*!*/;
SET @@session.foreign_key_checks=0, @@session.unique_checks=0/*!*/;
SET @@session.sql_mode=524288/*!*/;
DROP TABLE IF EXISTS `info` /* generated by server */
/*!*/;
# at 1368
#200108 19:20:43 server id 1  end_log_pos 1433 CRC32 0xa62f4bea         Anonymous_GTID  last_committed=5        sequence_number=6
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1433
#200108 19:20:43 server id 1  end_log_pos 1719 CRC32 0x26b3c872         Query   thread_id=9     exec_time=0     error_code=0
SET TIMESTAMP=1578482443/*!*/;
CREATE TABLE `info` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `name` varchar(6) DEFAULT NULL,
  `score` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
/*!*/;
# at 1719
#200108 19:20:43 server id 1  end_log_pos 1784 CRC32 0x720c8f38         Anonymous_GTID  last_committed=6        sequence_number=7
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1784
#200108 19:20:43 server id 1  end_log_pos 1907 CRC32 0x4be303fe         Query   thread_id=9     exec_time=0     error_code=0
SET TIMESTAMP=1578482443/*!*/;
/*!40000 ALTER TABLE `info` DISABLE KEYS */
/*!*/;
# at 1907
#200108 19:20:43 server id 1  end_log_pos 1972 CRC32 0xc086d7f8         Anonymous_GTID  last_committed=7        sequence_number=8
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1972
#200108 19:20:43 server id 1  end_log_pos 2047 CRC32 0x8c907d67         Query   thread_id=9     exec_time=0     error_code=0
SET TIMESTAMP=1578482443/*!*/;
BEGIN
/*!*/;
# at 2047
#200108 19:20:43 server id 1  end_log_pos 2103 CRC32 0x9f5b0ea3         Table_map: `student`.`info` mapped to number 220
# at 2103
#200108 19:20:43 server id 1  end_log_pos 2165 CRC32 0x6312013c         Write_rows: table id 220 flags: STMT_END_F
### INSERT INTO `student`.`info`
### SET
###   @1=1
###   @2='zhsan'
###   @3=85.00
### INSERT INTO `student`.`info`
### SET
###   @1=2
###   @2='lisi'
###   @3=78.00
# at 2165
#200108 19:20:43 server id 1  end_log_pos 2196 CRC32 0xf91d3d32         Xid = 82
COMMIT/*!*/;
# at 2196
#200108 19:20:43 server id 1  end_log_pos 2261 CRC32 0x3038bc9d         Anonymous_GTID  last_committed=8        sequence_number=9
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2261
#200108 19:20:43 server id 1  end_log_pos 2383 CRC32 0xab22187f         Query   thread_id=9     exec_time=0     error_code=0
SET TIMESTAMP=1578482443/*!*/;
/*!40000 ALTER TABLE `info` ENABLE KEYS */
/*!*/;
# at 2383
#200108 19:22:07 server id 1  end_log_pos 2430 CRC32 0xd1601a9d         Rotate to mysql-bin.000004  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@localhost data]# mysqlbinlog --no-defaults --stop-datetime='2020-01-08 19:11:54' /usr/local/mysql/data/mysql-bin.000003 | mysql -u root -p
Enter password: 
[root@localhost data]# mysqlbinlog --no-defaults --start-datetime='2020-01-08 19:12:04' /usr/local/mysql/data/mysql-bin.000003 | mysql -u root -p 
Enter password: 
[root@localhost data]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.17-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use student;
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> select * from info;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | zhsan | 85.00 |
|  2 | lisi  | 78.00 |
+----+-------+-------+
2 rows in set (0.00 sec)

mysql> exit
Bye
[root@localhost data]# mysqlbinlog --no-defaults --stop-datetime='2020-01-08 19:11:54' /usr/local/mysql/data/mysql-bin.000003 | mysql -u root -pEnter password: 
[root@localhost data]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.17-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use student;
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> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | zhsan  | 85.00 |
|  2 | lisi   | 78.00 |
|  3 | wangwu | 88.00 |
+----+--------+-------+
3 rows in set (0.01 sec)

mysql> exit
Bye
[root@localhost data]# cat /opt/1.txt 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200108 19:07:19 server id 1  end_log_pos 123 CRC32 0x64c66a17  Start: binlog v 4, server v 5.7.17-log created 200108 19:07:19
# at 123
#200108 19:07:19 server id 1  end_log_pos 154 CRC32 0xb0f35ef5  Previous-GTIDs
# [empty]
# at 154
#200108 19:11:26 server id 1  end_log_pos 219 CRC32 0x93232076  Anonymous_GTID  last_committed=0        sequence_number=1
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#200108 19:11:26 server id 1  end_log_pos 294 CRC32 0x9f329e2e  Query   thread_id=8     exec_time=0     error_code=0
SET TIMESTAMP=1578481886/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1437073414/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 294
#200108 19:11:26 server id 1  end_log_pos 350 CRC32 0x400006fc  Table_map: `student`.`info` mapped to number 219
# at 350
#200108 19:11:26 server id 1  end_log_pos 400 CRC32 0x39a71a8c  Write_rows: table id 219 flags: STMT_END_F
### INSERT INTO `student`.`info`
### SET
###   @1=3
###   @2='wangwu'
###   @3=88.00
# at 400
#200108 19:11:26 server id 1  end_log_pos 431 CRC32 0xdf332cf6  Xid = 53
COMMIT/*!*/;
# at 431
#200108 19:11:54 server id 1  end_log_pos 496 CRC32 0xe47ccb34  Anonymous_GTID  last_committed=1        sequence_number=2
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 496
#200108 19:11:54 server id 1  end_log_pos 571 CRC32 0xd8019486  Query   thread_id=8     exec_time=0     error_code=0
SET TIMESTAMP=1578481914/*!*/;
BEGIN
/*!*/;
# at 571
#200108 19:11:54 server id 1  end_log_pos 627 CRC32 0xf435652f  Table_map: `student`.`info` mapped to number 219
# at 627
#200108 19:11:54 server id 1  end_log_pos 676 CRC32 0xa514f9cc  Delete_rows: table id 219 flags: STMT_END_F
### DELETE FROM `student`.`info`
### WHERE
###   @1=1
###   @2='zhsan'
###   @3=85.00
# at 676
#200108 19:11:54 server id 1  end_log_pos 707 CRC32 0x97f66430  Xid = 54
COMMIT/*!*/;
# at 707
#200108 19:12:04 server id 1  end_log_pos 772 CRC32 0x4a6ce3e0  Anonymous_GTID  last_committed=2        sequence_number=3
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 772
#200108 19:12:04 server id 1  end_log_pos 847 CRC32 0xe4524691  Query   thread_id=8     exec_time=0     error_code=0
SET TIMESTAMP=1578481924/*!*/;
BEGIN
/*!*/;
# at 847
#200108 19:12:04 server id 1  end_log_pos 903 CRC32 0x868e76af  Table_map: `student`.`info` mapped to number 219
# at 903
#200108 19:12:04 server id 1  end_log_pos 951 CRC32 0x5f906c6d  Write_rows: table id 219 flags: STMT_END_F
### INSERT INTO `student`.`info`
### SET
###   @1=4
###   @2='sswu'
###   @3=98.00
# at 951
#200108 19:12:04 server id 1  end_log_pos 982 CRC32 0x2315c471  Xid = 55
COMMIT/*!*/;
# at 982
#200108 19:20:26 server id 1  end_log_pos 1047 CRC32 0x22e3dd74         Anonymous_GTID  last_committed=3        sequence_number=4
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1047
#200108 19:20:26 server id 1  end_log_pos 1170 CRC32 0x0835dab9         Query   thread_id=9     exec_time=0     error_code=0
use `student`/*!*/;
SET TIMESTAMP=1578482426/*!*/;
DROP TABLE "info" /* generated by server */
/*!*/;
# at 1170
#200108 19:20:43 server id 1  end_log_pos 1235 CRC32 0xceae6cad         Anonymous_GTID  last_committed=4        sequence_number=5
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1235
#200108 19:20:43 server id 1  end_log_pos 1368 CRC32 0x77138dd6         Query   thread_id=9     exec_time=0     error_code=0
SET TIMESTAMP=1578482443/*!*/;
SET @@session.foreign_key_checks=0, @@session.unique_checks=0/*!*/;
SET @@session.sql_mode=524288/*!*/;
DROP TABLE IF EXISTS `info` /* generated by server */
/*!*/;
# at 1368
#200108 19:20:43 server id 1  end_log_pos 1433 CRC32 0xa62f4bea         Anonymous_GTID  last_committed=5        sequence_number=6
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1433
#200108 19:20:43 server id 1  end_log_pos 1719 CRC32 0x26b3c872         Query   thread_id=9     exec_time=0     error_code=0
SET TIMESTAMP=1578482443/*!*/;
CREATE TABLE `info` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `name` varchar(6) DEFAULT NULL,
  `score` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
/*!*/;
# at 1719
#200108 19:20:43 server id 1  end_log_pos 1784 CRC32 0x720c8f38         Anonymous_GTID  last_committed=6        sequence_number=7
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1784
#200108 19:20:43 server id 1  end_log_pos 1907 CRC32 0x4be303fe         Query   thread_id=9     exec_time=0     error_code=0
SET TIMESTAMP=1578482443/*!*/;
/*!40000 ALTER TABLE `info` DISABLE KEYS */
/*!*/;
# at 1907
#200108 19:20:43 server id 1  end_log_pos 1972 CRC32 0xc086d7f8         Anonymous_GTID  last_committed=7        sequence_number=8
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1972
#200108 19:20:43 server id 1  end_log_pos 2047 CRC32 0x8c907d67         Query   thread_id=9     exec_time=0     error_code=0
SET TIMESTAMP=1578482443/*!*/;
BEGIN
/*!*/;
# at 2047
#200108 19:20:43 server id 1  end_log_pos 2103 CRC32 0x9f5b0ea3         Table_map: `student`.`info` mapped to number 220
# at 2103
#200108 19:20:43 server id 1  end_log_pos 2165 CRC32 0x6312013c         Write_rows: table id 220 flags: STMT_END_F
### INSERT INTO `student`.`info`
### SET
###   @1=1
###   @2='zhsan'
###   @3=85.00
### INSERT INTO `student`.`info`
### SET
###   @1=2
###   @2='lisi'
###   @3=78.00
# at 2165
#200108 19:20:43 server id 1  end_log_pos 2196 CRC32 0xf91d3d32         Xid = 82
COMMIT/*!*/;
# at 2196
#200108 19:20:43 server id 1  end_log_pos 2261 CRC32 0x3038bc9d         Anonymous_GTID  last_committed=8        sequence_number=9
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 2261
#200108 19:20:43 server id 1  end_log_pos 2383 CRC32 0xab22187f         Query   thread_id=9     exec_time=0     error_code=0
SET TIMESTAMP=1578482443/*!*/;
/*!40000 ALTER TABLE `info` ENABLE KEYS */
/*!*/;
# at 2383
#200108 19:22:07 server id 1  end_log_pos 2430 CRC32 0xd1601a9d         Rotate to mysql-bin.000004  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

6）增量備份恢復(fù)數(shù)據(jù)

首先需要先進(jìn)行一次完全備份的恢復(fù)操作

mysql> use student;
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;
Query OK, 0 rows affected (0.00 sec)

mysql> source /opt/student.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | zhsan | 85.00 |
|  2 | lisi  | 78.00 |
+----+-------+-------+
2 rows in set (0.00 sec)

1、根據(jù)時(shí)間節(jié)點(diǎn)進(jìn)行數(shù)據(jù)恢復(fù)

設(shè)置--stop-datetime 2020-01-8 19:11:54

--start-datetime=2020-01-08 19:12:04

由于我的刷新日志包含了完全備份的一次恢復(fù)操作所以還需要設(shè)置一個(gè)結(jié)束的時(shí)間節(jié)點(diǎn)與start結(jié)合使用：

--stop-datetime=2020-01-08 19:20:26

[root@localhost data]# mysqlbinlog --no-defaults --stop-datetime='2020-01-08 19:11:54' /usr/local/mysql/data/mysql-bin.000003 | mysql -u root -pEnter password: 
[root@localhost data]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.17-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use student;
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> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | zhsan  | 85.00 |
|  2 | lisi   | 78.00 |
|  3 | wangwu | 88.00 |
+----+--------+-------+
3 rows in set (0.01 sec)

恢復(fù)數(shù)據(jù)（包括誤刪的id為1的操作）

[root@localhost data]# mysqlbinlog --no-defaults --start-datetime='2020-01-08 19:12:04' --stop-datetime='2020-01-08 19:20:26' /usr/local/mysql/data/mysql-bin.000003 | mysql -u root -p
Enter password: 
[root@localhost data]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.7.17-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use student;
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> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | zhsan  | 85.00 |
|  2 | lisi   | 78.00 |
|  3 | wangwu | 88.00 |
|  4 | sswu   | 98.00 |
+----+--------+-------+
4 rows in set (0.00 sec)

2、根據(jù)斷點(diǎn)位置進(jìn)行數(shù)據(jù)恢復(fù)

斷點(diǎn)位置：就是上面日志文件1.txt中的at之后的數(shù)字

1）我們根據(jù)上面的操作先模擬刪除數(shù)據(jù)表，進(jìn)行一次完全備份恢復(fù)操作

mysql> drop table info;
Query OK, 0 rows affected (0.00 sec)

mysql> source /opt/student.sql;
Query OK, 0 rows affected (0.00 sec)

...#省略部分內(nèi)容
mysql> select * from info;
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | zhsan | 85.00 |
|  2 | lisi  | 78.00 |
+----+-------+-------+
2 rows in set (0.00 sec)

2）查看斷點(diǎn)位置并且記錄

斷點(diǎn)1：at 571

斷點(diǎn)2：at 676

斷點(diǎn)3：at 982

先進(jìn)行一次驗(yàn)證操作：

[root@localhost data]# mysqlbinlog --no-defaults --stop-position='571' /usr/local/mysql/data/mysql-bin.000003 | mysql -u root -p
Enter password: 
[root@localhost data]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.7.17-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use student;
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> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | zhsan  | 85.00 |
|  2 | lisi   | 78.00 |
|  3 | wangwu | 88.00 |
+----+--------+-------+
3 rows in set (0.00 sec)

mysql> exit

進(jìn)行恢復(fù)：

[root@localhost data]# mysqlbinlog --no-defaults --start-position='676' --stop-position='982' /usr/local/mysql/data/mysql-bin.000003 | mysql -u root -p
Enter password: 
[root@localhost data]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.7.17-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use student;
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> select * from info;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | zhsan  | 85.00 |
|  2 | lisi   | 78.00 |
|  3 | wangwu | 88.00 |
|  4 | sswu   | 98.00 |
+----+--------+-------+
4 rows in set (0.00 sec)

四、總結(jié)

? 本文主要介紹了增量備份的概念以及如何實(shí)現(xiàn)增量數(shù)據(jù)備份恢復(fù)的方法。熟悉使用mysql、mysqldump、mysqladmin等命令實(shí)現(xiàn)數(shù)據(jù)恢復(fù)的功能。

本文標(biāo)題：MySQL增量備份與恢復(fù)
文章分享：http://jinyejixie.com/article38/jjpcpp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站導(dǎo)航、網(wǎng)站設(shè)計(jì)公司、面包屑導(dǎo)航、企業(yè)建站、靜態(tài)網(wǎng)站、做網(wǎng)站

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容

成人午夜视频全免费观看高清-秋霞福利视频一区二区三区-国产精品久久久久电影小说-亚洲不卡区三一区三区一区

MySQL增量備份與恢復(fù)