Linux运维学习笔记之MySQL备份和恢复(增量恢复)教程,学习笔记mysql


第三十三章 MySQL备份和恢复进阶-增量恢复

一、什么情况下需要数据库增量恢复

1、主库或从库因硬件损坏宕机,是否需要增量恢复?

不需要,主库宕机,只需将一个同步最快的从库切换为主库即可。从库宕机,不影响使用,正常修复就行了。

2、人为操作数据库的SQL语句破坏主库是否需要增量恢复?

需要,因为此时,主从库都已同步执行了相关操作,必须通过备份来恢复。如在主库上执行了drop database test;这样的删除语句,从库也会执行这个语句,从而导致主从库上的test库丢失,只有通过备份文件来恢复数据了。

所以现在有此企业是不让执行delete语句的,所有的delete操作通过update语句更新记录上的状态码,查询时根据状态码来排除相关记录。

3、只有一个主库,是否需要增量恢复?

如果只一个主库,首先应该做定时的全量备份(每天一次)及增量备份(每隔1-10分钟对binlog日志做切割后备份到其它服务器上,或本地其它硬盘或网络文件系统的备份服务器中)

如果不允许数据丢失,最好的办法就是做从库,通过drbd(基于磁盘块的)同步。

4、总结

一般由人为(或程序)逻辑方式在数据库执行的SQL语句等误操作,是需要增量恢复的,因为此时,从库也执行误操作语句。

正常情况下,主从同步除了分担读写分离压力外,还可以防止物理设备损坏导致的数据丢失,便于数据恢复。

在从库进行全量和增量方式的备份,可以防止人为对主库的误操作导致的数据丢失。

任何时刻都应确保备份的从库和主库是同步状态。

二、生产场景备份策略选择

1、中小型公司

全量备份一般是每天一次,在业务流量低谷执行全备,备份时锁表。如果是单节点数据库,用rsync配合定时任务(频率要大一点,或用inotify)把所有binlog备份到远程服务器。但不建议用这种方法,最好还是做主从复制,无论如何也要加一台服务器(可与web共用)做从库。

2、大公司

一般是每周全备一次。如每周六0点开始一次全量备份,周日到下周六0点前都是增量备份。

优点是节省备份时间,减小备份压力。

缺点是增量的binlog文件副本太多,还原比较麻烦。

3、当前主流企业作法

当前,企业主流作法是一主多从,会有一个从库做备份,延迟同步,不对外提供服务,并开启binlog,实施定时全备和实时增量备份。

4、主流企业作法原因(也就是使用MySQL的备份的场景)

(1)迁移或升级数据库时。

(2)增加从库时。

(3)人为的DDL、DML语句,主从库都同步执行了,此时需要备份。

(4)跨机房灾备,需把备份拷贝走。

(5)因硬伯或特殊异常情况,主库或从库宕机,主从可以互相切换,是无需备份的

三、MySQL增量恢复的必备条件

1、主库和备份的从库都开启了binlog日志功能。

2、存在一份全备及全备之后到出问题时刻的所有增量binlog文件的备份。

3、增量恢复图解

四、MySQL增量恢复实战演示

1、登录3306数据库,检查student表数据

mysql -uroot -p -S /data/3306/mysql.sock

use test;

show tables;

select * from student;

+----+------+

| id | name |

+----+------+

| 1 | a1|

| 2 | a2|

| 3 | a3|

| 4 | a4|

| 5 | a5|

+----+------+

2、全量备份test数据库

mysqldump -uroot -p'123456' -S /data/3306/mysql.sock -F -B--master-data=2 test | gzip > /wddg/dbbak/test_all_$(date +%F).sql.gz

3、向student表中写入新的数据

insert into student(name) values ('b1'),('b2'),('b3'),('b4'),('b5');

select * from student;

+----+------+

| id | name |

+----+------+

| 1 | a1|

| 2 | a2|

| 3 | a3|

| 4 | a4|

| 5 | a5|

| 6 | b1|

| 7 | b2|

| 8 | b3|

| 9 | b4|

| 10 | b5 |

+----+------+

3、模拟误操作,删除test数据库

drop database test;

4、发现故障,排查原因

通过报错日志,发现test数据库被误删除。

5、通过防火墙禁止Web等应用向主库写数据或直接锁表,让主库暂时停止更新,准备恢复

6、检查备份文件

ll dbbak

-rw-r--r-- 1root root 862 Apr 23 16:24 test_all_2017-04-23.sql.gz

7、解压备份文件,查看日志位置

gzip -d test_all_2017-04-23.sql.gz

grep -i "change" test_all_2017-04-23.sql

-- CHANGE MASTERTO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=107;

8、刷新binlog日志,生成新的binlog文件

flush logs;

or

mysqladmin -uroot -p'123456' -S /data/3306/mysql.sock flush-logs

9、 将恢复需要使用的binlog文件拷贝到备份目录

cp mysql-bin.000007 /dbbak/

10、将binlog文件内容中过滤出恢复需要的test数据库内容,写入bin.sql文件

mysqlbinlog -d test mysql-bin.000007 > bin.sql

11、用vi打开bin.sql文件,删除drop database test;语句后保存

vi bin.sql

# at 576

#170423 17:07:03 server id 1end_log_pos 657 Query thread_id=3 exec_time=0 error_code=0

SET TIMESTAMP=1492938423/*!*/;

dropdatabase test #删除这一句后保存

12、由于MySQL会把mysqldump的更新插入语句也会写入binlog日志中,所以分3种情况

(1)恢复时,主库对外不提供写入功能

a、恢复全备文件

mysql -uroot -p'123456' -S /data/3306/mysql.sock < /dbbak/test_all_2017-04-23.sql

b、恢复bin.sql文件中的SQL(需指定test库)

mysql -uroot -p'123456' -S /data/3306/mysql.sock test

(2)恢复时,主库对外还在提供写入功能

a、让主库暂时停止更新,准备恢复

b、关闭sql_log_bin参数,让mysql不记录sql语句的日志

(i)查看sql_log_bin参数状态

show variables like '%log_bin%';

+---------------------------------+-------+

| Variable_name| Value |

+---------------------------------+-------+

| log_bin| ON |

| log_bin_trust_function_creators | OFF |

|sql_log_bin | ON |

+---------------------------------+-------+

(ii)设置sql_log_bin =OFF

set sql_log_bin=0

(ii)设置sql_log_bin注意事项

千万不要不假思索的加上 global 修饰符(set global sql_log_bin=0),这样会导致所有在Master数据库上执行的语句都不记录binlog。INSERT、UPDATE、DELETE的SQL语句会导致Master和Slave数据库数据不一致,要谨慎操作。

c、恢复全备文件

mysql -uroot -p'123456' -S /data/3306/mysql.sock < /dbbak/test_all_2017-04-23.sql

d、恢复bin.sql文件中的SQL(需指定test库)

mysql -uroot -p'123456' -S /data/3306/mysql.sock test

e、将bin.sql时间点之后的binlog解析为SQL,进行恢复。

f、将sql_log_bin改为 ON状态

set sql_log_bin=1

(3)恢复时,主库对外还在提供写入功能,手动实行主从切换

a、停止一个从库,准备切换为主库

b、在主库上刷新binlog,并将刷新前的最后一个binlog文件(mysql-bin.000007)解析为bin.sql

c、去掉bin.sql中的drop语句

d、将全备文件和bin.sql恢复到该从库

e、停止主库把主库刷新后的binlog日志解析为sql,恢复到从库

f、切换到该从库对外提供服务

13、如果不是drop,而是update操作破坏了数据 ,解析起来就比drop复杂一些,一般是需要停库或禁止被应用服务写入,然后再恢复。

五、增量恢复小结

1、一般是人为SQL造成的误操作

2、先全备恢复,再增量恢复

3、恢复时建议对外停止更新

4、增量恢复时要将增量日志中的问题SQL删除,再恢复到数据库

六、增量恢复的核心思想

1、流程制度控制。如果不做,面临停止服务或数据丢失的问题,鱼和熊掌不可新兼得

2、延迟备份来解决。信息上做监控,黑、白名单机制

3、根据业务需求容忍度,制定可量化的目标,根据需求选择停库或锁表或容忍丢失部分数据

 

相关内容