mysql数据库异地备份脚本

最新推荐文章于 2024-06-03 13:35:25 发布

原创最新推荐文章于 2024-06-03 13:35:25 发布 · 1.7k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

mysql 专栏收录该内容

47 篇文章

订阅专栏

本文提供了一套详细的MySQL数据库异地备份脚本，通过定时任务确保数据的安全性和可靠性。脚本首先检查数据库使用情况，避免在高负载下进行备份，然后逐个备份指定数据库，并记录备份时间和位置，最后压缩备份文件并清理临时文件。适用于需要实现数据异地灾备的企业级应用。

mysql数据库存储在一个机器下，风险成本太高，需要做好异地备份，降低数据风险，以下是数据库异地备份的脚本

#!/bin/bash
precheck () {
n=0
while [ $n -le 60 ]
do
${mysqlhome}/bin/mysql -P ${myport} -u ${myuser} -p${mypass} -h 10.3.51.203 << EOF > In_use.tmp
show OPEN TABLES where In_use > 0;
exit
EOF
reto=$?
if [ "`cat In_use.tmp`" != "" -a $reto -eq 0 ]
then
sleep 30
if [ $n -lt 60 ]
then
let n=$n+1
echo "retry $n"
cat In_use.tmp
continue
else
echo "give up after 60 times retry"
exit
fi
else
echo "clear, start backup now"
break
fi
let n=$n+1
sleep 30
done
}

dat=`date +%Y-%m-%d`
day=`date +%d`
month=`date +%Y%m`
datime=`date +'%Y-%m-%d %H:%M:%S'`
bkhome=/home/mysqlbackup
mysqlhome=/usr/local/mysql
#!/bin/bash
precheck () {
n=0
while [ $n -le 60 ]
do
${mysqlhome}/bin/mysql -P ${myport} -u ${myuser} -p${mypass} -h 10.3.51.203 << EOF > In_use.tmp
show OPEN TABLES where In_use > 0;
exit
EOF
reto=$?
if [ "`cat In_use.tmp`" != "" -a $reto -eq 0 ]
then
sleep 30
if [ $n -lt 60 ]
then
let n=$n+1
echo "retry $n"
cat In_use.tmp
continue
else
echo "give up after 60 times retry"
exit
fi
else
echo "clear, start backup now"
break
fi
let n=$n+1
sleep 30
done
}

dat=`date +%Y-%m-%d`
day=`date +%d`
month=`date +%Y%m`
datime=`date +'%Y-%m-%d %H:%M:%S'`
bkhome=/home/mysqlbackup备份地址
mysqlhome=/usr/local/mysql安装地址
mybin=${mysqlhome}/bin
bkdir=${bkhome}/mysql_${dat}_bk
myuser=root用户名
mypass=XXXXX密码
myport=3306
date
rm -rf $bkdir
mkdir $bkdir
echo ${datime} > ${bkdir}/bktime.t

while read db
do
mkdir ${bkdir}/${db}
precheck
${mybin}/mysqldump -P ${myport} -u ${myuser} -p${mypass} -h 10.3.51.203 --opt --hex-blob --force --single-transaction --master-data=2 -R $db > $bkdir/${db}/${db}.sql
grep "CHANGE MASTER TO" ${bkdir}/${db}/${db}.sql | sed 's/-- CHANGE MASTER TO //g' | sed 's/, /;\n/' > ${bkdir}/${db}/position
/bin/gzip $bkdir/${db}/${db}.sql
date
echo "backup $db is done."
done << Eaf
数据库1
数据库2
数据库3
Eaf

if [ $day -eq 28 ]
then
cp -r ${bkhome}/mysql_${dat}_bk ${bkhome}/month_bk_${month}
fi