将腾讯云mysql全量备份恢复到本地mysql
环境说明:
腾讯云mysql版本:5.7
本地mysql版本:5.7
一、获取最新备份链接
这块采用的是golang编写的一个脚本
main.go
package main
import (
"fmt"
cdb "github.com/tencentcloud/tencentcloud-sdk-go/tencentcloud/cdb/v20170320"
"github.com/tencentcloud/tencentcloud-sdk-go/tencentcloud/common"
"github.com/tencentcloud/tencentcloud-sdk-go/tencentcloud/common/errors"
"github.com/tencentcloud/tencentcloud-sdk-go/tencentcloud/common/profile"
)
func main() {
// 实例化一个认证对象,入参需要传入腾讯云账户 SecretId 和 SecretKey,此处还需注意密钥对的保密
// 代码泄露可能会导致 SecretId 和 SecretKey 泄露,并威胁账号下所有资源的安全性。以下代码示例仅供参考,建议采用更安全的方式来使用密钥,请参见:https://cloud.tencent.com/document/product/1278/85305
// 密钥可前往官网控制台 https://console.cloud.tencent.com/cam/capi 进行获取
credential := common.NewCredential(
"AKIDDE0Wotxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
"YrNcbMuxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
)
// 实例化一个client选项,可选的,没有特殊需求可以跳过
cpf := profile.NewClientProfile()
cpf.HttpProfile.Endpoint = "cdb.tencentcloudapi.com"
// 实例化要请求产品的client对象,clientProfile是可选的
client, _ := cdb.NewClient(credential, "ap-guangzhou", cpf)
// 实例化一个请求对象,每个接口都会对应一个request对象
request := cdb.NewDescribeBackupsRequest()
request.InstanceId = common.StringPtr("cdb-1mm5h1lv")
// 返回的resp是一个DescribeBackupsResponse的实例,与请求对象对应
response, err := client.DescribeBackups(request)
if _, ok := err.(*errors.TencentCloudSDKError); ok {
fmt.Printf("An API error has returned: %s", err)
return
}
if err != nil {
panic(err)
}
// 输出json格式的字符串回包
//fmt.Printf("%s", response.ToJsonString())
info := response.Response.Items[0]
fmt.Println(*info.InternetUrl)
}
编译成一个可执行文件,放到服务器上(最好在linux服务器编译打包)
我这边编译打包的文件名叫mysql-back-url
,后面脚本中会用到
二、编写恢复脚本
mysql-back.sh
#!/bin/bash
date=$(date +%F' '%T)
# 这个目录是mysql的数据目录,把备份文件回滚到这个目录,然后用这个目录去启动mysql容器
mysql_data_dir=/data/mysql
port=9876
log_dir=mysql-back.log
ck(){
if [ $? -eq 0 ];then
echo ''
else
echo "$date ${FUNCNAME[@]} 有问题,请查看" >> $log_dir
exit
fi
}
Mysql_data_dir_check() {
if [ "$(ls -A $mysql_data_dir)" ]; then
echo "$date mysql恢复目录不为空" >> $log_dir
exit
fi
}
Download_data() {
# 这个mysql-back-url是用go写的,需要去调腾讯云的api,获取最新的备份url下载链接
./mysql-back-url > mysql-back-url.txt
ck
url=$(cat mysql-back-url.txt)
wget -c $url -O $date.xb
ck
}
Xbstream_download() {
xbstream --help
if [ $? -eq 0 ];then
# 使用 xbstream 命令将备份文件解包到目标目录
xbstream -x -C /data/mysql < $date.xb
ck
echo "解包备份文件成功" >> $log_dir
else
echo "xbstream命令不存在,开始下载" >> $log_dir
wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.26/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.26-1.el7.x86_64.rpm
ck
yum install percona-xtrabackup-24-2.4.26-1.el7.x86_64.rpm -y
ck
# 使用 xbstream 命令将备份文件解包到目标目录
xbstream -x -C $mysql_data_dir < $date.xb
ck
echo "解包备份文件成功" >> $log_dir
fi
}
Qpress_download() {
wget -d --user-agent="Mozilla/5.0 (Windows NT x.y; rv:10.0) Gecko/20100101 Firefox/10.0" https://docs-tencentdb-1256569818.cos.ap-guangzhou.myqcloud.com/qpress-11-linux-x64.tar
ck
tar -xf qpress-11-linux-x64.tar -C /usr/local/bin
ck
source /etc/profile
}
Xtrabackup_data() {
# 将/data/mysql目录下所有以.qp结尾的文件都解压出来
xtrabackup --decompress --target-dir=$mysql_data_dir
ck
}
Prepare() {
# 备份解压出来之后,执行如下命令进行 apply log 操作
xtrabackup --prepare --target-dir=$mysql_data_dir
ck
}
Mycnf() {
cat >my.cnf<<-EOF
# This MySQL options file was generated by innobackupex.
# The MySQL server
[mysqld]
innodb_data_file_path=ibdata1:12M:autoextend
innodb_log_files_in_group=4
innodb_log_file_size=1048576000
innodb_undo_directory=./
innodb_undo_tablespaces=0
server_id=0
sql_mode='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES'
EOF
ck
cp -r my.cnf $mysql_data_dir/
}
Mysql_docker() {
ss -antl|grep $port
if [ $? -ne 0 ];then
docker ps |grep mysql-rds
if [ $? -ne 0 ];then
docker run -itd -p $port:3306 --privileged=true -v $mysql_data_dir/my.cnf:/etc/my.cnf \
-v $mysql_data_dir:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=zby1q2w3e --name mysql-rds mysql:5.7 --character-set-server=utf8mb4
if [ $? -eq 0 ];then
echo "$date 备份恢复成功,使用 ip+$port 测试,账号密码跟备份数据库是一样的" >> $log_dir
else
echo "$date 容器启动失败,请自行查看" >> $log_dir
exit
fi
else
echo "$date 容器名称被占用" >> $log_dir
exit
fi
else
echo "$date 容器端口被占用" >> $log_dir
exit
fi
}
# 判断mysql恢复目录是否为空
Mysql_data_dir_check
# 下载腾讯云最近一次的全量备份数据
Download_data
# 解包备份文件
Xbstream_download
# 解压备份文件
Qpress_download
Xtrabackup_data
# Prepare 备份文件
Prepare
# mysql配置文件
Mycnf
# 容器启动mysql
Mysql_docker