阿里OceanBase备份与恢复
#backup.sh备份脚本
推荐NFS共享目录
#!/bin/bash
# 配置参数
OB_USER="root"
OB_PASS="11111111!"
OB_HOST="172.17.111.123"
OB_PORT="3306"
OB_TENANT="hbst"
BACKUP_DIR="/dbbak/"
DATE=bak_$(date +%Y%m%d)
LOG_FILE="$BACKUP_DIR$DATE/backup_$DATE.log"
Status='';
LastJobid='';
LastStatus='';
# 创建备份目录（如果不存在）
mkdir -p $BACKUP_DIR$DATE
# 执行备份命令
#obclient -h 172.17.111.123 -P 3306 -u root@hbst -p11111111! -e SELECT `STATUS` FROM DBA_OB_ARCHIVELOG
#执行备份命令, 这里需要根据具体需求调整命令,先停止归档日志在修改归档日志路径
wait
while true
do
Status=$(obclient -h $OB_HOST -P $OB_PORT -u $OB_USER@$OB_TENANT -p$OB_PASS -o oceanbase -e "SELECT STATUS FROM DBA_OB_ARCHIVELOG" |sed '1d')
if [ "$Status" = "STOP" ];
then
echo "日志归档停止状态"
echo "即将执行开始执行备份"
rm -rf $BACKUP_DIR$DATE/*
# 创建备份目录（如果不存在）
mkdir -p $BACKUP_DIR$DATE/archive
mkdir -p $BACKUP_DIR$DATE/data
chmod -R 777 $BACKUP_DIR$DATE
wait
obclient -h $OB_HOST -P $OB_PORT -u $OB_USER@$OB_TENANT -p$OB_PASS <<EOF
ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file://$BACKUP_DIR$DATE/archive';
ALTER SYSTEM ARCHIVELOG;
EOF
echo "等待进程"
while true
do
Status=$(obclient -h $OB_HOST -P $OB_PORT -u $OB_USER@$OB_TENANT -p$OB_PASS -o oceanbase -e "SELECT STATUS FROM DBA_OB_ARCHIVELOG" |sed '1d')
if [ "$Status" = "DOING" ];
then break;
fi
sleep 2
done
Status=$(obclient -h $OB_HOST -P $OB_PORT -u $OB_USER@$OB_TENANT -p$OB_PASS -o oceanbase -e "SELECT STATUS FROM DBA_OB_ARCHIVELOG" |sed '1d')
if [ "$Status" = "DOING" ];
then
echo "归档状态为DOING开始执行备份"
obclient -h $OB_HOST -P $OB_PORT -u $OB_USER@$OB_TENANT -p$OB_PASS <<EOF
ALTER SYSTEM SET DATA_BACKUP_DEST='file://$BACKUP_DIR$DATE/data';
ALTER SYSTEM BACKUP DATABASE;
EOF
fi
sleep 2
LastJobid=$(obclient -h $OB_HOST -P $OB_PORT -u $OB_USER@$OB_TENANT -p$OB_PASS -o oceanbase -e "SELECT JOB_ID FROM DBA_OB_BACKUP_JOBS" |sed '1d')
echo "等待备份任务结束"
while true
do
LastStatus=$(obclient -h $OB_HOST -P $OB_PORT -u $OB_USER@$OB_TENANT -p$OB_PASS -o oceanbase -e "SELECT STATUS FROM DBA_OB_BACKUP_JOB_HISTORY where JOB_ID=$LastJobid" |sed '1d')
if [ "$LastStatus" = "COMPLETED" ];
then
echo "备份任务结束"
break;
fi
sleep 2
done
echo "关闭归档日志"
obclient -h $OB_HOST -P $OB_PORT -u $OB_USER@$OB_TENANT -p$OB_PASS <<EOF
ALTER SYSTEM noARCHIVELOG;
EOF
echo "等待进程"
while true
do
Status=$(obclient -h $OB_HOST -P $OB_PORT -u $OB_USER@$OB_TENANT -p$OB_PASS -o oceanbase -e "SELECT STATUS FROM DBA_OB_ARCHIVELOG" |sed '1d')
if [ "$Status" = "STOP" ];
then
echo "日志归档停止状态"
break;
fi
sleep 2
done
echo "大结束状态"
break;
else
echo "关闭归档日志"
obclient -h $OB_HOST -P $OB_PORT -u $OB_USER@$OB_TENANT -p$OB_PASS <<EOF
ALTER SYSTEM noARCHIVELOG;
EOF
while true
do
Status=$(obclient -h $OB_HOST -P $OB_PORT -u $OB_USER@$OB_TENANT -p$OB_PASS -o oceanbase -e "SELECT STATUS FROM DBA_OB_ARCHIVELOG" |sed '1d')
if [ "$Status" = "STOP" ];
then
echo "日志归档停止状态"
break;
fi
sleep 2
done
rm -rf $BACKUP_DIR$DATE/*
mkdir -p $BACKUP_DIR$DATE/archive
mkdir -p $BACKUP_DIR$DATE/data
chmod -R 777 $BACKUP_DIR$DATE
wait
Status=$(obclient -h $OB_HOST -P $OB_PORT -u $OB_USER@$OB_TENANT -p$OB_PASS -o oceanbase -e "SELECT STATUS FROM DBA_OB_ARCHIVELOG" |sed '1d')
if [ "$Status" = "STOP" ];
then
echo "日志归档停止状态"
echo "日志归拉起"
obclient -h $OB_HOST -P $OB_PORT -u $OB_USER@$OB_TENANT -p$OB_PASS <<EOF
ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file://$BACKUP_DIR$DATE/archive';
ALTER SYSTEM ARCHIVELOG;
EOF
echo "等待进程"
while true
do
Status=$(obclient -h $OB_HOST -P $OB_PORT -u $OB_USER@$OB_TENANT -p$OB_PASS -o oceanbase -e "SELECT STATUS FROM DBA_OB_ARCHIVELOG" |sed '1d')
if [ "$Status" = "DOING" ];
then
echo "开始执行备份";
break;
fi
sleep 2
done
fi
Status=$(obclient -h $OB_HOST -P $OB_PORT -u $OB_USER@$OB_TENANT -p$OB_PASS -o oceanbase -e "SELECT STATUS FROM DBA_OB_ARCHIVELOG" |sed '1d')
if [ "$Status" = "DOING" ];
then
echo "日志归档DOING状态"
echo "开始执行备份"
obclient -h $OB_HOST -P $OB_PORT -u $OB_USER@$OB_TENANT -p$OB_PASS <<EOF
ALTER SYSTEM SET DATA_BACKUP_DEST='file://$BACKUP_DIR$DATE/data';
ALTER SYSTEM BACKUP DATABASE;
EOF
sleep 2
LastJobid=$(obclient -h $OB_HOST -P $OB_PORT -u $OB_USER@$OB_TENANT -p$OB_PASS -o oceanbase -e "SELECT JOB_ID FROM DBA_OB_BACKUP_JOBS" |sed '1d')
echo "等待备份任务结束"
while true
do
LastStatus=$(obclient -h $OB_HOST -P $OB_PORT -u $OB_USER@$OB_TENANT -p$OB_PASS -o oceanbase -e "SELECT STATUS FROM DBA_OB_BACKUP_JOB_HISTORY where JOB_ID=$LastJobid" |sed '1d')
if [ "$LastStatus" = "COMPLETED" ];
then
echo "备份任务结束"
break;
fi
sleep 2
done
echo "关闭归档日志"
obclient -h $OB_HOST -P $OB_PORT -u $OB_USER@$OB_TENANT -p$OB_PASS <<EOF
ALTER SYSTEM noARCHIVELOG;
EOF
echo "等待进程"
while true
do
Status=$(obclient -h $OB_HOST -P $OB_PORT -u $OB_USER@$OB_TENANT -p$OB_PASS -o oceanbase -e "SELECT STATUS FROM DBA_OB_ARCHIVELOG" |sed '1d')
if [ "$Status" = "STOP" ];
then
echo "日志归档停止状态"
break;
fi
sleep 2
done
echo "大结束状态"
break;
fi
fi
done
wait
# 检查备份是否成功
if [ $? -eq 0 ]; then
echo "Backup successful: $BACKUP_DIR$DATE" | tee -a $LOG_FILE
else
echo "Backup failed: Check logs for details." | tee -a $LOG_FILE
fi
恢复数据库实例
以下为完整的过程介绍和解释，脚本备份忽略步骤一、步骤2
步骤一：发起日志归档
1.oracle_test 租户的租户管理员登录数据库。
2.执行以下命令，配置归档目的端。
obclient> ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/nfs/backup/archive';
3.开启日志归档模式。
obclient> ALTER SYSTEM ARCHIVELOG;
4.确认日志归档状态是否为 DOING。仅当日志归档状态为 DOING，才能发起数据备份。
obclient> SELECT * FROM DBA_OB_ARCHIVELOG\G
从查询结果中可知，日志归档状态 STATUS 为 DOING。
步骤二：发起数据备份
确认日志归档状态为 DOING 后，即可发起数据备份。
oracle_test 租户的租户管理员登录数据库。
执行以下命令，配置备份目的端。
obclient> ALTER SYSTEM SET DATA_BACKUP_DEST='file:///data/nfs/backup/data';
发起全量数据备份。
obclient> ALTER SYSTEM BACKUP DATABASE;
等待数据备份结束。
可以查询视图 DBA_OB_BACKUP_TASKS，如果查询出来的任务列表为空，则表示数据备份结束。
obclient> SELECT * FROM DBA_OB_BACKUP_TASKS;
查看数据备份结果。
obclient> SELECT * FROM DBA_OB_BACKUP_JOB_HISTORY;
步骤三：执行物理恢复
使用 root 用户登录目标租户所在集群的 sys 租户。
创建目标租户所需的资源。
创建资源单元 unit_backup。
obclient> CREATE RESOURCE UNIT unit_backup MAX_CPU 8, MEMORY_SIZE = '16G', MAX_IOPS 10240, MIN_IOPS=10240;
创建目标租户所使用的资源池，建议尽量与源租户保持同构，即资源池中 UNIT_NUM 的个数与源租户相同。
obclient> CREATE RESOURCE POOL restore_pool UNIT = 'unit_backup', UNIT_NUM = 1, ZONE_LIST = ('z1');
执行物理恢复，恢复出新租户 oracle_backup。
obclient> ALTER SYSTEM RESTORE oracle_backup FROM 'file:///data/nfs/backup/data,file:///data/nfs/backup/archive' WITH 'pool_list=restore_pool&locality=F@z1';
租户的恢复
从数据备份路径与日志归档路径恢复租户 mysql 恢复到当前最新的归档位点, 指定资源池为 restore_pool，同时指定恢复的并发度 concurrency 为 5。
ALTER SYSTEM RESTORE mysql FROM 'file:///data/nfs/backup/data,file:///data/nfs/backup/archive' WITH 'pool_list=restore_pool&concurrency=5';
评论区