mysql自动备份脚本

目录

1.配置免密

2.备份脚本

3.定时任务

4.备份结果

---

1.配置免密

[root@host]vim /etc/my.cnf

[client]

user=root
password=PassW@rd
socket=/data/binglog/mysql.sock

2.备份脚本

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

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

#Delete the obsolete backupset
find $dumpDir -name "*.gz" -type f -mtime +5 -exec rm -f {} \;
find $extraDir -type f -mtime +3 -exec rm -f {} \;
#find $rootPath/logs -name "*.log" -type f -mtime +200 -exec rm -f {} \;


#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.gz
mysqldump --triggers --routines --events --set-gtid-purged=OFF --single-transaction $dbname | gzip > ${dumpDir}/${dumpfile}
done

if [ `date "+%d"`  == "01" ]
then
	#cp ${dumpDir}/*${daytime}*  $rootPath/monthly_backup/
	#cp ${extraPath}/*${daytime}*  $rootPath/monthly_backup/
fi

doy=`date +%j`
dow=`date +%u`  
if [ $dow == 0 ];then
	find $extraDir -type f  -mtime +3 -exec rm -f {} \;
	#innobackupex  --stream=tar  /backup/xtrabackup/ 2>/backup/logs/$logFile  gzip 1>/backup/xtrabackup/$str
	#xtrabackup database
	xtrabackup --backup --no-server-version-check --compress --log=$logFile --stream=xbstream --target-dir=$extraDir > $extraFile
	gzip $extraFile
	echo -e '\n\n\n'
fi

#transfer dump to remote host
#scp $dumpDir/*${daytime}.dmp.gz  192.168.136.198/backup/mysqldump

3.定时任务

chmod +x   /app/backup/scripts/mysql_backup.sh

cat >>/var/spool/cron/root<<eof

#MySQL backup
0 2  * * *   /bin/bash /app/backup/scripts/mysql_backup.sh >>/backup/logs/mysql_backup_\$(date "+\%Y\%m\%d").log 2>&1
eof
 

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

4.备份结果

[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