在GBase 8a数据库集群的V95版本里,支持虚拟集群来物理隔离计算节点资源,实现多租户。本文介绍该版本实现在虚拟集间创建按镜像表的功能,通过镜像功能是双向的的特性,可以实现实时的平等双活,也可以实现读写分离。
测试版本
gbase> select @@version;
+-----------------+
| @@version |
+-----------------+
| 9.5.2.26.121440 |
+-----------------+
1 row in set (Elapsed: 00:00:00.00)
镜像库
以数据库为单位做整体镜像。
语法
ALTER DATABASE VC1.DB CREATE MIRROR TO VC2;
检查一致性。
ALTER DATABASE VC1.DB CREATE MIRROR TO VC2 force;
说明
要求 VC1.DB 库和 VC2.DB 库必须存在。创建镜像库,除了给库下的表创建镜像表之外,还会把 VC1.DB下已存在的存储过程、函数在 VC2.DB 下创建。
如果VC2下没有同名的表,则创建新表,否则只创建镜像。
如果已经存在的表结构不一致,请先行调整。或者用 force参数强行检查,如不同会重建,所以请注意数据的安全性。
目标库存在,而源库不存在的表,不会自动同步,也就是镜像操作本身是单向的。
用例
创建数据库
gbase> use vc vc01;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> create database testmirror;
Query OK, 1 row affected (Elapsed: 00:00:00.00)
gbase> create database vc02.testmirror;
Query OK, 1 row affected (Elapsed: 00:00:00.01)
创建测试用的表
我们在源库vc01创建一个int字段的t1表,在vc02创建一个name类型的表结构不同的t1表,以及一个vc01不存在的t2表。
gbase> create table vc01.testmirror.t1(id int);
Query OK, 0 rows affected (Elapsed: 00:00:00.08)
-- 表名字相同但表结构不同
gbase> create table vc02.testmirror.t1(name varchar(100));
Query OK, 0 rows affected (Elapsed: 00:00:00.09)
-- 多了一个表
gbase> create table vc02.testmirror.t2(id int);
Query OK, 0 rows affected (Elapsed: 00:00:00.09)
gbase> show tables from vc01.testmirror;
+----------------------+
| Tables_in_testmirror |
+----------------------+
| t1 |
+----------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> show tables from vc02.testmirror;
+----------------------+
| Tables_in_testmirror |
+----------------------+
| t1 |
| t2 |
+----------------------+
2 rows in set (Elapsed: 00:00:00.00)
普通镜像
如下的普通镜像库,会看到vc02的t2表并没有镜像到vc01,证明镜像操作本身是单向的。
gbase> alter database testmirror create mirror to vc02;
Query OK, 0 rows affected, 1 warning (Elapsed: 00:00:00.01)
gbase> show warnings;
+---------+------------+-------------------------------+
| Level | Code | Message |
+---------+------------+-------------------------------+
| Warning | 4294967295 | vc00002.testmirror.t1 exists. |
+---------+------------+-------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> use testmirror;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> show tables from vc01.testmirror;
+----------------------+
| Tables_in_testmirror |
+----------------------+
| t1 |
+----------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> show tables from vc02.testmirror;
+----------------------+
| Tables_in_testmirror |
+----------------------+
| t1 |
| t2 |
+----------------------+
2 rows in set (Elapsed: 00:00:00.00)
查看同名的t1表结构,发现保持原样,并没有和vc01保持一致。
gbase> show create table vc02.testmirror.t1;
+-------+--------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE "t1" (
"name" varchar(100) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+-------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
强制镜像库
增加force参数,进行强制镜像库
gbase> alter database testmirror delete mirror;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase>
gbase> alter database testmirror create mirror to vc02 force;
Query OK, 0 rows affected (Elapsed: 00:00:00.31)
查看信息,发现vc02的t1表的结构和vc01的一致了。
gbase> show tables from vc01.testmirror;
+----------------------+
| Tables_in_testmirror |
+----------------------+
| t1 |
+----------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> show tables from vc02.testmirror;
+----------------------+
| Tables_in_testmirror |
+----------------------+
| t1 |
| t2 |
+----------------------+
2 rows in set (Elapsed: 00:00:00.00)
gbase> show create table vc02.testmirror.t1;
+-------+-------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE "t1" (
"id" int(11) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+-------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
测试存储过程、函数和视图
gbase> create view v_t1 as select * from t1;
Query OK, 0 rows affected (Elapsed: 00:00:00.02)
gbase> delimiter //
gbase> create function f_1() returns datetime
-> begin
-> return now();
-> end//
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> select vc01.testmirror.f_1();
+-----------------------+
| vc01.testmirror.f_1() |
+-----------------------+
| 2021-01-12 18:56:31 |
+-----------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select * from vc01.testmirror.v_t1;
Empty set (Elapsed: 00:00:00.01)
强制同步后,发现函数自动镜像过去了,视图没有。
gbase> alter database testmirror create mirror to vc02 force;
Query OK, 0 rows affected (Elapsed: 00:00:00.34)
gbase> use vc02.testmirror;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> show tables;
+----------------------+
| Tables_in_testmirror |
+----------------------+
| t1 |
| t2 |
+----------------------+
2 rows in set (Elapsed: 00:00:00.00)
gbase> select vc01.testmirror.f_1();
+-----------------------+
| vc01.testmirror.f_1() |
+-----------------------+
| 2021-01-12 18:56:31 |
+-----------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> select * from vc01.testmirror.v_t1;
Empty set (Elapsed: 00:00:00.01)
gbase> select * from vc02.testmirror.v_t1;
ERROR 1146 (42S02): Table 'vc02.testmirror.v_t1' doesn't exist
gbase>
镜像库总结
镜像库适合全新的库,否则一定要注意原有数据的安全。另外测试版本视图是不同步的,从现有手册看也没有写视图自动创建。需要注意新版本是否已经提供。
镜像表
语法
包括2种情况,一,全新的表创建镜像;二、已经存在的表创建镜像。
新建表直接指定镜像
通过在建表语句后面,增加mirror to VC2来创建。
CREATE TABLE VC1.DB.T1(A INT, B VARCHAR(10)) MIRROR TO VC2;
已存在的表创建镜像
通过Alter语句,在后面增加 create mirror to vc2的方式创建。
ALTER TABLE VC1.DB.T1 CREATE MIRROR TO VC2;
强制创建镜像表并同步结构
ALTER TABLE VC1.DB.T1 CREATE MIRROR TO VC2;
说明
如果目标表已经存在,或者删除,或者确认和原始表结构一致,否则有数据丢失风险。
样例
新表全新建立镜像表
在vc01创建的表,会在vc02直接建立。
gbase> show tables from vc02.testdb;
+------------------+
| Tables_in_testdb |
+------------------+
| t1 |
+------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> create table t2(id int) mirror to vc02;
Query OK, 0 rows affected (Elapsed: 00:00:00.18)
gbase> show tables from vc01.testdb;
+------------------+
| Tables_in_testdb |
+------------------+
| t1 |
| t2 |
+------------------+
2 rows in set (Elapsed: 00:00:00.00)
gbase> show tables from vc02.testdb;
+------------------+
| Tables_in_testdb |
+------------------+
| t1 |
| t2 |
+------------------+
2 rows in set (Elapsed: 00:00:00.00)
gbase>
建新表时,目标镜像表已存在
报表已经存在错误,执行失败。
gbase> create table t3(id int) mirror to vc02;
ERROR 1702 (HY000): gcluster table error: mirror vc vc00002 already exists table t3
gbase>
目标镜像表已存在,新建表
现有版本,不支持在create table时指定force参数。
gbase> create table t3(id int) mirror to vc02 force;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your GBase server version for the right syntax to use near 'force' at line 1
gbase>
需要先创建本地表,然后使用alter进行镜像。参看下一节
目标镜像表已存在,已存在表
通过force参数,强行将目标表重建。如果已经存在数据会造成丢失。
gbase> create table t3(id int, name varchar(100));
Query OK, 0 rows affected (Elapsed: 00:00:00.09)
gbase> show create table vc01.testdb.t3;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| t3 | CREATE TABLE "t3" (
"id" int(11) DEFAULT NULL,
"name" varchar(100) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> show create table vc02.testdb.t3;
+-------+-------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------+
| t3 | CREATE TABLE "t3" (
"id" int(11) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+-------+-------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> alter table t3 create mirror to vc02 force;
Query OK, 0 rows affected (Elapsed: 00:00:00.33)
gbase> show create table vc01.testdb.t3;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| t3 | CREATE TABLE "t3" (
"id" int(11) DEFAULT NULL,
"name" varchar(100) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
gbase> show create table vc02.testdb.t3;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| t3 | CREATE TABLE "t3" (
"id" int(11) DEFAULT NULL,
"name" varchar(100) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace' |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
镜像表总结
镜像表要求目标表不能存在。
如果强行重建(force),请注意数据安全,一旦用了force,有数据丢失风险。
镜像功能测试
通过如下测试,确认镜像功能是双向的。
vc01到vc02
在vc01里数据变动,可以在vc02里看到。
gbase> select * from vc01.testdb.t4;
Empty set (Elapsed: 00:00:00.00)
gbase> select * from vc02.testdb.t4;
Empty set (Elapsed: 00:00:00.01)
gbase> insert into vc01.testdb.t4 values(1,'First');
Query OK, 1 row affected (Elapsed: 00:00:00.11)
gbase> select * from vc01.testdb.t4;
+------+-------+
| id | name |
+------+-------+
| 1 | First |
+------+-------+
1 row in set (Elapsed: 00:00:00.01)
gbase> select * from vc02.testdb.t4;
+------+-------+
| id | name |
+------+-------+
| 1 | First |
+------+-------+
1 row in set (Elapsed: 00:00:00.00)
vc02到vc01
在vc02里数据变动,可以立即在vc01里看到。
gbase> insert into vc02.testdb.t4 values(2,'vc02.Second');
Query OK, 1 row affected (Elapsed: 00:00:00.11)
gbase> select * from vc01.testdb.t4;
+------+-------------+
| id | name |
+------+-------------+
| 1 | First |
| 2 | vc02.Second |
+------+-------------+
2 rows in set (Elapsed: 00:00:00.01)
gbase> select * from vc02.testdb.t4;
+------+-------------+
| id | name |
+------+-------------+
| 1 | First |
| 2 | vc02.Second |
+------+-------------+
2 rows in set (Elapsed: 00:00:00.00)
业务方案
通过镜像功能,可以实现一些业务上的优化,满足特定的业务需求。
平等双活
由于镜像功能保持数据实时同步,可以做到实时的主备双活。
在考虑到数据同步是双向的,而不是常见主备集群的单向,可以实行平等双活。也就是业务可以将负载均衡到任意一个镜像集群上,可以实现更高的高可用。
读写分离方案
利用主备同步机制,将复杂、耗时的计算放在vc01,然后集群通过内部机制,只将处理的结果同步到镜像集群vc02。这样在vc02专注于提供更高的并发查询性能,实现读写分离。
总结
GBase 8a通过镜像表功能,实现数据的实时同步传输,是实时双活和读写分离的最佳方案。
如对实时性要求不高,比如每天同步一次即可,或者2套集群不在一个机房或网络带宽不足,可以考虑跨集群的同步方案。