MySQL auto backup

 

本脚本中mysqldump命令没有使用用户和密码参数，因为在my.cnf文件中已经配置了用户名和密码。

准备路径

mkdir -p /backup/{scripts,logs,mysqldump,xtrabackup}

 

vi  /backup/scripts/mysql_backup.sh

#!/bin/bash
export PATH=$PATH:/mysql/bin
dumpBakPath=/backup/mysqldump
extraBakPath=/backup/xtrabackup
IPsuffix=`hostname -I | awk -F. '{print $3 $4}'| awk -F' ' '{print $1}'`
daytime=`date +%Y%m%d`
extraFile="${extraBakPath}/${IPsuffix}_xtrabackup_${daytime}.xbstream"
logFile="/backup/logs/xtrabackup_${daytime}.log"
mkdir -p /backup/{mysqldump,xtrabackup,logs,scripts}

#Delete the obsolete backupset
find $dumpBakPath -name "*.gz" -type f -mtime +5 -exec rm -f {} \;
find $extraBakPath -name "*.gz" -type f -mtime +3 -exec rm -f {} \;
find /backup/logs -name "*.log" -type f -mtime +100 -exec rm -f {} \;

#xtrabackup database
xtrabackup --backup --no-server-version-check --compress --log=$logFile --stream=xbstream --target-dir=$extraBakPath > $extraFile
#gzip $extraFile
echo -e '\n\n\n'

sleep 10
#mysqldump backup database
dblist=`mysql -uroot -Bse "SELECT schema_name FROM information_schema.schemata where schema_name not in ('information_schema','mysql','performance_schema','sys')"`
echo $dblist | sed 's/ /\n/g' | while read dbname
do
echo "`date` The current database name is: $dbname."
dumpfile=${dbname}-${IPsuffix}_${daytime}.dmp
mysqldump --triggers --routines --events --set-gtid-purged=OFF --single-transaction $dbname > ${dumpBakPath}/${dumpfile}
gzip -f ${dumpBakPath}/${dumpfile}
done

 添加定时任务

[root@HKDCL020010 scripts]# crontab -l
#mysql backup
0 2  * * *   /bin/bash /backup/scripts/mysql_backup.sh >> /backup/logs/mysql_backup_$(date "+\%Y\%m\%d").log 2>&1

查看结果

[root@HKDCL020010 scripts]# ll -h /backup/mysqldump/
total 843M
-rw-r----- 1 root root 4.1M Apr  8 11:36 clio_sso-2010_20210408_1136.dmp.gz
-rw-r--r-- 1 root root 4.1M Apr  9 01:00 clio_sso-2010_20210409_0100.dmp.gz
-rw-r--r-- 1 root root 4.1M Apr 10 01:00 clio_sso-2010_20210410_0100.dmp.gz
-rw-r--r-- 1 root root 4.1M Apr 11 01:00 clio_sso-2010_20210411_0100.dmp.gz
-rw-r--r-- 1 root root 4.1M Apr 12 01:00 clio_sso-2010_20210412_0100.dmp.gz
-rw-r--r-- 1 root root 4.1M Apr 13 01:00 clio_sso-2010_20210413_0100.dmp.gz