Mysql DBA 高级运维学习之路-MySQL备份与恢复实战案例及生产方案

原创

海风掠过 2018-02-13 16:24:26 ©著作权

文章标签 mysql 运维学习笔记 文章分类 运维

©著作权归作者所有：来自51CTO博客作者海风掠过的原创作品，请联系作者获取转载授权，否则将追究法律责任

1.全量备份与增量备份

1.1全量备份

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

(1)备份所有库：

Innode引擎：

[root@linzhongniao ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -F -A -B --events --single-transaction --master-data=1|gzip >/server/backup/mysql_backup_$(date +%F).sql.gz

MYISAM引擎：

[root@linzhongniao ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -F -A -B --events --lock-all-tables --master-data=1|gzip >/server/backup/mysql_backup_$(date +%F).sql.gz

参数说明：

-F参数：刷新binlog

-A参数：备份所有库

-B参数：备份数据的时候添加建库建表等语句

--events参数相当于-E:在输出中包含转储数据库的事件调度器事件

--master-data参数：如果参数值等于1，在备份文件中添加change master语句在主从同步change master的时候就不用制定binlog日志文件以及更新的位置，如果等于2则不会添加change master语句。

--single-transaction参数：用于Inode引擎的备份

--lock-all-tables参数：锁表，它针对于MyIsam引擎的备份。在这种情况下日志之刷新一次因为所有的表的都是锁着的(允许并发插入数据)。如果想让你的转储和日志刷新发生在同一时刻，应该将--lock-all-tables参数、--master-data参数和flush-logs(即-F参数)一起使用，对于Innode这样的事务表，单事务表市更好的选择，以为它根本不用锁表。

(2)备份指定库：

Innode引擎：

[root@linzhongniao ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -F -B --events --single-transaction --master-data=1 linzhongniao|gzip >/server/backup/mysql_backup_$(date +%F).sql.gz

MYISAM引擎：

[root@linzhongniao ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -F -B --events --lock-all-tables --master-data=1 linzhongniao|gzip >/server/backup/mysql_backup_$(date +%F).sql.gz

1.2 增量备份

增量备份的数据是从上次全量备份之后更新的数据，对mysql来说binlog日志文件存储着增量备份的数据。增量备份可以按天备份，第二天做全备了，理论上前一天的备份就没用了。一般我们主服务器的binlog日志文件保持七天（受my.cnf配置文件中expire_logs_days控制），备份服务器上增量备份一般保持180天。全量被份和增量备份分为两种情况，第一种情况就是按天备份一天做一次全量备份，每天零点之前做增量备份，零点之后做全量备份；第二种情况就是按天全备也可以按周全备，一周做一次全备每天做一次增量备份。

按天全备的优点：恢复时间短、维护成本低

缺点：占用空间多，占用系统资源多，经常锁表影响用户体验。

按周全备的优点：占用空间少，占用系统资源少用户体验好一些。

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

企业场景全备和增量的频率是怎么做的呢？

（1）中小公司，全量一般是每天一次，业务流量低谷执行全备，执行前需要锁表。

（2）单台数据库没有做主从同步，如何增量？用rsync(配合定时任务频率大点，或者inotify和主从复制)把所有binlog备份到远程服务器，尽量做主从复制。

rsync –avz /data/3306/mysql-bin.000* rsync_backup@192.168.1.116::backup –-password-file=/etc/rsync.password

（3）大公司周备，每周六00点一次全量，下周日-下周六00点前都是增量。

优点：节省备份时间，减小备份压力，缺点：增量的binlog文件副本太多，还原会很麻烦。

（4）一主多从，会有一个从库做备份，延迟同步。

Mysql的mysqldump备份什么时候派上用场？

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

(2)增加从库的时候，因为硬件或特殊异常情况，主库或从库宕机，主从可以切换，无需备份。把其中一个同步最快的切换为主库即可。

(3)人为的DDL、DML语句，主从库没办法了，所有库都会执行，此时需要备份。

(4)跨机房灾备，需备份拷贝走数据。

2.MySQL增量恢复必备条件

主库和从库开启binlog

[root@linzhongniao ~]# egrep "\[mysqld]|log-bin" /data/3306/my.cnf
[mysqld]
log-bin = /data/3306/mysqlbin_linzhongniao

小结：增量恢复的条件：

存在一份全备数据和全备之后的某一时刻到出问题时刻的所有增量binlog文件备份。

3.全备即增量备份数据恢复思想

恢复思想就是先找到发生故障的原因，谁在什么时候做了什么操作，检查是不是这个操作导致数据库故障。之后尽量停止数据库对外访问，先备份0:00到十点更新的数据binlog日志文件，刷新binlog。第一步先恢复全备，第二步增量恢复0：00到10:00这个时间段的数据。

4.实战演示数据恢复过程

4.1 全备数据

(1)备份数据

[root@mysql ~]# date -s '2018/02/11' 更改系统时间模拟零点时刻
2018年 02月 11日 星期日 00:00:00 CST 
[root@linzhongniao scripts]# cat quanbei.sh
#/bin/sh
#Date: 2018-02-11 
#Author: Create by linzhongniao  
#Mail: xxxxxxxxx@163.com 
#Function:This scripts function is More complex backup scripts which need to find binlog log files and location points 
#Version: 1.1 
USER=root
PASS=123456
MYSOCK=/data/3306/mysql.sock
DATA_PATH=/server/backup
DATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gz
LOG_FILE=${DATA_PATH}/mysql_backup_`date +%F`.log
MYSQL_PATH=/usr/local/mysql/bin
mysqldb=linzhongniao
#--single-transaction Specifically for the InnoDB engine when the data is updated when the data is updated it can't see the whole isolation
MYSQL_DUMP="${MYSQL_PATH}/mysqldump -u$USER -p$PASS -S $MYSOCK --events -B -F --master-data=2 --single-transaction $mysqldb"
MYSQL_CMD="${MYSQL_PATH}/mysql -u$USER -p$PASS -S $MYSOCK"
cat |${MYSQL_CMD}<<EOF
flush table with read lock;
system echo "-----show master status result-----" >>${LOG_FILE};
system ${MYSQL_CMD} -e "show master status"|tail -1 >>${LOG_FILE};
system ${MYSQL_DUMP}|gzip > ${DATA_FILE};
unlock tables;
quit
EOF
[root@linzhongniao scripts]# date -s '2018/10/31'
Wed Oct 31 00:00:00 CST 2018
[root@linzhongniao scripts]# sh quanbei.sh 
quanbei.sh: line 25: warning: here-document at line 18 delimited by end-of-file (wanted `EOF')

(2) 执行脚本全量备份linzhongniao库原理

[root@mysql ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -F -B --master-data=2 --events --single-transaction linzhongniao|gzip >/server/backup/bak_$(date +%F).sql.gz

(3)查看备份的数据

[root@linzhongniao scripts]# ll /server/backup/
total 8
 -rw-r--r--. 1 root root  71 Oct 31 00:00 mysql_backup_2018-10-31.log
 -rw-r--r--. 1 root root 960 Oct 31 00:00 mysql_backup_2018-10-31.sql.gz

(4)实际工作中是通过定时任务来备份的

[root@linzhongniao ~]# crontab –l
#mysql backup by linzhongniao on 20180211
00 00 * * * /bin/sh beifen1.sh >/dev/null 2>&1

4.2 备份后查看binlog日志情况

[root@linzhongniao scripts]# ll /data/3306/mysqlbin_linzhongniao.000049
-rw-rw----. 1 mysql mysql 107 Oct 31 00:00 /data/3306/mysqlbin_linzhongniao.000049
[root@linzhongniao scripts]# mysqlbinlog /data/3306/mysqlbin_linzhongniao.000049
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#181031  0:00:10 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.32-log created 181031  0:00:10
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
     CoDYWw8BAAAAZwAAAGsAAAABAAQANS41LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

提示：我们可以看用-F参数备份生成的新的文件mysqlbin_linzhongniao.000049，增量恢复就是从mysqlbin_linzhongniao.000049开始的。

4.3 模拟网站用户更新数据

因为在0点备份后到发生故障这个时间段内，用户还会随时的更新数据

[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.5.32-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use linzhongniao
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------------+
| Tables_in_linzhongniao |
+------------------------+
| student|
+------------------------+
1 row in set (0.00 sec)

mysql> select * from student;
+----+-------------+
| id | name|
+----+-------------+
|  1 | linzhogniao |
|  2 | wwn1314 |
|  3 | 张三|
|  4 | woshishei   |
|  5 | xiaozhang   |
|  7 | lisi|
|  9 | lisi1   |
| 11 | lisi2   |
| 12 | lisi3   |
| 14 | lisi4   |
| 16 | lisi5   |
+----+-------------+
11 rows in set (0.00 sec)

mysql> insert into student(name) values('xiaohong');  
Query OK, 1 row affected (0.00 sec)

mysql> insert into student(name) values('xiaohong1');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student(name) values('xiaohong2');
Query OK, 1 row affected (0.00 sec)

查看标的内容，模拟故障恢复后还应是这些数据，如果不是这些数据就是丢失了数据。

mysql> select * from student;
+----+-------------+
| id | name|
+----+-------------+
|  1 | linzhogniao |
|  2 | wwn1314 |
|  3 | 张三|
|  4 | woshishei   |
|  5 | xiaozhang   |
|  7 | lisi|
|  9 | lisi1   |
| 11 | lisi2   |
| 12 | lisi3   |
| 14 | lisi4   |
| 16 | lisi5   |
| 17 | xiaohong|
| 19 | xiaohong1   |
| 21 | xiaohong2   |
+----+-------------+
14 rows in set (0.00 sec)

4.4 模拟用户破坏数据库

公司的老大于早晨10点，执行了删除数据库语句。本来想删除一个没有用的库。

mysql> drop database linzhongniao;
Query OK, 1 row affected (0.11 sec)

linzhongniao库不见了

mysql> drop database linzhongniao;
Query OK, 1 row affected (0.09 sec)

mysql> show databases;
+--------------------+
| Database   |
+--------------------+
| information_schema |
| linzhongniao_gbk   |
| mysql  |
| nidayede   |
| nihao  |
| nihaoa |
| nihaob |
| nihaoc |
| nihaod |
| nishishei  |
| performance_schema |
| school |
| test   |
+--------------------+
13 rows in set (0.01 sec)

4.5 发现故障排查并检查原因

数据库出问题10分钟后，公司的网站运营人员报网站故障，联系dba运维人员解决。此时，dba人员或开发人员查看网站报错（或者查看后台日志）。可以看到连不上linzhongniao数据库的显示。然后登录数据库排查，可以发现数据库linzhongniao库确实不在。经过多方询问得知老大早晨要处理一下数据库，于是问老大都干啥了。答曰，10点左右刚刚清除了一个“没有用的”数据库。致此问题原因找到，开始准备恢复，原因还有可能是开发人员通过程序日志判断。提示：数据库的权限管理思想就在这里。不让别人有delete权限。

4.6 增量备份

通过防火墙禁止web等应用向主库写数据，让主库暂停更新，然后进行恢复。发现问题所在，查看零点的全备

[root@linzhongniao ~]# ll /server/backup/
total 8
 -rw-r--r--. 1 root root  71 Oct 31 00:00  mysql_backup_2018-10-31.log
 -rw-r--r--. 1 root root 960 Oct 31 00:00 mysql_backup_2018-10-31.sql.gz

查看binlog刷新的位置以及在那个binlog文件开始刷新，从下面可以得知从mysqlbin_linzhongniao.000048日志文件的107这个位置开始的全备，mysqlbin_linzhongniao.000048日志文件以后生成的文件是从零点全备到出现问题这个时间段的增量

[root@linzhongniao backup]# cat mysql_backup_2018-10-31.log 
-----show master status result-----
mysqlbin_linzhongniao.000048        107

查看一下所有binlog确保，只有 mysqlbin_linzhongniao.000049日志文件

提示：我们在0点执行的全备，所以binlog日志文件是从0点往后更新的。我们可以用mysqlbinlog参数查看一下零点之后更新的binlog文件，看看里面有没有更新的数据。当然我们也可以通过查看0点全备的数据文件里面的change master的位置来查看最新更新的binlog文件。可以看到执行全备后的所有binlog日志文件，它里面记录了从0点全备之后到第二天十点所有有更新的数据包括执行的误操作，所以我们在增量恢复的时候必须把执行误操作的那条语句删掉。例如本次演示应该把drop那条语句删掉。

4.6.1 刷新binlog

一般数据库故障我们要停止数据库，不能停这种情况下我们要刷新一下binlog。刷新一下binlog就会生成一个新的mysql-bin日志文件mysql-bin.000050，这时候再更新数据就会往这个新的里面写。现在增量恢复的目标就是mysql-bin.000050。

`[root@linzhongniao ~]# mysqladmin -uroot -p123456 -S /data/3306/mysql.sock flush-logs` 
[root@linzhongniao ~]# ll /data/3306/mysqlbin_linzhongniao.000050
 -rw-rw----. 1 mysql mysql 107 Nov  1 20:38 /data/3306/mysqlbin_linzhongniao.000050

4.6.2 增量备份binlog

（1）要把mysqlbin_linzhongniao.000049拷贝出来防止二次破坏数据

[root@linzhongniao ~]# cp /data/3306/mysqlbin_linzhongniao.000049 /server/backup/  [root@linzhongniao ~]# ll /server/backup/
total 16
 -rw-r--r--. 1 root root   71 Oct 31 00:00 mysql_backup_2018-10-31.log
 -rw-r--r--. 1 root root  960 Oct 31 00:00 mysql_backup_2018-10-31.sql.gz
 -rw-r-----. 1 root root  998 Nov  1 20:52 mysqlbin_linzhongniao.000049

（2）将mysqlbin_linzhongniao.000049解析成linzhongniao.sql数据文件

[root@linzhongniao ~]# mysqlbinlog -d linzhongniao /server/backup/mysqlbin_linzhongniao.000049>/server/backup/linzhongniao.sql  
[root@linzhongniao ~]# ll /server/backup/
total 16
 -rw-r--r--. 1 root root 2824 Nov  1 21:01 linzhongniao.sql
 -rw-r--r--. 1 root root   71 Oct 31 00:00 mysql_backup_2018-10-31.log
 -rw-r--r--. 1 root root  960 Oct 31 00:00 mysql_backup_2018-10-31.sql.gz
 -rw-r-----. 1 root root  998 Nov  1 20:52 mysqlbin_linzhongniao.000049

（3）删除导致故障语句

[root@linzhongniao ~]# grep "drop" /server/backup/linzhongniao.sql  
drop database linzhongniao
[root@linzhongniao ~]# grep "drop" /server/backup/linzhongniao.sql

4.7 恢复数据

如果不挺库禁止对外访问就会有一个问题，什么问题呢？第一个问题就是，在恢复的时候还有用户往数据库写数据，mysqlbin_linzhongniao.000050还会记录更新的内容；全备恢复和增量恢复后还要恢复mysqlbin_linzhongniao.000050更新的数据，备份mysqlbin_linzhongniao.000050还得刷新binlog，增量恢复完mysqlbin_linzhongniao.000050还得增量恢复更新的binlog，这样就死锁了老得恢复。第二个问题就是，我们在增量恢复的时候，mysqlbin_linzhongniao.000050也会记录全量和增量恢复的更新。在全量和增量恢复之后再恢复mysqlbin_linzhongniao.000050更新的记录的时候就会导致主键冲突，可以编辑mysqlbin_linzhongniao.000050解析成的mysql数据文件将冲突的数据删掉，如果数据多呢？还可以关闭sql_log_bin。关闭sql_log_bin就不会更新binlog日志文件了，这样会导致数据缺失。最好的方法就是停库，禁止对外访问，再做全量和增量备份。最根本的就是数据库的权限管理，不给删除修改权限，只给数据库管理员删除修改权限，防止故障的发生。谁有权限都要有记录，谁导致的故障要负责任。

mysql> show variables like '%log_bin%';
 +---------------------------------+-------+
| Variable_name   | Value |
 +---------------------------------+-------+
| log_bin | ON|
| log_bin_trust_function_creators | OFF   |
| sql_log_bin | ON|
3 rows in set (0.00 sec)
mysql> set global sql_log_bin=OFF; 
Query OK, 0 rows affected (0.00 sec)

查看没有修改，退出客户端在登陆查看

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name   | Value |
+---------------------------------+-------+
| log_bin | ON|
| log_bin_trust_function_creators | OFF   |
| sql_log_bin | OFF   |
+---------------------------------+-------+
3 rows in set (0.00 sec)

4.7.1 全量恢复

[root@linzhongniao ~]# gzip -d /server/backup/mysql_backup_2018-10-31.sql.gz 
[root@linzhongniao ~]# ll /server/backup/
total 16
 -rw-r--r--. 1 root root 2824 Nov  1 21:01 linzhongniao.sql
 -rw-r--r--. 1 root root   71 Oct 31 00:00 mysql_backup_2018-10-31.log
 -rw-r--r--. 1 root root 2409 Oct 31 00:00 mysql_backup_2018-10-31.sql
 -rw-r-----. 1 root root  998 Nov  1 20:52 mysqlbin_linzhongniao.000049
[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock </server/backup/mysql_backup_2018-10-31.sql

4.7.2 增量恢复

[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock </server/backup/linzhongniao.sql

4.7.3 检查恢复后的数据

[root@linzhongniao ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "select * from linzhongniao.student" 
+----+-------------+
| id | name|
+----+-------------+
|  1 | linzhogniao |
|  2 | wwn1314 |
|  3 | 张三|
|  4 | woshishei   |
|  5 | xiaozhang   |
|  7 | lisi|
|  9 | lisi1   |
| 11 | lisi2   |
| 12 | lisi3   |
| 14 | lisi4   |
| 16 | lisi5   |
| 17 | xiaohong|
| 19 | xiaohong1   |
| 21 | xiaohong2   |
+----+-------------+