OceanBase4.1 物理主备库搭建

物理主备库功能

OceanBase在4.1版本之前，物理备库只支持集群级别，备集群下所有租户都是备租户，所有的操作都是在主集群做操作，在实际的场景中备集群的资源会有很大的浪费，并且对于主集群的压力也是比较大，在4.1版本中物理备库支持租户级别，可以两套集群分别有主租户和备租户，增加了资源利用率，也将流量进行了分布

如下图集群 A,B中分别由主备租户做相互的复制，两套集群中的Primary租户可以做读写操作，Standby为只读服务。这里就参照下面这张图做部署搭建

OceanBase4.1 物理主备库搭建-1

环境信息

	集群A	集群B
zone1-IP	172.31.88.35	172.31.88.32
zone2-IP	172.31.88.33	172.31.88.36
zone3-IP	172.31.88.34	172.31.88.31
归档地址	/data/obce3zone-A/backup/tenant_A_primary/archive	/data/obce3zone-B/backup/tenant_B_primary/archive
备份地址	/data/obce3zone-A/backup/tenant_A_primary/data	/data/obce3zone-B/backup/tenant_B_primary/archive
主租户	tenant_A	tenant_B
备租户	tenant_B_standby	tenant_A_standby

前提需要搭建好A,B两套集群，并分别创建两个租户tenant_A, tenant_B , 两个租户相互之间互作物理备库，租户资源规格4C8G。

搭建可以参考:

OceanBase 4.1社区版可视化部署

整体操作会比较多，操作时会在两套集群中来回切换，需要注意所操作的集群、租户、目录

设置集群归档备份

首先分别要对集群A,B开启归档和备份操作，后面会利用备份进行恢复及使用归档日志实现数据同步，关于更多备份恢复内容可以参考:

OceanBase物理备份恢复-初试

集群A:

ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/obce3zone-A/backup/tenant_A_primary/archive';
ALTER SYSTEM SET data_backup_dest='file:///data/obce3zone-A/backup/tenant_A_primary/data';
ALTER SYSTEM ARCHIVELOG;

查看归档状态为DOING后再执行备份

obclient [(none)]> SELECT * FROM oceanbase.DBA_OB_ARCHIVELOG;
+---------+----------+-------------+---------+--------+---------------------+----------------------------+---------------------+----------------------------+------------+---------------+---------------+-----------------------+-----------+-------------+-------------+---------------------+--------------+----------------------+-------------------+---------------------+-----------------------------+----------------------+------------------------------+---------+----------------------------------------------------------+
| DEST_ID | ROUND_ID | INCARNATION | DEST_NO | STATUS | START_SCN           | START_SCN_DISPLAY          | CHECKPOINT_SCN      | CHECKPOINT_SCN_DISPLAY     | COMPATIBLE | BASE_PIECE_ID | USED_PIECE_ID | PIECE_SWITCH_INTERVAL | UNIT_SIZE | COMPRESSION | INPUT_BYTES | INPUT_BYTES_DISPLAY | OUTPUT_BYTES | OUTPUT_BYTES_DISPLAY | COMPRESSION_RATIO | DELETED_INPUT_BYTES | DELETED_INPUT_BYTES_DISPLAY | DELETED_OUTPUT_BYTES | DELETED_OUTPUT_BYTES_DISPLAY | COMMENT | PATH                                                     |
+---------+----------+-------------+---------+--------+---------------------+----------------------------+---------------------+----------------------------+------------+---------------+---------------+-----------------------+-----------+-------------+-------------+---------------------+--------------+----------------------+-------------------+---------------------+-----------------------------+----------------------+------------------------------+---------+----------------------------------------------------------+
|    1001 |        1 |           1 |       0 | DOING  | 1683124331278804535 | 2023-05-03 22:32:11.278804 | 1683124430888802489 | 2023-05-03 22:33:50.888802 |          1 |             1 |             1 |           86400000000 |         1 | none        |    13508462 | 12.88MB             |     13508462 | 12.88MB              |              1.00 |                   0 | 0.00MB                      |                    0 | 0.00MB                       |         | file:///data/obce3zone-A/backup/tenant_A_primary/archive |
+---------+----------+-------------+---------+--------+---------------------+----------------------------+---------------------+----------------------------+------------+---------------+---------------+-----------------------+-----------+-------------+-------------+---------------------+--------------+----------------------+-------------------+---------------------+-----------------------------+----------------------+------------------------------+---------+----------------------------------------------------------+
1 row in set (0.001 sec)

ALTER SYSTEM BACKUP DATABASE;

记录备份集时间点，后面恢复时需要用到:

obclient [(none)]> SELECT * FROM oceanbase.DBA_OB_BACKUP_TASK_HISTORY;
+---------+--------+-------------+---------------+----------------------------+----------------------------+-----------+---------------------+---------------------+---------------------+-----------------+--------+-------------+--------------+-------------------+------------------+--------------+---------------------+-------------------+--------------------------+------------+--------------+--------------+--------+---------+-------------------------------------------------------+
| TASK_ID | JOB_ID | INCARNATION | BACKUP_SET_ID | START_TIMESTAMP            | END_TIMESTAMP              | STATUS    | START_SCN           | END_SCN             | USER_LS_START_SCN   | ENCRYPTION_MODE | PASSWD | INPUT_BYTES | OUTPUT_BYTES | OUTPUT_RATE_BYTES | EXTRA_META_BYTES | TABLET_COUNT | FINISH_TABLET_COUNT | MACRO_BLOCK_COUNT | FINISH_MACRO_BLOCK_COUNT | FILE_COUNT | META_TURN_ID | DATA_TURN_ID | RESULT | COMMENT | PATH                                                  |
+---------+--------+-------------+---------------+----------------------------+----------------------------+-----------+---------------------+---------------------+---------------------+-----------------+--------+-------------+--------------+-------------------+------------------+--------------+---------------------+-------------------+--------------------------+------------+--------------+--------------+--------+---------+-------------------------------------------------------+
|       1 |      1 |           1 |             1 | 2023-05-03 22:34:07.140669 | 2023-05-03 22:35:51.021938 | COMPLETED | 1683124447176879000 | 1683124550994716000 | 1683124453294985499 | NONE            |        |   126506262 |     20012388 |       192646.7417 |                0 |          566 |                 566 |                60 |                       60 |          0 |            1 |            1 |      0 |         | file:///data/obce3zone-A/backup/tenant_A_primary/data |
+---------+--------+-------------+---------------+----------------------------+----------------------------+-----------+---------------------+---------------------+---------------------+-----------------+--------+-------------+--------------+-------------------+------------------+--------------+---------------------+-------------------+--------------------------+------------+--------------+--------------+--------+---------+-------------------------------------------------------+
1 row in set (0.001 sec)

同样集群B也做相同的操作:

ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/obce3zone-B/backup/tenant_B_primary/archive';
ALTER SYSTEM SET data_backup_dest='file:///data/obce3zone-B/backup/tenant_B_primary/data';
ALTER SYSTEM ARCHIVELOG;
ALTER SYSTEM BACKUP DATABASE;

记录备份集时间点:

obclient [OCEANBASE]> SELECT * FROM oceanbase.DBA_OB_BACKUP_TASK_HISTORY;
+---------+--------+-------------+---------------+----------------------------+----------------------------+-----------+---------------------+---------------------+---------------------+-----------------+--------+-------------+--------------+-------------------+------------------+--------------+---------------------+-------------------+--------------------------+------------+--------------+--------------+--------+---------+-------------------------------------------------------+
| TASK_ID | JOB_ID | INCARNATION | BACKUP_SET_ID | START_TIMESTAMP            | END_TIMESTAMP              | STATUS    | START_SCN           | END_SCN             | USER_LS_START_SCN   | ENCRYPTION_MODE | PASSWD | INPUT_BYTES | OUTPUT_BYTES | OUTPUT_RATE_BYTES | EXTRA_META_BYTES | TABLET_COUNT | FINISH_TABLET_COUNT | MACRO_BLOCK_COUNT | FINISH_MACRO_BLOCK_COUNT | FILE_COUNT | META_TURN_ID | DATA_TURN_ID | RESULT | COMMENT | PATH                                                  |
+---------+--------+-------------+---------------+----------------------------+----------------------------+-----------+---------------------+---------------------+---------------------+-----------------+--------+-------------+--------------+-------------------+------------------+--------------+---------------------+-------------------+--------------------------+------------+--------------+--------------+--------+---------+-------------------------------------------------------+
|       1 |      1 |           1 |             1 | 2023-05-03 22:44:42.152611 | 2023-05-03 22:46:18.981214 | COMPLETED | 1683125082188117000 | 1683125178954701000 | 1683125085300019485 | NONE            |        |   126506213 |     19742049 |       203886.5417 |                0 |          566 |                 566 |                60 |                       60 |          0 |            1 |            1 |      0 |         | file:///data/obce3zone-B/backup/tenant_B_primary/data |
+---------+--------+-------------+---------------+----------------------------+----------------------------+-----------+---------------------+---------------------+---------------------+-----------------+--------+-------------+--------------+-------------------+------------------+--------------+---------------------+-------------------+--------------------------+------------+--------------+--------------+--------+---------+-------------------------------------------------------+
1 row in set (0.001 sec)

恢复出备租户

要利用备份恢复租户之前，需要在对应的集群上创建所承接租户的资源池，例如我们要在B集群上恢复A集群中的租户tenant_A，我们就要在A集群中创建对应的资源池来承接恢复出的tenant_A_standby，同理A集群也一样恢复出的租户名是tenant_B_standby，具体操作如下:

B集群创建资源pool

create resource unit config_tenant_A_zone1_4C8G_bha max_cpu=4, min_cpu=4, MEMORY_SIZE='8G', max_iops=40000, min_iops=40000, LOG_DISK_SIZE='15G';
create resource unit config_tenant_A_zone2_4C8G_jcz max_cpu=4, min_cpu=4, MEMORY_SIZE='8G', max_iops=40000, min_iops=40000, LOG_DISK_SIZE='15G';
create resource unit config_tenant_A_zone3_4C8G_fjs max_cpu=4, min_cpu=4, MEMORY_SIZE='8G', max_iops=40000, min_iops=40000, LOG_DISK_SIZE='15G';
CREATE RESOURCE POOL config_tenant_A_zone1_4C8G_bha unit = 'config_tenant_A_zone1_4C8G_bha', unit_num = 1, zone_list = ('zone1');
CREATE RESOURCE POOL config_tenant_A_zone2_4C8G_jcz unit = 'config_tenant_A_zone2_4C8G_jcz', unit_num = 1, zone_list = ('zone2');
CREATE RESOURCE POOL config_tenant_A_zone3_4C8G_fjs unit = 'config_tenant_A_zone3_4C8G_fjs', unit_num = 1, zone_list = ('zone3');

B集群中恢复备租户tenant_A

ALTER SYSTEM RESTORE tenant_A_standby FROM 'file:///data/obce3zone-A/backup/tenant_A_primary/data,file:///data/obce3zone-A/backup/tenant_A_primary/archive' until TIME='2023-05-03 22:35:51.021938' WITH 'pool_list=config_tenant_A_zone1_4C8G_bha,config_tenant_A_zone2_4C8G_jcz,config_tenant_A_zone3_4C8G_fjs';
obclient [oceanbase]> SELECT * FROM CDB_OB_RESTORE_HISTORY;
+-----------+--------+---------------------+-------------------+--------------------+------------------+---------------------+----------------------------------------------------------------------------------------------------------------+---------------------+----------------------------+--------------------------------------------------------------------------------------------------------+----------------------------+----------------------------+---------+--------------------------------------------------------------------------+-------------------------------------------------------------------------+------------------------+----------+-----------------+--------------+---------------------+-------------+---------------------+--------------+----------------------+-------------+---------+
| TENANT_ID | JOB_ID | RESTORE_TENANT_NAME | RESTORE_TENANT_ID | BACKUP_TENANT_NAME | BACKUP_TENANT_ID | BACKUP_CLUSTER_NAME | BACKUP_DEST                                                                                                    | RESTORE_SCN         | RESTORE_SCN_DISPLAY        | RESTORE_OPTION                                                                                         | START_TIMESTAMP            | FINISH_TIMESTAMP           | STATUS  | BACKUP_PIECE_LIST                                                        | BACKUP_SET_LIST                                                         | BACKUP_CLUSTER_VERSION | LS_COUNT | FINISH_LS_COUNT | TABLET_COUNT | FINISH_TABLET_COUNT | TOTAL_BYTES | TOTAL_BYTES_DISPLAY | FINISH_BYTES | FINISH_BYTES_DISPLAY | DESCRIPTION | COMMENT |
+-----------+--------+---------------------+-------------------+--------------------+------------------+---------------------+----------------------------------------------------------------------------------------------------------------+---------------------+----------------------------+--------------------------------------------------------------------------------------------------------+----------------------------+----------------------------+---------+--------------------------------------------------------------------------+-------------------------------------------------------------------------+------------------------+----------+-----------------+--------------+---------------------+-------------+---------------------+--------------+----------------------+-------------+---------+
|         1 |      3 | tenant_A_standby    |              1006 | tenant_A           |             1004 | obce3zoneA          | file:///data/obce3zone-A/backup/tenant_A_primary/data,file:///data/obce3zone-A/backup/tenant_A_primary/archive | 1683124551021938000 | 2023-05-03 22:35:51.021938 | pool_list=config_tenant_A_zone1_4C8G_bha,config_tenant_A_zone2_4C8G_jcz,config_tenant_A_zone3_4C8G_fjs | 2023-05-03 23:07:52.881064 | 2023-05-03 23:11:07.154044 | SUCCESS | file:///data/obce3zone-A/backup/tenant_A_primary/archive/piece_d1001r1p1 | file:///data/obce3zone-A/backup/tenant_A_primary/data/backup_set_1_full |            17179934720 |        4 |               0 |          566 |                   0 |    20012388 | 19.09MB             |            0 | 0.00MB               | NULL        | NULL    |
|      1006 |      3 | tenant_A_standby    |              1006 | tenant_A           |             1004 | obce3zoneA          | file:///data/obce3zone-A/backup/tenant_A_primary/data,file:///data/obce3zone-A/backup/tenant_A_primary/archive | 1683124551021938000 | 2023-05-03 22:35:51.021938 | pool_list=config_tenant_A_zone1_4C8G_bha,config_tenant_A_zone2_4C8G_jcz,config_tenant_A_zone3_4C8G_fjs | 2023-05-03 23:07:52.881064 | 2023-05-03 23:10:56.726624 | SUCCESS | file:///data/obce3zone-A/backup/tenant_A_primary/archive/piece_d1001r1p1 | file:///data/obce3zone-A/backup/tenant_A_primary/data/backup_set_1_full |            17179934720 |        4 |               0 |          566 |                   0 |    20012388 | 19.09MB             |            0 | 0.00MB               | NULL        | NULL    |
+-----------+--------+---------------------+-------------------+--------------------+------------------+---------------------+----------------------------------------------------------------------------------------------------------------+---------------------+----------------------------+--------------------------------------------------------------------------------------------------------+----------------------------+----------------------------+---------+--------------------------------------------------------------------------+-------------------------------------------------------------------------+------------------------+----------+-----------------+--------------+---------------------+-------------+---------------------+--------------+----------------------+-------------+---------+
2 rows in set (0.018 sec)

在执行完restore后通过SELECT * FROM CDB_OB_RESTORE_PROGRESS;可以查看恢复进度，恢复完成后会在CDB_OB_RESTORE_HISTORY表中有相应的记录，等待恢复完成后再执行recover，设置UNLIMITED表示一直进行同步归档日志

ALTER SYSTEM RECOVER STANDBY tenant = tenant_A_standby UNTIL UNLIMITED;

上面这几步操作都使用sys租户管理员用户执行

执行后监控同步状态，SCN_TO_TIMESTAMP(SYNC_SCN)就是将同步的SCN转换成时间，拿这个时间和集群B本地时间做对比能判断延迟:

obclient [oceanbase]> SELECT TENANT_NAME, TENANT_ROLE, SWITCHOVER_STATUS, SCN_TO_TIMESTAMP(SYNC_SCN)  FROM oceanbase.DBA_OB_TENANTS WHERE TENANT_NAME = 'tenant_A_standby';
+------------------+-------------+-------------------+----------------------------+
| TENANT_NAME      | TENANT_ROLE | SWITCHOVER_STATUS | SCN_TO_TIMESTAMP(SYNC_SCN) |
+------------------+-------------+-------------------+----------------------------+
| tenant_A_standby | STANDBY     | NORMAL            | 2023-05-03 23:17:18.434825 |
+------------------+-------------+-------------------+----------------------------+
1 row in set (0.005 sec)

在集群A中做一些操作看下是否同步正常

[obadmin@iZ2zec4al0gmf3iqwixdhyZ ~]$ obclient -h172.31.88.35 -P2883 -uroot@tenant_A -p
Enter password:
Welcome to the OceanBase.  Commands end with ; or \\g.
Your OceanBase connection id is 1048636
Server version: OceanBase_CE 4.1.0.0 (r100000202023040520-0765e69043c31bf86e83b5d618db0530cf31b707) (Built Apr  5 2023 20:26:14)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.

obclient [(none)]> use test;
Database changed
obclient [test]> create table dhytest (id int , name char(20));
Query OK, 0 rows affected (0.181 sec)

obclient [test]> insert into dhytest values (1,'donghy');
Query OK, 1 row affected (0.044 sec)

obclient [test]> insert into dhytest values (2,'hanjie');
Query OK, 1 row affected (0.001 sec)

obclient [test]> insert into dhytest values (3,'shengang');
Query OK, 1 row affected (0.001 sec)

obclient [test]>

[obadmin@iZ2zec4al0gmf3iqwixdhzZ ~]$ obclient -h172.31.88.32 -P2883 -uroot@tenant_A_standby -p
Enter password:
Welcome to the OceanBase.  Commands end with ; or \\g.
Your OceanBase connection id is 3221510497
Server version: OceanBase_CE 4.1.0.0 (r100000202023040520-0765e69043c31bf86e83b5d618db0530cf31b707) (Built Apr  5 2023 20:26:14)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.

obclient [(none)]>
obclient [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
obclient [test]> select * from dhytest;
+------+----------+
| id   | name     |
+------+----------+
|    1 | donghy   |
|    2 | hanjie   |
|    3 | shengang |
+------+----------+
3 rows in set (0.001 sec)

数据可以顺利同步，证明从A→B集群 tenant_A租户物理复制是没有问题的。

同样在集群A中也做相同操作，搭建从B→A集群 tenant_B租户的物理复制

A集群创建资源pool

create resource unit config_tenant_B_zone1_4C8G_bha max_cpu=4, min_cpu=4, MEMORY_SIZE='8G', max_iops=40000, min_iops=40000, LOG_DISK_SIZE='15G';
create resource unit config_tenant_B_zone2_4C8G_jcz max_cpu=4, min_cpu=4, MEMORY_SIZE='8G', max_iops=40000, min_iops=40000, LOG_DISK_SIZE='15G';
create resource unit config_tenant_B_zone3_4C8G_fjs max_cpu=4, min_cpu=4, MEMORY_SIZE='8G', max_iops=40000, min_iops=40000, LOG_DISK_SIZE='15G';
CREATE RESOURCE POOL config_tenant_B_zone1_4C8G_bha unit = 'config_tenant_B_zone1_4C8G_bha', unit_num = 1, zone_list = ('zone1');
CREATE RESOURCE POOL config_tenant_B_zone2_4C8G_jcz unit = 'config_tenant_B_zone2_4C8G_jcz', unit_num = 1, zone_list = ('zone2');
CREATE RESOURCE POOL config_tenant_B_zone3_4C8G_fjs unit = 'config_tenant_B_zone3_4C8G_fjs', unit_num = 1, zone_list = ('zone3');

在A集群中恢复备租户tenant_B

ALTER SYSTEM RESTORE tenant_B_standby FROM 'file:///data/obce3zone-B/backup/tenant_B_primary/data,file:///data/obce3zone-B/backup/tenant_B_primary/archive' until TIME='2023-05-03 22:46:18.981214' WITH 'pool_list=config_tenant_B_zone1_4C8G_bha,config_tenant_B_zone2_4C8G_jcz,config_tenant_B_zone3_4C8G_fjs';
ALTER SYSTEM RECOVER STANDBY tenant = tenant_B_standby UNTIL UNLIMITED;

主备切换

通常切换有Switchover和Failover，Switchover通常是计划内的切换，不会丢数据。Failover发生在主不可用时的切换，可能会存在数据丢失。

我们分别用tenant_A和tenant_B两个租户模拟两个不同的切换场景：

tenant_A→tenant_A_standby 模拟Switchover

tenant_B→tenant_B_standby 模拟Failover

Switchover

包括以下三个阶段：

Switchover 前准备

主要是配置备租户归档路径，开启归档，当备租户切换为主租户后，需要向新备租户(原主租户)同步日志归档

将主租户切换为备租户
将备租户切换为主租户

OceanBase4.1 物理主备库搭建-2

设置备租户(tenant_A_standby)归档，在集群B上租户(tenant_A_standby)管理员用户进行操作

ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/obce3zone-B/backup/tenant_A_standby/archive';
ALTER SYSTEM ARCHIVELOG;

集群A上tenant_A租户管理员用户操作

将主租户tenant_A切换为standby

ALTER SYSTEM SWITCHOVER TO STANDBY;

检查是否已切换为standby， tenant_role是否已变成standby

obclient [oceanbase]> SELECT TENANT_ID, TENANT_NAME,TENANT_TYPE,TENANT_ROLE,SWITCHOVER_STATUS  FROM DBA_OB_TENANTS  WHERE TENANT_NAME='tenant_A';
+-----------+-------------+-------------+-------------+-------------------+
| TENANT_ID | TENANT_NAME | TENANT_TYPE | TENANT_ROLE | SWITCHOVER_STATUS |
+-----------+-------------+-------------+-------------+-------------------+
|      1004 | tenant_A    | USER        | STANDBY     | NORMAL            |
+-----------+-------------+-------------+-------------+-------------------+
1 row in set (0.013 sec)

查询 V$OB_ARCHIVE_DEST_STATUS 视图，检查 SYNCHRONIZED 字段，等待主租户归档完成如果SYNCHRONIZED 字段显示为 YES，则表示主租户已归档完成，保证切换后不丢数据

obclient [oceanbase]> SELECT * FROM V$OB_ARCHIVE_DEST_STATUS WHERE TENANT_ID = 1004;
+-----------+---------+----------------------------------------------------------+--------+---------------------+--------------+---------+
| TENANT_ID | DEST_ID | PATH                                                     | STATUS | CHECKPOINT_SCN      | SYNCHRONIZED | COMMENT |
+-----------+---------+----------------------------------------------------------+--------+---------------------+--------------+---------+
|      1004 |    1001 | file:///data/obce3zone-A/backup/tenant_A_primary/archive | DOING  | 1683127967130793236 | YES          |         |
+-----------+---------+----------------------------------------------------------+--------+---------------------+--------------+---------+
1 row in set (0.028 sec)

集群B上备租户(tenand_A_standby)管理员用户操作

将备租户(tenant_A_standby)切换为Primary

ALTER SYSTEM SWITCHOVER TO PRIMARY;

检查是否切换为Primary

obclient [oceanbase]> SELECT TENANT_ID, TENANT_NAME,TENANT_TYPE,TENANT_ROLE,SWITCHOVER_STATUS FROM DBA_OB_TENANTS WHERE TENANT_NAME='tenant_A_standby';
+-----------+------------------+-------------+-------------+-------------------+
| TENANT_ID | TENANT_NAME      | TENANT_TYPE | TENANT_ROLE | SWITCHOVER_STATUS |
+-----------+------------------+-------------+-------------+-------------------+
|      1006 | tenant_A_standby | USER        | PRIMARY     | NORMAL            |
+-----------+------------------+-------------+-------------+-------------------+
1 row in set (0.006 sec)

设置原主租户恢复源，在集群A上tenant_A租户操作，设置后可以tenant_A_standby→tenant_A的同步

ALTER SYSTEM SET LOG_RESTORE_SOURCE ='LOCATION=file:///data/obce3zone-B/backup/tenant_A_standby/archive';

验证同步是否正常，登录到tenant_A_standby租户上执行dml操作，查看是否可以同步到tenant_A中

[obadmin@iZ2zec4al0gmf3iqwixdhzZ ~]$ obclient -h172.31.88.32 -P2883 -uroot@tenant_A_standby -p
Enter password:
Welcome to the OceanBase.  Commands end with ; or \\g.
Your OceanBase connection id is 3221617047
Server version: OceanBase_CE 4.1.0.0 (r100000202023040520-0765e69043c31bf86e83b5d618db0530cf31b707) (Built Apr  5 2023 20:26:14)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.

obclient [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
obclient [test]> select * from dhytest;
+------+----------+
| id   | name     |
+------+----------+
|    1 | donghy   |
|    2 | hanjie   |
|    3 | shengang |
+------+----------+
3 rows in set (0.000 sec)

obclient [test]> delete from dhytest where id = 1;
Query OK, 1 row affected (0.002 sec)

[obadmin@iZ2zec4al0gmf3iqwixdhyZ ~]$ obclient -h172.31.88.35 -P2883 -uroot@tenant_A -p
Enter password:
Welcome to the OceanBase.  Commands end with ; or \\g.
Your OceanBase connection id is 1048646
Server version: OceanBase_CE 4.1.0.0 (r100000202023040520-0765e69043c31bf86e83b5d618db0530cf31b707) (Built Apr  5 2023 20:26:14)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.

obclient [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
obclient [test]> select * from dhytest;
+------+----------+
| id   | name     |
+------+----------+
|    2 | hanjie   |
|    3 | shengang |
+------+----------+
2 rows in set (0.001 sec)

如果在备库执行dml会报read only
obclient [test]> delete from dhytest where id = 2;
ERROR 4688 (HY000): standby tenant is read only

Failover

上面演示的的是Switchover，接下来演示下从tenant_B到tenant_B_standby的Failover操作，Failover主要是当租户不可用时做紧急切换。

集群A上tenant_B_standby租户管理员操作

查看备租户状态，并将备租户(tenant_B_standby)激活为Primary

obclient [oceanbase]> SELECT TENANT_ID, TENANT_NAME,TENANT_TYPE,TENANT_ROLE,SWITCHOVER_STATUS FROM DBA_OB_TENANTS WHERE TENANT_NAME='tenant_B_standby';
+-----------+------------------+-------------+-------------+-------------------+
| TENANT_ID | TENANT_NAME      | TENANT_TYPE | TENANT_ROLE | SWITCHOVER_STATUS |
+-----------+------------------+-------------+-------------+-------------------+
|      1006 | tenant_B_standby | USER        | STANDBY     | NORMAL            |
+-----------+------------------+-------------+-------------+-------------------+
1 row in set (0.009 sec)

obclient [oceanbase]> ALTER SYSTEM ACTIVATE STANDBY;
Query OK, 0 rows affected (2.335 sec)

obclient [oceanbase]> SELECT TENANT_ID, TENANT_NAME,TENANT_TYPE,TENANT_ROLE,SWITCHOVER_STATUS FROM DBA_OB_TENANTS WHERE TENANT_NAME='tenant_B_standby';
+-----------+------------------+-------------+-------------+-------------------+
| TENANT_ID | TENANT_NAME      | TENANT_TYPE | TENANT_ROLE | SWITCHOVER_STATUS |
+-----------+------------------+-------------+-------------+-------------------+
|      1006 | tenant_B_standby | USER        | PRIMARY     | NORMAL            |
+-----------+------------------+-------------+-------------+-------------------+
1 row in set (0.022 sec)

做完Failover切换后原主租户是不能在作为备租户接入进来的，所以需要重新做备份恢复搭建物理复制

设置归档及备份

ALTER SYSTEM SET LOG_ARCHIVE_DEST='LOCATION=file:///data/obce3zone-A/backup/tenant_B_standby/archive';
ALTER SYSTEM SET data_backup_dest='file:///data/obce3zone-A/backup/tenant_B_standby/data';
ALTER SYSTEM ARCHIVELOG;
ALTER SYSTEM BACKUP DATABASE;

查看备份结束时间点

obclient [oceanbase]>  SELECT * FROM oceanbase.DBA_OB_BACKUP_TASK_HISTORY;
+---------+--------+-------------+---------------+----------------------------+----------------------------+-----------+---------------------+---------------------+---------------------+-----------------+--------+-------------+--------------+-------------------+------------------+--------------+---------------------+-------------------+--------------------------+------------+--------------+--------------+--------+---------+-------------------------------------------------------+
| TASK_ID | JOB_ID | INCARNATION | BACKUP_SET_ID | START_TIMESTAMP            | END_TIMESTAMP              | STATUS    | START_SCN           | END_SCN             | USER_LS_START_SCN   | ENCRYPTION_MODE | PASSWD | INPUT_BYTES | OUTPUT_BYTES | OUTPUT_RATE_BYTES | EXTRA_META_BYTES | TABLET_COUNT | FINISH_TABLET_COUNT | MACRO_BLOCK_COUNT | FINISH_MACRO_BLOCK_COUNT | FILE_COUNT | META_TURN_ID | DATA_TURN_ID | RESULT | COMMENT | PATH                                                  |
+---------+--------+-------------+---------------+----------------------------+----------------------------+-----------+---------------------+---------------------+---------------------+-----------------+--------+-------------+--------------+-------------------+------------------+--------------+---------------------+-------------------+--------------------------+------------+--------------+--------------+--------+---------+-------------------------------------------------------+
|       1 |      1 |           1 |             1 | 2023-05-03 23:52:39.339150 | 2023-05-03 23:54:12.629289 | COMPLETED | 1683129159375734000 | 1683129252602444000 | 1683129162490330522 | NONE            |        |   122311996 |     20639753 |       221242.6010 |                0 |          567 |                 567 |                58 |                       58 |          0 |            1 |            1 |      0 |         | file:///data/obce3zone-A/backup/tenant_B_standby/data |
+---------+--------+-------------+---------------+----------------------------+----------------------------+-----------+---------------------+---------------------+---------------------+-----------------+--------+-------------+--------------+-------------------+------------------+--------------+---------------------+-------------------+--------------------------+------------+--------------+--------------+--------+---------+-------------------------------------------------------+
1 row in set (0.001 sec)

集群B上sys租户管理员用户操作

设置恢复租户所需的资源池，这里只需要将租户tenant_B删除，继续沿用原有的资源池即可，删除可以看到pool_tenant_B_xxx对应的三个资源池没有对应的租户

obclient [oceanbase]> SELECT t1.name resource_pool_name,
    ->        t2.`name` unit_config_name,
    ->        t2.max_cpu,
    ->        t2.min_cpu,
    ->        t2.memory_size/1024/1024/1024 memory_size,
    ->                                      t3.unit_id,
    ->                                      t3.zone,
    ->                                      concat(t3.svr_ip,':',t3.`svr_port`) observer,
    ->                                      t4.tenant_id,
    ->                                      t4.tenant_name
    -> FROM __all_resource_pool t1
    -> JOIN __all_unit_config t2 ON (t1.unit_config_id=t2.unit_config_id)
    -> JOIN __all_unit t3 ON (t1.`resource_pool_id` = t3.`resource_pool_id`)
    -> LEFT JOIN __all_tenant t4 ON (t1.tenant_id=t4.tenant_id)
    -> ORDER BY t1.`resource_pool_id`,
    ->          t2.`unit_config_id`,
    ->          t3.unit_id ;
+--------------------------------+--------------------------------+---------+---------+----------------+---------+-------+-------------------+-----------+------------------+
| resource_pool_name             | unit_config_name               | max_cpu | min_cpu | memory_size    | unit_id | zone  | observer          | tenant_id | tenant_name      |
+--------------------------------+--------------------------------+---------+---------+----------------+---------+-------+-------------------+-----------+------------------+
| sys_pool                       | sys_unit_config                |       1 |       1 | 2.500000000000 |       1 | zone1 | 172.31.88.32:2882 |         1 | sys              |
| sys_pool                       | sys_unit_config                |       1 |       1 | 2.500000000000 |       2 | zone2 | 172.31.88.36:2882 |         1 | sys              |
| sys_pool                       | sys_unit_config                |       1 |       1 | 2.500000000000 |       3 | zone3 | 172.31.88.31:2882 |         1 | sys              |
| ocp_pool                       | ocp_unit                       |       1 |       1 | 2.000000000000 |    1001 | zone3 | 172.31.88.31:2882 |      1002 | ocp              |
| ocp_pool                       | ocp_unit                       |       1 |       1 | 2.000000000000 |    1002 | zone1 | 172.31.88.32:2882 |      1002 | ocp              |
| ocp_pool                       | ocp_unit                       |       1 |       1 | 2.000000000000 |    1003 | zone2 | 172.31.88.36:2882 |      1002 | ocp              |
| pool_tenant_B_zone1_upk        | config_tenant_B_zone1_4C8G_upk |       4 |       4 | 8.000000000000 |    1004 | zone1 | 172.31.88.32:2882 |      NULL | NULL             |
| pool_tenant_B_zone2_lbs        | config_tenant_B_zone2_4C8G_lbs |       4 |       4 | 8.000000000000 |    1005 | zone2 | 172.31.88.36:2882 |      NULL | NULL             |
| pool_tenant_B_zone3_qeq        | config_tenant_B_zone3_4C8G_qeq |       4 |       4 | 8.000000000000 |    1006 | zone3 | 172.31.88.31:2882 |      NULL | NULL             |
| config_tenant_A_zone1_4C8G_bha | config_tenant_A_zone1_4C8G_bha |       4 |       4 | 8.000000000000 |    1010 | zone1 | 172.31.88.32:2882 |      1006 | tenant_A_standby |
| config_tenant_A_zone2_4C8G_jcz | config_tenant_A_zone2_4C8G_jcz |       4 |       4 | 8.000000000000 |    1011 | zone2 | 172.31.88.36:2882 |      1006 | tenant_A_standby |
| config_tenant_A_zone3_4C8G_fjs | config_tenant_A_zone3_4C8G_fjs |       4 |       4 | 8.000000000000 |    1012 | zone3 | 172.31.88.31:2882 |      1006 | tenant_A_standby |
+--------------------------------+--------------------------------+---------+---------+----------------+---------+-------+-------------------+-----------+------------------+
12 rows in set (0.007 sec)

执行恢复操作，将租户tenant_B_standby恢复到A集群中的tenant_B

ALTER SYSTEM RESTORE tenant_B FROM 'file:///data/obce3zone-A/backup/tenant_B_standby/data,file:///data/obce3zone-A/backup/tenant_B_standby/archive' until TIME='2023-05-03 23:54:12.629289' WITH 'pool_list=pool_tenant_B_zone1_upk,pool_tenant_B_zone2_lbs,pool_tenant_B_zone3_qeq';
可以查询CDB_OB_RESTORE_PROGRESS视图，查看恢复进度
obclient [oceanbase]> SELECT * FROM CDB_OB_RESTORE_PROGRESS;
+-----------+--------+---------------------+-------------------+--------------------+------------------+---------------------+----------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------+---------------------+----------------------------+----------------------------+----------------------------+-------------------------------------------------------------------------+--------------------------------------------------------------------------+-------------+---------------------+--------------+----------------------+-------------+
| TENANT_ID | JOB_ID | RESTORE_TENANT_NAME | RESTORE_TENANT_ID | BACKUP_TENANT_NAME | BACKUP_TENANT_ID | BACKUP_CLUSTER_NAME | BACKUP_DEST                                                                                                    | RESTORE_OPTION                                                                    | RESTORE_SCN         | RESTORE_SCN_DISPLAY        | STATUS                     | START_TIMESTAMP            | BACKUP_SET_LIST                                                         | BACKUP_PIECE_LIST                                                        | TOTAL_BYTES | TOTAL_BYTES_DISPLAY | FINISH_BYTES | FINISH_BYTES_DISPLAY | DESCRIPTION |
+-----------+--------+---------------------+-------------------+--------------------+------------------+---------------------+----------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------+---------------------+----------------------------+----------------------------+----------------------------+-------------------------------------------------------------------------+--------------------------------------------------------------------------+-------------+---------------------+--------------+----------------------+-------------+
|         1 |      4 | tenant_B            | 1008              | tenant_B_standby   | 1006             | obce3zoneA          | file:///data/obce3zone-A/backup/tenant_B_standby/data,file:///data/obce3zone-A/backup/tenant_B_standby/archive | pool_list=pool_tenant_B_zone1_upk,pool_tenant_B_zone2_lbs,pool_tenant_B_zone3_qeq | 1683129252629289000 | 2023-05-03 23:54:12.629289 | WAIT_TENANT_RESTORE_FINISH | 2023-05-03 23:58:55.790402 | file:///data/obce3zone-A/backup/tenant_B_standby/data/backup_set_1_full | file:///data/obce3zone-A/backup/tenant_B_standby/archive/piece_d1001r1p1 |        NULL | NULL                |         NULL | NULL                 |             |
|      1008 |      4 | tenant_B            | 1008              | tenant_B_standby   | 1006             | obce3zoneA          | file:///data/obce3zone-A/backup/tenant_B_standby/data,file:///data/obce3zone-A/backup/tenant_B_standby/archive | pool_list=pool_tenant_B_zone1_upk,pool_tenant_B_zone2_lbs,pool_tenant_B_zone3_qeq | 1683129252629289000 | 2023-05-03 23:54:12.629289 | RESTORING                  | 2023-05-03 23:58:55.790402 | file:///data/obce3zone-A/backup/tenant_B_standby/data/backup_set_1_full | file:///data/obce3zone-A/backup/tenant_B_standby/archive/piece_d1001r1p1 |    20639753 | 19.68MB             |            0 | 0.00MB               |             |
+-----------+--------+---------------------+-------------------+--------------------+------------------+---------------------+----------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------+---------------------+----------------------------+----------------------------+----------------------------+-------------------------------------------------------------------------+--------------------------------------------------------------------------+-------------+---------------------+--------------+----------------------+-------------+
2 rows in set (0.004 sec)
恢复完成后再CDB_OB_RESTORE_HISTORY表中可看到恢复记录
obclient [oceanbase]> SELECT * FROM CDB_OB_RESTORE_HISTORY where RESTORE_TENANT_NAME = 'tenant_B';
+-----------+--------+---------------------+-------------------+--------------------+------------------+---------------------+----------------------------------------------------------------------------------------------------------------+---------------------+----------------------------+-----------------------------------------------------------------------------------+----------------------------+----------------------------+---------+--------------------------------------------------------------------------+-------------------------------------------------------------------------+------------------------+----------+-----------------+--------------+---------------------+-------------+---------------------+--------------+----------------------+-------------+---------+
| TENANT_ID | JOB_ID | RESTORE_TENANT_NAME | RESTORE_TENANT_ID | BACKUP_TENANT_NAME | BACKUP_TENANT_ID | BACKUP_CLUSTER_NAME | BACKUP_DEST                                                                                                    | RESTORE_SCN         | RESTORE_SCN_DISPLAY        | RESTORE_OPTION                                                                    | START_TIMESTAMP            | FINISH_TIMESTAMP           | STATUS  | BACKUP_PIECE_LIST                                                        | BACKUP_SET_LIST                                                         | BACKUP_CLUSTER_VERSION | LS_COUNT | FINISH_LS_COUNT | TABLET_COUNT | FINISH_TABLET_COUNT | TOTAL_BYTES | TOTAL_BYTES_DISPLAY | FINISH_BYTES | FINISH_BYTES_DISPLAY | DESCRIPTION | COMMENT |
+-----------+--------+---------------------+-------------------+--------------------+------------------+---------------------+----------------------------------------------------------------------------------------------------------------+---------------------+----------------------------+-----------------------------------------------------------------------------------+----------------------------+----------------------------+---------+--------------------------------------------------------------------------+-------------------------------------------------------------------------+------------------------+----------+-----------------+--------------+---------------------+-------------+---------------------+--------------+----------------------+-------------+---------+
|         1 |      4 | tenant_B            |              1008 | tenant_B_standby   |             1006 | obce3zoneA          | file:///data/obce3zone-A/backup/tenant_B_standby/data,file:///data/obce3zone-A/backup/tenant_B_standby/archive | 1683129252629289000 | 2023-05-03 23:54:12.629289 | pool_list=pool_tenant_B_zone1_upk,pool_tenant_B_zone2_lbs,pool_tenant_B_zone3_qeq | 2023-05-03 23:58:55.790402 | 2023-05-04 00:01:22.923685 | SUCCESS | file:///data/obce3zone-A/backup/tenant_B_standby/archive/piece_d1001r1p1 | file:///data/obce3zone-A/backup/tenant_B_standby/data/backup_set_1_full |            17179934720 |        4 |               0 |          567 |                   0 |    20639753 | 19.68MB             |            0 | 0.00MB               | NULL        | NULL    |
|      1008 |      4 | tenant_B            |              1008 | tenant_B_standby   |             1006 | obce3zoneA          | file:///data/obce3zone-A/backup/tenant_B_standby/data,file:///data/obce3zone-A/backup/tenant_B_standby/archive | 1683129252629289000 | 2023-05-03 23:54:12.629289 | pool_list=pool_tenant_B_zone1_upk,pool_tenant_B_zone2_lbs,pool_tenant_B_zone3_qeq | 2023-05-03 23:58:55.790402 | 2023-05-04 00:01:12.621435 | SUCCESS | file:///data/obce3zone-A/backup/tenant_B_standby/archive/piece_d1001r1p1 | file:///data/obce3zone-A/backup/tenant_B_standby/data/backup_set_1_full |            17179934720 |        4 |               0 |          567 |                   0 |    20639753 | 19.68MB             |            0 | 0.00MB               | NULL        | NULL    |
+-----------+--------+---------------------+-------------------+--------------------+------------------+---------------------+----------------------------------------------------------------------------------------------------------------+---------------------+----------------------------+-----------------------------------------------------------------------------------+----------------------------+----------------------------+---------+--------------------------------------------------------------------------+-------------------------------------------------------------------------+------------------------+----------+-----------------+--------------+---------------------+-------------+---------------------+--------------+----------------------+-------------+---------+
2 rows in set (0.007 sec)

执行recover 设置UNLIMITED，一直应用归档日志将复制建立起来

ALTER SYSTEM RECOVER STANDBY tenant = tenant_B UNTIL UNLIMITED;

验证数据同步是否正常，在租户tenant_B_standby执行DML操作，查看是否可以同步到tenant_B中

[obadmin@iZ2zec4al0gmf3iqwixdhyZ ~]$ obclient -h172.31.88.35 -P2883 -uroot@tenant_B_standby -p
Enter password:
Welcome to the OceanBase.  Commands end with ; or \\g.
Your OceanBase connection id is 1048648
Server version: OceanBase_CE 4.1.0.0 (r100000202023040520-0765e69043c31bf86e83b5d618db0530cf31b707) (Built Apr  5 2023 20:26:14)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.

obclient [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
obclient [test]> insert into dhytest values (1,'donghy');
Query OK, 1 row affected (0.014 sec)

obclient [test]> insert into dhytest values (2,'hanjie');
Query OK, 1 row affected (0.001 sec)

obclient [test]> insert into dhytest values (3,'shengang');
Query OK, 1 row affected (0.001 sec)

[obadmin@iZ2zec4al0gmf3iqwixdhzZ ~]$ obclient -h172.31.88.32 -P2881 -uroot@tenant_B -p
Enter password:
Welcome to the OceanBase.  Commands end with ; or \\g.
Your OceanBase connection id is 3221718837
Server version: OceanBase_CE 4.1.0.0 (r100000202023040520-0765e69043c31bf86e83b5d618db0530cf31b707) (Built Apr  5 2023 20:26:14)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.

obclient [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
obclient [test]> select * from dhytest;
+------+----------+
| id   | name     |
+------+----------+
|    1 | donghy   |
|    2 | hanjie   |
|    3 | shengang |
+------+----------+
3 rows in set (0.000 sec)

小结

整体下来操作步骤还算好，就是需要的机器比较多，6台8C32G云服务器每小时需要21元。

租户级的物理复制由于租户之间是完全隔离的，不用担心像物理复制一样出现数据冲突的风险，并且能更好的提高资源利用率

目前物理主备库同步只支持最大性能模式，我理解OceanBase本身集群就是3副本之间就可以满足高可用，这种物理主备的高可用多数可能会出现在极端情况下的Failover，且部署时应该多数都是跨物理机房或地域部署

OceanBase4.1 物理主备库搭建

OceanBase4.1 物理主备库搭建

物理主备库功能

环境信息

设置集群归档备份

恢复出备租户

主备切换

Switchover

Failover

小结

相关文章

发布评论取消回复

三掌柜

百度搜索：蓝易云 – CentOS7编译生成openssl和openssh RPM包教程

【Rocky 9】Step by Step 安装 Rocky Linux 9.2 系统

蔚来汽车 x TiDB丨单表超 20 亿条数据，从 MySQL 到 TiDB 的迁移思考与实践

OceanBase4.1 物理主备库搭建

OceanBase4.1 物理主备库搭建

物理主备库功能

环境信息

设置集群归档备份

恢复出备租户

主备切换

Switchover

Failover

小结

相关文章

发布评论 取消回复

三掌柜

百度搜索：蓝易云 – CentOS7编译生成openssl和openssh RPM包教程

【Rocky 9】Step by Step 安装 Rocky Linux 9.2 系统

蔚来汽车 x TiDB丨单表超 20 亿条数据，从 MySQL 到 TiDB 的迁移思考与实践

发布评论取消回复