20251024- 使用shell脚本分库定时备份MySQL数据

#!/bin/bash

# 注意mysql和mysqldump命令使用绝对路径

# mkdir -p /data/backup_mysql
# touch /data/backup_mysql/backup_mysql.log

host="127.0.0.1"
port="3306"
user="root"
password="password"
backdir="/data/backup_mysql"
time=`date +%Y%m%d%H%M%S`
log="/data/backup_mysql/backup_mysql.log"

mkdir -p ${backdir}/${time}

for db in `/usr/bin/mysql -h$host -u$user -P$port -p$password -e"show databases"|egrep -v "information_schema|mysql|performance_schema|sys|Database"`
do
  /usr/bin/mysqldump  -h$host -u$user -P$port -p$password --skip-opt --quick --single-transaction --order-by-primary --hex-blob --routines --events --set-gtid-purged=OFF --skip-triggers --skip-extended-insert ${db}  > ${backdir}/${time}/${db}_${time}.sql
    [[ $? == 0 ]] && echo -e "\033[32m $time mysql backup success \033[0m" >> $${log} ||  echo -e "\033[31m $time mysql backup failed \033[0m" >> ${log}
done

find ${backdir} -name "*.sql.gz" -mtime +15 -type f -exec rm -rf {} \;
[[ $? == 0 ]] && echo -e "\033[32m $time mysql rmdir success \033[0m" >> ${log} ||  echo -e "\033[31m $time mysql rmdir failed \033[0m" >> ${log}

# chmod a+x /data/backup_mysql/mysqlbackup.sh
# 00 4 * * * /usr/bin/sh /data/backup_mysql/mysqlbackup.sh > /dev/null 2>&1
# systemctl restart crond.service

# 备份文件解压缩命令:gunzip ${db}_${time}.sql.gz,此时会出现一个以sql结尾的文件