灾备数据库同步 haproxy mysql灾备方案

文章目录

• 一、准备条件
• 二、宿主机环境

• 1.创建几个目录
• 2.然后编写脚本 vi backup.sh
• 3.SCP灾备

• 三、docker 环境

• 1.创建脚本文件 vi backup.sh
• 2.SCP进行灾备
• 3.编写清除旧数据脚本 backup_clean.sh
• 4.设置定时任务
• 5.注意

日常业务运行中常常因为机器故障或失误操作导致mysql数据丢失或损坏, 轻则造成业务中断或用户流失, 重则造成财产损失。 那么mysql的日常数据备份和scp mysql灾备就必不可缺, 本文列举了两种情形的数据备份:
🌶一种是手动安装的mysql
🌶另一种是docker运行的mysql备份
🌶各位可以根据自己的场景去应用~

一、准备条件

云服务器已经安装mysql服务(docker或手动安装)，并自动启动

二、宿主机环境

预计创建结构如下:

[harry@k8s-master01 mysql]$ tree
.
├── aiprose.sh
├── backup_clean.sh
├── backup.sh
├── data
│   ├── blogdb_20220418.sql.gz
│   ├── newsdb_20220417.sql.gz
│   ├── newsdb_20220418.sql.gz
│   ├── newsdb_20220419.sql.gz
├── logs
│   ├── backup_full_clean.log
│   └── backup.log
└── scpdata
    ├── blogdb_20220417.sql.gz
    ├── blogdb_20220418.sql.gz
    ├── newsdb_20220417.sql.gz
    └── newsdb_20220418.sql.gz

1.创建几个目录

data 存放备份数据，logs存放每天的日志

注意：目录可以自己随意改，但是一定要修改脚本里的路径

2.然后编写脚本 vi backup.sh

chmod +x backup.sh

#!/bin/bash
#备份目录
BACKUP_ROOT=/home/harry/task/mysql
BACKUP_FILEDIR=$BACKUP_ROOT/data
#当前日期
DATE=$(date +%Y%m%d)

#查询所有数据库
DATABASES=$(mysql  -uroot -p'123456' -e "show databases" | grep -Ev "Database|sys|information_schema|performance_schema|mysql")
#循环数据库进行备份
for db in $DATABASES
do
echo
# 过滤有些不需要备份的数据库
if [ "${db}" = "move_oct09" ] || [ "${db}" = "wisdomtourism" ];then
echo "jump over ${db}"
else
echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz BEGIN----------
mysqldump -uroot -p'123456' --default-character-set=utf8 -q --lock-all-tables --flush-logs -E -R --triggers -B ${db} | gzip > $BACKUP_FILEDIR/${db}_$DATE.sql.gz
echo ${db}
echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz COMPLETE----------
fi
done
echo "done"

3.SCP灾备

把备份文件复制到其他的服务器上，利用scp远程复制，可能服务器需要安装一下expect

chmod +x aiprose.sh

#!/bin/bash
#备份目录
BACKUP_ROOT=/home/harry/task/mysql
BACKUP_FILEDIR=$BACKUP_ROOT/data
#当前日期
DATE=$(date +%Y%m%d)

#查询所有数据库
DATABASES=$(mysql  -uroot -p'123456' -e "show databases" | grep -Ev "Database|sys|information_schema|performance_schema|mysql")
#循环数据库进行备份
for db in $DATABASES
do
echo
if [ "${db}" = "move_oct09" ] || [ "${db}" = "wisdomtourism" ];then
echo "jump over ${db}"
else
echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz BEGIN----------
mysqldump -uroot -p'123456' --default-character-set=utf8 -q --lock-all-tables --flush-logs -E -R --triggers -B ${db} | gzip > $BACKUP_FILEDIR/${db}_$DATE.sql.gz
echo ${db}
echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz COMPLETE----------
echo
echo ----------scp 226  begin----------
expect -c "
    spawn scp -r /home/harry/task/mysql/data/${db}_$DATE.sql.gz harry@39.10.140.126:/home/harry/task/mysql/scpdata
    expect {
        \"*assword\" {set timeout 300; send \"123456\r\"; exp_continue;}
        \"yes/no\" {send \"yes\r\";}
    }
expect eof"
echo ----------scp 226  end---------
fi
done

echo "done"

三、docker 环境

1.创建脚本文件 vi backup.sh

注意一点：docker exec -i ,这里不能加t，因为么有tty输入，所以会报错 the input device is not a TTY

#!/bin/bash
#备份目录
BACKUP_ROOT=/home/harry/task/mysql
BACKUP_FILEDIR=$BACKUP_ROOT/data
#当前日期
DATE=$(date +%Y%m%d)

#查询所有数据库394e3f25589f为容器id
DATABASES=$(docker exec -i 394e3f25589f mysql  -uroot -p123456 -e "show databases" | grep -Ev "Database|sys|information_schema|performance_schema|mysql")
#循环数据库进行备份
for db in $DATABASES
do
echo
if [[ "${db}" =~ "+" ]] || [[ "${db}" =~ "|" ]];then
echo "jump over ${db}"
else
echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz BEGIN----------
docker exec -i 394e3f25589f  mysqldump -uroot -p123456 --default-character-set=utf8 -q --lock-all-tables --flush-logs -E -R --triggers -B ${db} | gzip > $BACKUP_FILEDIR/${db}_$DATE.sql.gz
echo ${db}
echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz COMPLETE----------
echo
fi
done

echo "done"

2.SCP进行灾备

把备份文件复制到其他的服务器上，利用scp远程复制，可能服务器需要安装一下expect, 一般云服务器都默认安装了

#!/bin/bash
#备份目录
BACKUP_ROOT=/home/harry/task/mysql
BACKUP_FILEDIR=$BACKUP_ROOT/data
#当前日期
DATE=$(date +%Y%m%d)

#查询所有数据库 394e3f25589f 为容器id
DATABASES=$(docker exec -i 394e3f25589f mysql  -uroot -p123456 -e "show databases" | grep -Ev "Database|sys|information_schema|performance_schema|mysql")
#循环数据库进行备份
for db in $DATABASES
do
echo
if [[ "${db}" =~ "+" ]] || [[ "${db}" =~ "|" ]];then
echo "jump over ${db}"
else
echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz BEGIN----------
docker exec -i 394e3f25589f  mysqldump -uroot -p123456 --default-character-set=utf8 -q --lock-all-tables --flush-logs -E -R --triggers -B ${db} | gzip > $BACKUP_FILEDIR/${db}_$DATE.sql.gz
echo ${db}
echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz COMPLETE----------
echo
echo ----------scp 109.5  begin----------
expect -c "
    spawn scp -P 22 -r /home/harry/task/mysql/data/${db}_$DATE.sql.gz harry@39.101.243.154:/home/harry/task/mysql/scpdata
    expect {
        \"*assword\" {set timeout 300; send \"123456\r\"; exp_continue;}
        \"yes/no\" {send \"yes\r\";}
    }
expect eof"
echo ----------scp 109.5  end---------
fi
done

echo "done"

3.编写清除旧数据脚本 backup_clean.sh

#!/bin/bash
echo ----------CLEAN BEGIN----------
find /home/harry/task/mysql/data -mtime +7 -name "*.gz" -exec rm -rf {} \;
echo ----------CLEAN COMPLETE---------

4.设置定时任务

脚本编写好了，需要加到系统的定时任务里，负责需要手动执行

# 查看定时任务
crontab -l

# 修改定时任务
crontab -e

# 修改定时任务的时候增加如下，记得修改为自己的脚本路径，下面是凌晨2点自动清理数据，凌晨3点自动备份数据
00 2 * * * /home/harry/task/mysql/backup_clean.sh > /home/harry/task/mysql/logs/backup_full_clean.log 2>&1
00 3 * * * /home/harry/task/mysql/aiprose.sh > /home/harry/task/mysql/logs/backup.log 2>&1

# 重启服务
service crond reload
# 或者
service crond restart
# 查看状态
service crond status

5.注意

灾备和正常备份通常跑一个即可; scp 备份需要多运行1次再加入到定时任务, 因为第一次运行会将主机ip加入到主机列表, 首次运行如下提示:

spawn scp -P 22 -r /home/harry/task/mysql/data/newsdb_20220416.sql.gz harry@39.101.243.154:/home/harry/task/mysql/scpdata
The authenticity of host '39.22.244.154 (39.22.244.154)' can't be established.
ECDSA key fingerprint is SHA256:VTO/+Pw1Qai3ZGuRlRWyl2L/5A9JzwGaSPcbaa6ySC4.
ECDSA key fingerprint is MD5:75:db:12:2b:17:2a:2b:e9:26:bb:cd:50:c3:90:14:86.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '39.22.244.154' (ECDSA) to the list of known hosts.
harry@39.101.243.154's password: ----------scp 109.5 end---------
done

好啦🌶🌶, 关于mysql数据备份和scp灾备就介绍到这里, 喜欢就点个赞吧~❤☕️