mysql备份脚本--mysqlpump

最新推荐文章于 2024-07-19 03:41:59 发布

lijun_work

最新推荐文章于 2024-07-19 03:41:59 发布

阅读量186

点赞数

分类专栏： mysql 文章标签： linux

本文链接：https://blog.csdn.net/lijun_work/article/details/106193868

版权

mysql 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

本文介绍了如何在Linux环境中使用mysqlpump工具来创建MySQL数据库的备份脚本，详细阐述了备份过程和相关参数的设置，为数据库管理提供可靠的数据安全保障。

摘要由CSDN通过智能技术生成

#!/bin/bash
set -eu
exclude_acc_tables=XXXXX
exclude_tms_tables=XXXXX
exclude_tms_string=''
exclude_acc_string=''
for tms_tb in "${exclude_tms_tables[@]}"
do
exclude_tms_string+=" --exclude-tables=${tms_tb}"
done
for acc_tb in "${exclude_acc_tables[@]}"
do 
exclude_acc_string+=" --exclude-tables=${acc_tb}"
done
backfiledir=/logs/mysqlbackupfile
backuplist=/data/scripts/mysqlscripts/dumplist
backuptype=mysqlpump
center_DB_IP=XXXXXX
center_DB_Port=XXXXXX
basedir=/usr/local/mysql/bin
username=root
passwd=XXXXXXX
for dbname in `cat $backuplist`
do
remoteIP=`echo $dbname|cut -d '_' -f 1`
BACKUPStarttime=`date +%Y%m%d%H%M%S`
if [ "$remoteIP" == "XXXXXX" ]; then
$basedir/mysqlpump --user=$username --password=$passwd --add-drop-database --host=$remoteIP -A --single-transaction --default-parallelism=8 --exclude-databases=mysql,sys,information_schema,performance_schema --default-character-set=utf8 ${exclude_tms_string} --max-allowed-packet=1024M | gzip > $backfiledir/$dbname.$BACKUPStarttime.gz 
BACKUPendtime=`date +%Y%m%d%H%M%S`
backupfileSize=`du -sh $backfiledir/$dbname.$BACKUPStarttime.gz |cut -f 1`
if [ "$backupfileSize" = "0" -o "$backupfileSize" = "4.0K" ];then
echo $dbnam backup failed! > $backfiledir/backup_result
/usr/bin/python /data/scripts/mysqlscripts/mysql_backup_result.py
else 
echo "$dbname backup successful!" > $backfiledir/backup_result
rsync -P  $backfiledir/$dbname.$BACKUPStarttime.gz rsync_backup@XXXXXX::backup/mysqlbak --password-file=/etc/rsync.password
/usr/bin/python /data/scripts/mysqlscripts/mysql_backup_result.py
fi
else
BACKUPStarttime=`date +%Y%m%d%H%M%S`
$basedir/mysqlpump --user=$username --password=$passwd --host=$remoteIP -B accountdb --single-transaction --default-parallelism=8 --default-character-set=utf8 ${exclude_acc_string} --max-allowed-packet=1024M | gzip > $backfiledir/$dbname.$BACKUPStarttime.gz
BACKUPendtime=`date +%Y%m%d%H%M%S`
backupfileSize=`du -sh $backfiledir/$dbname.$BACKUPStarttime.gz |cut -f 1`
if [ "$backupfileSize" = "0" -o "$backupfileSize" = "4.0K" ];then
     echo $dbname backup failed! > $backfiledir/backup_result
	 /usr/bin/python /data/scripts/mysqlscripts/mysql_backup_result.py
else 
     echo "$dbname backup successful!" > $backfiledir/backup_result
     rsync -P  $backfiledir/$dbname.$BACKUPStarttime.gz rsync_backup@XXXXX::backup/mysqlbak --password-file=/etc/rsync.password
     /usr/bin/python /data/scripts/mysqlscripts/mysql_backup_result.py
fi
fi
done
find $backfiledir/ -mtime +10 |xargs rm -rf