腾讯云数据库恢复方案对比

需求：需要每天8点之前把生产数据库3点之前的数据还原到预发布环境

测试两种方案如下：

1.根据时间节点回档（克隆）出新实例

编写脚本通过api定时创建：

import  datetime,time
import json
from tencentcloud.common import credential
from tencentcloud.common.profile.client_profile import ClientProfile
from tencentcloud.common.profile.http_profile import HttpProfile
from tencentcloud.common.exception.tencent_cloud_sdk_exception import TencentCloudSDKException
from tencentcloud.cdb.v20170320 import cdb_client, models
today = datetime.datetime.now().strftime("%Y%m%d")
#指定回档时间每天早上6点
SpecifiedRollbackTime = today + "06:00:00"
#指定新实例名称
InstanceName = "pxt_recovery" + today
try:
    cred = credential.Credential("id", "key")
    httpProfile = HttpProfile()
    httpProfile.endpoint = "cdb.tencentcloudapi.com"

    clientProfile = ClientProfile()
    clientProfile.httpProfile = httpProfile
    client = cdb_client.CdbClient(cred, "ap-guangzhou", clientProfile)

    req = models.CreateCloneInstanceRequest()
    params = {
        "InstanceId": "cdb-kkbfg05n",
        "SpecifiedRollbackTime": SpecifiedRollbackTime,
        "UniqVpcId": "vpcid",
        "UniqSubnetId": "subnet-id",
        "InstanceName": InstanceName
    }
    req.from_json_string(json.dumps(params))

    resp = client.CreateCloneInstance(req)
    print(resp.to_json_string())

except TencentCloudSDKException as err:
    print(err)

2.编写脚本定时下载冷备（或逻辑）文件恢复到数据库

需要安装较多工具详细恢复步骤参考：云数据库 MySQL 使用物理备份恢复数据库 - 操作指南 - 文档中心 - 腾讯云

import json
import  os
from tencentcloud.common import credential
from tencentcloud.common.profile.client_profile import ClientProfile
from tencentcloud.common.profile.http_profile import HttpProfile
from tencentcloud.common.exception.tencent_cloud_sdk_exception import TencentCloudSDKException
from tencentcloud.cdb.v20170320 import cdb_client, models
#获取最新的备份链接
try:
    cred = credential.Credential("id", "key")
    httpProfile = HttpProfile()
    httpProfile.endpoint = "cdb.tencentcloudapi.com"

    clientProfile = ClientProfile()
    clientProfile.httpProfile = httpProfile
    client = cdb_client.CdbClient(cred, "ap-guangzhou", clientProfile)

    req = models.DescribeBackupsRequest()
    params = {
        "InstanceId": "cdb-id",
        "Limit": 1
    }
    req.from_json_string(json.dumps(params))

    resp = client.DescribeBackups(req)
    rows = json.loads(resp.to_json_string())["Items"][0].get("IntranetUrl")
    #清空历史备份文件
    cmd = 'rm -rf /data/mysql/pxt.xb'
    os.system(cmd)
    #下载备份文件
    cmd = 'wget -q -c "%s" -O /data/mysql/pxt.xb' % rows
    os.system(cmd)
    cmd = "sh  /data/mysql/mysql.sh"
    os.system(cmd)

except TencentCloudSDKException as err:
    print(err)

echo "停止mysql"
ps -ef |grep mysql |grep -v grep
kill -9 $(ps -ef |grep mysql |grep -v grep|grep -v mysql.sh |awk '{print $2}')
echo "清除前一天的数据库文件"
rm -rf  /data/pxt/*
echo "解压备份文件"
xbstream -x -C /data/pxt/ < /data/mysql/pxt.xb
xtrabackup --decompress --target-dir=/data/pxt  --remove-original
xtrabackup --prepare  --target-dir=/data/pxt
sed -i '/checksum/d'  /data/pxt/backup-my.cnf
sed -i '/innodb_page_size/d'   /data/pxt/backup-my.cnf
sed -i '/innodb_log_block_size/d'   /data/pxt/backup-my.cnf
sed -i '/server_uuid/d'   /data/pxt/backup-my.cnf
sed -i '/redo_log_version/d'   /data/pxt/backup-my.cnf
sed -i '/master_key_id/d'   /data/pxt/backup-my.cnf
#可以选择开启免密登录
#echo "skip-grant-tables" >> /data/pxt/backup-my.cnf
chown -R mysql:mysql /data/pxt/
mysqld_safe   --defaults-file=/data/pxt/backup-my.cnf --user=mysql --datadir=/data/pxt &

两种方案对比：

克隆新实例：

优点：恢复速度快（800G数据还原大概在25分钟）

           操作简单一个定时脚本就可以搞定

缺点：费用较高，克隆实例按量计费

           每天新生成的实例IP地址会变更，需要调整服务链接地址和重启服务

还原冷备文件：

优点：费用相对较低，一台服务器的费用

           IP固定，每天还原之后业务无感知不需要重启服务

缺点：时间长（备份下载加恢复大概4小时）

           操作较复杂