# MySQL增量备份与恢复

# MySQL增量备份与恢复

增量备份概念：

备份自上一次备份之后增加或改变的文件或内容

增量备份的特点：

优点：没有重复数据，备份量不大，时间短。

缺点：需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且对所有增量备份进行逐个反推恢复，操作较为繁琐。

mysql没有直接提供的增量备份方法，但是可以通过MySQL的二进制日志（binary logs）间接实现增量备份。二进制日志对备份的意义如下：

（1）二进制日志保存了所有更新或者可能更新数据库的操作。

（2）二进制日志在启动MySQL服务器后开始记录，并在文件到达max_binlog_size所设置的大小或者接收到flush-logs命令后重新创建新的日志文件。

（3） 只需要定时执行fulsh-logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份。

增量备份

1.要进行MySQL增量备份，首先要开启二进制日志功能。开启二进制日志方法：

（1）在MySQL配置文件的[mysqld]项中加入log-binfilepath项，如log-bin=mysql-bin，然后重启mysqld服务。

[root@localhost ~]# vim /etc/my.cnf [mysqld] user = mysql basedir = /usr/local/mysql datadir = /usr/local/mysql/data port = 3306 character_set_server=utf8 log-bin=mysql-bin //添加项// pid-file = /usr/local/mysql/mysqld.pid socket = /usr/local/mysql/mysql.sock server-id = 1 [root@localhost ~]# systemctl restart mysqld.service //重启MySQL服务//

（2）使用mysqladmin的选项flush-logs生成新的二进制文件mysql-bin.000001，这样在插入新的数据后，新的二进制文件对应的就是数据库的变化的内容。

[root@localhost data]# mysqladmin -uroot -p flush-logs //增量备份// [root@localhost data]# ls auto.cnf ibdata1 ib_logfile1 kgc mysql-bin.000001 performance_schema ib_buffer_pool ib_logfile0 ibtmp1 mysql mysql-bin.index sys

增量恢复

当数据发生错误时，应根据实际 情况选择使用完全备份恢复，还是增量恢复。增量恢复的场景是：

（1）人为的SQL语句破坏了数据库。

（2）在进行下一次全备之前发生系统故障导致数据库数据丢失。

（3）在主从架构中，主库数据发生了故障。

1.丢失了完全备份之后更改的数据恢复步骤

（1）首先进入MySQL数据库中，创建school库和info表。

[root@localhost data]# mysql -uroot -p //登录mysql数据库// mysql> create database school; //创建新的数据库// Query OK, 1 row affected (0.01 sec) mysql> show databases; //查看数据库// +--------------------+ | Database | +--------------------+ | information_schema | | kgc | | mysql | | performance_schema | | school | | sys | +--------------------+ 6 rows in set (0.00 sec) mysql> use school; //进入数据库//

（2）向数据库插入1条数据，退出MySQL数据库。使用mysqldump完全备份school库。

mysql> create table info(name varchar(10),score decimal(5,2)); //创建info表// mysqlmysql> show tables; //查看表信息// +------------------+ | Tables_in_school | +------------------+ | info | +------------------+ 1 row in set (0.00 sec) mysql> insert into info(name,score) values ('tom',88); //插入数据// mysql> select * from info; //查看数据记录// +------+-------+ | name | score | +------+-------+ | tom | 88.00 | +------+-------+ 1 row in set (0.00 sec) [root@localhost data]# mysqldump -uroot -p school > /opt/school.sql //完全备份school库// [root@localhost data]# ls /opt/ school.sql

（3）使用flush-log生成新的二进制文件，用以保存之后的数据库操作语句。

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

（4）再次向数据库插入1条数据，使用flush-log生成新的二进制文件，用以保存之后的数据库操作语句。

mysql> insert into info(name,score) values ('abc01',77); //插入数据// Query OK, 1 row affected (0.03 sec) mysql> select * from info; +-------+-------+ | name | score | +-------+-------+ | tom | 88.00 | | abc01 | 77.00 | +-------+-------+ 2 rows in set (0.00 sec) [root@localhost data]# mysqladmin -uroot -p flush-logs //增量备份// [root@localhost data]# ls auto.cnf ibdata1 ib_logfile1 kgc mysql-bin.000001 mysql-bin.000003 performance_schema sys ib_buffer_pool ib_logfile0 ibtmp1 mysql mysql-bin.000002 mysql-bin.index

（6）使用delete删除插入的1条数据，也就是假设完全备份后的数据丢失了。

mysql> delete from info where name='abc01'; //删除数据// mysql> select * from info; //查看数据记录// +------+-------+ | name | score | +------+-------+ | tom | 88.00 | +------+-------+

（7）使用二进制文件进行增量恢复。

[root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000002 | mysql -u root -p //增量恢复// [root@localhost data]# mysql -uroot -p //登录MySQL数据库// mysql> use school; //进入school库// mysql> select * from info; //查看数据记录// +-------+-------+ | name | score | +-------+-------+ | tom | 88.00 | | abc01 | 77.00 | +-------+-------+

2.基于时间点与位置的恢复

利用二进制日志可实现基于时间点与位置的恢复，例如由于误操作删除了一张表，这时完全恢复是没有用的，因为日志里面还存在误操作的语句，我们需要的是恢复到误操作前的状态，然后跳过误操作的语句，再恢复后面操作的语句。

假设需要往数据库中插入两条数据，但由于误操作，两条插入语句中间删除了一条数据，而这条数据是不应该删除的。

mysql> insert into info(name,score) values ('test01',77); //插入数据test01// mysql> delete from info where name='tom'; //误删除tom数据// mysql> insert into info(name,score) values ('test02',77); //插入数据test02// mysql> select * from info; //查看数据记录// +--------+-------+ | name | score | +--------+-------+ | jack | 88.00 | | test01 | 77.00 | | test02 | 77.00 | +--------+-------+ 3 rows in set (0.00 sec)

（1）进行增量备份，编号为000002的二进制文件中保存了正确的插入语句，同时也保存了不应该执行的删除语句。

[root@localhost data]# mysqladmin -uroot -p flush-logs //增量备份// [root@localhost data]# ls auto.cnf ib_logfile1 mysql-bin.000001 performance_schema ib_buffer_pool ibtmp1 mysql-bin.000002 school ibdata1 kgc mysql-bin.000003 sys ib_logfile0 mysql mysql-bin.index

（2）查看编号为000002的二进制文件，查找误操作的位置和正确操作的位置，便于进行恢复。

[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 //查看二进制文件 # at 617 //位置标记// #180704 17:12:01 server id 1 end_log_pos 660 CRC32 0xe2f72a72 Delete_rows: table id 118 flags: STMT_END_F //误操作的起始时间// ### DELETE FROM `school`.`info` //误删的数据// ### WHERE ### @1='tom' ### @2=88.00 # at 884 //位置标记// #180704 17:12:08 server id 1 end_log_pos 930 CRC32 0xd17eb525 Write_rows: table id 118 flags: STMT_END_F //操作正确的起始时间// ### INSERT INTO `school`.`info` //插入的数据// ### SET ### @1='test02' ### @2=77.00

（3）登录Mysql数据库把原有误删数据的info表删除，使用mysql恢复school库。

mysql> drop table info; //删除info表// mysql> show tables; Empty set (0.00 sec) [root@localhost opt]# mysql -u root -p school < /opt/school.sql //恢复school库// [root@localhost opt]# mysql -uroot -p mysql> show tables; +------------------+ | Tables_in_school | +------------------+ | info | +------------------+ 1 row in set (0.00 sec) mysql> select * from info; +------+-------+ | name | score | +------+-------+ | tom | 88.00 | //数据tom恢复// | jack | 88.00 | +------+-------+ 2 rows in set (0.00 sec)

（4）基于时间点的恢复

使用mysqlbinlong加上--stop-datetime选项，表示在哪个时间点结束，后面误操作语句不执行，--start-datetime选项表示执行后面的语句，结合使用它们就可以跳过误操作的语句，完成恢复工作。需要注意的是，二进制文件中保存的日期格式需要调整为用“-”分割。

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

（5）基于位置的恢复

就是使用基于时间点的恢复，可能会出现在一个时间点里既同时存在正确的操作又存在错误的操作，基于位置是一种更为准确的恢复方式。

①删除test01和test02数据

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

②打开编号000002的二进制文件，找到误操作的位置。

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

③第一条插入语句后面的位置是563，--stop-position设置为563，表示执行第一条插入语句后结束。第二条插入语句后面的位置是660，--start-position设置为660，表示执行第二条插入语句，跳过了误操作的删除语句，达到了恢复数据的目的。

[root@localhost opt]# mysqlbinlog --no-defaults --stop-position='563' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p [root@localhost opt]# mysqlbinlog --no-defaults --start-position='660' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p [root@localhost opt]# mysql -uroot -p //登录mysql数据库// mysql> use school; //进入school库// mysql> select * from info; //查看数据记录// +--------+-------+ | name | score | +--------+-------+ | tom | 88.00 | | jack | 88.00 | | test01 | 77.00 | | test02 | 77.00 | +--------+-------+ 4 rows in set (0.00 sec)

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。