Python3 - Mysql 数据备份与SCP灾备
文章目录
- 一、准备条件
- 二、宿主机环境
-
- 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.gz1.创建几个目录
data 存放备份数据，logs存放每天的日志
注意：目录可以自己随意改，但是一定要修改脚本里的路径
2.然后编写脚本 vi backup.sh
chmod +x backup.sh
#!/bin/bash#备份目录BACKUP_ROOT=/home/harry/task/mysqlBACKUP_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 $DATABASESdoecho# 过滤有些不需要备份的数据库if [ "${db}" = "move_oct09" ] || [ "${db}" = "wisdomtourism" ];thenecho "jump over ${db}"elseecho ----------$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.gzecho ${db}echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz COMPLETE----------fidoneecho "done"3.SCP灾备
把备份文件复制到其他的服务器上，利用scp远程复制，可能服务器需要安装一下expect
chmod +x aiprose.sh
#!/bin/bash#备份目录BACKUP_ROOT=/home/harry/task/mysqlBACKUP_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 $DATABASESdoechoif [ "${db}" = "move_oct09" ] || [ "${db}" = "wisdomtourism" ];thenecho "jump over ${db}"elseecho ----------$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.gzecho ${db}echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz COMPLETE----------echoecho ----------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---------fidoneecho "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/mysqlBACKUP_FILEDIR=$BACKUP_ROOT/data#当前日期DATE=$(date +%Y%m%d)#查询所有数据库394e3f25589f为容器idDATABASES=$(docker exec -i 394e3f25589f mysql -uroot -p123456 -e "show databases" | grep -Ev "Database|sys|information_schema|performance_schema|mysql")#循环数据库进行备份for db in $DATABASESdoechoif [[ "${db}" =~ "+" ]] || [[ "${db}" =~ "|" ]];thenecho "jump over ${db}"elseecho ----------$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.gzecho ${db}echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz COMPLETE----------echofidoneecho "done"2.SCP进行灾备
把备份文件复制到其他的服务器上，利用scp远程复制，可能服务器需要安装一下expect, 一般云服务器都默认安装了
#!/bin/bash#备份目录BACKUP_ROOT=/home/harry/task/mysqlBACKUP_FILEDIR=$BACKUP_ROOT/data#当前日期DATE=$(date +%Y%m%d)#查询所有数据库 394e3f25589f 为容器idDATABASES=$(docker exec -i 394e3f25589f mysql -uroot -p123456 -e "show databases" | grep -Ev "Database|sys|information_schema|performance_schema|mysql")#循环数据库进行备份for db in $DATABASESdoechoif [[ "${db}" =~ "+" ]] || [[ "${db}" =~ "|" ]];thenecho "jump over ${db}"elseecho ----------$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.gzecho ${db}echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz COMPLETE----------echoecho ----------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---------fidoneecho "done"3.编写清除旧数据脚本 backup_clean.sh
#!/bin/bashecho ----------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>&100 3 * * * /home/harry/task/mysql/aiprose.sh > /home/harry/task/mysql/logs/backup.log 2>&1# 重启服务service crond reload# 或者service crond restart# 查看状态service crond status5.注意
灾备和正常备份通常跑一个即可; 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/scpdataThe 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)? yesWarning: 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灾备就介绍到这里, 喜欢就点个赞吧~❤☕️
关注本专栏, 持续更新哦~ 限时免费"关注本专栏"