linux 中实现 mysql 数据库备份

👶🏻mysql 数据库备份

这段时间在搞数据库备份的事情，发个文章来记录一下

一、方案选择

先说最终实现的方案是逻辑备份，利用 mysql 官方自带的工具 mysqldump，然后用 crontab 命令做定时

• 之所以没有用数据库备份的工具，如 xtrabackup，是因为公司服务器没有外网，无法用 wget 从相关链接下载安装包，所以手动装的话会很麻烦 😂
• 刚开始也想过通过 java 代码去实现，通过 Process 的 Runtime.getRuntime().exec(commands)去执行数据库备份命令，然后通过 @Schedule 来实现定时，然后发现这么做是不是有点化蛇添足，还不如直接在服务器上操作

第一种：按天全备

每天的凌晨 3 点定时全量备份数据，根据空间情况保留全备份数，可以只保留 7 天内的备份数据

1、优点：恢复数据时需要的数据文件数量少，恢复时间短，维护成本低。

2、缺点：每天一个全备，占用空间多，占用系统资源多，经常备份会影响用户体验。
第二种：按周全备

第二种：按周全备

1、优点：每周仅有一个完整备份，因此占用磁盘总空间小，占用系统资源少，备份次数少，用户体验好一些。

2、缺点：恢复时数据文件多，导致恢复麻烦，维护成本高，恢复时间长。

两种方案都有利有弊，最后决定综合一下，按周来全量备份，按天来增量备份

二、实现过程

1.增量备份

• 登录 mysql 查看 binlog 日志的状态，输入 show variables like ‘%log_bin%’;查看 binlog 为 off 关闭状态
• 开启 mysql binlog 日志，进入 mysql 配置文件(vi /etc/my.cnf) 在 mysqld 区域内添加如下内容
  ①server-id = 1(单个节点 id)
  ②log-bin= /var/lib/mysql/mysql-bin(位置一般和 mysql 库文件所在位置一样) ③expire_logs_days = 10(表示此日志保存时间为 10 天)
  重启 mysqld,再次查看 binlog 日志开启状态为 ON
• Binlog 日志包括两类文件；第一个是二进制索引文件(后缀名为.index),第二个为日志文件(后缀名为.00000*),记录数据库所有的 DDL 和 DML(除了查询语句 select)语句事件
• 查看所有 binlog 日志文件列表:show master logs;
• 查看最后一个 binlog 日志的编号名称及其最后一个操作事件 pos 结束点的值：show master status;
• Flush logs 刷新日志，此刻开始产生一个新编号的 binlog 文件，例如：
  每当 mysqld 服务重启时，会自动执行刷新 binlog 日志命令，mysqldump 备份数据时加-F 选项也会刷新 binlog 日志
• 清空所有 binlog 日志命令:reset master;

做好这些前置工作，那么下面就可以编写 shell 脚本了，这里我直接放出增量备份脚本代码：

2.全量备份
全量备份没啥可说的，直接上代码：
3.解释说明
数据备份 shell 脚本放在/mnt/www/mysql 目录下，databak.sh 是全量备份脚本，binlogbak.sh 是增量备份脚本，脚本打印日志放在/mnt/www/mysql/backup 中的 bak.log 文件中（全量脚本日志是 qlbak.log，增量脚本日志是 zlbak.log），全量备份数据放在/mnt/www/mysql/backup 目录下，增量备份数据放在/mnt/www/mysql/backup/daily 目录下，定时设置的是，全量备份在每周日凌晨 3 点执行一次，增量备份是周一到周六的每天凌晨 3 点执行一次，到此为止，备份就设置完成了。
三、数据还原
备份是成功了，那么在数据出现问题的时候，怎么还原呢？
1.全量恢复
mysql -uroot -p密码 数据库名 < XXX220230517_092126.sql

这里 XXX220230517_092126.sql 就是生成的全量备份 sql 文件
2.增量恢复
恢复命令语法
mysqlbinlog  --start-position=1256  --stop-position=1279 --database=ccsy binlog.000002 | mysql -uroot -p123456
--start-position=1256                  起始pos点
--stop-position=1279                   结束pos点
--start-datetime="2021-03-29 09:18:54" 起始时间点
--stop-datetime="2021-03-29 10:30:54"  结束时间点
--database=ccsy                        指定只恢复ccsy数据库

执行 mysqlbinlog 时可能会出现 mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4' 问题
原因是 mysqlbinlog 这个工具无法识别 binlog 中的配置中的 default-character-set=utf8mb4 这个指令。
解决方法有 2 种:

在 MySQL 的配置 my.cnf 中将 default-character-set=utf8mb4 修改为 character-set-server = utf8mb4，但是这需要重启 MySQL 服务, 比较麻烦
加参数 --no-defaults

pos 点应该如何查看呢

通过在 mysql 中执行 show binlog events in 'mysql-bin.000001'\G 命令，可查看到



通过分析一个 sql 操作 pos 从多少到多少，然后还原的时候带入再执行一遍即可
3.还原总结

如果个别数据被误修改或者被误删除，则通过查看 binlog 文件定位到具体的 pos 位置然后手动还原即可。
如果是批量数据被误操作，则可以先通过全量还原至上周末的数据，然后再根据每天的 binlog 一步一步执行增量还原，直到误操作前的节点。

以上就是我对数据库备份所有的学习与思考，如果各位有什么更好的方案，欢迎评论告诉我 👦🏻