MySQL 自动备份表结构、备份数据
1、备份脚本编写步骤
- 定义相关变量
- 新建目录及授权
- 执行导出数据库dmup文件命令
- 压缩打包
- 删除原始文件
- 同步分发文件到备份服务器上
2、备份脚本代码
bkdate=$(date +%Y%m%d)
BACKDIR="/home/user1/dbbackup"
BackFileName=""
hh=$(date +%H)
mm=$(date +%M)
now=$bkdate$hh$mm
DBNAME1="dev"
uid=user1
pa="abc@123"
backname=backdb
backuphost=127.0.0.1
mkdir $BACKDIR
mkdir $BACKDIR/$bkdate
chmod 755 $BACKDIR
chmod 755 $BACKDIR/$bkdate
cd /usr/local/mysql8/bin
echo 开始数据备份中
./mysqldump -u$uid -p$pa $DBNAME1 -S /tmp/mysql.sock > $BACKDIR/$bkdate/$DBNAME1$now".dump"
cd $BACKDIR
echo $now
pwd
tar -czvf $BackFileName.$now"_"$backname.tar.gz ./$bkdate/$BackFileName*".dump"
echo "打包中*.dump"
echo 删除临时目录
cd $BACKDIR/$bkdate/
pwd
rm -f $DBNAME1$now".dump"
cd $BACKDIR
scp -r $BackFileName.$now"_"$backname.tar.gz $backuphost:/home/user1/dbbackup/backup
echo 同步数据备份文件到$backuphost服务器dbbackup/backup
echo 完成数据备份