MySQL备份

常规备份

执行编辑脚本 bash vim /opt/script/mysqlbak.sh 输入如下内容：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
#!/bin/bash
bak_date=`date +%Y%m%d`
log_date=`date +'%Y年%m月%d日%k时%M分%S秒'`

mkdir -p /opt/mysql_bak/log /opt/mysql_bak/bak

echo  "$log_date:  开始备份..." >> /opt/mysql_bak/log/mysqlbak.log
echo  "$log_date:  正在生成备份文件.sql..." >> /opt/mysql_bak/log/mysqlbak.log

#全量备份文件生成 
mysqldump -u{用户名} -p{密码} --all-databases > /opt/mysql_bak/bak/mysqlbak.sql
size=`ls -l /opt/mysql_bak/bak/  | grep mysqlbak.sql | awk '{print int($5)}'`
#验证备份文件大小
m_bak_size=100000
m_tar_size=10
if [ "$size" -gt "$m_bak_size" ]; then

        echo  "$log_date:  备份文件生成成功！" >> /opt/mysql_bak/log/mysqlbak.log
        echo  "$log_date:  正在生成打包文件..." >> /opt/mysql_bak/log/mysqlbak.log
        #打包备份文件
        cd  /opt/mysql_bak/bak/
        tar -zcf mysqlbak$bak_date.tar.gz mysqlbak.sql
        tar_size=`ls -l /opt/mysql_bak/bak/ | grep mysqlbak$bak_date.tar.gz | awk '{print int($5)}'`
        #验证打包文件并删除原备份文件
        
        if [ "$tar_size" -gt "$m_tar_size" ]; then
                rm -rf /opt/mysql_bak/bak/mysqlbak.sql
                tar_status='已经完成备份！'
                echo "$log_date:  $tar_status" >> /opt/mysql_bak/log/mysqlbak.log
        else
                tar_status='备份打包失败！'
                echo "$log_date:  $tar_status" >> /opt/mysql_bak/log/mysqlbak.log
        fi
else     
         tar_status='备份生成失败！'
        echo "$log_date:  $tar_status" >> /opt/mysql_bak/log/mysqlbak.log
fi

# 删除180天前的备份文件
find /opt/mysql_bak/bak/ -mtime "+180"  -exec rm -f {} \;

利用docker备份

执行编辑脚本 bash vim /opt/script/mysqlbak_docker.sh 输入如下内容：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
#!/bin/bash
bak_date=`date +%Y%m%d`
log_date=`date +'%Y年%m月%d日%k时%M分%S秒'`

mkdir -p /opt/mysql_bak/log /opt/mysql_bak/bak

echo  "$log_date:  开始备份..." >> /opt/mysql_bak/log/mysqlbak.log
echo  "$log_date:  正在生成备份文件.sql..." >> /opt/mysql_bak/log/mysqlbak.log

#全量备份文件生成 
docker exec {容器名称或容器ID}  mysqldump -u{用户名} -p{密码} --all-databases > /opt/mysql_bak/bak/mysqlbak.sql
size=`ls -l /opt/mysql_bak/bak/  | grep mysqlbak.sql | awk '{print int($5)}'`
#验证备份文件大小
m_bak_size=100000
m_tar_size=10
if [ "$size" -gt "$m_bak_size" ]; then

        echo  "$log_date:  备份文件生成成功！" >> /opt/mysql_bak/log/mysqlbak.log
        echo  "$log_date:  正在生成打包文件..." >> /opt/mysql_bak/log/mysqlbak.log
        #打包备份文件
        cd  /opt/mysql_bak/bak/
        tar -zcf mysqlbak$bak_date.tar.gz mysqlbak.sql
        tar_size=`ls -l /opt/mysql_bak/bak/ | grep mysqlbak$bak_date.tar.gz | awk '{print int($5)}'`
        #验证打包文件并删除原备份文件
        
        if [ "$tar_size" -gt "$m_tar_size" ]; then
                rm -rf /opt/mysql_bak/bak/mysqlbak.sql
                tar_status='已经完成备份！'
                echo "$log_date:  $tar_status" >> /opt/mysql_bak/log/mysqlbak.log
        else
                tar_status='备份打包失败！'
                echo "$log_date:  $tar_status" >> /opt/mysql_bak/log/mysqlbak.log
        fi
else     
         tar_status='备份生成失败！'
        echo "$log_date:  $tar_status" >> /opt/mysql_bak/log/mysqlbak.log
fi

# 删除180天前的备份文件
find /opt/mysql_bak/bak/ -mtime "+180"  -exec rm -f {} \;

利用操作系统的crontab进行定时备份

1
2
# 每天1点执行一次
0 1 * * * bash /opt/script/{备份脚本文件名}