OceanBase 集群扩容之后修改租户副本

SQL学习者 2023-07-23

487

1 系统扩容完成之后，查询节点信息

原来集群只有zone1单个节点，扩容zone2,zone3节点。

MySQL [oceanbase]> select * from __all_server;

+----------------------------+----------------------------+---------------+----------+----+-------+------------+-----------------+--------+-----------------------+------------------------------------------------------------------------+-----------+--------------------+--------------+----------------+-------------------+

| 2021-12-07 15:52:36.583969 | 2021-12-07 15:52:48.009135 | 192.168.5.200 | 2882 | 1 | zone1 | 2881 | 1 | active | 0 | 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14) | 0 | 1638863566011754 | 0 | 1 | 0 |

| 2021-12-07 15:56:07.681043 | 2021-12-07 15:56:30.391582 | 192.168.5.200 | 3882 | 2 | zone2 | 3881 | 0 | active | 0 | 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14) | 0 | 1638863788395674 | 0 | 0 | 0 |

| 2021-12-07 15:56:11.908905 | 2021-12-07 15:56:31.793939 | 192.168.5.200 | 4882 | 3 | zone3 | 4881 | 0 | active | 0 | 3.1.1_4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e(Oct 21 2021 10:33:14) | 0 | 1638863789811151 | 0 | 0 | 0 |

3 rows in set (0.000 sec)

扩容之后查看相关系统表信副本数仍未1. （REPLICA_NUM = 1）

MySQL [oceanbase]> show create table __all_zone;

+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

| __all_zone | CREATE TABLE `__all_zone` (

`gmt_create` timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6),

`gmt_modified` timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

`zone` varchar(128) NOT NULL,

`name` varchar(128) NOT NULL,

`value` bigint(20) NOT NULL,

`info` varchar(4096) NOT NULL,

PRIMARY KEY (`zone`, `name`)

) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = COMPACT COMPRESSION = 'none' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10 TABLEGROUP = 'oceanbase' |

1 row in set (0.005 sec)

2 修改sys_pool系统资源池配置

MySQL [oceanbase]> select * from __all_resource_pool ;

+----------------------------+----------------------------+------------------+----------+------------+----------------+-----------+-----------+--------------+--------------------+

+----------------------------+----------------------------+------------------+----------+------------+----------------+-----------+-----------+--------------+--------------------+

| 2021-12-07 15:52:44.063479 | 2021-12-07 15:52:44.067033 | 1 | sys_pool | 1 | 1 | zone1 | 1 | 0 | 0 |

+----------------------------+----------------------------+------------------+----------+------------+----------------+-----------+-----------+--------------+--------------------+

1 row in set (0.001 sec)

MySQL [oceanbase]> ALTER RESOURCE POOL sys_pool zone_list=('zone1','zone2','zone3');

Query OK, 0 rows affected (0.010 sec)

MySQL [oceanbase]> select * from __all_resource_pool ;

+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+

+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+

| 2021-12-07 15:52:44.063479 | 2021-12-07 16:11:07.047749 | 1 | sys_pool | 1 | 1 | zone1;zone2;zone3 | 1 | 0 | 0 |

+----------------------------+----------------------------+------------------+----------+------------+----------------+-------------------+-----------+--------------+--------------------+

1 row in set (0.001 sec)

3 查看sys租户信息

副本数量为1，通过修改租户locality，更副本数量为3。

MySQL [oceanbase]> select * from __all_tenant where tenant_id=1;

+----------------------------+----------------------------+-----------+-------------+-------------+-----------+--------------+--------+----------------+---------------+-----------+-----------------------+---------------+---------------------+-------------------+------------------------+-----------------------------+-----------------------+--------------------+------------------+----------------------+---------------+

| 2021-12-07 15:52:44.071727 | 2021-12-07 15:52:44.071727 | 1 | sys | -1 | zone1 | zone1 | 0 | 0 | system tenant | 0 | 0 | FULL{1}@zone1 | 0 | | 0 | 0 | -1 | 0 | -1 | TENANT_STATUS_NORMAL | 0 |

1 row in set (0.001 sec)

4 修改sys租户副本数量

4.1 直接修改副本数量为3，失败！

修改locality必须缓慢的从一个zone到两个zone，这期间后台会做数据补副本操作，根据实际数据量需要点时间。结束了才能做两个zone到三个zone的扩容。

MySQL [oceanbase]> ALTER TENANT sys locality="FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3";

ERROR 4179 (HY000): violate locality principal not allowed

MySQL [oceanbase]>

4.2修改副本数为2

Locality 的设置通常用于集群的副本数升级、降级或集群的搬迁。
参考地址：
https://open.oceanbase.com/docs/community/oceanbase-database/V3.1.0/locality-management-overview
变更命令：
alter tenant sys locality='FULL{1}@zone1, FULL{1}@zone2';
任务进度：
select gmt_create,gmt_modified,job_id,job_type,job_status,return_code,progress,tenant_id from __all_rootservice_job;

MySQL [oceanbase]> alter tenant sys locality='FULL{1}@zone1, FULL{1}@zone2';

Query OK, 0 rows affected (0.046 sec)

MySQL [oceanbase]> select gmt_create,gmt_modified,job_id,job_type,job_status,return_code,progress,tenant_id from __all_rootservice_job;

+----------------------------+----------------------------+--------+-----------------------+------------+-------------+----------+-----------+

+----------------------------+----------------------------+--------+-----------------------+------------+-------------+----------+-----------+

| 2021-12-07 16:17:55.253671 | 2021-12-07 16:17:55.253671 | 1 | ALTER_TENANT_LOCALITY | INPROGRESS | NULL | 0 | 1 |

+----------------------------+----------------------------+--------+-----------------------+------------+-------------+----------+-----------+

1 row in set (0.001 sec)

MySQL [oceanbase]> select * from __all_tenant where tenant_id=1;

+----------------------------+----------------------------+-----------+-------------+-------------+-------------+--------------+--------+----------------+---------------+-----------+-----------------------+------------------------------+---------------------+-------------------+------------------------+-----------------------------+-----------------------+--------------------+------------------+----------------------+---------------+

| 2021-12-07 16:17:55.248673 | 2021-12-07 16:17:55.248673 | 1 | sys | -1 | zone1;zone2 | zone1;zone2 | 0 | 0 | system tenant | 0 | 0 | FULL{1}@zone1, FULL{1}@zone2 | 0 | FULL{1}@zone1 | 0 | 0 | -1 | 0 | -1 | TENANT_STATUS_NORMAL | 0 |

1 row in set (0.000 sec)

MySQL [oceanbase]> select gmt_create,gmt_modified,job_id,job_type,job_status,return_code,progress,tenant_id from __all_rootservice_job;

+----------------------------+----------------------------+--------+-----------------------+------------+-------------+----------+-----------+

+----------------------------+----------------------------+--------+-----------------------+------------+-------------+----------+-----------+

| 2021-12-07 16:17:55.253671 | 2021-12-07 16:22:13.609055 | 1 | ALTER_TENANT_LOCALITY | SUCCESS | 0 | 100 | 1 |

+----------------------------+----------------------------+--------+-----------------------+------------+-------------+----------+-----------+

1 row in set (0.002 sec)

MySQL [oceanbase]> select * from __all_tenant where tenant_id=1;

| 2021-12-07 16:22:13.606250 | 2021-12-07 16:22:13.606250 | 1 | sys | -1 | zone1;zone2 | zone1;zone2 | 0 | 0 | system tenant | 0 | 0 | FULL{1}@zone1, FULL{1}@zone2 | 0 | | 0 | 0 | -1 | 0 | -1 | TENANT_STATUS_NORMAL | 0 |

1 row in set (0.002 sec)

4.3修改副本数为3

要等待上一个扩容任务完成之后才可以执行，否则报错。

MySQL [oceanbase]> ALTER TENANT sys locality="FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3";

ERROR 4179 (HY000): alter tenant locality when previous operation is in progress not allowed

三节点扩容

MySQL [oceanbase]> ALTER TENANT sys locality="FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3";

Query OK, 0 rows affected (0.054 sec)

MySQL [oceanbase]> select gmt_create,gmt_modified,job_id,job_type,job_status,return_code,progress,tenant_id from __all_rootservice_job;

+----------------------------+----------------------------+--------+-----------------------+------------+-------------+----------+-----------+

+----------------------------+----------------------------+--------+-----------------------+------------+-------------+----------+-----------+

| 2021-12-07 16:17:55.253671 | 2021-12-07 16:22:13.609055 | 1 | ALTER_TENANT_LOCALITY | SUCCESS | 0 | 100 | 1 |

| 2021-12-07 16:24:28.769181 | 2021-12-07 16:24:28.769181 | 2 | ALTER_TENANT_LOCALITY | INPROGRESS | NULL | 0 | 1 |

+----------------------------+----------------------------+--------+-----------------------+------------+-------------+----------+-----------+

2 rows in set (0.000 sec)

MySQL [oceanbase]> select * from __all_tenant where tenant_id=1;

+----------------------------+----------------------------+-----------+-------------+-------------+-------------------+-------------------+--------+----------------+---------------+-----------+-----------------------+---------------------------------------------+---------------------+------------------------------+------------------------+-----------------------------+-----------------------+--------------------+------------------+----------------------+---------------+

| 2021-12-07 16:24:28.764615 | 2021-12-07 16:24:28.764615 | 1 | sys | -1 | zone1;zone2;zone3 | zone1;zone2;zone3 | 0 | 0 | system tenant | 0 | 0 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | 0 | FULL{1}@zone1, FULL{1}@zone2 | 0 | 0 | -1 | 0 | -1 | TENANT_STATUS_NORMAL | 0 |

1 row in set (0.000 sec)

MySQL [oceanbase]> select gmt_create,gmt_modified,job_id,job_type,job_status,return_code,progress,tenant_id from __all_rootservice_job;

+----------------------------+----------------------------+--------+-----------------------+------------+-------------+----------+-----------+

+----------------------------+----------------------------+--------+-----------------------+------------+-------------+----------+-----------+

| 2021-12-07 16:17:55.253671 | 2021-12-07 16:22:13.609055 | 1 | ALTER_TENANT_LOCALITY | SUCCESS | 0 | 100 | 1 |

| 2021-12-07 16:24:28.769181 | 2021-12-07 16:28:51.344647 | 2 | ALTER_TENANT_LOCALITY | SUCCESS | 0 | 100 | 1 |

+----------------------------+----------------------------+--------+-----------------------+------------+-------------+----------+-----------+

2 rows in set (0.024 sec)

MySQL [oceanbase]> select * from __all_tenant where tenant_id=1;

+----------------------------+----------------------------+-----------+-------------+-------------+-------------------+-------------------+--------+----------------+---------------+-----------+-----------------------+---------------------------------------------+---------------------+-------------------+------------------------+-----------------------------+-----------------------+--------------------+------------------+----------------------+---------------+

| 2021-12-07 16:28:51.341516 | 2021-12-07 16:28:51.341516 | 1 | sys | -1 | zone1;zone2;zone3 | zone1;zone2;zone3 | 0 | 0 | system tenant | 0 | 0 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | 0 | | 0 | 0 | -1 | 0 | -1 | TENANT_STATUS_NORMAL | 0 |

+----------------------------+----------------------------+-----------+-------------+-------------+-------------------+-------------------+--------+----------------+---------------+-----------+-----------------------+---------------------------------------------+---------------------+-------------------+------------------------+-----------------------------+-----------------------+--------------------+------------------+----------------------+---------------+

1 row in set (0.000 sec)

5 扩容完成验证表的副本属性

副本修改完成之后，查看表的副本参数已经更改：REPLICA_NUM = 3

验证表的副本属性，已经更改为3.

MySQL [oceanbase]> show create table __all_zone;

| Table | Create Table |

| __all_zone | CREATE TABLE `__all_zone` (

`gmt_create` timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6),

`gmt_modified` timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

`zone` varchar(128) NOT NULL,

`name` varchar(128) NOT NULL,

`value` bigint(20) NOT NULL,

`info` varchar(4096) NOT NULL,

PRIMARY KEY (`zone`, `name`)

) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = COMPACT COMPRESSION = 'none' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10 TABLEGROUP = 'oceanbase' |

1 row in set (0.008 sec)

6 修改租户副本数量相关命令，和注意事项。

修改locality必须缓慢的从一个zone到两个zone，这期间后台会做数据补副本操作，根据实际数据量需要点时间。结束了才能做两个zone到三个zone的扩容。

alter tenant sys locality='FULL{1}@zone1, FULL{1}@zone2';

ALTER TENANT sys locality="FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3";

7 租户缩容 3副本变更为1副本

7.1 目前租户为3副本

MySQL [oceanbase]> select * from __all_tenant where tenant_id=1\G;

*************************** 1. row ***************************gmt_create: 2021-12-07 16:28:51.341516gmt_modified: 2021-12-07 16:28:51.341516tenant_id: 1tenant_name: sysreplica_num: -1zone_list: zone1;zone2;zone3primary_zone: zone1;zone2;zone3locked: 0collation_type: 0info: system tenantread_only: 0rewrite_merge_version: 0locality: FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3logonly_replica_num: 0previous_locality:

storage_format_version: 0

storage_format_work_version: 0default_tablegroup_id: -1compatibility_mode: 0drop_tenant_time: -1status: TENANT_STATUS_NORMALin_recyclebin: 0

1 row in set (0.000 sec)

7.2 更改为2副本

MySQL [oceanbase]> alter tenant sys locality='FULL{1}@zone1, FULL{1}@zone2';

Query OK, 0 rows affected (0.021 sec)

MySQL [oceanbase]> select gmt_create,gmt_modified,job_id,job_type,job_status,return_code,progress,tenant_id from __all_rootservice_job;

+----------------------------+----------------------------+--------+-----------------------+------------+-------------+----------+-----------+

+----------------------------+----------------------------+--------+-----------------------+------------+-------------+----------+-----------+

| 2021-12-07 16:17:55.253671 | 2021-12-07 16:22:13.609055 | 1 | ALTER_TENANT_LOCALITY | SUCCESS | 0 | 100 | 1 |

| 2021-12-07 16:24:28.769181 | 2021-12-07 16:28:51.344647 | 2 | ALTER_TENANT_LOCALITY | SUCCESS | 0 | 100 | 1 |

| 2021-12-07 16:42:00.997775 | 2021-12-07 16:42:12.529335 | 3 | ALTER_TENANT_LOCALITY | SUCCESS | 0 | 100 | 1 |

+----------------------------+----------------------------+--------+-----------------------+------------+-------------+----------+-----------+

3 rows in set (0.000 sec)

MySQL [oceanbase]> select * from __all_tenant where tenant_id=1\G;

*************************** 1. row ***************************

gmt_create: 2021-12-07 16:42:12.523525

gmt_modified: 2021-12-07 16:42:12.523525

tenant_id: 1

tenant_name: sys

replica_num: -1

zone_list: zone1;zone2

primary_zone: zone1;zone2

locked: 0

collation_type: 0

info: system tenant

read_only: 0

rewrite_merge_version: 0

locality: FULL{1}@zone1, FULL{1}@zone2

logonly_replica_num: 0

previous_locality:

storage_format_version: 0

storage_format_work_version: 0

default_tablegroup_id: -1

compatibility_mode: 0

drop_tenant_time: -1

status: TENANT_STATUS_NORMAL

in_recyclebin: 0

1 row in set (0.002 sec)

ERROR: No query specified

7.3 更改为1副本

报错禁止修改！！

MySQL [oceanbase]> alter tenant sys locality='FULL{1}@zone1';

ERROR 4179 (HY000): violate locality principal not allowed

7.4 验证表对应副本信息

表对应的副本属性已经更改为2，REPLICA_NUM = 2。

MySQL [oceanbase]> show create table __all_zone;

| Table | Create Table |

| __all_zone | CREATE TABLE `__all_zone` (

`gmt_create` timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6),

`gmt_modified` timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

`zone` varchar(128) NOT NULL,

`name` varchar(128) NOT NULL,

`value` bigint(20) NOT NULL,

`info` varchar(4096) NOT NULL,

PRIMARY KEY (`zone`, `name`)

) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = COMPACT COMPRESSION = 'none' REPLICA_NUM = 2 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10 TABLEGROUP = 'oceanbase' |

1 row in set (0.013 sec)

oceanbase

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

OceanBase 集群扩容之后修改租户副本

评论