MySQL mysqldump 备份脚本(按照db.sql)
mysqldump逻辑备份,按照db.sql文件区分,并压缩
#! /bin/bash #35 02 * * * mysql /data/mysqldata/scripts/mysqldump_per_db.sh >> /data/mysql/backup/mysql_back1.log 2>&1 ############################################################ # Define the variables the script used for executing. #mysql backup by hongquan on 2017 #day=`date +%u` curHour=`date +%H` curDate=`date +'%Y%m%d'` backupDir=/data/mysqldata/backup mysqlHost=127.0.0.1 HOST_PORT=3306 logicuser=**** logicpass=*** logicalBackupDir=/data/mysqldata/backup/logical logfile=/data/mysqldata/backup/mysql_backup.log sockfile=/data/mysqldata/3306/mysql.sock MYSQL_PATH=/usr/local/mysql/bin MYSQL_CMD="${MYSQL_PATH}/mysql -u${logicuser} -p${logicpass} -h${mysqlHost} -S /data/mysqldata/${HOST_PORT}/mysql.sock" MYSQL_DUMP="${MYSQL_PATH}/mysqldump -u${logicuser} -p${logicpass} -h${mysqlHost} -P${HOST_PORT} -R -S \ ${sockfile} --single-transaction --master-data=2 --default-character-set=utf8 --set-gtid-purged=OFF -q " echo "NOW is : Backup type='logical'" >> ${logfile} echo "backup start" `date` >> ${logfile} echo >> ${logfile} #list="mysql aquarius" if [ ! -d "${backupDir}" ]; then mkdir -p "${backupDir}" echo -e "===${backupDir} does not exsits,created them!" >> ${logfile} fi if [ ! -d "${logicalBackupDir}" ]; then mkdir -p "${logicalBackupDir}" echo -e "===${logicalBackupDir} does not exsits,created them!" >> ${logfile} fi # -------------------------- logical backup with mysqldump -------------------------- echo -e "mysqldumpbk per db list= ${list} " >> ${logfile} for dbs in `${MYSQL_CMD} 2>/dev/null -e "show databases" | sed '1d' | egrep -v "information_schema|performance_schema|sys"` do echo -e "echo $dbs is appoint ; " >> ${logfile} ${MYSQL_DUMP} ${dbs} 2>/dev/null | gzip > ${logicalBackupDir}/${dbs}_${curDate}.sql.gz echo -e " **** database :${dbs} backup stop_time :`date +%F' '%T' '%w` **** \n">> ${logfile} echo -e "**** backup file size:`du -sh ${logicalBackupDir}/`**** \n" >> ${logfile} done echo -e "\n=== backup dened at `date +%F' '%T' '%w`===\n" >> ${logfile}