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分库分表备份脚本开发