mysql text backup,mysql 全库备份方法及html显示

mysql备份全库脚本及相应脚本

#!/bin/bash

backup_path=/home/backup/mysql_backup/`date +%Y%m%d%H%M`

mkdir -p ${backup_path}

dt=`/usr/bin/date +%D-%T`

find /home/backup/mysql_backup/ -ctime +5 -exec rm -rf {} \;

host=192.168.1.1

mysqluser=abc

mysqlpswd=abc

## 备份数据库

for dbname in `mysql -h$host -u$mysqluser -p$mysqlpswd -e “show databases”|grep -v Database`

do

if [ “$dbname” == “information_schema” ]

then

mysqldump -h $host -u$mysqluser -p$mysqlpswd –single-transaction -E ${dbname} > ${backup_path}/${dbname}.sql

cd ${backup_path}

tar -zcvf ${dbname}.tar.gz ${dbname}.sql –remove-files

elif [ “$dbname” == “performance_schema” ]

then

mysqldump -h $host -u$mysqluser -p$mysqlpswd –single-transaction –quick –databases -E ${dbname} > ${backup_path}/${dbname}.sql

cd ${backup_path}

tar -zcvf ${dbname}.tar.gz ${dbname}.sql –remove-files

else

mysqldump -h $host -u$mysqluser -p$mysqlpswd -E ${dbname} > ${backup_path}/${dbname}.sql

cd ${backup_path}

tar -zcvf ${dbname}.tar.gz ${dbname}.sql –remove-files

fi

done

## backup to nfs同步目录

/usr/bin/rsync -avz –delete $backup_path 192.168.1.2:/data/DataBackup/mysql/192.168.1.3

#把备份结果汇总到192.168.1.3机器的/opt/script/bak.log文件中

cd ${backup_path}

for dbsname in `ls -lth|awk ‘{print $9}’`

do

size=`ls -lth|grep -v total|grep $dbsname|awk ‘{print $5}’`

ssh root@192.168.1.3 “echo $dt  $size  $dbsname  mysql  192.168.1.2  >>/opt/script/bak.log”

done

192.168.1.3服务器上的脚本如下：

#!/bin/bash

file=”/opt/script/bak.log”

cp /opt/script/bak.htmlbak /opt/script/bak.html

cat $file |while read rq size dbname type ip

do

sed -i “/\/table/i

$rq$size$dbname$type$ip192.168.1.3” /opt/script/bak.html

done

nosql=`cat $file|grep mysql`

mosql=`cat $file|grep mongodb`

if [ “$nosql” == “” ]

then

sed -i “/\/body/i注意:没有备份mysql库的计划任务” /opt/script/bak.html

elif [ “$mosql” == “” ]

then

sed -i “/\/body/i注意:没有备份mongodb库的计划任务” /opt/script/bak.html

fi

>$file

#发邮件

cat /opt/script/bak.html |/usr/bin/mutt -s “备份记录” -e ‘set content_type=”text/html”‘ abc@abc.com

bak.htmlbak文件内容：

备份记录

#thetable{

background-color:green;

font-size:12px;

}

#thetable th{

text-align:center;

background-color:#CCF;

height:30px;

line-height:30px;

}

#thetable td{

width:150px;

height:30px;

line-height:30px;

text-align:center;

background-color:#FFF;

}

备份日期	备份文件大小	备份文件名	数据源类型	备份源	备份地址

Originally posted 2020-02-23 16:14:27.