mysql全量备份脚本

1、全量备份

1.1、添加备份脚本

[root@localhost ~]# vim /mnt/data/backup/mysql/mysql_m_bak_full.sh

#!/bin/bash
#mysql 全量备份
time=`date +%Y%m%d`
now=`date +%F' '%T`
etime=`date -d '-7 day' +%Y%m%d`
#备份文件路径
mkdir -p /mnt/data/backup/mysql/full_back/$time
mkdir -p /mnt/data/backup/mysql/full_back/$time/log
backupdir="/mnt/data/backup/mysql/full_back/$time"
log_dir="/mnt/data/backup/mysql/full_back/$time/log"
user=root
#host=localhost
#port=3308
pass=123456echo "$now Begin backup mysql deploybase" >> $log_dir/$time.log
sleep 2
#mysqldump and socket配置绝对路径
/mnt/data/mysql/bin/mysqldump -u${user} -p${pass} --socket=/mnt/data/mysql/mysql.sock --all-databases > $backupdir/master_bak_$time.sql
if [ $? -eq 0 ]
then
echo "$now mysql 连接成功 " >> $log_dir/$time.log
echo "$now 开始执行压缩......" >> $log_dir/$time.log
tar -zcf $backupdir/master_bak_$time.tar.gz -C $backupdir master_bak_$time.sql
    if [ $? -eq 0 ]
    then
    echo  "$now 文件压缩成功......" >> $log_dir/$time.log
    rm -rf $backupdir/master_bak_$time.sql
    size=`du -sh $backupdir/master_bak_$time.tar.gz |awk '{print $1}'`
    echo "$now backup mysql deploybase successfully completed" >> $log_dir/$time.log
    echo "$now deploy-数据库数据备份成功——全量备份,ip:127.0.0.1,文件大小为:$size" > /tmp/tmp.log
    rm -rf /tmp/tmp.log
    else
    echo  "$now 文件压缩失败......" >> $log_dir/$time.log
    echo "$now deploy-数据库数据备份成功——全量备份,但文件压缩失败,ip:127.0.0.1" > /tmp/tmp.log
    rm -rf /tmp/tmp.log
    fi
else
echo "$now backup mysql deploybase faild" >> $log_dir/$time.log
echo "$now deploy-数据库数据备份失败——全量备份,ip:127.0.0.1,请检查mysql是否正常" > /tmp/tmp.log
rm -rf /tmp/tmp.log
fi

1.2、添加执行权限

[root@localhost ~]# chmod +x /mnt/data/backup/mysql/mysql_m_bak_full.sh

1.3、添加计划任务

[root@localhost ~]# crontab -e   #添加内容，每周6的2点30执行
30 01 * * 0 sh /mnt/data/backup/mysql/mysql_m_bak_full.sh
​
#wq保存退出

1.4、手动备份

[root@localhost]# sh /mnt/data/backup/mysql/mysql_m_bak_full.sh
mysqldump: [Warning] Using a password on the command line interface can be insecure.

1.5、查看备份文件

[root@localhost]# ll -h /mnt/data/backup/mysql/full_back/20230223/
总用量 63M
drwxr-xr-x. 2 root root  26 2月  23 14:04 log
-rw-r--r--. 1 root root 63M 2月  23 14:06 master_bak_20230223.tar.gz
[root@localhost]# cat /mnt/data/backup/mysql/full_back/20230223/log/20230223.log 
2023-02-23 14:06:18 Begin backup mysql deploybase
2023-02-23 14:06:18 mysql 连接成功 
2023-02-23 14:06:18 开始执行压缩......
2023-02-23 14:06:18 文件压缩成功......
2023-02-23 14:06:18 backup mysql deploybase successfully completed
​

1.6、全量数据恢复

[root@localhost]# mysql -uroot -p123456 < /mnt/data/backup/mysql/full_back/20230223/master_bak_20230223.sql