记一次 mysql 数据库定时备份

环境：Centos 7.9

数据库：mysql 8.0.30

需求：生产环境 mysql 数据（约670MB）备份。其中存在大字段、longblob字段

参考博客：Linux环境下使用crontab实现mysql定时备份 - 知乎

一、数据库备份

1. 备份脚本。创建 back.sh，文件内容如下

BakDir=/opt/back/mysql
LogFile=/opt/back/mysql/mysql_backup.log
Date=`date +%Y%m%d`
Begin=`date +"%Y年%m月%d日 %H:%M:%S"`
cd $BakDir
DB=test
DumpFile=$DB$Date.sql
GZDumpFile=$DB-$Date.sql.tar.gz
 
# 检查备份目录是否存在
if [ -d "$BakDir" ]
then  echo "[INFO]开始执行数据库备份程序....."
else  mkdir $BakDir
fi
 
# 进入指定目录开始执行备份逻辑 -- 在使用crontab执行命令时，会因为绝对路径问题导致导出数据为null，需在mysql绝对路径下执行
# 下面的/opt/mysql/bin为我这边的mysql安装目录的bin目录
# 执行备份命令
echo "[INFO] mysqldump -uroot -p123456 --databases $DB > $DumpFile" >> $LogFile
mysqldump -uroot -p123456 --databases $DB > $DumpFile
 
mv $DumpFile $BakDir
cd $BakDir
# 对备份数据进行压缩
echo "[INFO] tar czvf $GZDumpFile $DumpFile" >> $LogFile
tar czvf $GZDumpFile $DumpFile
 
# 删除备份的sql文件
echo "[INFO] rm $DumpFile" >> $LogFile
rm $DumpFile
 
# 备份 备份的sql文件到指定目录
echo "[INFO] mv $GZDumpFile $BakDir" >> $LogFile
mv $GZDumpFile $BakDir
 
Last=`date +"%Y年%m月%d日 %H:%M:%S"`
echo 开始:$Begin 结束:$Last $GZDumpFile 执行成功 >> $LogFile
#清理备份，保留30天的备份
find $BakDir/* -mtime +30 -exec rm {} \;
echo "[INFO]备份任务执行结束....."

2. 步骤介绍

     1）首先我们通过mysqldump命令将数据库备份下来，按照当前日期进行命名；

     2）然后将数据库备份文件移动到指定位置；

     3）对数据库备份文件进行文件压缩并删除备份文件

     4）清理一个月前的备份文件，任务执行结束；

3. 原理介绍

     主要使用mysqldump命令：

mysqldump -uusername -ppassword --databases databasename  > dumpfilename

     还可指定下列参数：

--databases databasename # 指定数据库
--tables tablename # 指定表
--ignore-table=tablename # 忽略表
--where='id=1' # 筛选条件

二、定时执行备份

1. 软件检查

     使用 cron 创建执行计划，cron 是一个 linux 下的定时执行工具。先检查 cron 服务是否正常启动启动

service crond status   // 查看服务状态 

     如果没有启动，则使用下面【启动服务】命令启动

service crond start    //启动服务
service crond stop     //关闭服务
service crond restart  //重启服务
service crond reload   //重新载入配置
service crond status   //查看服务状态 

 2. 创建执行计划

     每天凌晨3点0分执行脚本mysql_buckup.sh。 不熟悉规则的可以使用可视化工具生成：Cron - 在线Cron表达式生成器

crontab -e
# 按 i 进行编辑，输入
0 3 * * * /opt/back/mysql/backup.sh
# 然后 wq 保存，可以通过 crontab -l 查看已配置的定时任务
crontab -l

3. 查看执行计划日志

tail -fn 200 /var/log/cron

三、导出SQL脚本执行恢复数据遇到问题

1. 2006-MySQL server has gone away

     参考博客：mysql错误码:2006-MySQL server has gone away详解_2006 - mysql server has gone away_我有我向往的生活的博客-CSDN博客

      问题分析：导出的SQL文件单条插入语句太长，超出 mysql 服务器端和客户端在一次传送数据包的过程当中最大允许的数据包大小，即 max_allowed_packet。

      解决方案：临时增大这个值即可

mysql> show global variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)
mysql> set global max_allowed_packet=1024*1024*16;

2. [ERR] 1153 - Got a packet bigger than 'max_allowed_packet' bytes

     与上一个问题解决方案一致

3. [ERR] 1118 - Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

      参考博客：
MySQL导入数据库1118错误解决方案[ERR] 1118 - Row size too large (＞ 8126). Changing some columns to TEXT or BLOB_pymysql.err.operationalerror: (1118, 'row size too_大王来巡山了的博客-CSDN博客

      问题分析：mysql-innodb是按照page存储数据的，每个page max size是16k，然后每个page两行数据，所以每行最大8k数据。

      解决方案：查看严格模式是否开启，如果开启，关闭即可。

4. [ERR] 1449 - The user specified as a definer ('root'@'%') does not exist 

      问题分析：root 用户主机没有配置成 %

      解决方案：开启 root 用户远程访问即可

update user set host='%' where user ='root';