MySQL备份方案实践

MySQL备份方案实践

数据备份，目的在于防患于未然

全量备份

全量数据，指的是某一整个数据库（如kings）中所有的表、以及表数据，进行备份。

例如备份所有数据库、以及所有数据

--all--database，-A

--compact 产生少量输出

mysqldump -uroot -pyuchao7777 -P3307 -h127.0.0.1 --master-data=2  -A |gzip  > /data/3307/all-data.sql.gz

备份其中一个db

mysqldump -uroot -pyuchao7777 -P3307 -h127.0.0.1 --master-data=2  kings|gzip > /data/3307/kings-data.sql.gz

增量备份

增量指的就是，在上一次全量备份数据之后，到下一次全量备份之间的新增数据。

增量数据，也就是以binlog形式进行记录数据，基于binlog日志的备份，也就是增量备份。

全量+增量

全量备份特点

优点

• 恢复数据时需要的数据文件数量少，维护成本低

缺点

• 每天一个全量备份，占用磁盘，且全量备份时，消耗计算机资源，造成极其压力上升。

中小公司最常用的就是全量备份，定制备份数据删除规则，例如仅保留7天内的数据，若是有特殊需求，可以长时间保留备份数据。

备份方案

逻辑备份

逻辑备份指的是用mysqldump命令或者其他工具，把mysql的数据以SQL的形式导出。

恢复的时候，导入该数据，source或者mysql命令，重新将SQL还原为数据。

特点

逻辑备份优点是简单、方便、可靠、备份后的数据可以跨平台、跨版本、跨操作系统恢复，因为都遵循SQL语句。

缺点：比起物理备份，效率较低，海量数据下，效率很低。

物理备份

物理备份是直接备份mysql的数据源，例如datadir=/data/3307/data

冷备

物理备份指的就是，利用cp,rsync,tar,scp等工具把mysql数据文件复制多份，但是在备份期间，仍可能有用户在写入数据，因此该方案，会导致数据丢失，数据复制不完整。

为了确保备份期间的数据一致性，可以人工的停止数据库，或者锁表，再进行复制数据，但是这种方案还是太粗暴。

因此是不推荐使用的，只是大家需要有这个概念，知道备份的方式。

冷备，指的是，停机情况下对数据备份

热备

热备，指的是，不停机，进行数据备份

结合Xtrabackup备份工具，可以实现物理全备+增量备份。

特点

和逻辑备份相反

优点：速度快，效率极高

缺点：不支持跨平台、版本、软件、操作系统，恢复麻烦些

中小公司全量备份实战

中小公司，一般是逻辑备份，mysqldump即可，设置每天进行全量备份，备份时间在业务流量最低谷时操作。jianli

备份脚本

如开发备份脚本

这里的mysql是超哥机器上的多实例环境，因此加上了些参数

#!/bin/bash

bak_path=/mysql_data/3307/
[ -d $bak_path ] && mkdir -p $bak_path
# 备份数据，逻辑备份
mysqldump -uroot -pyuchao7777 -P3307 -h127.0.0.1 -B -A --master-data=2|gzip >$bak_path/$(date +%F)_3307.sql.gz

# md5验证完整文件，用于未来检查传输结果
md5sum $bak_path/$(date +%F)_3307.sql.gz >$bak_path/$(date +%F)_3307.sql.gz.md5


# 复制、同步备份文件，物理备份
# 需要配置好rsync服务
rsync -az $bak_path/ rsync_backup@10.211.55.12::mysql/ --password-file=/etc/rsync.password

# 删除超过7天的备份
find $bak_path/ -type f -name "*.sql.gz" -mtime +7|xargs rm -f

定时任务

[root@mysql-server56 3307]# crontab -l
0 0 * * * /bin/bash /mysql_scripts/bak.sh &>/dev/null

中小公司增量备份实战

既然用增量备份

表示全量的数据文件，无法达到恢复的目的

例如

一位刚入职场的萌新开发，于超同学，一个不留神，删错了数据，凉凉

如何恢复

1.得有全量备份的数据

2.得有binlog增量日志

实战

实验环境准备

当前的kings库下，tanks表数据内容

[root@mysql-server56 3307]# mysql -uroot -pyuchao7777 -P3307 -h127.0.0.1 -e "select * from kings.tanks"
Warning: Using a password on the command line interface can be insecure.
+----+--------------+--------------+-----------------+-------+-----------------------------+--------+----------------------------------------------------------------+
| id | name         | skills       | summoner_skills | price | introduction                | camp   | pic                                                            |
+----+--------------+--------------+-----------------+-------+-----------------------------+--------+----------------------------------------------------------------+
|  1 | 亚瑟         | 圣剑裁决     | flush           |  5888 | 能抗能打，技能沉默          | 近战   | https://img.18183.com/uploads/allimg/190924/266-1Z9241Q224.jpg |
|  3 | 东皇太一     | 堕神契约     | flush           |     0 |                             | NULL   | NULL                                                           |
|  4 | 吕布         | 魔神降临     | flush           | 18888 |                             | NULL   | NULL                                                           |
|  9 | 庄周         |              | flush           |  7777 |                             | NULL   | NULL                                                           |
| 10 | 关羽         |              | flush           |  8888 |                             | NULL   | NULL                                                           |
| 11 | 钟馗         | 轮回吞噬     | flush           |  9888 |                             | NULL   | NULL                                                             |
+----+--------------+--------------+-----------------+-------+-----------------------------+--------+----------------------------------------------------------------+
[root@mysql-server56 3307]#

模拟在0点整进行全量备份

这里非常重要！！！！

基于binlog的增量备份，核心就在于这里

我们在执行mysqldump备份时，添加的–master-data=2参数，能够立即计算出当前的数据在哪一个位置，表示，目前数据一直写到了这里。

[root@mysql-server56 3307]# date -s "2021/04/21"
Wed Apr 21 00:00:00 CST 2021
[root@mysql-server56 3307]#
[root@mysql-server56 3307]# date
Wed Apr 21 00:00:03 CST 2021


# 备份脚本

# /bin/bash
[root@mysql-server56 mysql_script]# cat bak_db.sh
date -s "2021/04/21"
mysqldump -uroot -pyuchao7777 -P3307 -h127.0.0.1 -B --master-data=2 kings|gzip >/mysql_data/3307/$(date +%F)_3307.sql.gz
ls -l /mysql_data/3307/

# 执行脚本，查看备份文件
[root@mysql-server56 mysql_script]# bash bak_db.sh
[root@mysql-server56 mysql_script]# bash bak_db.sh
Wed Apr 21 00:00:00 CST 2021
Warning: Using a password on the command line interface can be insecure.
total 4
-rw-r--r-- 1 root root 1348 Apr 21 00:00 2021-04-21_3307.sql.gz

模拟0点全量备份后后，继续写入数据

模拟在上午11点，二货开发，于超同学，手残删掉了kings整个数据库

[root@mysql-server56 mysql_script]# cat drop_db.sh
date -s "2021/04/21 11:00"
mysql -uroot -pyuchao7777 -P3307 -h127.0.0.1 -e "drop database kings;show databases;"
[root@mysql-server56 mysql_script]#


# 模拟修改时间
[root@mysql-server56 mysql_script]# bash drop_db.sh
Wed Apr 21 11:00:00 CST 2021
Warning: Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| K8S                |
| blog               |
| luffy              |
| mysql              |
| performance_schema |
+--------------------+

问题排查

此时在上午11点，kings数据库以及被删除了，后端代码，或者监控，一定会报错，使用到该kings数据库的人员，一定会投诉到DBA，运维部门。

此时dba超哥需要做如下事

• 检查网站报错
• 检查linux上后端代码日志，这里基本上就可以知道无法连接数据库
• 登录数据库机器，发现数据库消失。。。

因此数据库的权限控制，尤其重要

开始恢复

找到原因，就需要开始恢复数据

• 准备好全量备份数据，0点整的备份文件
• 备份好所有的binlog增量日志文件，防止二次破坏

[root@mysql-server56 mysql_script]# cp -a /data/3307/mysql-bin.* /mysql_data/3307/
[root@mysql-server56 mysql_script]# ls /mysql_data/3307/
2021-04-21_3307.sql.gz  mysql-bin.000001  mysql-bin.000002  mysql-bin.000002.sql  mysql-bin.000003  mysql-bin.index

停止数据库访问，可以利用如iptables进行屏蔽请求

# 非127.0.0.1的请求，禁止访问3307端口，这样就禁止除了本地，其他人都无法连接

[root@mysql-server56 mysql_script]# iptables -I INPUT -p tcp --dport 3307 ! -s 127.0.0.1 -j DROP

解压缩全量备份的数据

确保其中，并没有刚才超哥插入的2条数据，孙悟空，猪八戒

[root@mysql-server56 mysql_script]# cd /mysql_data/3307
[root@mysql-server56 3307]# gzip -cd 2021-04-21_3307.sql.gz > kings.sql
[root@mysql-server56 3307]# ls -lrt kings.sql
-rw-r--r-- 1 root root 3538 Apr 21 11:30 kings.sql

分析binlog日志

全量数据有了，我们就已经有了到4月21号0点整的数据

问题是应该从binlog什么位置开始恢复

答案是，基于binlog的位置，pos参数

因为我们全量备份时，添加了–master-data=2参数，当时记录了数据的一个切割点，也就是如下的mysql-bin.000003 ，数据位置是3704

[root@mysql-server56 3307]# ls
2021-04-21_3307.sql.gz  kings.sql  mysql-bin.000001  mysql-bin.000002  mysql-bin.000002.sql  mysql-bin.000003  mysql-bin.index
[root@mysql-server56 3307]#


# 开始复制或时间点恢复的位置为
[root@mysql-server56 3307]# sed -n '19,22p' kings.sql
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=3704;
[root@mysql-server56 3307]#

可以看到，用于主从数据复制，或者恢复的位置是

mysql-bin.000003，位置是3704

解析binlog

从binlog的位置，以及binlog文件，解析出我们能看得懂的SQL语句

并且可以从基于binlog解析出的SQL文件中，找到我们增量写入的新数据，孙悟空，猪八戒

[root@mysql-server56 3307]# mysqlbinlog -d kings mysql-bin.000003 --start-position=3704 -r kings_binlog.sql

并且

找出有问题的sql语句，将其删除，因为我们要恢复数据

[root@mysql-server56 3307]# grep -w drop kings_binlog.sql
drop database kings

# 删除该语句
[root@mysql-server56 3307]# sed -i '/drop database kings/d' kings_binlog.sql
[root@mysql-server56 3307]#
[root@mysql-server56 3307]# grep -w drop kings_binlog.sql
[root@mysql-server56 3307]#

执行恢复

此时此刻，数据库中，仍然是没有tanks数据表的

[root@mysql-server56 3307]# mysql -uroot -pyuchao7777 -P3307 -h127.0.0.1 -e 'select * from kings.tanks;'
Warning: Using a password on the command line interface can be insecure.
ERROR 1146 (42S02) at line 1: Table 'kings.tanks' doesn't exist

先恢复全量备份，也就是0点前的内容

mysql -uroot -pyuchao7777 -P3307 -h127.0.0.1 < /mysql_data/3307/kings.sql

图解

全量恢复结果

此时我们的tanks表数据就回来了，但是后续插入的新数据，孙悟空，猪八戒还没有，这是因为数据在增量备份的binlog里

[root@mysql-server56 3307]# mysql -uroot -pyuchao7777 -P3307 -h127.0.0.1 < /mysql_data/3307/kings.sql
Warning: Using a password on the command line interface can be insecure.
[root@mysql-server56 3307]#
[root@mysql-server56 3307]# mysql -uroot -pyuchao7777 -P3307 -h127.0.0.1 -e 'select * from kings.tanks;'
Warning: Using a password on the command line interface can be insecure.
+----+--------------+--------------+-----------------+-------+-----------------------------+--------+----------------------------------------------------------------+
| id | name         | skills       | summoner_skills | price | introduction                | camp   | pic                                                            |
+----+--------------+--------------+-----------------+-------+-----------------------------+--------+----------------------------------------------------------------+
|  1 | 亚瑟         | 圣剑裁决     | flush           |  5888 | 能抗能打，技能沉默          | 近战   | https://img.18183.com/uploads/allimg/190924/266-1Z9241Q224.jpg |
|  3 | 东皇太一     | 堕神契约     | flush           |     0 |                             | NULL   | NULL                                                           |
|  4 | 吕布         | 魔神降临     | flush           | 18888 |                             | NULL   | NULL                                                           |
|  9 | 庄周         |              | flush           |  7777 |                             | NULL   | NULL                                                           |
| 10 | 关羽         |              | flush           |  8888 |                             | NULL   | NULL                                                           |
| 11 | 钟馗         | 轮回吞噬     | flush           |  9888 |                             | NULL   | NULL                                                           |
+----+--------------+--------------+-----------------+-------+-----------------------------+--------+----------------------------------------------------------------+
[root@mysql-server56 3307]#

---

恢复增量数据

再恢复binlog解析出的kings_binlog.sql

恢复增量的数据

[root@mysql-server56 3307]# mysql -uroot -pyuchao7777 -P3307 -h127.0.0.1 < /mysql_data/3307/kings_binlog.sql
Warning: Using a password on the command line interface can be insecure.
[root@mysql-server56 3307]# mysql -uroot -pyuchao7777 -P3307 -h127.0.0.1 -e 'select * from kings.tanks;'
Warning: Using a password on the command line interface can be insecure.
+----+--------------+--------------+-----------------+-------+-----------------------------+--------+----------------------------------------------------------------+
| id | name         | skills       | summoner_skills | price | introduction                | camp   | pic                                                            |
+----+--------------+--------------+-----------------+-------+-----------------------------+--------+----------------------------------------------------------------+
|  1 | 亚瑟         | 圣剑裁决     | flush           |  5888 | 能抗能打，技能沉默          | 近战   | https://img.18183.com/uploads/allimg/190924/266-1Z9241Q224.jpg |
|  3 | 东皇太一     | 堕神契约     | flush           |     0 |                             | NULL   | NULL                                                           |
|  4 | 吕布         | 魔神降临     | flush           | 18888 |                             | NULL   | NULL                                                           |
|  9 | 庄周         |              | flush           |  7777 |                             | NULL   | NULL                                                           |
| 10 | 关羽         |              | flush           |  8888 |                             | NULL   | NULL                                                           |
| 11 | 钟馗         | 轮回吞噬     | flush           |  9888 |                             | NULL   | NULL                                                           |
| 12 | 孙悟空       |              | flush           |     0 |                             | NULL   | NULL                                                           |
| 13 | 猪八戒         |              | flush           |     0 |                             | NULL   | NULL                                                           |
+----+--------------+--------------+-----------------+-------+-----------------------------+--------+----------------------------------------------------------------+
[root@mysql-server56 3307]#

此时mysql数据的全量备份＋binlog增量备份，恢复完毕

觉得超哥笔记写的好的，弹幕、评论，刷一波666~~~

最后注意把时间改回去

[root@mysql-server56 ~]# ntpdate -u ntp.aliyun.com
21 Apr 18:50:18 ntpdate[12116]: step time server 203.107.6.88 offset 11650.696802 sec
[root@mysql-server56 ~]#

mysql备份架构总结

中小公司做法一般是，每天0点全量备份，数据按照日期备份到数据库本地，且进行定期如七天删除脚本。

主从复制架构，两台mysql服务器，机器A挂了，可以切换机器B，保证数据安全。

但是该方案，无法解决如drop、delete语句这样的删除问题（主从复制，是从库执行一遍主库的动作）

方案是可以通过从库的延迟复制解决。

练习

• 总结
• 数据库增量备份、全量备份
• 逻辑备份、物理备份
• mysql增量备份全流程
• mysql分库分表备份脚本开发