ps前言: 又是好久没实操更新文章了，一是最近比较忙，二是自己也在不断的摸索学习OceanBase中，准备OBCP考试。。。
本系列总的专栏：一步一步学习OceanBase系列
关于OceanBase的备份与恢复
Ø 支持 OSS 和 NFS 两种备份介质。
Ø 从 V2.2.52 版本开始支持集群级别的物理备份。
Ø 物理备份由基线数据、日志归档数据两种数据组成。
• 日志归档是指日志数据的自动归档功能，OBServer 会定期将日志数据归档到指定的备份路径。这个动作是全自动的，只需要用户发起一次 alter system archivelog，日志备份就会在后台持续进行，不需要外部定期触发。
• 数据备份指的是备份基线数据的功能，该功能分为全量备份和增量备份两种
注意事项：
• 开始备份前一定要执行一次合并major_freeze，不然会由于版本1没有冻结时间而导致备份失败。
• 合并完成后，才能进行备份，否则会报错（后面有示例）。
• 恢复的最小粒度为租户级别，执行恢复命令恢复租户的时候是不会自动创建资源的，因此恢复前，要先创建要恢复的租户的资源池resource pool
由于OCP界面上可以进行OB的备份和恢复，本篇不通过OCP方式进行备份恢复测试，而是采用黑屏（命令操作）方式体验OceanBase的备份恢复。
以下是命令方式的操作步骤：
一、物理备份操作方法（黑屏）
1 、部署 nfs 服务器，所有 observer 都要挂载nfs目录，并能连接到 nfs 服务器 （或采用 oss 服务器）
nfs服务器端部署：10.10.10.145
yum install -y nfs-utils
systemctl status nfs
echo '/obnfs *(rw,all_squash,anonuid=500,anongid=500)' >/etc/exports
chmod 777 /obnfs
systemctl start nfs
exportfs
df -h
客户端：所有 observer 都要挂载nfs目录
yum install -y nfs-utils
showmount -e 10.10.10.145
mkdir /obbackup
chmod 777 /obbackup
mount 10.10.10.145:/obnfs /obbackup
df -h
2 、执行备份
• 2.1 配置备份目的地 ： 执行 alter system 语句配置备份目的地
nfs： obclient> alter system set backup_dest='file:///obbackup';
oss： obclient> alter system set backup_dest='oss://xxxxxxxxxxx';
• 2.2 启动 oceanbase 的数据库日志归档功能
obclient> alter system archivelog;
• 2.3 执行全量备份或增量备份
obclient> alter system major freeze; <----执行全量备份前，对集群进行一次合并
obclient> set encryption on identified by 'password' only; <----设置备份密码（可选）
obclient> alter system backup database; <----执行全量备份
obclient> alter system backup incremental database; <----执行增量备份， 确保已经有全量备份存在
3 、查看任务状态
obclient> select * from cdb_ob_backup_progress; <----查看备份任务进度
obclient> select * from cdb_ob_backup_set_details; <----查看备份历史信息
二、物理恢复操作方法（黑屏）
1 、停止日志备份
• obclient> alter system noarchivelog;
2 、执行恢复
• 2.1 创建恢复目标租户需要用到的 unit 、 resource pool
unit ： obclient> create resource unit ......
resource pool： obclient> create resource pool .....;
• 2.2 设置加密信息，以及恢复密码（如果未加密，或者恢复时可以访问原来的 KMS ，跳过本步骤； 恢复密码是备份时添加了密码场景才需要）
obclient> SET @kms_encrypt_info = '<加密string>'; <----值为EXTERNAL_KMS_INFO 的值
obclient> SET DECRYPTION IDENTIFIED BY 'password1','password2'; <----备份时设置的“全量备份”，“增量备份”的密码，未设置可跳过次步骤
• 2.3 打开恢复配置
obclient> alter system set restore_concurrency = 50; <----检查 restore_concurrency 是否为 0， 为 0 的话需要执行这条语句
• 2.4 执行恢复任务
obclient> ALTER SYSTEM RESTORE <dest_tenantname> FROM <source_tenantname> at 'uri' UNTIL 'timestamp' WITH 'restore_option';
uri指备份的时候设置的 backup_dest。
timestamp指恢复的时间戳。
restore_option 恢复选项，支持以下几种：
backup_cluster_name ：源集群的名字，必选项
backup_cluster_id ：源集群的cluster_id，必选项
pool_list ：用户的资源池，必选项
locality ：租户的locality信息，可选项
kms_encrypt：为 true 表示需要恢复的时候指定kms_encrypt_info，可选项
示例:恢复租户的数据。
alter system restore restored_trade from trade
at 'oss://antsys-oceanbasebackup/backup_rd/20200323?host=cn-hangzhou-alipay-b.oss-cdn.aliyun-inc.com&access_id=xxx&access_key=xxx'
until ' 2020-03-23 08:59:45'
with 'backup_cluster_name=obdemo&backup_cluster_id=1&pool_list=restore_pool';
alter system restore restored_trade from trade
at 'file:///obbackup'
until '2020-05-21 09:39:54.071670'
with 'backup_cluster_name=obdemo&backup_cluster_id=1&pool_list=restore_pool&locality=F@zone1,F@zone2,F@zone3';
3 、查看任务状态
obclient> SELECT svr_ip,role, is_restore, COUNT(*)
FROM __all_virtual_meta_table AS a, (SELECT value FROM __all_restore_info WHERE name='tenant_id') AS b
WHERE a.tenant_id=b.value GROUP BY role, is_restore, svr_ip ORDER BY svr_ip, is_restore
obclient> SELECT * FROM __all_restore_info;
=====================================================================
以下是实操:
一、NFS配置
1、NFS服务端配置
yum install -y nfs-utils
systemctl status nfs
echo '/obnfs *(rw,all_squash,anonuid=500,anongid=500)' >/etc/exports
chmod 777 /obnfs
systemctl start nfs
exportfs
2、NFS客户端配置：
[root@ob211 ~]# showmount -e 10.10.10.145
Export list for 10.10.10.145:
/obnfs 10.10.10.0/24
[root@ob211 ~]#
[root@ob211 ~]# mkdir /obbackup
[root@ob211 ~]# chmod 777 /obbackup/
[root@ob211 ~]#
[root@ob211 ~]# mount 10.10.10.145:/obnfs /obbackup
[root@ob211 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 130G 0 130G 0% /dev
tmpfs 130G 0 130G 0% /dev/shm
tmpfs 130G 4.2G 126G 4% /run
tmpfs 130G 0 130G 0% /sys/fs/cgroup
/dev/mapper/vg00-lv_root 8.0G 1.4G 6.7G 18% /
/dev/vda1 509M 130M 380M 26% /boot
/dev/mapper/ob_vg-ob_home 100G 9.2G 91G 10% /home
/dev/mapper/ob_vg-ob_data 400G 380G 20G 96% /data/1
/dev/mapper/ob_vg-ob_log 100G 34G 67G 34% /data/log1
/dev/mapper/vg00-lv_tmp 4.0G 34M 4.0G 1% /tmp
/dev/mapper/vg00-lv_opt 4.0G 33M 4.0G 1% /opt
/dev/mapper/vg00-lv_var 4.0G 1.8G 2.3G 44% /var
tmpfs 26G 0 26G 0% /run/user/0
10.10.10.145:/obnfs 100G 32M 100G 1% /obbackup
[root@ob211 ~]#
二、开始备份前
这里查看下mysql租户下的test库，新增几张表，供恢复后验证，比如mysql_test_tent租户下test库有三张表，test表备份前没有数据。
[admin@ocp201 ~]$ obclient -h10.10.10.33 -P2883 -uroot@mysql_test_tent#ob2273 -padmin123 -c -A test
obclient> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
| test_hash |
| test_range4 |
+----------------+
3 rows in set (0.00 sec)
obclient> select * from test;
Empty set (0.00 sec)
obclient> exit
Bye
[admin@ocp201 my]$
三、开始全备份
[admin@ocp201 my]$ obclient -h10.10.10.33 -P2883 -uroot@sys#ob2273 -pYH_admin123.com -c -A oceanbase
obclient: [Warning] Using a password on the command line interface can be insecure.
Welcome to the OceanBase monitor. Commands end with ; or \g.
Your OceanBase connection id is 1216486
Server version: 5.6.25 OceanBase 2.2.75 (r20210107182621-81357ec10e1342ef9f9e993ea38ef1cdd8778cf6) (Built Jan 7 2021 18:54:53)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
--给集群设置备份目录backup_dest
obclient> show parameters like 'backup_dest';
+-------+----------+--------------+----------+-------------+-----------+-------+-------------+----------+---------+---------+-------------------+
| zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level |
+-------+----------+--------------+----------+-------------+-----------+-------+-------------+----------+---------+---------+-------------------+
| zone2 | observer | 10.10.10.202 | 2882 | backup_dest | NULL | | backup dest | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone3 | observer | 10.10.10.131 | 2882 | backup_dest | NULL | | backup dest | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | 10.10.10.91 | 2882 | backup_dest | NULL | | backup dest | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+--------------+----------+-------------+-----------+-------+-------------+----------+---------+---------+-------------------+
3 rows in set (0.01 sec)
obclient> alter system set backup_dest='file:///obbackup';
Query OK, 0 rows affected (0.07 sec)
obclient> show parameters like 'backup_dest';
+-------+----------+--------------+----------+-------------+-----------+------------------+-------------+----------+---------+---------+-------------------+
| zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level |
+-------+----------+--------------+----------+-------------+-----------+------------------+-------------+----------+---------+---------+-------------------+
| zone2 | observer | 10.10.10.202 | 2882 | backup_dest | NULL | file:///obbackup | backup dest | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | 10.10.10.91 | 2882 | backup_dest | NULL | file:///obbackup | backup dest | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone3 | observer | 10.10.10.131 | 2882 | backup_dest | NULL | file:///obbackup | backup dest | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+--------------+----------+-------------+-----------+------------------+-------------+----------+---------+---------+-------------------+
3 rows in set (0.01 sec)
--开启日志归档
obclient> alter system archivelog;
Query OK, 0 rows affected (0.07 sec)
--第一次备份前执行一个合并
obclient> alter system major freeze;
Query OK, 0 rows affected (0.01 sec)
--开始全备份
obclient> alter system backup database;
ERROR 9040 (HY000): backup can not start, because log archive is not doing. log archive status : INTERRUPTED.
obclient> select * from cdb_ob_backup_progress;
Empty set (0.04 sec)
obclient> select * from cdb_ob_backup_set_details;
Empty set (0.01 sec)
obclient>
obclient> --这里报错，日志归档状态不正确，检查了半天，原因是合并没完成！
obclient>
obclient> source merge.sql
+-------+--------------+----------+---------------+-------+--------------+----------------+----------+----------+---------------+
| ZONE | svr_ip | svr_port | major_version | ss_sc | merged_ss_sc | modified_ss_sc | merge_st | merge_ft | merge_process |
+-------+--------------+----------+---------------+-------+--------------+----------------+----------+----------+---------------+
| zone2 | 10.10.10.202 | 2882 | 22 | 459 | 459 | 13 | 17:02:29 | 17:03:08 | 100 |
| zone3 | 10.10.10.131 | 2882 | 23 | 1395 | 1395 | 39 | 17:39:04 | 17:40:12 | 100 |
| zone2 | 10.10.10.202 | 2882 | 23 | 1395 | 1395 | 39 | 17:40:34 | 17:41:38 | 100 |
| zone1 | 10.10.10.91 | 2882 | 23 | 1395 | 1395 | 39 | 17:39:04 | 17:40:32 | 100 |
| zone3 | 10.10.10.131 | 2882 | 24 | 1395 | 1395 | 35 | 17:41:44 | 17:42:38 | 100 |
| zone2 | 10.10.10.202 | 2882 | 24 | 936 | 936 | 26 | 17:42:52 | NULL | 67 |
| zone1 | 10.10.10.91 | 2882 | 24 | 1395 | 1395 | 35 | 17:41:44 | 17:42:49 | 100 |
+-------+--------------+----------+---------------+-------+--------------+----------------+----------+----------+---------------+
7 rows in set (0.04 sec)
+----------------------------+----------------------------+-------+--------------+-------+---------+
| gmt_create | gmt_modified | zone | name | value | info |
+----------------------------+----------------------------+-------+--------------+-------+---------+
| 2021-01-06 19:17:52.486250 | 2021-01-25 17:41:38.860259 | | merge_status | 1 | MERGING |
| 2021-01-06 19:17:52.487297 | 2021-01-25 17:42:49.491282 | zone1 | merge_status | 0 | IDLE |
| 2021-01-06 19:17:52.488144 | 2021-01-25 17:42:51.713886 | zone2 | merge_status | 1 | MERGING |
| 2021-01-06 19:17:52.488969 | 2021-01-25 17:42:35.635447 | zone3 | merge_status | 0 | IDLE |
+----------------------------+----------------------------+-------+--------------+-------+---------+
4 rows in set (0.00 sec)
obclient>
obclient> source merge.sql
+-------+--------------+----------+---------------+-------+--------------+----------------+----------+----------+---------------+
| ZONE | svr_ip | svr_port | major_version | ss_sc | merged_ss_sc | modified_ss_sc | merge_st | merge_ft | merge_process |
+-------+--------------+----------+---------------+-------+--------------+----------------+----------+----------+---------------+
| zone3 | 10.10.10.131 | 2882 | 23 | 1395 | 1395 | 39 | 17:39:04 | 17:40:12 | 100 |
| zone2 | 10.10.10.202 | 2882 | 23 | 1395 | 1395 | 39 | 17:40:34 | 17:41:38 | 100 |
| zone1 | 10.10.10.91 | 2882 | 23 | 1395 | 1395 | 39 | 17:39:04 | 17:40:32 | 100 |
| zone3 | 10.10.10.131 | 2882 | 24 | 1395 | 1395 | 35 | 17:41:44 | 17:42:38 | 100 |
| zone2 | 10.10.10.202 | 2882 | 24 | 1395 | 1395 | 35 | 17:42:52 | 17:43:23 | 100 |
| zone1 | 10.10.10.91 | 2882 | 24 | 1395 | 1395 | 35 | 17:41:44 | 17:42:49 | 100 |
+-------+--------------+----------+---------------+-------+--------------+----------------+----------+----------+---------------+
6 rows in set (0.04 sec)
+----------------------------+----------------------------+-------+--------------+-------+------+
| gmt_create | gmt_modified | zone | name | value | info |
+----------------------------+----------------------------+-------+--------------+-------+------+
| 2021-01-06 19:17:52.486250 | 2021-01-25 17:43:55.400238 | | merge_status | 0 | IDLE |
| 2021-01-06 19:17:52.487297 | 2021-01-25 17:42:49.491282 | zone1 | merge_status | 0 | IDLE |
| 2021-01-06 19:17:52.488144 | 2021-01-25 17:43:45.257637 | zone2 | merge_status | 0 | IDLE |
| 2021-01-06 19:17:52.488969 | 2021-01-25 17:42:35.635447 | zone3 | merge_status | 0 | IDLE |
+----------------------------+----------------------------+-------+--------------+-------+------+
4 rows in set (0.00 sec)
obclient> --等合并完成后，开始执行全备份
obclient> alter system backup database;
Query OK, 0 rows affected (0.18 sec)
obclient> --查看备份任务状态
obclient> select * from cdb_ob_backup_progress;
Empty set (0.00 sec)
obclient> select * from cdb_ob_backup_set_details;
+-------------+-----------+--------+---------+-------------+-----------------+----------------------------+----------------------------+------------------+------+------------+-------------+------------+--------------+-------------------+-------------------+----------------------+---------------------------+--------------------+-----------+
| INCARNATION | TENANT_ID | BS_KEY | COPY_ID | BACKUP_TYPE | ENCRYPTION_MODE | START_TIME | COMPLETION_TIME | ELAPSED_SECONDES | KEEP | KEEP_UNTIL | DEVICE_TYPE | COMPRESSED | OUTPUT_BYTES | OUTPUT_RATE_BYTES | COMPRESSION_RATIO | OUTPUT_BYTES_DISPLAY | OUTPUT_RATE_BYTES_DISPLAY | TIME_TAKEN_DISPLAY | STATUS |
+-------------+-----------+--------+---------+-------------+-----------------+----------------------------+----------------------------+------------------+------+------------+-------------+------------+--------------+-------------------+-------------------+----------------------+---------------------------+--------------------+-----------+
| 1 | 1 | 1 | 0 | D | NONE | 2021-01-25 17:44:03.774420 | 2021-01-25 17:44:07.530663 | 4 | NO | | FILE | NO | 0 | 0.0000 | NULL | 0.00MB | 0.00MB/S | 00:00:03.756243 | COMPLETED |
| 1 | 1001 | 1 | 0 | D | NONE | 2021-01-25 17:44:03.774420 | 2021-01-25 17:44:06.913808 | 3 | NO | | FILE | NO | 1242592 | 395807.0809 | 0.02 | 1.19MB | 0.38MB/S | 00:00:03.139388 | COMPLETED |
+-------------+-----------+--------+---------+-------------+-----------------+----------------------------+----------------------------+------------------+------+------------+-------------+------------+--------------+-------------------+-------------------+----------------------+---------------------------+--------------------+-----------+
2 rows in set (0.00 sec)
obclient>
从以上可以看出，集群执行一次全备份完成，备份类型BACKUP_TYPE为D-全备份。查看START_TIME和COMPLETION_TIME两列的值，可以看到备份所花费的时间，后面恢复可以根据COMPLETION_TIME完成时间进行时间点恢复。
下面开始增量备份，增量备份前，往mysql_test_tent租户下test库的test表插入一条数据，后续验证增量恢复后数据是否一致。
[admin@ocp201 my]$ obclient -h10.10.10.33 -P2883 -uroot@mysql_test_tent#ob2273 -padmin123 -c -A test
obclient: [Warning] Using a password on the command line interface can be insecure.
Welcome to the OceanBase monitor. Commands end with ; or \g.
Your OceanBase connection id is 4017026
Server version: 5.6.25 OceanBase 2.2.75 (r20210107182621-81357ec10e1342ef9f9e993ea38ef1cdd8778cf6) (Built Jan 7 2021 18:54:53)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
| test_hash |
| test_range4 |
+----------------+
3 rows in set (0.00 sec)
obclient> select * from test;
Empty set (0.00 sec)
obclient> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| dt | datetime | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
obclient>
obclient> insert into test values(1,'obbackup',18,now());
Query OK, 1 row affected (0.01 sec)
obclient> commit;
Query OK, 0 rows affected (0.00 sec)
obclient>
obclient> exit
Bye
[admin@ocp201 my]$
四、增量备份
obclient> --开始增量备份
obclient> alter system backup incremental database;
Query OK, 0 rows affected (0.09 sec)
obclient> select * from cdb_ob_backup_progress;
+-------------+--------+-------------+-----------+-----------------+-------------------+------------------------+--------------------------+-------------+--------------+----------------------------+----------------------------+---------+
| INCARNATION | BS_KEY | BACKUP_TYPE | TENANT_ID | PARTITION_COUNT | MACRO_BLOCK_COUNT | FINISH_PARTITION_COUNT | FINISH_MACRO_BLOCK_COUNT | INPUT_BYTES | OUTPUT_BYTES | START_TIME | COMPLETION_TIME | STATUS |
+-------------+--------+-------------+-----------+-----------------+-------------------+------------------------+--------------------------+-------------+--------------+----------------------------+----------------------------+---------+
| 1 | 2 | I | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 2021-01-25 17:49:42.781455 | 2021-01-25 17:49:42.944130 | RUNNING |
| 1 | 2 | I | 1001 | 139 | 0 | 0 | 0 | 0 | 0 | 2021-01-25 17:49:42.781455 | 2021-01-25 17:49:42.903673 | RUNNING |
+-------------+--------+-------------+-----------+-----------------+-------------------+------------------------+--------------------------+-------------+--------------+----------------------------+----------------------------+---------+
2 rows in set (0.00 sec)
obclient> select * from cdb_ob_backup_progress;
Empty set (0.00 sec)
obclient> select * from cdb_ob_backup_set_details;
+-------------+-----------+--------+---------+-------------+-----------------+----------------------------+----------------------------+------------------+------+------------+-------------+------------+--------------+-------------------+-------------------+----------------------+---------------------------+--------------------+-----------+
| INCARNATION | TENANT_ID | BS_KEY | COPY_ID | BACKUP_TYPE | ENCRYPTION_MODE | START_TIME | COMPLETION_TIME | ELAPSED_SECONDES | KEEP | KEEP_UNTIL | DEVICE_TYPE | COMPRESSED | OUTPUT_BYTES | OUTPUT_RATE_BYTES | COMPRESSION_RATIO | OUTPUT_BYTES_DISPLAY | OUTPUT_RATE_BYTES_DISPLAY | TIME_TAKEN_DISPLAY | STATUS |
+-------------+-----------+--------+---------+-------------+-----------------+----------------------------+----------------------------+------------------+------+------------+-------------+------------+--------------+-------------------+-------------------+----------------------+---------------------------+--------------------+-----------+
| 1 | 1 | 1 | 0 | D | NONE | 2021-01-25 17:44:03.774420 | 2021-01-25 17:44:07.530663 | 4 | NO | | FILE | NO | 0 | 0.0000 | NULL | 0.00MB | 0.00MB/S | 00:00:03.756243 | COMPLETED |
| 1 | 1 | 2 | 0 | I | NONE | 2021-01-25 17:49:42.781455 | 2021-01-25 17:49:48.266004 | 5 | NO | | FILE | NO | 0 | 0.0000 | NULL | 0.00MB | 0.00MB/S | 00:00:05.484549 | COMPLETED |
| 1 | 1001 | 1 | 0 | D | NONE | 2021-01-25 17:44:03.774420 | 2021-01-25 17:44:06.913808 | 3 | NO | | FILE | NO | 1242592 | 395807.0809 | 0.02 | 1.19MB | 0.38MB/S | 00:00:03.139388 | COMPLETED |
| 1 | 1001 | 2 | 0 | I | NONE | 2021-01-25 17:49:42.781455 | 2021-01-25 17:49:47.739042 | 5 | NO | | FILE | NO | 882144 | 177938.1784 | 5.03 | 0.84MB | 0.17MB/S | 00:00:04.957587 | COMPLETED |
+-------------+-----------+--------+---------+-------------+-----------------+----------------------------+----------------------------+------------------+------+------------+-------------+------------+--------------+-------------------+-------------------+----------------------+---------------------------+--------------------+-----------+
4 rows in set (0.00 sec)
obclient>
以上可以看出，增量备份完成后，历史备份信息表cdb_ob_backup_set_details多了一次增量备份信息。备份类型BACKUP_TYPE为I-增量备份。
到此，全备和增量备份已完成，下面开始进行恢复测试：
五、全备时间点恢复测试：
--停止日志归档
obclient> alter system noarchivelog;
Query OK, 0 rows affected (0.10 sec)
obclient>
obclient> source unit_config.sql
+----------------+-----------------+---------+---------+------------+------------+------------------+
| unit_config_id | name | max_cpu | min_cpu | max_mem_gb | min_mem_gb | max_disk_size_gb |
+----------------+-----------------+---------+---------+------------+------------+------------------+
| 1 | sys_unit_config | 5 | 2.5 | 16 | 12 | 380 |
| 1001 | my_unit_1c5g | 1 | 1 | 5 | 5 | 20 |
+----------------+-----------------+---------+---------+------------+------------+------------------+
2 rows in set (0.00 sec)
obclient> source pool.sql
+--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-----------------+
| resource_pool_name | unit_config_name | max_cpu | min_cpu | max_mem_gb | min_mem_gb | unit_id | zone | observer | tenant_id | tenant_name |
+--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-----------------+
| sys_pool | sys_unit_config | 5 | 2.5 | 16 | 12 | 1 | zone1 | 10.10.10.91:2882 | 1 | sys |
| sys_pool | sys_unit_config | 5 | 2.5 | 16 | 12 | 2 | zone2 | 10.10.10.202:2882 | 1 | sys |
| sys_pool | sys_unit_config | 5 | 2.5 | 16 | 12 | 3 | zone3 | 10.10.10.131:2882 | 1 | sys |
| mysql_pool_test | my_unit_1c5g | 1 | 1 | 5 | 5 | 1001 | zone1 | 10.10.10.91:2882 | 1001 | mysql_test_tent |
| mysql_pool_test | my_unit_1c5g | 1 | 1 | 5 | 5 | 1002 | zone2 | 10.10.10.202:2882 | 1001 | mysql_test_tent |
| mysql_pool_test | my_unit_1c5g | 1 | 1 | 5 | 5 | 1003 | zone3 | 10.10.10.131:2882 | 1001 | mysql_test_tent |
+--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-----------------+
6 rows in set (0.01 sec)
obclient>
obclient> --执行恢复命令恢复租户的时候是不会自动创建资源的，所以得预先创建要恢复的租户的资源池resource pool
obclient> create resource pool mysql_pool_test_restore unit = 'my_unit_1c5g', unit_num = 1;
Query OK, 0 rows affected (0.19 sec)
obclient> source unit_config.sql
+----------------+-----------------+---------+---------+------------+------------+------------------+
| unit_config_id | name | max_cpu | min_cpu | max_mem_gb | min_mem_gb | max_disk_size_gb |
+----------------+-----------------+---------+---------+------------+------------+------------------+
| 1 | sys_unit_config | 5 | 2.5 | 16 | 12 | 380 |
| 1001 | my_unit_1c5g | 1 | 1 | 5 | 5 | 20 |
+----------------+-----------------+---------+---------+------------+------------+------------------+
2 rows in set (0.00 sec)
obclient> source pool.sql
+-------------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-----------------+
| resource_pool_name | unit_config_name | max_cpu | min_cpu | max_mem_gb | min_mem_gb | unit_id | zone | observer | tenant_id | tenant_name |
+-------------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-----------------+
| sys_pool | sys_unit_config | 5 | 2.5 | 16 | 12 | 1 | zone1 | 10.10.10.91:2882 | 1 | sys |
| sys_pool | sys_unit_config | 5 | 2.5 | 16 | 12 | 2 | zone2 | 10.10.10.202:2882 | 1 | sys |
| sys_pool | sys_unit_config | 5 | 2.5 | 16 | 12 | 3 | zone3 | 10.10.10.131:2882 | 1 | sys |
| mysql_pool_test | my_unit_1c5g | 1 | 1 | 5 | 5 | 1001 | zone1 | 10.10.10.91:2882 | 1001 | mysql_test_tent |
| mysql_pool_test | my_unit_1c5g | 1 | 1 | 5 | 5 | 1002 | zone2 | 10.10.10.202:2882 | 1001 | mysql_test_tent |
| mysql_pool_test | my_unit_1c5g | 1 | 1 | 5 | 5 | 1003 | zone3 | 10.10.10.131:2882 | 1001 | mysql_test_tent |
| mysql_pool_test_restore | my_unit_1c5g | 1 | 1 | 5 | 5 | 1004 | zone1 | 10.10.10.91:2882 | NULL | NULL |
| mysql_pool_test_restore | my_unit_1c5g | 1 | 1 | 5 | 5 | 1005 | zone2 | 10.10.10.202:2882 | NULL | NULL |
| mysql_pool_test_restore | my_unit_1c5g | 1 | 1 | 5 | 5 | 1006 | zone3 | 10.10.10.131:2882 | NULL | NULL |
+-------------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-----------------+
9 rows in set (0.00 sec)
obclient> --设置恢复并行度 restore_concurrency 是否为 0， 为 0 的话需要执行这条语句
obclient> show parameters like 'restore_concurrency';
+-------+----------+--------------+----------+---------------------+-----------+-------+-------------------------------------------------------------------------------+----------+---------+---------+-------------------+
| zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level |
+-------+----------+--------------+----------+---------------------+-----------+-------+-------------------------------------------------------------------------------+----------+---------+---------+-------------------+
| zone3 | observer | 10.10.10.131 | 2882 | restore_concurrency | NULL | 0 | the current work thread num of restore macro block. Range: [0,512] in integer | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone2 | observer | 10.10.10.202 | 2882 | restore_concurrency | NULL | 0 | the current work thread num of restore macro block. Range: [0,512] in integer | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone1 | observer | 10.10.10.91 | 2882 | restore_concurrency | NULL | 0 | the current work thread num of restore macro block. Range: [0,512] in integer | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+--------------+----------+---------------------+-----------+-------+-------------------------------------------------------------------------------+----------+---------+---------+-------------------+
3 rows in set (0.00 sec)
obclient> alter system set restore_concurrency = 20;
Query OK, 0 rows affected (0.05 sec)
obclient> show parameters like 'restore_concurrency';
+-------+----------+--------------+----------+---------------------+-----------+-------+-------------------------------------------------------------------------------+----------+---------+---------+-------------------+
| zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level |
+-------+----------+--------------+----------+---------------------+-----------+-------+-------------------------------------------------------------------------------+----------+---------+---------+-------------------+
| zone1 | observer | 10.10.10.91 | 2882 | restore_concurrency | NULL | 20 | the current work thread num of restore macro block. Range: [0,512] in integer | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone2 | observer | 10.10.10.202 | 2882 | restore_concurrency | NULL | 20 | the current work thread num of restore macro block. Range: [0,512] in integer | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
| zone3 | observer | 10.10.10.131 | 2882 | restore_concurrency | NULL | 20 | the current work thread num of restore macro block. Range: [0,512] in integer | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
+-------+----------+--------------+----------+---------------------+-----------+-------+-------------------------------------------------------------------------------+----------+---------+---------+-------------------+
3 rows in set (0.01 sec)
obclient>
obclient> --开始恢复到全备份的时间点
obclient> alter system restore mysql_tent_restore from mysql_test_tent
-> at 'file:///obbackup'
-> until '2021-01-25 17:44:06.913808'
-> with 'backup_cluster_name=ob2273&backup_cluster_id=1&pool_list=mysql_pool_test_restore&locality=F@zone1,F@zone2,F@zone3';
Query OK, 0 rows affected (0.04 sec)
obclient>
obclient> --查看恢复进度
obclient> SELECT svr_ip,role, is_restore, COUNT(*)
-> FROM __all_virtual_meta_table AS a, (SELECT value FROM __all_restore_info WHERE name='mysql_pool_test_restore') AS b
-> WHERE a.tenant_id=b.value GROUP BY role, is_restore, svr_ip ORDER BY svr_ip, is_restore
-> ;
+--------------+------+------------+----------+
| svr_ip | role | is_restore | count(*) |
+--------------+------+------------+----------+
| 10.10.10.131 | 2 | 6 | 18 |
| 10.10.10.202 | 2 | 2 | 18 |
| 10.10.10.91 | 4 | 6 | 18 |
+--------------+------+------------+----------+
3 rows in set (0.01 sec)
obclient>
obclient> SELECT * FROM __all_restore_info;
+----------------------------+----------------------------+--------+-------------------------+-------------------------------------------------------------------------------------------------------------------+
| gmt_create | gmt_modified | job_id | name | value |
+----------------------------+----------------------------+--------+-------------------------+-------------------------------------------------------------------------------------------------------------------+
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | backup_cluster_name | ob2273 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | backup_dest | file:///obbackup |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | backup_locality | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | backup_primary_zone | zone1;zone2 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | backup_tenant_id | 1001 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | backup_tenant_name | mysql_test_tent |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | cluster_id | 1 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | compatible | 1 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | compat_mode | 0 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | frozen_data_version | 24 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | frozen_schema_version | 1611557281103424 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | frozen_snapshot_version | 1611567554519797 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | full_backup_set_id | 1 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | incarnation | 1 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | inc_backup_set_id | 1 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | info | |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | kms_encrypt | 0 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | kms_info | |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | locality | F@zone1,F@zone2,F@zone3 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | log_archive_round | 2 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | passwd_array | |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | pool_list | mysql_pool_test_restore |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | post_cluster_version | 2.2.75 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | pre_cluster_version | 2.2.75 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | primary_zone | |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | restore_data_version | 24 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | restore_job_id | -1 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | restore_option | backup_cluster_name=ob2273&backup_cluster_id=1&pool_list=mysql_pool_test_restore&locality=F@zone1,F@zone2,F@zone3 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | restore_start_ts | 1611568978213317 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | restore_timestamp | 1611567846913808 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | schema_version | 1611557281103424 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | snapshot_version | 1611567843774420 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | source_cluster_version | 2.2.75 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | status | RESTORE_SYS_REPLICA |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | tenant_id | 1002 |
| 2021-01-25 18:02:58.897874 | 2021-01-25 18:02:58.897874 | 6 | tenant_name | mysql_tent_restore |
+----------------------------+----------------------------+--------+-------------------------+-------------------------------------------------------------------------------------------------------------------+
36 rows in set (0.00 sec)
obclient>
obclient> --恢复完成后
obclient> select svr_ip,role, is_restore, count(*) from __all_virtual_meta_table as a, (select value from __all_restore_info where name='tenant_id') as b where a.tenant_id=b.value group by role, is_restore, svr_ip order by svr_ip, is_restore;
Empty set (0.01 sec)
obclient>
obclient> SELECT * FROM __all_restore_info;
Empty set (0.00 sec)
obclient>
obclient> source tent.sql
+-----------+--------------------+--------------------+-------------------+---------------------------------------------+-------------------+----------------------------+
| tenant_id | tenant_name | compatibility_mode | zone_list | locality | primary_zone | gmt_modified |
+-----------+--------------------+--------------------+-------------------+---------------------------------------------+-------------------+----------------------------+
| 1 | sys | 0 | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | zone1;zone2,zone3 | 2021-01-06 19:20:14.239456 |
| 1001 | mysql_test_tent | 0 | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | zone1;zone2 | 2021-01-25 14:47:10.257519 |
| 1002 | mysql_tent_restore | 0 | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | zone1;zone2 | 2021-01-25 18:02:58.338411 |
+-----------+--------------------+--------------------+-------------------+---------------------------------------------+-------------------+----------------------------+
3 rows in set (0.00 sec)
obclient>
--验证全备恢复
[admin@ocp201 my]$ obclient -h10.10.10.33 -P2883 -uroot@mysql_tent_restore#ob2273 -padmin123 -c -A test
obclient: [Warning] Using a password on the command line interface can be insecure.
Welcome to the OceanBase monitor. Commands end with ; or \g.
Your OceanBase connection id is 166356
Server version: 5.6.25 OceanBase 2.2.75 (r20210107182621-81357ec10e1342ef9f9e993ea38ef1cdd8778cf6) (Built Jan 7 2021 18:54:53)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
| test_hash |
| test_range4 |
+----------------+
3 rows in set (0.00 sec)
obclient> select * from test;
Empty set (0.00 sec)
obclient> exit
Bye
[admin@ocp201 my]$
可以看到，全备恢复后，test表没有数据。
六、增量备份时间点恢复测试
obclient> --下面开始恢复到增量备份的时间点，再创建一个新租户的资源池
obclient> create resource pool mysql_pool_test_restore2 unit = 'my_unit_1c5g', unit_num = 1;
Query OK, 0 rows affected (0.14 sec)
obclient> source pool.sql
+--------------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+--------------------+
| resource_pool_name | unit_config_name | max_cpu | min_cpu | max_mem_gb | min_mem_gb | unit_id | zone | observer | tenant_id | tenant_name |
+--------------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+--------------------+
| sys_pool | sys_unit_config | 5 | 2.5 | 16 | 12 | 1 | zone1 | 10.10.10.91:2882 | 1 | sys |
| sys_pool | sys_unit_config | 5 | 2.5 | 16 | 12 | 2 | zone2 | 10.10.10.202:2882 | 1 | sys |
| sys_pool | sys_unit_config | 5 | 2.5 | 16 | 12 | 3 | zone3 | 10.10.10.131:2882 | 1 | sys |
| mysql_pool_test | my_unit_1c5g | 1 | 1 | 5 | 5 | 1001 | zone1 | 10.10.10.91:2882 | 1001 | mysql_test_tent |
| mysql_pool_test | my_unit_1c5g | 1 | 1 | 5 | 5 | 1002 | zone2 | 10.10.10.202:2882 | 1001 | mysql_test_tent |
| mysql_pool_test | my_unit_1c5g | 1 | 1 | 5 | 5 | 1003 | zone3 | 10.10.10.131:2882 | 1001 | mysql_test_tent |
| mysql_pool_test_restore | my_unit_1c5g | 1 | 1 | 5 | 5 | 1004 | zone1 | 10.10.10.91:2882 | 1002 | mysql_tent_restore |
| mysql_pool_test_restore | my_unit_1c5g | 1 | 1 | 5 | 5 | 1005 | zone2 | 10.10.10.202:2882 | 1002 | mysql_tent_restore |
| mysql_pool_test_restore | my_unit_1c5g | 1 | 1 | 5 | 5 | 1006 | zone3 | 10.10.10.131:2882 | 1002 | mysql_tent_restore |
| mysql_pool_test_restore2 | my_unit_1c5g | 1 | 1 | 5 | 5 | 1007 | zone1 | 10.10.10.91:2882 | NULL | NULL |
| mysql_pool_test_restore2 | my_unit_1c5g | 1 | 1 | 5 | 5 | 1008 | zone2 | 10.10.10.202:2882 | NULL | NULL |
| mysql_pool_test_restore2 | my_unit_1c5g | 1 | 1 | 5 | 5 | 1009 | zone3 | 10.10.10.131:2882 | NULL | NULL |
+--------------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+--------------------+
12 rows in set (0.01 sec)
obclient> --开始恢复到增量备份的时间点
obclient> alter system restore mysql_tent_restore_inc from mysql_test_tent
-> at 'file:///obbackup'
-> until '2021-01-25 17:49:47.739042'
-> with 'backup_cluster_name=ob2273&backup_cluster_id=1&pool_list=mysql_pool_test_restore2&locality=F@zone1,F@zone2,F@zone3';
Query OK, 0 rows affected (0.03 sec)
obclient>
obclient> --查看恢复进度
obclient> select svr_ip,role, is_restore, count(*)
-> from __all_virtual_meta_table as a, (select value from __all_restore_info where name='tenant_id') as b
-> where a.tenant_id=b.value group by role, is_restore, svr_ip order by svr_ip, is_restore;
+--------------+------+------------+----------+
| svr_ip | role | is_restore | count(*) |
+--------------+------+------------+----------+
| 10.10.10.131 | 2 | 2 | 18 |
| 10.10.10.202 | 2 | 2 | 18 |
| 10.10.10.91 | 4 | 6 | 18 |
+--------------+------+------------+----------+
3 rows in set (0.01 sec)
obclient>
obclient> select svr_ip,role, is_restore, count(*) from __all_virtual_meta_table as a, (select value from __all_restore_info where name='tenant_id') as b where a.tenant_id=b.value group by role, is_restore, s
vr_ip order by svr_ip, is_restore;
+--------------+------+------------+----------+
| svr_ip | role | is_restore | count(*) |
+--------------+------+------------+----------+
| 10.10.10.131 | 2 | 0 | 18 |
| 10.10.10.202 | 1 | 0 | 6 |
| 10.10.10.202 | 2 | 0 | 9 |
| 10.10.10.202 | 1 | 7 | 3 |
| 10.10.10.91 | 1 | 0 | 9 |
| 10.10.10.91 | 2 | 0 | 9 |
+--------------+------+------------+----------+
6 rows in set (0.01 sec)
obclient>
obclient> SELECT * FROM __all_restore_info;
+----------------------------+----------------------------+--------+-------------------------+--------------------------------------------------------------------------------------------------------------------+
| gmt_create | gmt_modified | job_id | name | value |
+----------------------------+----------------------------+--------+-------------------------+--------------------------------------------------------------------------------------------------------------------+
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | backup_cluster_name | ob2273 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | backup_dest | file:///obbackup |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | backup_locality | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | backup_primary_zone | zone1;zone2 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | backup_tenant_id | 1001 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | backup_tenant_name | mysql_test_tent |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | cluster_id | 1 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | compatible | 1 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | compat_mode | 0 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | frozen_data_version | 24 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | frozen_schema_version | 1611557281103424 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | frozen_snapshot_version | 1611567554519797 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | full_backup_set_id | 1 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | incarnation | 1 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | inc_backup_set_id | 2 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | info | |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | kms_encrypt | 0 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | kms_info | |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | locality | F@zone1,F@zone2,F@zone3 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | log_archive_round | 2 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | passwd_array | |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | pool_list | mysql_pool_test_restore2 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | post_cluster_version | 2.2.75 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | pre_cluster_version | 2.2.75 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | primary_zone | |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | restore_data_version | 24 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | restore_job_id | -1 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | restore_option | backup_cluster_name=ob2273&backup_cluster_id=1&pool_list=mysql_pool_test_restore2&locality=F@zone1,F@zone2,F@zone3 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | restore_start_ts | 1611569653670668 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | restore_timestamp | 1611568187739042 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | schema_version | 1611557281103424 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | snapshot_version | 1611568182781455 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | source_cluster_version | 2.2.75 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | status | RESTORE_SYS_REPLICA |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | tenant_id | 1003 |
| 2021-01-25 18:14:14.140595 | 2021-01-25 18:14:14.140595 | 7 | tenant_name | mysql_tent_restore_inc |
+----------------------------+----------------------------+--------+-------------------------+--------------------------------------------------------------------------------------------------------------------+
36 rows in set (0.00 sec)
obclient>
obclient> --恢复完成后
obclient> select svr_ip,role, is_restore, count(*) from __all_virtual_meta_table as a, (select value from __all_restore_info where name='tenant_id') as b where a.tenant_id=b.value group by role, is_restore, svr_ip order by svr_ip, is_restore;
Empty set (0.01 sec)
obclient>
obclient> SELECT * FROM __all_restore_info;
Empty set (0.00 sec)
obclient>
obclient> source tent.sql
+-----------+------------------------+--------------------+-------------------+---------------------------------------------+-------------------+----------------------------+
| tenant_id | tenant_name | compatibility_mode | zone_list | locality | primary_zone | gmt_modified |
+-----------+------------------------+--------------------+-------------------+---------------------------------------------+-------------------+----------------------------+
| 1 | sys | 0 | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | zone1;zone2,zone3 | 2021-01-06 19:20:14.239456 |
| 1001 | mysql_test_tent | 0 | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | zone1;zone2 | 2021-01-25 14:47:10.257519 |
| 1002 | mysql_tent_restore | 0 | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | zone1;zone2 | 2021-01-25 18:09:40.325184 |
| 1003 | mysql_tent_restore_inc | 0 | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | zone1;zone2 | 2021-01-25 18:14:13.759212 |
+-----------+------------------------+--------------------+-------------------+---------------------------------------------+-------------------+----------------------------+
4 rows in set (0.01 sec)
obclient>
--验证增量恢复
[admin@ocp201 my]$ obclient -h10.10.10.33 -P2883 -uroot@mysql_tent_restore_inc#ob2273 -padmin123 -c -A test
obclient: [Warning] Using a password on the command line interface can be insecure.
Welcome to the OceanBase monitor. Commands end with ; or \g.
Your OceanBase connection id is 166642
Server version: 5.6.25 OceanBase 2.2.75 (r20210107182621-81357ec10e1342ef9f9e993ea38ef1cdd8778cf6) (Built Jan 7 2021 18:54:53)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
| test_hash |
| test_range4 |
+----------------+
3 rows in set (0.01 sec)
obclient> select * from test;
+----+----------+------+---------------------+
| id | name | age | dt |
+----+----------+------+---------------------+
| 1 | obbackup | 18 | 2021-01-25 17:49:29 |
+----+----------+------+---------------------+
1 row in set (0.01 sec)
obclient>
可以看到，恢复到增量时间点，test表新插入的数据存在。
到此，OceanBase的备份恢复命令方式操作体验到此结束。
一步一步学习OceanBase系列
最后修改时间：2021-02-23 22:17:20
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
