oceanbase2.2集群——MySQL租户的使用体验
1、创建mysql租户之前，查看ob2.2集群信息
$ mysql -h172.16.18.81 -uroot@sys#obdemo -P2883 -padmin123 -c -A oceanbase
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 3222011913
Server version: 5.7.25 OceanBase 2.2.30 (r20200515183156-366007805d18f72e1fbd8568ada91849305c53ac) (Built May 15 2020 19:45:08)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
查看OB集群所有节点信息：
MySQL [oceanbase]> select zone,svr_ip,svr_port,inner_port,with_rootserver,status,gmt_create from __all_server order by zone, svr_ip;
+-------+--------------+----------+------------+-----------------+--------+----------------------------+
| zone | svr_ip | svr_port | inner_port | with_rootserver | status | gmt_create |
+-------+--------------+----------+------------+-----------------+--------+----------------------------+
| zone1 | 172.16.18.81 | 2882 | 2881 | 1 | active | 2020-08-12 12:04:47.793896 |
| zone2 | 172.16.18.82 | 2882 | 2881 | 0 | active | 2020-08-12 12:04:45.388593 |
| zone3 | 172.16.18.91 | 2882 | 2881 | 0 | active | 2020-08-12 12:04:45.374074 |
+-------+--------------+----------+------------+-----------------+--------+----------------------------+
3 rows in set (0.00 sec)
查看OB集群所有租户信息: 租户类别compatibility_mode，0为mysql兼容模式的租户， 1为oracle兼容模式的租户
MySQL [oceanbase]> select tenant_id, tenant_name, compatibility_mode,zone_list, locality ,gmt_modified from __all_tenant;
+-----------+-----------------+--------------------+-------------------+---------------------------------------------+----------------------------+
| tenant_id | tenant_name | compatibility_mode | zone_list | locality | gmt_modified |
+-----------+-----------------+--------------------+-------------------+---------------------------------------------+----------------------------+
| 1 | sys | 0 | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | 2020-08-12 12:05:00.986146 |
+-----------+-----------------+--------------------+-------------------+---------------------------------------------+----------------------------+
1 row in set (0.02 sec)
查看OceanBase集群所有节点可用资源情况：
MySQL [oceanbase]> select zone, svr_ip, svr_port,inner_port, cpu_total, cpu_assigned,
-> round(mem_total/1024/1024/1024) mem_total_gb,
-> round(mem_assigned/1024/1024/1024) mem_ass_gb,
-> round(disk_total/1024/1024/1024) disk_total_gb,
-> unit_num, substr(build_version,1,6) version
-> from __all_virtual_server_stat
-> order by zone, svr_ip, inner_port;
+-------+--------------+----------+------------+-----------+--------------+--------------+------------+---------------+----------+---------+
| zone | svr_ip | svr_port | inner_port | cpu_total | cpu_assigned | mem_total_gb | mem_ass_gb | disk_total_gb | unit_num | version |
+-------+--------------+----------+------------+-----------+--------------+--------------+------------+---------------+----------+---------+
| zone1 | 172.16.18.81 | 2882 | 2881 | 14 | 2.5 | 43 | 11 | 40 | 1 | 2.2.30 |
| zone2 | 172.16.18.82 | 2882 | 2881 | 14 | 2.5 | 43 | 11 | 40 | 1 | 2.2.30 |
| zone3 | 172.16.18.91 | 2882 | 2881 | 14 | 2.5 | 43 | 11 | 40 | 1 | 2.2.30 |
+-------+--------------+----------+------------+-----------+--------------+--------------+------------+---------------+----------+---------+
3 rows in set (0.02 sec)
MySQL [oceanbase]> select a.zone,concat(a.svr_ip,':',a.svr_port) observer, cpu_total, (cpu_total-cpu_assigned) cpu_free,
-> round(mem_total/1024/1024/1024) mem_total_gb, round((mem_total-mem_assigned)/1024/1024/1024) mem_free_gb,
-> round(disk_total/1024/1024/1024) disk_total_gb,
-> substr(a.build_version,1,6) version,usec_to_time(b.start_service_time) start_service_time
-> from __all_virtual_server_stat a join __all_server b on (a.svr_ip=b.svr_ip and a.svr_port=b.svr_port)
-> order by a.zone, a.svr_ip;
+-------+-------------------+-----------+----------+--------------+-------------+---------------+---------+----------------------------+
| zone | observer | cpu_total | cpu_free | mem_total_gb | mem_free_gb | disk_total_gb | version | start_service_time |
+-------+-------------------+-----------+----------+--------------+-------------+---------------+---------+----------------------------+
| zone1 | 172.16.18.81:2882 | 14 | 11.5 | 43 | 33 | 40 | 2.2.30 | 2020-08-12 12:05:02.983508 |
| zone2 | 172.16.18.82:2882 | 14 | 11.5 | 43 | 33 | 40 | 2.2.30 | 2020-08-12 12:05:04.159878 |
| zone3 | 172.16.18.91:2882 | 14 | 11.5 | 43 | 33 | 40 | 2.2.30 | 2020-08-12 12:05:05.166067 |
+-------+-------------------+-----------+----------+--------------+-------------+---------------+---------+----------------------------+
3 rows in set (0.01 sec)
查看集群资源池具体使用情况：
MySQL [oceanbase]> select t1.name resource_pool_name, t2.name unit_config_name, t2.max_cpu, t2.min_cpu,
-> round(t2.max_memory/1024/1024/1024) max_mem_gb, round(t2.min_memory/1024/1024/1024) min_mem_gb,
-> 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 | max_mem_gb | min_mem_gb | unit_id | zone | observer | tenant_id | tenant_name |
+--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-------------+
| sys_pool | sys_unit_config | 5 | 2.5 | 13 | 11 | 1 | zone1 | 172.16.18.81:2882 | 1 | sys |
| sys_pool | sys_unit_config | 5 | 2.5 | 13 | 11 | 2 | zone2 | 172.16.18.82:2882 | 1 | sys |
| sys_pool | sys_unit_config | 5 | 2.5 | 13 | 11 | 3 | zone3 | 172.16.18.91:2882 | 1 | sys |
+--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-------------+
3 rows in set (0.02 sec)
查看OB集群资源单元unit配置情况：
MySQL [oceanbase]> select unit_config_id,name,max_cpu,min_cpu,round(max_memory/1024/1024/1024) max_mem_gb,
-> round(min_memory/1024/1024/1024) min_mem_gb, round(max_disk_size/1024/1024/1024) max_disk_size_gb
-> from __all_unit_config
-> order by unit_config_id;
+----------------+-----------------+---------+---------+------------+------------+------------------+
| 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 | 13 | 11 | 40 |
+----------------+-----------------+---------+---------+------------+------------+------------------+
1 row in set (0.00 sec)
MySQL [oceanbase]>
2、创建资源单元和资源池
create resource unit my_unit_1c5g max_cpu=1, min_cpu=1, max_memory='5g', min_memory='5g', max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='10g';
create resource pool mysql_pool_test unit = 'my_unit_1c5g', unit_num = 1;
先创建资源单元unit，再创建资源池，资源池最小规格要求是1c5g，如果不满足最小资源要求，就会报错。
如下：
MySQL [oceanbase]>
MySQL [oceanbase]> create resource unit my_unit_1c2g max_cpu=2, min_cpu=1, max_memory='3g', min_memory='2g', max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='10g';
Query OK, 0 rows affected (0.04 sec)
MySQL [oceanbase]>
MySQL [oceanbase]> select unit_config_id,name,max_cpu,min_cpu,round(max_memory/1024/1024/1024) max_mem_gb, round(min_memory/1024/1024/1024) min_mem_gb, round(max_disk_size/1024/1024/1024) max_disk_size_gb
-> from __all_unit_config
-> order by unit_config_id;
+----------------+-----------------+---------+---------+------------+------------+------------------+
| 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 | 13 | 11 | 40 |
| 1001 | my_unit_1c2g | 2 | 1 | 3 | 2 | 10 |
+----------------+-----------------+---------+---------+------------+------------+------------------+
2 rows in set (0.01 sec)
MySQL [oceanbase]>
MySQL [oceanbase]> create resource pool mysql_pool_test unit = 'my_unit_1c2g', unit_num = 1;
ERROR 1235 (0A000): unit min memory less than __min_full_resource_pool_memory not supported
MySQL [oceanbase]>
另外，还要注意，每个资源池，指定的unit_num数量，不能超过每个zone下的observer服务器个数，否则也会报错。
可以创建不同的资源池，每个不同的资源池对应的unit单元规格可以相同。
比如我在1-1-1架构：
MySQL [oceanbase]> create resource pool mysql_pool_test unit = 'my_unit_1c2g', unit_num =2;
ERROR 4656 (HY000): resource pool unit num is bigger than zone server count
MySQL [oceanbase]>
MySQL [oceanbase]> drop resource unit my_unit_1c2g;
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]>
MySQL [oceanbase]> select unit_config_id,name,max_cpu,min_cpu,round(max_memory/1024/1024/1024) max_mem_gb, round(min_memory/1024/1024/1024) min_mem_gb, round(max_disk_size/1024/1024/1024) max_disk_size_gb
-> from __all_unit_config
-> order by unit_config_id;
+----------------+-----------------+---------+---------+------------+------------+------------------+
| 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 | 13 | 11 | 40 |
+----------------+-----------------+---------+---------+------------+------------+------------------+
1 row in set (0.00 sec)
MySQL [oceanbase]>
MySQL [oceanbase]> create resource unit my_unit_1c5g max_cpu=1, min_cpu=1, max_memory='5g', min_memory='5g', max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='10g';
Query OK, 0 rows affected (0.02 sec)
MySQL [oceanbase]> select unit_config_id,name,max_cpu,min_cpu,round(max_memory/1024/1024/1024) max_mem_gb, round(min_memory/1024/1024/1024) min_mem_gb, round(max_disk_size/1024/1024/1024) max_disk_size_gb
-> from __all_unit_config
-> order by unit_config_id;
+----------------+-----------------+---------+---------+------------+------------+------------------+
| 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 | 13 | 11 | 40 |
| 1002 | my_unit_1c5g | 1 | 1 | 5 | 5 | 10 |
+----------------+-----------------+---------+---------+------------+------------+------------------+
2 rows in set (0.00 sec)
MySQL [oceanbase]>
MySQL [oceanbase]> create resource pool mysql_pool_test unit = 'my_unit_1c5g', unit_num = 1;
Query OK, 0 rows affected (0.08 sec)
MySQL [oceanbase]>
MySQL [oceanbase]> select zone, svr_ip, svr_port,inner_port, cpu_total, cpu_assigned,
-> round(mem_total/1024/1024/1024) mem_total_gb,
-> round(mem_assigned/1024/1024/1024) mem_ass_gb,
-> round(disk_total/1024/1024/1024) disk_total_gb,
-> unit_num, substr(build_version,1,6) version
-> from __all_virtual_server_stat
-> order by zone, svr_ip, inner_port;
+-------+--------------+----------+------------+-----------+--------------+--------------+------------+---------------+----------+---------+
| zone | svr_ip | svr_port | inner_port | cpu_total | cpu_assigned | mem_total_gb | mem_ass_gb | disk_total_gb | unit_num | version |
+-------+--------------+----------+------------+-----------+--------------+--------------+------------+---------------+----------+---------+
| zone1 | 172.16.18.81 | 2882 | 2881 | 14 | 3.5 | 43 | 16 | 40 | 2 | 2.2.30 |
| zone2 | 172.16.18.82 | 2882 | 2881 | 14 | 3.5 | 43 | 16 | 40 | 2 | 2.2.30 |
| zone3 | 172.16.18.91 | 2882 | 2881 | 14 | 3.5 | 43 | 16 | 40 | 2 | 2.2.30 |
+-------+--------------+----------+------------+-----------+--------------+--------------+------------+---------------+----------+---------+
3 rows in set (0.01 sec)
MySQL [oceanbase]> select a.zone,concat(a.svr_ip,':',a.svr_port) observer, cpu_total, (cpu_total-cpu_assigned) cpu_free,
-> round(mem_total/1024/1024/1024) mem_total_gb, round((mem_total-mem_assigned)/1024/1024/1024) mem_free_gb,
-> round(disk_total/1024/1024/1024) disk_total_gb,
-> substr(a.build_version,1,6) version,usec_to_time(b.start_service_time) start_service_time
-> from __all_virtual_server_stat a join __all_server b on (a.svr_ip=b.svr_ip and a.svr_port=b.svr_port)
-> order by a.zone, a.svr_ip;
+-------+-------------------+-----------+----------+--------------+-------------+---------------+---------+----------------------------+
| zone | observer | cpu_total | cpu_free | mem_total_gb | mem_free_gb | disk_total_gb | version | start_service_time |
+-------+-------------------+-----------+----------+--------------+-------------+---------------+---------+----------------------------+
| zone1 | 172.16.18.81:2882 | 14 | 10.5 | 43 | 27 | 40 | 2.2.30 | 2020-08-12 12:05:02.983508 |
| zone2 | 172.16.18.82:2882 | 14 | 10.5 | 43 | 27 | 40 | 2.2.30 | 2020-08-12 12:05:04.159878 |
| zone3 | 172.16.18.91:2882 | 14 | 10.5 | 43 | 27 | 40 | 2.2.30 | 2020-08-12 12:05:05.166067 |
+-------+-------------------+-----------+----------+--------------+-------------+---------------+---------+----------------------------+
3 rows in set (0.00 sec)
查看资源池，这里看到新创建的资源池 mysql_pool_test 还未归属租户
MySQL [oceanbase]> select t1.name resource_pool_name, t2.name unit_config_name, t2.max_cpu, t2.min_cpu,
-> round(t2.max_memory/1024/1024/1024) max_mem_gb, round(t2.min_memory/1024/1024/1024) min_mem_gb,
-> 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 | max_mem_gb | min_mem_gb | unit_id | zone | observer | tenant_id | tenant_name |
+--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-------------+
| sys_pool | sys_unit_config | 5 | 2.5 | 13 | 11 | 1 | zone1 | 172.16.18.81:2882 | 1 | sys |
| sys_pool | sys_unit_config | 5 | 2.5 | 13 | 11 | 2 | zone2 | 172.16.18.82:2882 | 1 | sys |
| sys_pool | sys_unit_config | 5 | 2.5 | 13 | 11 | 3 | zone3 | 172.16.18.91:2882 | 1 | sys |
| mysql_pool_test | my_unit_1c5g | 1 | 1 | 5 | 5 | 1001 | zone1 | 172.16.18.81:2882 | NULL | NULL |
| mysql_pool_test | my_unit_1c5g | 1 | 1 | 5 | 5 | 1002 | zone2 | 172.16.18.82:2882 | NULL | NULL |
| mysql_pool_test | my_unit_1c5g | 1 | 1 | 5 | 5 | 1003 | zone3 | 172.16.18.91:2882 | NULL | NULL |
+--------------------+------------------+---------+---------+------------+------------+---------+-------+-------------------+-----------+-------------+
6 rows in set (0.00 sec)
MySQL [oceanbase]>
3、开始创建MySQL租户
创建租户的需指定租户使用的资源池、数据分布策略(primary_zone为RANDOM)、租户字符集(默认utf8，也可以改为gbk)、租户访问白名单(ob_tcp_invited_nodes)、租户兼容级别(ob_compatibility_mode)。
MySQL [oceanbase]> create tenant mysql_test_tent resource_pool_list=('mysql_pool_test'), primary_zone='RANDOM',comment 'mysql tenant/instance', charset='utf8' set ob_tcp_invited_nodes='%', ob_compatibility_mode='mysql';
Query OK, 0 rows affected (2.33 sec)
查看OB集群所有租户信息: 租户类别compatibility_mode，0为mysql兼容模式的租户， 1为oracle兼容模式的租户
MySQL [oceanbase]> select tenant_id, tenant_name, compatibility_mode,zone_list, locality ,gmt_modified from __all_tenant;
+-----------+-----------------+--------------------+-------------------+---------------------------------------------+----------------------------+
| tenant_id | tenant_name | compatibility_mode | zone_list | locality | gmt_modified |
+-----------+-----------------+--------------------+-------------------+---------------------------------------------+----------------------------+
| 1 | sys | 0 | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | 2020-08-12 12:05:00.986146 |
| 1001 | mysql_test_tent | 0 | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | 2020-08-12 15:30:23.097537 |
+-----------+-----------------+--------------------+-------------------+---------------------------------------------+----------------------------+
2 rows in set (0.00 sec)
MySQL [oceanbase]>
注意：同一个资源池resource pool 只能属于一个租户。也就是说，一个资源池已经分配给某个租户了，不能再分配给其它租户。
MySQL [oceanbase]> create tenant ora_test_tent resource_pool_list=('mysql_pool_test'), primary_zone='RANDOM',comment 'oracle tenant/instance', charset='utf8' set ob_tcp_invited_nodes='%', ob_compatibility_mode='oracle';
ERROR 4626 (HY000): resource pool 'mysql_pool_test' has already been granted to a tenant
MySQL [oceanbase]>
4、登录mysql租户，创建业务数据库和用户
登录新租户 mysql_test_tent ，修改新租户mysql_test_tent下root用户密码，默认为空，创建业务数据库，创建业务用户等。
$ mysql -h172.16.18.81 -uroot@mysql_test_tent#obdemo -P2883 oceanbase -A -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.25 OceanBase 2.2.30 (r20200515183156-366007805d18f72e1fbd8568ada91849305c53ac) (Built May 15 2020 19:45:08)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [oceanbase]> show databases;
+--------------------+
| Database |
+--------------------+
| oceanbase |
| information_schema |
| mysql |
| test |
+--------------------+
4 rows in set (0.02 sec)
MySQL [oceanbase]> alter user root identified by 'admin123';
Query OK, 0 rows affected (0.17 sec)
MySQL [oceanbase]> create database testdb;
Query OK, 1 row affected (0.30 sec)
--给用户aps2分配可对数据库testdb所有表进行所有操作的权限，并设定口令为aps2#12345
MySQL [oceanbase]> grant all privileges on testdb.* to aps2@'%' identified by 'aps2#12345';
Query OK, 0 rows affected (0.27 sec)
MySQL [oceanbase]> show grants for aps2;
+----------------------------------------------+
| Grants for aps2@% |
+----------------------------------------------+
| GRANT USAGE ON . TO 'aps2' |
| GRANT ALL PRIVILEGES ON testdb.* TO 'aps2' |
+----------------------------------------------+
2 rows in set (0.02 sec)
MySQL [oceanbase]> exit
Bye
$
5、使用新用户，连接新租户下的业务数据库，创建表等
$ mysql -h172.16.18.81 -uaps2@mysql_test_tent#obdemo -P2883 -paps2#12345 -c -A testdb
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.6.25 OceanBase 2.2.30 (r20200515183156-366007805d18f72e1fbd8568ada91849305c53ac) (Built May 15 2020 19:45:08)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
创建一个非分区表test
MySQL [testdb]> create table test(id int,name varchar(20),age int,dt datetime);
Query OK, 0 rows affected (0.70 sec)
MySQL [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test |
+------------------+
1 row in set (0.06 sec)
MySQL [testdb]> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| dt | datetime | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.03 sec)
MySQL [testdb]> insert into test values(1,'ob2.2.30',20,now());
Query OK, 1 row affected (0.08 sec)
MySQL [testdb]> insert into test values(2,'张三',26,'1998-06-17');
Query OK, 1 row affected (0.02 sec)
MySQL [testdb]> insert into test values(3,'李四',32,'1989-10-13');
Query OK, 1 row affected (0.02 sec)
MySQL [testdb]> insert into test values(4,'Tom',30,'1990-02-21');
Query OK, 1 row affected (0.02 sec)
MySQL [testdb]>
MySQL [testdb]> select * from test;
+------+----------+------+---------------------+
| id | name | age | dt |
+------+----------+------+---------------------+
| 1 | ob2.2.30 | 20 | 2020-08-12 15:50:27 |
| 2 | 张三 | 26 | 1998-06-17 00:00:00 |
| 3 | 李四 | 32 | 1989-10-13 00:00:00 |
| 4 | Tom | 30 | 1990-02-21 00:00:00 |
+------+----------+------+---------------------+
4 rows in set (0.01 sec)
创建一个hash分区表
MySQL [testdb]> create table test_hash(id int not null,name varchar(20) not null,age int,dt datetime default now())
-> partition by hash(id)
-> partitions 3;
Query OK, 0 rows affected (0.54 sec)
MySQL [testdb]> desc test_hash;
+-------+-------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+-------------------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
| dt | datetime | YES | | CURRENT_TIMESTAMP | |
+-------+-------------+------+-----+-------------------+-------+
4 rows in set (0.01 sec)
MySQL [testdb]> show create table test_hash;
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table +
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_hash | CREATE TABLE test_hash (id int(11) NOT NULL,name varchar(20) NOT NULL,age int(11) DEFAULT NULL,dt datetime DEFAULT CURRENT_TIMESTAMP
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.0' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10
partition by hash(id) partitions 3
|
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
MySQL [testdb]>
MySQL [testdb]> insert into test_hash values(1,'张三',26,'1998-06-17');
Query OK, 1 row affected (0.09 sec)
MySQL [testdb]> insert into test_hash values(2,'李四',32,'1989-10-13');
Query OK, 1 row affected (0.05 sec)
MySQL [testdb]> insert into test_hash values(3,'Tom',30,'1990-02-21');
Query OK, 1 row affected (0.06 sec)
MySQL [testdb]> select * from test_hash;
+----+--------+------+---------------------+
| id | name | age | dt |
+----+--------+------+---------------------+
| 3 | Tom | 30 | 1990-02-21 00:00:00 |
| 1 | 张三 | 26 | 1998-06-17 00:00:00 |
| 2 | 李四 | 32 | 1989-10-13 00:00:00 |
+----+--------+------+---------------------+
3 rows in set (0.02 sec)
MySQL [testdb]> exit
Bye
6、查看mysql租户下的表分区主副本和备副本所在的节点。
如下sql： 其中role=1为主副本，role=2为备副本
$ mysql -h172.16.18.81 -uroot@sys#obdemo -P2883 -padmin123 -c -A oceanbase
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 49
Server version: 5.6.25 OceanBase 2.2.30 (r20200515183156-366007805d18f72e1fbd8568ada91849305c53ac) (Built May 15 2020 19:45:08)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [oceanbase]> select tenant_id, tenant_name, compatibility_mode,zone_list, locality ,gmt_modified from __all_tenant;
+-----------+-----------------+--------------------+-------------------+---------------------------------------------+----------------------------+
| tenant_id | tenant_name | compatibility_mode | zone_list | locality | gmt_modified |
+-----------+-----------------+--------------------+-------------------+---------------------------------------------+----------------------------+
| 1 | sys | 0 | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | 2020-08-12 12:05:00.986146 |
| 1001 | mysql_test_tent | 0 | zone1;zone2;zone3 | FULL{1}@zone1, FULL{1}@zone2, FULL{1}@zone3 | 2020-08-12 15:30:23.097537 |
+-----------+-----------------+--------------------+-------------------+---------------------------------------------+----------------------------+
2 rows in set (0.00 sec)
MySQL [oceanbase]> SELECT t1.tenant_id,t1.tenant_name,t2.database_name,t3.table_id,t3.table_Name,t3.tablegroup_id,t3.part_num,t4.partition_Id,
-> t4.zone,t4.svr_ip,t4.role, round(t4.data_size/1024/1024) data_size_mb
-> from gv$tenant t1
-> join gv$database t2 on (t1.tenant_id = t2.tenant_id)
-> join gv$table t3 on (t2.tenant_id = t3.tenant_id and t2.database_id = t3.database_id and t3.index_type = 0)
-> left join gv$partition t4 on (t2.tenant_id = t4.tenant_id and ( t3.table_id = t4.table_id or t3.tablegroup_id = t4.table_id ) and t4.role in (1,2))
-> where t1.tenant_id = 1001
-> order by t3.tablegroup_id, t3.table_name,t4.partition_Id ;
+-----------+-----------------+---------------+------------------+------------+---------------+----------+--------------+-------+--------------+------+--------------+
| tenant_id | tenant_name | database_name | table_id | table_Name | tablegroup_id | part_num | partition_Id | zone | svr_ip | role | data_size_mb |
+-----------+-----------------+---------------+------------------+------------+---------------+----------+--------------+-------+--------------+------+--------------+
| 1001 | mysql_test_tent | testdb | 1100611139453777 | test | -1 | 1 | 0 | zone1 | 172.16.18.81 | 2 | 0 |
| 1001 | mysql_test_tent | testdb | 1100611139453777 | test | -1 | 1 | 0 | zone2 | 172.16.18.82 | 2 | 0 |
| 1001 | mysql_test_tent | testdb | 1100611139453777 | test | -1 | 1 | 0 | zone3 | 172.16.18.91 | 1 | 0 |
| 1001 | mysql_test_tent | testdb | 1100611139453778 | test_hash | -1 | 3 | 0 | zone1 | 172.16.18.81 | 1 | 0 |
| 1001 | mysql_test_tent | testdb | 1100611139453778 | test_hash | -1 | 3 | 0 | zone2 | 172.16.18.82 | 2 | 0 |
| 1001 | mysql_test_tent | testdb | 1100611139453778 | test_hash | -1 | 3 | 0 | zone3 | 172.16.18.91 | 2 | 0 |
| 1001 | mysql_test_tent | testdb | 1100611139453778 | test_hash | -1 | 3 | 1 | zone1 | 172.16.18.81 | 2 | 0 |
| 1001 | mysql_test_tent | testdb | 1100611139453778 | test_hash | -1 | 3 | 1 | zone2 | 172.16.18.82 | 1 | 0 |
| 1001 | mysql_test_tent | testdb | 1100611139453778 | test_hash | -1 | 3 | 1 | zone3 | 172.16.18.91 | 2 | 0 |
| 1001 | mysql_test_tent | testdb | 1100611139453778 | test_hash | -1 | 3 | 2 | zone1 | 172.16.18.81 | 2 | 0 |
| 1001 | mysql_test_tent | testdb | 1100611139453778 | test_hash | -1 | 3 | 2 | zone2 | 172.16.18.82 | 2 | 0 |
| 1001 | mysql_test_tent | testdb | 1100611139453778 | test_hash | -1 | 3 | 2 | zone3 | 172.16.18.91 | 1 | 0 |
+-----------+-----------------+---------------+------------------+------------+---------------+----------+--------------+-------+--------------+------+--------------+
12 rows in set (0.23 sec)
MySQL [oceanbase]>
以上结果可以看到: mysql_test_tent 租户下testdb库中
非分区表test的p0号分区，主副本在zone3下的91节点，两个备副本在zone1的81节点和zone2的82节点。
分区表test_hash三个分区的主副本都在不同的zone下:
test_hash分区表的p0分区主副本在zone1下的81节点，两个备副本在zone2的82节点和zone3的91节点。
test_hash分区表的p1分区主副本在zone2下的82节点，两个备副本在zone1的81节点和zone3的91节点。
test_hash分区表的p2分区主副本在zone3下的91节点，两个备副本在zone1的81节点和zone2的82节点。
到此，OceanBase2.2集群——mysql租户的体验到此结束。
