Mysql实现增量恢复的方法详解

47次阅读
没有评论

共计 6116 个字符,预计需要花费 16 分钟才能阅读完成。

1、人为的 sql 语句破坏了数据库

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

3、在主从架构中,主库数据发生了故障

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

1、首先做一个完全备份,确保生成完全备份的 sql 文件。

mysql select * from yx; #完全备份前数据库
+----------+--------+
| name | score |
+----------+--------+
| zhangsan | 100.00 |
| lisi | 90.00 |
| wangwu | 80.00 |
| zhaoliu | 99.00 |
+----------+--------+
4 rows in set (0.00 sec)
[root@promote data]# mysqldump -u root -p test /opt/test.sql #对数据库完全备份 

2、使用 flush-logs 生成新的二进制日志文件,用以保存之后的数据库操作语句。

[root@promote data]# mysqladmin -u root -p flush-logs  #生成二进制文件
Enter password:
[root@promote data]# 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 insert into yx(name,score) values(‘tom’,87);
Query OK, 1 row affected (0.00 sec)

mysql select * from yx;
+———-+——–+
| name  | score  |
+———-+——–+
| zhangsan | 100.00 |
| lisi  |  90.00 |
| wangwu  |  80.00 |
| zhaoliu  |  99.00 |
| tom  |  87.00 |
+———-+——–+
5 rows in set (0.00 sec)
[root@promote data]# mysqladmin -u root -p flush-logs  #生成二进制文件
Enter password:
[root@promote data]# 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 delete from yx where name=’tom’;
Query OK, 1 row affected (0.00 sec)

mysql select * from yx;
+———-+——–+
| name  | score  |
+———-+——–+
| zhangsan | 100.00 |
| lisi  |  90.00 |
| wangwu  |  80.00 |
| zhaoliu  |  99.00 |
+———-+——–+
4 rows in set (0.00 sec)

5、使用二进制文件进行恢复操作

[root@promote data]# mysqlbinlog –no-defaults mysql-bin.000001 | mysql -u root -p

6、查看数据库内容,删除的数据有了。说明数据恢复成功。

mysql select * from yx;
+———-+——–+
| name  | score  |
+———-+——–+
| zhangsan | 100.00 |
| lisi  |  90.00 |
| wangwu  |  80.00 |
| zhaoliu  |  99.00 |
| tom  |  87.00 |
+———-+——–+
5 rows in set (0.00 sec)

完全备份之后丢失所有数据的恢复步骤

1、使用 drop 删除表 yx,模拟数据完全丢失

mysql drop table yx;
Query OK, 0 rows affected (0.01 sec)

mysql show tables;
Empty set (0.00 sec)

2、先使用 mysql 命令进行完全备份恢复操作。

[root@promote data]# mysql -u root -p test /opt/test.sql
mysql use test;
Database changed
mysql select * from yx;
+———-+——–+
| name  | score  |
+———-+——–+
| zhangsan | 100.00 |
| lisi  |  90.00 |
| wangwu  |  80.00 |
| zhaoliu  |  99.00 |
+———-+——–+
4 rows in set (0.00 sec)

3、使用二进制文件进行增量备份操作。

[root@promote data]# mysqlbinlog –no-defaults mysql-bin.000001 | mysql -u root -p
mysql select * from yx;
+———-+——–+
| name  | score  |
+———-+——–+
| zhangsan | 100.00 |
| lisi  |  90.00 |
| wangwu  |  80.00 |
| zhaoliu  |  99.00 |
| tom  |  87.00 |
+———-+——–+
5 rows in set (0.00 sec)

基于时间点与位置的恢复

利用二进制日志实现局域时间点与位置的恢复,假如需要往数据库中插入两条数据,但是由于误操作,两条插入语句中间删除一条数据,而这条数据不应该删除,这时候,需要基于时间点与位置进行恢复。

–start-datetime=datetime

从二进制日志中第 1 个日期时间等于或晚于 datetime 参量的事件开始读。

–stop-datetime=datetime
从二进制日志中第 1 个日期时间等于或晚于 datetime 参量的事件起停止读。

–start-position=N
从二进制日志中第 1 个位置等于 N 参量时的事件开始读。

–stop-position=N
从二进制日志中第 1 个位置等于和大于 N 参量时的事件起停止读。

mysql select * from yx;
+———-+——–+
| name  | score  |
+———-+——–+
| zhangsan | 100.00 |
| lisi  |  90.00 |
| wangwu  |  80.00 |
| zhaoliu  |  99.00 |
+———-+——–+
5 rows in set (0.00 sec)

mysql insert into yx values(‘test01’,87);
Query OK, 1 row affected (0.00 sec)

mysql delete from yx where name=’zhangsan’;
Query OK, 1 row affected (0.00 sec)

mysql insert into yx values(‘test02’,99);
Query OK, 1 row affected (0.17 sec)

mysql select * from yx;
+———+——-+
| name  | score |
+———+——-+
| lisi  | 90.00 |
| wangwu  | 80.00 |
| zhaoliu | 99.00 |
| test01  | 87.00 |
| test02  | 99.00 |
+———+——-+
6 rows in set (0.00 sec)

1、基于时间点的恢复。18-07-03 21:56:04 是错误语句节点,18-07-03 21:56:11 第二句正确语句节点

[root@promote data]# mysqlbinlog –no-defaults –base64-output=decode-rows mysql-bin.000003
# at 298
#180703 21:55:35 server id 1  end_log_pos 406 CRC32 0x257c67ab  Query  thread_id=46  exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1530626135/*!*/;
insert into yx values(‘test01’,87)
/*!*/;
# at 406
#180703 21:55:35 server id 1  end_log_pos 437 CRC32 0xdd7913a3  Xid = 392
COMMIT/*!*/;
# at 437
#180703 21:56:04 server id 1  end_log_pos 502 CRC32 0x0d09bd0b  Anonymous_GTID  last_committed=1  sequence_number=2
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/*!*/;
# at 502
#180703 21:56:04 server id 1  end_log_pos 581 CRC32 0xe6040c79  Query  thread_id=46  exec_time=0 error_code=0
SET TIMESTAMP=1530626164/*!*/;
BEGIN
/*!*/;
# at 581
#180703 21:56:04 server id 1  end_log_pos 691 CRC32 0x2d99f699  Query  thread_id=46  exec_time=0 error_code=0
SET TIMESTAMP=1530626164/*!*/;
delete from yx where name=’zhangsan’
/*!*/;
# at 691
#180703 21:56:04 server id 1  end_log_pos 722 CRC32 0x4a742173  Xid = 393
COMMIT/*!*/;
# at 722
#180703 21:56:11 server id 1  end_log_pos 787 CRC32 0x6d0b47d8  Anonymous_GTID  last_committed=2  sequence_number=3
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/*!*/;
# at 787
#180703 21:56:11 server id 1  end_log_pos 866 CRC32 0x97e2deb7  Query  thread_id=46  exec_time=0 error_code=0
SET TIMESTAMP=1530626171/*!*/;
BEGIN
/*!*/;
# at 866
#180703 21:56:11 server id 1  end_log_pos 974 CRC32 0x9e24e8af  Query  thread_id=46  exec_time=0 error_code=0
SET TIMESTAMP=1530626171/*!*/;
insert into yx values(‘test02’,99)
[root@promote data]# mysql -u root -p test /opt/test.sql  #先进行完全恢复
mysql select * from yx;
+———-+——–+
| name  | score  |
+———-+——–+
| zhangsan | 100.00 |
| lisi  |  90.00 |
| wangwu  |  80.00 |
| zhaoliu  |  99.00 |
+———-+——–+
4 rows in set (0.00 sec)
[root@promote data]# mysqlbinlog –no-defaults –stop-datetime=’18-07-03 21:56:04′ mysql-bin.000003 | mysql -u root -p  #结束节点
Enter password:
[root@promote data]# mysqlbinlog –no-defaults –start-datetime=’18-07-03 21:56:11′ mysql-bin.000003 | mysql -u root -p  #重新开始节点
Enter password:
mysql select * from yx;
+———-+——–+
| name  | score  |
+———-+——–+
| zhangsan | 100.00 |
| lisi  |  90.00 |
| wangwu  |  80.00 |
| zhaoliu  |  99.00 |
| test01  |  87.00 |
| test02  |  99.00 |
+———-+——–+
6 rows in set (0.00 sec)

2、基于位置恢复,其中 581 是错误语句的节点,866 是第二句正确语句的节点

[root@promote data]# mysql -u root -p test /opt/test.sql
mysql select * from yx;
+———-+——–+
| name  | score  |
+———-+——–+
| zhangsan | 100.00 |
| lisi  |  90.00 |
| wangwu  |  80.00 |
| zhaoliu  |  99.00 |
+———-+——–+
4 rows in set (0.01 sec)
[root@promote data]# mysqlbinlog –no-defaults –stop-position=’581′ mysql-bin.000003 | mysql -u root -p
Enter password:
[root@promote data]# mysqlbinlog –no-defaults –start-position=’866′ mysql-bin.000003 | mysql -u root -p
Enter password:
mysql select * from yx;
+———-+——–+
| name  | score  |
+———-+——–+
| zhangsan | 100.00 |
| lisi  |  90.00 |
| wangwu  |  80.00 |
| zhaoliu  |  99.00 |
| test01  |  87.00 |
| test02  |  99.00 |
+———-+——–+
6 rows in set (0.00 sec)

向 AI 问一下细节

丸趣 TV 网 – 提供最优质的资源集合!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-12-18发表,共计6116字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)