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 脚本了，这里我直接放出增量备份脚本代码：

#!/bin/bash
# Program
# use cp to backup mysql data everyday!
# History
# Path
BakDir=/mnt/www/mysql/backup/daily
BinDir=/usr/local/mysql/data
LogFile=/mnt/www/mysql/backup/zlbak.log
BinFile=/usr/local/mysql/data/mysql-bin.index
/usr/local/mysql/bin/mysqladmin -uroot -p密码 flush-logs
#这个是用于产生新的mysql-bin.00000*文件
Counter=`wc -l $BinFile |awk '{print $1}'`
NextNum=0
#这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的
for file in `cat $BinFile`
do
    base=`basename $file`
    #basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./
    NextNum=`expr $NextNum + 1`
    if [ $NextNum -eq $Counter ]
    then
        echo $base skip! >> $LogFile
    else
        dest=$BakDir/$base
        if(test -e $dest)
        #test -e用于检测目标文件是否存在，存在就写exist!到$LogFile去
        then
            echo $base exist! >> $LogFile
        else
            cp $BinDir/$base $BakDir
            echo $base copying >> $LogFile
         fi
     fi
done
echo `date +"%Y年%m月%d日 %H:%M:%S"` $Next 增量Bakup succ! >> $LogFile

最后通过配置 crontab，然后通过-e 来实现定时每日凌晨 3 点执行该增量备份脚本

#周一到周六凌晨3:00做增量备份
0 3 * * 1-6 root sh /mnt/www/mysql/binlogbak.sh

2.全量备份

全量备份没啥可说的，直接上代码：

#!/bin/bash
#全量备份
LogFile=/mnt/www/mysql/backup/qlbak.log
Date=`date +%Y%m%d_%H%M%S`
/usr/local/mysql/bin/mysqldump -uroot -p密码
数据库名>/mnt/www/mysql/backup/XXX$Date.sql
echo `date +"%Y年%m月%d日 %H:%M:%S"` $Next 全量Bakup succ! >> $LogFile

也是通过配置 crontab，然后通过-e 来实现定时每日凌晨 3 点执行该增量备份脚本

#每个星期日凌晨3:00执行完全备份脚本
0 3 * * 0 root sh /mnt/www/mysql/databak.sh

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 种:

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

pos 点应该如何查看呢
通过在 mysql 中执行 show binlog events in 'mysql-bin.000001'\G 命令，可查看到

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

3.还原总结

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

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