MySQL数据库备份&还原
0 前言
我们日常工作生活中会对一些重要的资料进行备份场景,这也就是给自己留一条退路,在不可抗因素的情况下可以把这些重要的资料找回来。此外,我们也听过像“删库跑路”这样的段子,像这样的人为因素(或无意或有意),如果没有备份,那制成的损失是无法挽回的。毕竟,数据对于一个网站而是多么的重要。本文将以MySQL数据库来简单探讨一下备份和还原。【如有错误之处,请在下面评论指出,拜谢】
1 为什么要做备份?
首先就是为了数据的安全性,当原数据丢失时可以从备份中找出来大部分数据。数据丢失的情况:
- 硬件故障
- 软件故障
- 自然灾害
- 黑客攻击
- 误操作(占比最大)
还有哪些情况呢?
- 审计:当被卷入到一场扯皮的纠纷中,备份或许会成为你有力的证据。
- 测试:把部分生产数据通过备份导入到测试环境中,可以测试出好的效果。
2 怎么做备份?
2.1 备份相关的一些概念
2.1.1 还原和恢复的区别?
-
首先,还原是把数据还原到备份时的状态,而恢复是要把数据尽可能的恢复到异常出现前的状态。
-
其次,在原数据完全丢失或被破坏的情况下,恢复会包含了从备份还原数据的操作,以及从日志文件中恢复数据
-
此外,恢复除了处理数据的操作外,还会包括重启MySQL,预热服务器的缓存等步骤。
2.1.2 完全备份 / 增量备份 / 差异备份
2.1.3 物理备份 / 逻辑备份
2.1.4 热备 / 温备 / 冷备
-
热备份
- 特点:不影响数据库的读写操作(不加锁)
- 存储引擎的支持:MyISAM 不支持,InnoDB支持
-
温备份
- 特点:只影响数据库的写操作(加读锁)
- 存储引擎的支持:MyISAM 和 InnoDB 都支持
-
冷备份
- 特点:影响数据库的读写操作(部分表加写锁)
2.1.5 离线备份 / 在线备份
- 离线备份
- 优点
- 不用关心InnoDB缓冲池中的脏页或其它缓存
- 不用担心数据在备份过程中被修改
- 备份速度快
- 缺点
- 不能支持MySQL服务的高可用(都关闭了。。。)
- 优点
- 在线备份
- 优点
- 当然是支持MySQL服务的可用
- 缺点
- 会锁表(用 flush table with read lock)
- 备份耗时长
- 要考虑的性能问题
- 备份要多长时间,相应的要锁表多长时间
- 备份任务对机器的负载
- 所以要选一个业务处理低的时机来做备份(一般是凌晨)
- 优点
2.1.6 加锁 / 释放锁
在线备份,建议操作前先锁一下表(账号要有足够权限),以免在备份时相关的表有写操作等(导致备份中有数据不一致的情况)。
------------------ 锁住所有的表 start ------------------
-- 全局读锁,上了之后全局的表只能读,不能写
flush tables with read lock;
-- 锁表期间,我们可以通过命令查看到等待处理的写操作(正常备份时就别查看了)
show processlist;
-- 锁释放
unlock tables;
------------------- 锁住所有的表 end -------------------
-------------------- 锁部分的表 start ------------------
-- 给一张表上写锁(一般不用写锁,因为上了之后,这表的读操作会阻塞)
lock table vblog_user write;
-- 给一张表上读锁()
lock table vblog_user read;
-- 给多张表上锁
lock table vblog_user write, vblog_tag read;
-- 锁释放
unlock tables;
-------------------- 锁部分的表 end ------------------
2.2 逻辑备份&还原(mysqldump)
一般而言,最好在备库(MySQL的从节点)中做备份,这样不影响主库(MySQL的主节点)的性能。
2.2.1 可视化工具方式
本文用的是 DBeaver(一般可视化工具都有这个导出的功能,大同小异),默认不锁表。
-
在目标数据库图标上右击,选择导出工具 Dump database(其实也就是集成了 mysqldump)
-
选择要导出表后,选择执行的方法(是否要锁表)
2.2.2 系统命令行方式
-
备份指定数据库
# 格式: mysqldump [选项] 数据库名 > 目标文件名 mysqldump -uroot -p vblog > /etc/mysql/dump/dump_vblog_$(date -I).sql
-
备份指定数据库指定表(多个表用空格隔开)
# 格式: mysqldump [选项] 数据库名 [表名] > 目标文件名 mysqldump -uroot -p vblog vblog_user > /etc/mysql/dump/dump_vblog_user_$(date -I).sql
2.2.3 还原数据
系统命令行方式:(这里的vblog_tmp数据库是新创建的,我们把逻辑备份的数据还原进去)
# 格式: mysql [选项] 数据库名称 < 逻辑备份
mysql -uroot -p vblog_tmp < /etc/mysql/dump_vblog.sql
2.3 物理备份&还原
备份的内容:主来说数据存放目录下(linux的一般在 /var/lib/mysql 目录下)的文件都应该备份
- 数据 & 表定义(这个是主要的)
- 非显著数据(比如:二进制日志(binlog)、事务日志等)
- 代码(比如:触发器、存储过程等)
- 复制配置(比如:二进制日志(binlog)、中继日志(relay log)、日志索引文件(binlog.index)和.info文件。至少应该包含 show master status / show slave status 的输出。(如果有配置主从集群的话))
- 服务器配置
- 选定的操作系统文件
其实,离线备份下做物理备份是最好的:
- 不用关心InnoDB缓冲池中的脏页或其它缓存
- 不用担心数据在备份过程中被修改
- 备份速度快
如果不能做离线备份,那么做备份前还是参考上面的先加锁吧。
2.3.1 tar命令方式
# 完全备份(进到数据存放目录后)
tar -zcvf /home/data/backup_vblog_$(date -I).tar.gz ./*
# 还原备份(mysql数据存放目录:/var/lib/mysql)
tar -xvf /home/data/backup_vblog_2022-07-14.tar.gz -C /var/lib/mysql
# 增量备份(第一次备份snapshot.snap不存在,故第一次做的是完全备份,后续用同样的命令即可)
tar -g /home/data/snapshot.snap -zcpf /home/data/backup—_incre_vblog_$(date -I).tar.gz ./*
# 还原增量备份(按文件顺序恢复即可)
tar -zxpf /home/data/backup_incre_vblog_2022-07-14.tar.gz -C /var/lib/mysql
tar -zxpf /home/data/backup_incre_vblog_2022-07-13.tar.gz -C /var/lib/mysql
...
tar常用选项:
- -c -- create,tar打包,后缀一般为.tar
- -f -- file,后面接打包或者压缩后得到的文件名
- -p -- 保留备份数据的原本权限和属性
- -v -- 输出文件清单
- -x -- extract,解包命令,与-c对应
- -z -- gzip,用gzip工具自动识别压缩或解压
- -g -- 后面接增量备份的快照文件
- -C -- 指定解压缩的目录
- --exclude -- 排除不打包的目录或文件,支持正则
tar加密压缩
# 加密压缩(目标是当前目录下的所有文件,password--密码)
tar -czvf - ./* | openssl des3 -salt -k password -out /home/data/secure.tar.gz
# 解密解密(目的地是当前目录,password--密码)
openssl des3 -d -k password -salt -in /home/data/secure.tar.gz | tar zxvf -
des3 -- 3DES(或称为Triple DES)是三重数据加密算法(TDEA,Triple** Data Encryption Algorithm)块密码的通称 。
3 传输备份
备份做好后,大概率不会保存在原服务器上,需要传输到单独的服务器中保存。那就涉及到传输文件的方式了。
ftp来做文件的传输的话,需要安装vsftpd(即服务端),另外还要开放21端口。
经过考量,我决定用ssh的方式来做。ssh方式用的是scp这个组件,demo如下:
# 从服务器下载文件
# 服务器目标文件:/home/backup/bak_storage_2022-07-14.tar.gz
# 本地目录:/home/data
scp root@192.168.1.2:/home/backup/bak_storage_2022-07-14.tar.gz /home/data
# 从服务器下载整个目录
# 服务器目录目录:/home/backup
# 本地目录:/home/data
scp -r root@192.168.1.2:/home/backup /home/data
########################################################################################
# 上传文件到服务器
# 本地目标文件:/home/backup/bak_storage_2022-07-14.tar.gz
# 服务器目录: /home/data
scp /home/backup/bak_storage_2022-07-14.tar.gz root@192.168.1.2:/home/data
# 上传目录到服务器
# 本地目录:/home/backup
# 服务器目录: /home/data
scp -r /home/backup root@192.168.1.2:/home/data
4 备份脚本化
为备份写一些脚本是标准的做法(相关命令就比较固定),但由于对shell脚本等不太熟悉,这里就不贴出来了。
5 总结
- 数据库备份工作涉及的知识点多且复杂,比如存储引擎、事务、备份的工具、安全方面等等。
- 备份做得好,就算被删库也能把数据找回来,但如果做不好,连还原数据都成问题,更不用恢复数据。
参考资料
- 《高性能MySQL(第3版)》 - 电子工业出版社
- Linux tar命令高级用法——备份数据 https://zhuanlan.zhihu.com/p/83921782
- tar高阶操作之加密分卷压缩与解密压缩 https://www.cnblogs.com/wufj/p/14698759.html
注意:本文归作者所有,未经作者允许,不得转载