1.1.1 虚拟集群镜像表
1.1.1.1 功能说明
虚拟集群镜像表功能是基于虚拟集群多VC概念基础之上的功能，主要用于在两个VC之间给虚拟集群的表设置镜像关系，从而使用户对其中一张表的数据写操作（DDL, DML, LOAD）能实时同步到镜像关系对应的另一张表。
本功能要求分别属于两个不同VC表具有相同的库名和表名时，才可以有镜像关系。并且删除已存在镜像关系时，要求主表和镜像表都是可用的。
1.1.1.2 命令说明
虚拟集群镜像功能相关的命令主要包含创建镜像，删除镜像两类。使用镜像功能需要保证主表和镜像表所在的两个VC的HASHMAP是相同的。可以用如下方法确认两个VC的HASHMAP是否相同。
1. 查看两个VC的distribution。
gcadmin showdistribution vc vc1 |
镜像功能要求两个VC的distribution的主分片数相同。比如distribution 1有3个主分片，那么distribution2就要求也有3个主分片，备份分片数无要求。
2. 比对两个VC的HASHMAP是否相同。HASHMAP的ID等于distribution id。执行如下SQL后结果与以下相同就说明vc1的hashmap1与vc2的hashmap2相同。
gbase> select distinct(res.r), count(res.r) from (select count(*) r from gbase.nodedatamap t where t.data_distribution_id in (1,2) group by t.hashkey, t.nodeid) res group by res.r; +---+--------------+ | r | count(res.r) | +---+--------------+ | 2 | 65536 | +---+--------------+ |
为了使两个VC的HASHMAP相同，虚拟集群镜像功能版本增加了如下命令来初始化HASHMAP。
INITNODEDATAMAP FROM VC1 |
比如有两个VC，VC1已经使用INITNODEDATAMAP命令初始化过HASHMAP，VC2需要使用INITNODEDATAMAP FROM VC1来初始化HASHMAP，这样VC1和VC2和HASHMAP就会相同。
1.1.1.2.1 创建镜像
虚拟集群镜像功能提供了三种方式创建表镜像。分别是：
1. 创建单个表镜像；
2. 以库为单位创建表镜像；
3. 同时创建主表和镜像表。
l 创建单个表镜像
给已存在的表创建镜像表时，使用如下命令：
ALTER TABLE VC1.DB.T1 CREATE MIRROR TO VC2; |
执行以上命令要求VC1.DB.T1表和VC2.DB库必须存在，否则报错。如果VC2.DB.T1表也存在，以上命令也会报错，如果需要强制给VC1.DB.T1表创建镜像，那么需要使用改下命令：
ALTER TABLE VC1.DB.T1 CREATE MIRROR TO VC2 FORCE; |
以上命令使用FORCE参数，功能是当VC2.DB.T1表存在时，也会强制创建VC1.DB.T1的镜像表。创建过程根据这两个表的表结构是否相同而略有不同。当VC1.DB.T1与VC2.DB.T1的表结构不相同时，该命令会删除VC2.DB.T1表，然后使用VC1.DB.T1表的建表SQL重新创建VC2.DB.T1表，最后再同步VC1.DB.T1表数据到VC2.DB.T1表。当VC1.DB.T1与VC2.DB.T1的表结构相同时，该命令会直接同步VC1.DB.T1表的数据到VC2.DB.T1表，数据同步采用增量同步方式。
l 以库为单位创建表镜像
给DataBase下所有表创建镜像表时，使用如下命令：
ALTER DATABASE VC1.DB CREATE MIRROR TO VC2; |
执行以上命令要求VC1.DB库和VC2.DB库必须存在，否则VC1.DB下所有表将创建镜像失败，失败信息以warning的方式返回。
本功能主要是批量给库下的表创建镜像，也就是多个表并发创建镜像，并发数由参数gcluster_mirror_parallel_count指定。注：gcluster_mirror_parallel_count是session级参数，以库为单位创建镜像表时 gcluster_mirror_parallel_count值不可修改，需要ctrl-C后再修改gcluster_mirror_parallel_count值，然后再执行以库为单位创建镜像表。
需要给DB下的表使用Force参数创建镜像表，使用如下命令。
ALTER DATABASE VC1.DB CREATE MIRROR TO VC2 FORCE; |
以库为单位创建表镜像功能除了给库下的表创建镜像表之外，还会把VC1.DB下已存在的存储过程、函数在VC2.DB下创建。
l 同时创建主表和镜像表
有两个种方式同时创建主表和镜像表。命令如下：
方法1是在CREATE TABLE语句中增加MIRROR TO 参数来实现同时创建主表和镜像表。
CREATE TABLE VC1.DB.T1(A INT, B VARCHAR(10)) MIRROR TO VC2; |
方法2是先设置VC1.DB库的默认镜像值，然后在VC1.DB库下创建的表默认都会在同时创建主表和镜像表。
ALTER DATABASE VC1.DB SET DEFAULT MIRROR = VC2; CREATE TABLE VC1.DB.T1(A INT, B VARCHAR(10)); |
需要特别说明一下VC1.DB库的默认镜像值，如果库有默认镜像值，那么在该库下创建表时默认会同时创建主表和镜像表，镜像表所在VC就是该库的默认镜像值。但是变更库的默认镜像值并不会对已存在的表造成影响。如果库有默认镜像值，那么在该库下创建函数和存储过程时默认也会同时在VC2.DB下创建相同的函数和存储过程。
1.1.1.2.2 删除镜像
虚拟集群镜像功能有两种方法删除镜像。需要特别说明的是，删除镜像功能只删除表的镜像关系，不删除表数据。比如VC1.DB.T1和VC2.DB.T1有镜像关系，删除镜像后，两张表不再有镜像关系，但是两张表仍然存在。
l 删除单个表的镜像
删除表的镜像命令如下：
ALTER TABLE VC1.DB.T1 DELETE MIRROR; |
删除表的镜像后，VC1.DB.T1和VC2.DB.T1不再有镜像关系，但是两张表仍然存在。对其中一张表的写操作将不再同步到另一张表。
删除表的镜像时要求VC1.DB.T1和VC2.DB.T1两张表都可用，即两张表不存在镜像关系后，可以是两张独立可用的表。
l 以库为单位删除表镜像
以库为单位删除表镜像命令如下：
ALTER DATABASE VC1.DB DELETE MIRROR; |
该命令将删除VC1.DB下所有表的镜像。
1.1.1.3 样例说明
l 搭建环境样例
1. 先安装虚拟集群。
2. 创建两个VC，分别是vc1和vc2。
3. 在vc1和vc2下分别创建一个distribution。
gcadmin distribution gcChangeInfo.xml p 1 d 1 vc vc1 gcadmin distribution gcChangeInfo.xml p 1 d 1 vc vc2 |
4. 初始化vc1和vc2的hashmap。
gccli -uroot -Dvc1. -e"initnodedatamap" gccli -uroot -Dvc2. -e"initnodedatamap from vc1" |
l 镜像功能样例
-- prepare data drop database if exists vc1.mirror_test; drop database if exists vc2.mirror_test; create database vc1.mirror_test; create database vc2.mirror_test; create table vc1.mirror_test.t_rand (l_orderkey bigint, l_partkey bigint, l_suppkey bigint, l_linenumber bigint, l_quantity decimal(15,2), l_extendedprice decimal(15,2), l_discount decimal(15,2), l_tax decimal(15,2), l_returnflag char(1), l_linestatus char(1), l_shipdate date, l_commitdate date, l_receiptdate date, l_shipinstruct char(25), l_shipmode char(10), l_comment varchar(50)); create table vc1.mirror_test.t_hash (l_orderkey bigint, l_partkey bigint, l_suppkey bigint, l_linenumber bigint, l_quantity decimal(15,2), l_extendedprice decimal(15,2), l_discount decimal(15,2), l_tax decimal(15,2), l_returnflag char(1), l_linestatus char(1), l_shipdate date, l_commitdate date, l_receiptdate date, l_shipinstruct char(25), l_shipmode char(10), l_comment varchar(50)) distributed by ('l_orderkey'); create table vc1.mirror_test.t_rep (l_orderkey bigint, l_partkey bigint, l_suppkey bigint, l_linenumber bigint, l_quantity decimal(15,2), l_extendedprice decimal(15,2), l_discount decimal(15,2), l_tax decimal(15,2), l_returnflag char(1), l_linestatus char(1), l_shipdate date, l_commitdate date, l_receiptdate date, l_shipinstruct char(25), l_shipmode char(10), l_comment varchar(50)) replicated; insert into vc1.mirror_test.t_rand values ('1','310379','15395','1','17','23619.12','0.04','0.02','N','O','1996-03-13','1996-02-12','1996-03-22','DELIVER IN PERSON','TRUCK','blithely regular ideas caj'), ('2','212340','2361','1','38','47588.54','0.00','0.05','N','O','1997-01-28','1997-01-14','1997-02-02','TAKE BACK RETURN','RAIL','carefully ironic platelets against t'), ('3','8594','3595','1','45','67616.55','0.06','0.00','R','F','1994-02-02','1994-01-04','1994-02-23','NONE','AIR','blithely s'), ('4','176070','11095','1','30','34382.10','0.03','0.08','N','O','1996-01-10','1995-12-14','1996-01-18','DELIVER IN PERSON','REG AIR','special dependencies am'), ('5','217139','17140','1','15','15841.80','0.02','0.04','R','F','1994-10-31','1994-08-31','1994-11-20','NONE','AIR','unusual, even instructio'), ('6','279271','4285','1','37','46259.62','0.08','0.03','A','F','1992-04-27','1992-05-15','1992-05-02','TAKE BACK RETURN','TRUCK','ruthlessly unusual warhorses sleep slyly af'), ('7','364104','19159','1','12','14017.08','0.07','0.03','N','O','1996-05-07','1996-03-13','1996-06-03','TAKE BACK RETURN','FOB','pending requests sleep furiously above'), ('32','165408','15425','1','28','41255.20','0.05','0.08','N','O','1995-10-23','1995-08-27','1995-10-26','TAKE BACK RETURN','TRUCK','ironic requests dazzle. final d'), ('33','122671','17690','1','31','52503.77','0.09','0.04','A','F','1993-10-29','1993-12-19','1993-11-08','COLLECT COD','TRUCK','slyly even requests are f'), ('34','176723','1732','1','13','23396.36','0.00','0.07','N','O','1998-10-23','1998-09-14','1998-11-06','NONE','REG AIR','regular deposits grow. regu'); insert into vc1.mirror_test.t_hash select * from vc1.mirror_test.t_rand; insert into vc1.mirror_test.t_rep select * from vc1.mirror_test.t_rand; -- test create del mirror table alter table vc1.mirror_test.t_rand create mirror to vc2; select vc_id, index_name, mirror_vc_id from gbase.table_distribution where vc_id in (select ID from information_schema.vc where name in ('vc1', 'vc2')) and index_name='mirror_test.t_rand'; select * from vc1.mirror_test.t_rand where l_orderkey not in (select l_orderkey from vc2.mirror_test.t_rand); alter table vc1.mirror_test.t_hash create mirror to vc2; select vc_id, index_name, mirror_vc_id from gbase.table_distribution where vc_id in (select ID from information_schema.vc where name in ('vc1', 'vc2')) and index_name='mirror_test.t_hash'; select * from vc1.mirror_test.t_hash where l_orderkey not in (select l_orderkey from vc2.mirror_test.t_hash); alter table vc1.mirror_test.t_rep create mirror to vc2; select vc_id, index_name, mirror_vc_id from gbase.table_distribution where vc_id in (select ID from information_schema.vc where name in ('vc1', 'vc2')) and index_name='mirror_test.t_rep'; select * from vc1.mirror_test.t_rep where l_orderkey not in (select l_orderkey from vc2.mirror_test.t_rep); alter table vc1.mirror_test.t_rand delete mirror; select vc_id, index_name, mirror_vc_id from gbase.table_distribution where vc_id in (select ID from information_schema.vc where name in ('vc1', 'vc2')) and index_name='mirror_test.t_rand'; select * from vc1.mirror_test.t_rand where l_orderkey not in (select l_orderkey from vc2.mirror_test.t_rand); alter table vc1.mirror_test.t_hash delete mirror; select vc_id, index_name, mirror_vc_id from gbase.table_distribution where vc_id in (select ID from information_schema.vc where name in ('vc1', 'vc2')) and index_name='mirror_test.t_hash'; select * from vc1.mirror_test.t_hash where l_orderkey not in (select l_orderkey from vc2.mirror_test.t_hash); alter table vc1.mirror_test.t_rep delete mirror; select vc_id, index_name, mirror_vc_id from gbase.table_distribution where vc_id in (select ID from information_schema.vc where name in ('vc1', 'vc2')) and index_name='mirror_test.t_rep'; select * from vc1.mirror_test.t_rep where l_orderkey not in (select l_orderkey from vc2.mirror_test.t_rep); alter table vc2.mirror_test.t_rand create mirror to vc1 force; select vc_id, index_name, mirror_vc_id from gbase.table_distribution where vc_id in (select ID from information_schema.vc where name in ('vc1', 'vc2')) and index_name='mirror_test.t_rand'; select * from vc1.mirror_test.t_rand where l_orderkey not in (select l_orderkey from vc2.mirror_test.t_rand); alter table vc2.mirror_test.t_hash create mirror to vc1 force; select vc_id, index_name, mirror_vc_id from gbase.table_distribution where vc_id in (select ID from information_schema.vc where name in ('vc1', 'vc2')) and index_name='mirror_test.t_hash'; select * from vc1.mirror_test.t_hash where l_orderkey not in (select l_orderkey from vc2.mirror_test.t_hash); alter table vc2.mirror_test.t_rep create mirror to vc1 force; select vc_id, index_name, mirror_vc_id from gbase.table_distribution where vc_id in (select ID from information_schema.vc where name in ('vc1', 'vc2')) and index_name='mirror_test.t_rep'; select * from vc1.mirror_test.t_rep where l_orderkey not in (select l_orderkey from vc2.mirror_test.t_rep); -- test create del database mirror alter database vc1.mirror_test create mirror to vc2; select vc_id, index_name, mirror_vc_id from gbase.table_distribution where vc_id in (select ID from information_schema.vc where name in ('vc1', 'vc2')) and dbname='mirror_test' order by index_name; alter database vc1.mirror_test delete mirror; select vc_id, index_name, mirror_vc_id from gbase.table_distribution where vc_id in (select ID from information_schema.vc where name in ('vc1', 'vc2')) and dbname='mirror_test' order by index_name; alter database vc1.mirror_test create mirror to vc2 force; select vc_id, index_name, mirror_vc_id from gbase.table_distribution where vc_id in (select ID from information_schema.vc where name in ('vc1', 'vc2')) and dbname='mirror_test' order by index_name; drop database vc1.mirror_test; drop database vc2.mirror_test; |
