99偷拍视频精品区一区二,口述久久久久久久久久久久,国产精品夫妇激情啪发布,成人永久免费网站在线观看,国产精品高清免费在线,青青草在线观看视频观看,久久久久久国产一区,天天婷婷久久18禁,日韩动漫av在线播放直播

MySQL增量備份與恢復

MySQL增量備份與恢復

[TOC]

創新互聯專注為客戶提供全方位的互聯網綜合服務，包含不限于成都網站設計、做網站、新晃網絡推廣、微信小程序、新晃網絡營銷、新晃企業策劃、新晃品牌公關、搜索引擎seo、人物專訪、企業宣傳片、企業代運營等，從售前售中售后，我們都將竭誠為您服務，您的肯定，是我們最大的嘉獎；創新互聯為所有大學生創業者提供新晃建站搭建服務，24小時服務熱線：028-86922220，官方網址：www.yijiale78.com

一、前言

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

二、再談MySQL增量備份

2.1問題引出與解決

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

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

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

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

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

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

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

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

2.2.1增量備份的特點

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

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

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

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

2.2.2增量備份的優點
  1. 增量備份沒有冗余數據，備份量少；
  2. 時間短，效率高，成本低；
2.2.3增量備份的缺點

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

2.3適用場景

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

三、增量備份與模擬數據恢復操作

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

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

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

log-bin=mysql-bin

? 我們重啟服務之后就可以才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]#

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

? 具體的操作過程如下：

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

我們之后對數據的操作會以編碼形式存放到mysql-bin.000003文件中

2）插入新的數據

? 先了解一下當前表的結構內容

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)

插入新的數據

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

3）誤刪數據

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

4）再次插入數據

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）首先我們來查看一下二進制日志的相關內容

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）增量備份恢復數據

首先需要先進行一次完全備份的恢復操作

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、根據時間節點進行數據恢復

設置--stop-datetime 2020-01-8 19:11:54

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

由于我的刷新日志包含了完全備份的一次恢復操作所以還需要設置一個結束的時間節點與start結合使用：

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

恢復數據（包括誤刪的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、根據斷點位置進行數據恢復

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

1）我們根據上面的操作先模擬刪除數據表，進行一次完全備份恢復操作

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)

...#省略部分內容
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

進行恢復：

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

四、總結

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

本文標題：MySQL增量備份與恢復
文章分享：http://www.yijiale78.com/article38/jjpcpp.html

成都網站建設公司_創新互聯，為您提供網站導航、網站設計公司、面包屑導航、企業建站、靜態網站、做網站

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯

商城網站建設