MySQL mysqldump与innobackupex 组合备份
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL mysqldump与innobackupex 组合备份，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4686字，纯文字阅读大概需要7分钟。
内容图文
此脚本，在01点进行一次逻辑全备份，03点进行一次物理全备份，中午12点进行一次增量物理备份
#! /bin/bash
#05 01,03,12 * * * mysql /data/mysqldata/scripts/mysql_backup_dump_xtra.sh >> /data/mysql/backup/log/mysql_back2.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
defaultFile=/data/mysqldata/backup
mysqlHost=127.0.0.1
port=3306
logicuser=**
logicpass=**
logicalBackupDir=/data/mysqldata/backup/logical
fullBackupDir=/data/mysqldata/backup/full
incBackupDir=/data/mysqldata/backup/inc
logfile=/data/mysqldata/backup/mysql_backup.log
sockfile=/data/mysqldata/3306/mysql.sock
full_inbkpex_day=/data/mysqldata/backup/full/full_ipx$curDate$curHour
inc_inbkpex=/data/mysqldata/backup/inc/inc_ipx$curDate$curHour
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 "
case ${curHour} in
01)
type=logical
;;
03)
type=full
;;
12)
type=inc
;;
"*")
exit
esac
echo "NOW is : Backup type=$type" >> ${logfile}
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
if [ ! -d "${fullBackupDir}" ]; then
mkdir -p "${fullBackupDir}"
echo -e "===${fullBackupDir} does not exsits,created them!" >> ${logfile}
fi
if [ ! -d "${incBackupDir}" ]; then
mkdir -p "${incBackupDir}"
echo -e "===${incBackupDir} does not exsits,created them!" >> ${logfile}
fi
extra_lsndir=${backupDir}
ori_conf_file=/data/mysqldata/${port}/my.cnf
new_conf_file=${backupDir}/my_${port}_`date +%Y%m%d%H%M%S`.cnf
echo "backup start" `date` >> ${logfile}
echo -e "===first cp my.cnf file to backup directory ===" >> ${logfile}
/bin/cp ${ori_conf_file} ${new_conf_file}
echo >> ${logfile}
if [[ "${type}" = "logical" ]]; then
# -------------------------- 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
elif [[ "${type}" = "full" ]]; then
# -------------------------- full backup with xtrabackup --------------------------
cd ${backupDir}
#rm -rf /data/mysql/backup/full/
if [ ! -d "${full_inbkpex_day}" ]; then
mkdir -p "${full_inbkpex_day}"
echo -e "===${full_inbkpex_day} does not exsits,created them!" >> ${logfile}
fi
#tar -zcvf $tar_full.tar.gz ${full_inbkpex_day}
cd ${full_inbkpex_day}
/usr/bin/innobackupex --defaults-file=${ori_conf_file} --user=${logicuser} --password=${logicpass} \
--host=${mysqlHost} --port=${port} --no-timestamp --extra-lsndir=${extra_lsndir} --stream=tar ${backupDir} \
2>/tmp/full_ipx$curDate$curHour.log | gzip 1>${full_inbkpex_day}/`date +%Y%m%d%H%M%S`.tar.gz
echo -e "mysql full backup full_ipx$curDate$curHour" >> ${logfile}
elif [[ "${type}" = "inc" ]]; then
# -------------------------- incremental backup with xtrabackup --------------------------
if [ ! -d "${inc_inbkpex}" ]; then
mkdir -p "${inc_inbkpex}"
echo -e "===${inc_inbkpex} does not exsits,created them!" >> ${logfile}
fi
/usr/bin/innobackupex --defaults-file=${ori_conf_file} --user=${logicuser} --password=${logicpass} \
--host=${mysqlHost} --port=${port} --no-timestamp --extra-lsndir=${extra_lsndir} --incremental \
--incremental-basedir=${backupDir} ${inc_inbkpex} 2>/tmp/inc_ipx$curDate$curHour.log
echo -e "mysql inc backup inc_ipx$curDate$curHour" >> ${logfile}
else
echo -e "wrong backup type!" >> ${logfile}
exit 1
fi
#echo -e "----find expired backup and delete those files ----">> ${logfile}
#for tfile in $(/usr/bin/find ${backupDir}/ -mtime +7)
#do
# if [ -d $tfile ] ; then
# rmdir $tfile
# elif [ -f $tfile ]; then
# #rm -f $tfile
# echo -e "---not backupDir found ----">> ${logfile}
# fi
# echo -e "----delete file:$tfile ----">> ${logfile}
#done
echo -e "\n=== backup dened at `date +%F' '%T' '%w`===\n" >> ${logfile}
内容总结
以上是互联网集市为您收集整理的MySQL mysqldump与innobackupex 组合备份全部内容，希望文章能够帮你解决MySQL mysqldump与innobackupex 组合备份所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】