温馨提示×

温馨提示×

您好，登录后才能下订单哦！

密码登录×
登录注册×
获取短信验证码
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》
登 录 注册有礼
云服务器 香港服务器 高防服务器
最新更新 网站标签 地图导航
产品

MySQL增量备份与恢复

发布时间：2020-02-24 16:44:04 来源：网络 阅读：247 作者：wx5d8a17c45cb5b 栏目：MySQL数据库

MySQL增量备份与恢复

[TOC]

一、前言

​ 上篇文章（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等命令实现数据恢复的功能。

向AI问一下细节
推荐阅读：
  1. MySQL全量、增量备份与恢复
  2. MySQL怎么全量、增量备份与恢复数据

免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。

增量备份 mysqladmin mysqlbinlog

猜你喜欢

最新资讯
相关推荐

相关标签

增量备份与恢复 mysql5.7 nosql sqlpro sparksql -mysql mysql8.0.16 sqlite sqlyog spark sql mysql主从复制 mysql存储引擎 sqlserver_row_number mysql备份方式 sql 优化 msql mysql root密码 sql脚本 mysql-mmm pl/sql
AI
助
手
产品服务
地区划分
专题活动
帮助支持
关于我们
售后咨询
7*24小时在线电话：400-100-2938
7*24小时在线 QQ：800811969
关注亿速云

亿速云公众号

手机网站二维码

Copyright © Yisu Cloud Ltd. All Rights Reserved. 2018 版权所有

广州亿速云计算有限公司粤ICP备17096448号-1 粤公网安备 44010402001142号增值电信业务经营许可证编号：B1-20181529