溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

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

發(fā)布時間：2020-02-24 16:44:04 來源：網(wǎng)絡(luò) 閱讀：264 作者：wx5d8a17c45cb5b 欄目：MySQL數(shù)據(jù)庫

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

[TOC]

一、前言

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

二、再談MySQL增量備份

2.1問題引出與解決

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

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

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

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

2.2進一步理解增量備份及其優(yōu)缺點，考慮其適用場景

? 個人比較喜歡使用身邊生活中的例子來理解各種知識點，那么就舉一個例子來加深大家對“增量備份”的理解。

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

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

2.2.1增量備份的特點

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

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

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

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

2.2.2增量備份的優(yōu)點
  1. 增量備份沒有冗余數(shù)據(jù)，備份量少；
  2. 時間短，效率高，成本低；
2.2.3增量備份的缺點

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

2.3適用場景

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

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

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

? 修改配置文件，當(dāng)初我們在手工編譯安裝MySQL時，修改過/etc/my.cnf文件，開啟二進制日志功能也是需要更改配置文件，并重啟服務(wù)。

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

log-bin=mysql-bin

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

[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]#

? 因為差異備份和增量備份都是基于完全備份的基礎(chǔ)上執(zhí)行操作的，因此我們需要先進行一次完全備份操作，并模擬增加數(shù)據(jù)、誤操作刪除了數(shù)據(jù)的操作，最后依賴上述類型的二進制日志文件進行必要的數(shù)據(jù)恢復(fù)。

? 具體的操作過程如下：

1）首先是進行完全備份并查看文件

[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

我們之后對數(shù)據(jù)的操作會以編碼形式存放到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）首先我們來查看一下二進制日志的相關(guān)內(nèi)容

mysqlbinlog --no-defaults mysql-bin.000002

我們所操作的SQL語句就在該文件的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=
'/*!*/;

但是這樣的顯示并不友好，我們可以使用如下的命令來進行查看

[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ù)

首先需要先進行一次完全備份的恢復(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ù)時間節(jié)點進行數(shù)據(jù)恢復(fù)

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

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

由于我的刷新日志包含了完全備份的一次恢復(fù)操作所以還需要設(shè)置一個結(jié)束的時間節(jié)點與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ù)斷點位置進行數(shù)據(jù)恢復(fù)

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

1）我們根據(jù)上面的操作先模擬刪除數(shù)據(jù)表，進行一次完全備份恢復(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）查看斷點位置并且記錄

斷點1：at 571

斷點2：at 676

斷點3：at 982

先進行一次驗證操作：

[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

進行恢復(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é)

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

向AI問一下細節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手