Mysql实现增量恢复的方法详解
实验介绍
增量恢复一般适用的场景：
1、人为的sql语句破坏了数据库
2、在进行下一次完全备份之前发生系统故障导致数据库数据丢失
3、在主从架构中，主库数据发生了故障
丢失完全备份之后更改的数据的恢复步骤
1、首先做一个完全备份，确保生成完全备份的sql文件。
mysql>select*fromyx;#完全备份前数据库 +----------+--------+ |name|score| +----------+--------+ |zhangsan|100.00| |lisi|90.00| |wangwu|80.00| |zhaoliu|99.00| +----------+--------+ 4rowsinset(0.00sec) [root@promotedata]#mysqldump-uroot-ptest>/opt/test.sql#对数据库完全备份
2、使用flush-logs生成新的二进制日志文件，用以保存之后的数据库操作语句。
[root@promotedata]#mysqladmin-uroot-pflush-logs #生成二进制文件
Enterpassword:
[root@promotedata]#ls
auto.cnf ibdata1 ib_logfile1 mysql mysql-bin.index sys
ib_buffer_pool ib_logfile0 ibtmp1 mysql-bin.000001 performance_schema test
3、在数据库中插入一条记录，再执行flush-logs操作，生成新的二进制增量备份文件。
mysql>insertintoyx(name,score)values('tom',87);
QueryOK,1rowaffected(0.00sec)
mysql>select*fromyx;
+----------+--------+
|name |score |
+----------+--------+
|zhangsan|100.00|
|lisi | 90.00|
|wangwu | 80.00|
|zhaoliu | 99.00|
|tom | 87.00|
+----------+--------+
5rowsinset(0.00sec)
[root@promotedata]#mysqladmin-uroot-pflush-logs #生成二进制文件
Enterpassword:
[root@promotedata]#ls
auto.cnf ibdata1 ib_logfile1 mysql mysql-bin.000002 performance_schema test
ib_buffer_pool ib_logfile0 ibtmp1 mysql-bin.000001 mysql-bin.index sys
4、用delete删除刚才插入的数据。模拟完全备份后数据丢失。
mysql>deletefromyxwherename='tom';
QueryOK,1rowaffected(0.00sec)
mysql>select*fromyx;
+----------+--------+
|name |score |
+----------+--------+
|zhangsan|100.00|
|lisi | 90.00|
|wangwu | 80.00|
|zhaoliu | 99.00|
+----------+--------+
4rowsinset(0.00sec)
5、使用二进制文件进行恢复操作
[root@promotedata]#mysqlbinlog--no-defaultsmysql-bin.000001|mysql-uroot-p
6、查看数据库内容，删除的数据有了。说明数据恢复成功。
mysql>select*fromyx;
+----------+--------+
|name |score |
+----------+--------+
|zhangsan|100.00|
|lisi | 90.00|
|wangwu | 80.00|
|zhaoliu | 99.00|
|tom | 87.00|
+----------+--------+
5rowsinset(0.00sec）
完全备份之后丢失所有数据的恢复步骤
1、使用drop删除表yx，模拟数据完全丢失
mysql>droptableyx;
QueryOK,0rowsaffected(0.01sec)
mysql>showtables;
Emptyset(0.00sec)
2、先使用mysql命令进行完全备份恢复操作。
[root@promotedata]#mysql-uroot-ptest mysql>usetest;
Databasechanged
mysql>select*fromyx;
+----------+--------+
|name |score |
+----------+--------+
|zhangsan|100.00|
|lisi | 90.00|
|wangwu | 80.00|
|zhaoliu | 99.00|
+----------+--------+
4rowsinset(0.00sec)
3、使用二进制文件进行增量备份操作。
[root@promotedata]#mysqlbinlog--no-defaultsmysql-bin.000001|mysql-uroot-p
mysql>select*fromyx;
+----------+--------+
|name |score |
+----------+--------+
|zhangsan|100.00|
|lisi | 90.00|
|wangwu | 80.00|
|zhaoliu | 99.00|
|tom | 87.00|
+----------+--------+
5rowsinset(0.00sec)
基于时间点与位置的恢复
利用二进制日志实现局域时间点与位置的恢复，假如需要往数据库中插入两条数据，但是由于误操作，两条插入语句中间删除一条数据，而这条数据不应该删除，这时候，需要基于时间点与位置进行恢复。
–start-datetime=datetime
从二进制日志中第1个日期时间等于或晚于datetime参量的事件开始读。
–stop-datetime=datetime
从二进制日志中第1个日期时间等于或晚于datetime参量的事件起停止读。
–start-position=N
从二进制日志中第1个位置等于N参量时的事件开始读。
–stop-position=N
从二进制日志中第1个位置等于和大于N参量时的事件起停止读。
mysql>select*fromyx;
+----------+--------+
|name |score |
+----------+--------+
|zhangsan|100.00|
|lisi | 90.00|
|wangwu | 80.00|
|zhaoliu | 99.00|
+----------+--------+
5rowsinset(0.00sec)
mysql>insertintoyxvalues('test01',87);
QueryOK,1rowaffected(0.00sec)
mysql>deletefromyxwherename='zhangsan';
QueryOK,1rowaffected(0.00sec)
mysql>insertintoyxvalues('test02',99);
QueryOK,1rowaffected(0.17sec)
mysql>select*fromyx;
+---------+-------+
|name |score|
+---------+-------+
|lisi |90.00|
|wangwu |80.00|
|zhaoliu|99.00|
|test01 |87.00|
|test02 |99.00|
+---------+-------+
6rowsinset(0.00sec)
1、基于时间点的恢复。18-07-0321:56:04是错误语句节点，18-07-0321:56:11第二句正确语句节点
[root@promotedata]#mysqlbinlog--no-defaults--base64-output=decode-rowsmysql-bin.000003
#at298
#18070321:55:35serverid1 end_log_pos406CRC320x257c67ab Query thread_id=46 exec_time=0error_code=0
use`test`/*!*/;
SETTIMESTAMP=1530626135/*!*/;
insertintoyxvalues('test01',87)
/*!*/;
#at406
#18070321:55:35serverid1 end_log_pos437CRC320xdd7913a3 Xid=392
COMMIT/*!*/;
#at437
#18070321:56:04serverid1 end_log_pos502CRC320x0d09bd0b Anonymous_GTID last_committed=1 sequence_number=2
SET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;
#at502
#18070321:56:04serverid1 end_log_pos581CRC320xe6040c79 Query thread_id=46 exec_time=0error_code=0
SETTIMESTAMP=1530626164/*!*/;
BEGIN
/*!*/;
#at581
#18070321:56:04serverid1 end_log_pos691CRC320x2d99f699 Query thread_id=46 exec_time=0error_code=0
SETTIMESTAMP=1530626164/*!*/;
deletefromyxwherename='zhangsan'
/*!*/;
#at691
#18070321:56:04serverid1 end_log_pos722CRC320x4a742173 Xid=393
COMMIT/*!*/;
#at722
#18070321:56:11serverid1 end_log_pos787CRC320x6d0b47d8 Anonymous_GTID last_committed=2 sequence_number=3
SET@@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;
#at787
#18070321:56:11serverid1 end_log_pos866CRC320x97e2deb7 Query thread_id=46 exec_time=0error_code=0
SETTIMESTAMP=1530626171/*!*/;
BEGIN
/*!*/;
#at866
#18070321:56:11serverid1 end_log_pos974CRC320x9e24e8af Query thread_id=46 exec_time=0error_code=0
SETTIMESTAMP=1530626171/*!*/;
insertintoyxvalues('test02',99)
[root@promotedata]#mysql-uroot-ptest mysql>select*fromyx;
+----------+--------+
|name |score |
+----------+--------+
|zhangsan|100.00|
|lisi | 90.00|
|wangwu | 80.00|
|zhaoliu | 99.00|
+----------+--------+
4rowsinset(0.00sec)
[root@promotedata]#mysqlbinlog--no-defaults--stop-datetime='18-07-0321:56:04'mysql-bin.000003|mysql-uroot-p #结束节点
Enterpassword:
[root@promotedata]#mysqlbinlog--no-defaults--start-datetime='18-07-0321:56:11'mysql-bin.000003|mysql-uroot-p #重新开始节点
Enterpassword:
mysql>select*fromyx;
+----------+--------+
|name |score |
+----------+--------+
|zhangsan|100.00|
|lisi | 90.00|
|wangwu | 80.00|
|zhaoliu | 99.00|
|test01 | 87.00|
|test02 | 99.00|
+----------+--------+
6rowsinset(0.00sec)
2、基于位置恢复，其中581是错误语句的节点，866是第二句正确语句的节点
[root@promotedata]#mysql-uroot-ptest mysql>select*fromyx;
+----------+--------+
|name |score |
+----------+--------+
|zhangsan|100.00|
|lisi | 90.00|
|wangwu | 80.00|
|zhaoliu | 99.00|
+----------+--------+
4rowsinset(0.01sec)
[root@promotedata]#mysqlbinlog--no-defaults--stop-position='581'mysql-bin.000003|mysql-uroot-p
Enterpassword:
[root@promotedata]#mysqlbinlog--no-defaults--start-position='866'mysql-bin.000003|mysql-uroot-p
Enterpassword:
mysql>select*fromyx;
+----------+--------+
|name |score |
+----------+--------+
|zhangsan|100.00|
|lisi | 90.00|
|wangwu | 80.00|
|zhaoliu | 99.00|
|test01 | 87.00|
|test02 | 99.00|
+----------+--------+
6rowsinset(0.00sec)
声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。