MySQL数据库备份&还原

rollo 1年前 ⋅ 325 阅读

MySQL数据库备份&还原

0 前言

我们日常工作生活中会对一些重要的资料进行备份场景,这也就是给自己留一条退路,在不可抗因素的情况下可以把这些重要的资料找回来。此外,我们也听过像“删库跑路”这样的段子,像这样的人为因素(或无意或有意),如果没有备份,那制成的损失是无法挽回的。毕竟,数据对于一个网站而是多么的重要。本文将以MySQL数据库来简单探讨一下备份和还原。【如有错误之处,请在下面评论指出,拜谢】

1 为什么要做备份?

首先就是为了数据的安全性,当原数据丢失时可以从备份中找出来大部分数据。数据丢失的情况:

  • 硬件故障
  • 软件故障
  • 自然灾害
  • 黑客攻击
  • 误操作(占比最大)

还有哪些情况呢?

  • 审计:当被卷入到一场扯皮的纠纷中,备份或许会成为你有力的证据。
  • 测试:把部分生产数据通过备份导入到测试环境中,可以测试出好的效果。

2 怎么做备份?

2.1 备份相关的一些概念

2.1.1 还原和恢复的区别?

  • 首先,还原是把数据还原到备份时的状态,而恢复是要把数据尽可能的恢复到异常出现前的状态。

  • 其次,在原数据完全丢失或被破坏的情况下,恢复会包含了从备份还原数据的操作,以及从日志文件中恢复数据

  • 此外,恢复除了处理数据的操作外,还会包括重启MySQL,预热服务器的缓存等步骤。

2.1.2 完全备份 / 增量备份 / 差异备份

1657735286344.png

2.1.3 物理备份 / 逻辑备份

1657788760981.png

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) 1657707702865.png

  • 选择要导出表后,选择执行的方法(是否要锁表) 1657708833514.png

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 备份脚本化

1657788974286.png

为备份写一些脚本是标准的做法(相关命令就比较固定),但由于对shell脚本等不太熟悉,这里就不贴出来了。

5 总结

  • 数据库备份工作涉及的知识点多且复杂,比如存储引擎、事务、备份的工具、安全方面等等。
  • 备份做得好,就算被删库也能把数据找回来,但如果做不好,连还原数据都成问题,更不用恢复数据。

参考资料

  • 《高性能MySQL(第3版)》 - 电子工业出版社
  • Linux tar命令高级用法——备份数据 https://zhuanlan.zhihu.com/p/83921782
  • tar高阶操作之加密分卷压缩与解密压缩 https://www.cnblogs.com/wufj/p/14698759.html

全部评论: 0

    我有话说:

    目录