MySQl生产备份实战应用指南

MySQl生产备份实战应用指南

一、全量备份与增量备份

 

全量备份是指数据库中所有的数据，全量备份就是把数据库中所有的数据进行备份。

 

备份所有库：

[root@i-3vsptbun ~]# mysqldump  -uroot -pzhangjunchao -F -B -A --events | gzip > /opt/mysqlbak_$(date +%F).sql.gz

 

备份一个库：

[root@i-3vsptbun ~]# mysqldump -uroot -pzhangjunchao -F boy | gzip > /opt/boy_$(date +%F).sql.gz

 

 

增量备份：增量数据是从上次全量备份之后，更新的新数据。对于mysql来说，binlog日志就是mysql的增量数据。

1、按天备份情况

缺点：占用空间多、占用资源多，经常锁表影响用户体验。优点：恢复时间短、维护成本低。

 

2、按周全备

周六 00 全量备份
01.sql.gz
周一增量备份	周二增量数据	周三增量数据	周四增量数据	周五增量数据
mysql-bin.000017 mysql-bin.000018 mysql-bin.000019 mysql-bin.index	mysql-bin.000020 mysql-bin.000021 mysql-bin.000022 mysql-bin.index

优点：占用空间小，无需锁表，用户体验好。

缺点：维护成本高，恢复麻烦，时间长。

 

二、企业场景全量和增量备份频率

①中小公司，全量一般是每天一次，业务流量低估执行全备，要锁表。

②尽量做主从复制。

③大公司周备，每周6一次全量，其他时间是通过binlog进行增量。

小结：mysql的mysqldump备份什么时候派上用场？

1、迁移或升级数据库

2、增加从库的时候

3、如果因为硬件或特殊异常情况，主库或从库宕机，主动可以互相切换，无需备份。

4、认为的DDL、DML，主从库就没办法了，所有库都会执行，此时需要备份。

5、跨机房灾备，需要备份拷贝走。

 

三、mysql增量恢复必备条件

1、开启bin-log日志功能

2、存在mysql全备

Innodb引擎备份

mysqldump  -uroot -pzhangjunchao -F --single-transaction -B -A --events | gzip > /opt/mysqlbak_$(date +%F).sql.gz

Myisam引擎备份

mysqldump  -uroot -pzhangjunchao -F  -B -A --lock-all-tables --events | gzip > /opt/mysqlbak_$(date +%F).sql.gz

3、生产环境mysqldump备份脚本

 

四、模拟生产环境故障恢复

1、先对mysql数据库boy库进行全备

[root@i-3vsptbun mysql]# mysqldump -uroot -pzhangjunchao -F -B --master-data=2 boy | gzip > /opt/backup/boy_$(date +%F ).sql.gz

小结：因为这里只在master库上进行备份，所以可以用--master-data=2参数。

[root@i-3vsptbun mysql]# ls /opt/backup/

boy_2017-11-27.sql.gz

 

2、做完全备后再boy库里插入两条数据

mysql> insert into tomcat2 values(11,'chao11');

Query OK, 1 row affected (0.00 sec)

mysql> insert into tomcat2 values(12,'chao12');

Query OK, 1 row affected (0.00 sec)

 

3、某dba人员模拟删除boy库

mysql> drop database boy;

Query OK, 3 rows affected (0.01 sec)

这是发现mysql数据库里面没有boy库了。

 

3、对binlog日志进行增量备份

[root@i-3vsptbun backup]# mysqlbinlog -d boy mysql-bin.000025  > /opt/backup/bin.sql

重点：

1、如果有多个库，用-d选项分库。

2、编辑bin.sql找到之前操作的删除语句”drop database boy;”，把它删掉。

 

4、检查boy库的全备和增量备份binlog日志

[root@i-3vsptbun mysql]# ls /opt/backup/

boy_2017-11-27.sql.gz

[root@i-3vsptbun backup]# gzip -d  boy_2017-11-27.sql.gz   #解压*.gz备份文件

[root@i-3vsptbun backup]# ls

boy_2017-11-27.sql

[root@i-3vsptbun backup]# grep -i "change " boy_2017-11-27.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000025', MASTER_LOG_POS=106;  #查看增量备份是从哪个位置开始的，这非常关键

重点：通过防火墙或web等应用禁止向主库写数据或者锁表，让主库停止更新，然后进行恢复。

 

5、恢复boy库

[root@i-3vsptbun backup]# mysql -uroot -pzhangjunchao  < /opt/backup/boy_2017-11-27.sql  #boy库全库恢复

[root@i-3vsptbun backup]# mysql -uroot -pzhangjunchao boy < /opt/backup/bin.sql          #增量恢复

 

小结：

主从断开情况如何恢复数据？

1、停止一个从库，然后在主库上刷新binlog，把mysql-bin.000025恢复成bin.sql（去掉drop语句）。

2、把全备boy_2017-11-27.sql及增量mysql-bin.000025恢复到从库。

3、切换到从库提服务。

数据丢多少？重新刷新binlog以后的数据，mysql-bin.000026。

4、停止主库，快速把mysql-bin.000026解析成sql，恢复到从库。

 

增量恢复：

1、一般是认为的sql造成的误操作。恢复的前提是要有全备和增量。

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

3、在恢复增量的时，把增量日志中有问题的sql语句删除，恢复到数据库。

 

增量恢复的核心思想：

1、通过流程制度控制，防止问题发生，如果不做，可能会面临服务和数据不可兼得。

2、可以通过延迟备份来解决，或者通过监控，黑名单，白名单机制。

3、根据业务需求容忍度，选择停库。