基于mysqldump的MySQL数据库异地备份方案（含完整脚本和解释）

MySQL数据库异地备份方案

0 文档描述

• 本文描述了一个数据库异地备份方案，以下脚本代码都是在线上应用的
• 本文以CentOS7为例，其他系统请自行查询安装命令
• 如果评论有需求，我就对应系统做一下文档

1 基本原理

1.1 流程

原理本身很简单：

• 1、利用mysqldump导出指定的数据库
• 2、利用7za命令压缩文件
• 3、通过ssh下载到异地（本地机房）

1.2 说明

• 通常情况下，本地机房是无法访问异地的生产环境的，因此需要服务端一个脚本（用于导出数据库和压缩）、本地（即异地备份的“异地”）机房下载服务端的压缩包并保存。
• 那么如何保证安全呢？建议通过云供应商的安全组限定IP、ssh证书等方式保障服务器访问是可控的

1 安装必要环境

# 安装mysqldump
yum -y install holland-mysqldump.noarch
# 安装压缩软件7z
sudo yum install p7zip

2 导出数据库并打包

• 废话不多说，直接说内容、脚本，解释见注释
• 在服务端（能访问被备份数据库的服务器上）上导出数据库并打包
• 建议将下方脚本保存为bakcup.sh，在下一步中有调用
• 下方dbname改为你的数据库名称，数据库地址、数据库账号、数据库密码改为你的

# 删除上一次导出的数据库
rm dbname.sql
# 删除上一次压缩的文件
rm dbname.7z
# 导出数据库
# 请正确修改下方信息，如：
# mysqldump -h 'localhost' -u root -p'123456' test > test.sql
mysqldump -h '填你的MySQL数据库地址' -u 数据库账号 -p'你的密码' dbname > dbname.sql
# 加密压缩，如果7za命令不存在，建议试试7z命令，如果还不行就得在网上找找了，实在不行就用gzip命令，不加密
7za a -p dbname.7z dbname.sql

3 下载到本地机房（异地）

• 命令如下，主要是利用ssh远程执行上述
• ssh如何免密远程？见第4步（不是本文核心）

echo '正在备份和加密打包'
# 利用ssh远程执行服务器上的backup脚本
ssh root@你的服务器IP "cd /data/mysql;./backup.sh;exit;"
echo '正在下载文件'
scp -P 22 root@你的服务器IP:/data/mysql/dbname.7z ./dbname.7z
echo '正在存储文件'
mv dbname.7z backup/$(date +"%Y%m%d_%H%M%S").7z
cd backup/
ls
echo 'done.'
exit 0

• 这样数据库就实现了云端导出和打包并下载到本地，从而实现了异地备份存储

4 ssh免密登录

• 网上有很多种方法，您可以去搜索下，这里提供一种

ssh-keygen -t rsa
# 下方为显示内容，一直enter就行
# Enter file in which to save the key (/home/jsmith/.ssh/id_rsa):[Enter key] 
# Enter passphrase (empty for no passphrase): [Press enter key]
# Enter same passphrase again: [Pess enter key]
# Your identification has been saved in /home/jsmith/.ssh/id_rsa.
# Your public key has been saved in /home/jsmith/.ssh/id_rsa.pub. 
# The key fingerprint is: 33:b3:fe:af:95:95:18:11:31:d5:de:96:2f:f2:35:f9 
ssh-copy-id -i ~/.ssh/id_rsa.pub  root@你的服务器IP
# 需要按提示输入密码，输入后enter即可
# 完成之后进行如下验证， 看有没有上到你的云服务器上
ssh root@你的服务器IP

5 其他建议

• 1、为保障服务器安全，建议限制白名单IP执行文件下载命令
• 2、可以使用Jenkins、Cron等方法使用定时任务备份数据库
• 3、7z的压缩效果比较好，基本上能压缩为30%左右，推荐使用
• 4、本文以CentOS7上的应用为例