一、环境
名称 | 值 |
---|---|
cpu | Intel® Core™ i5-1035G1 CPU @ 1.00GHz |
操作系统 | CentOS Linux release 7.9.2009 (Core) |
内存 | 3G |
逻辑核数 | 2 |
VC1 | 192.168.142.10 |
VC2 | 192.168.142.11 |
二、库级镜像同步
用途:使不同VC间的两个数据库进行实时同步。
(1)不同VC下的需要的数据库名需要一致。
1、各VC间创建数据库
gbase> create database vc1.primarydb;
Query OK, 1 row affected (Elapsed: 00:00:00.02)
gbase> create database vc2.primarydb;
Query OK, 1 row affected (Elapsed: 00:00:00.01)
2、生成测试数据
gbase> use vc1.primarydb;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> create table test(a int);
Query OK, 0 rows affected (Elapsed: 00:00:00.14)
gbase> DELIMITER //
gbase> CREATE PROCEDURE "GenerateTestData"(num int)
-> begin
-> declare tempval int;
->
-> set tempval = 1;
-> set autocommit = off;
-> label: loop
-> insert into test values(tempval);
-> if tempval >= num then
-> leave label;
-> else
-> set tempval = tempval + 1;
-> end if;
-> end loop label;
-> commit;
-> end;
-> //
Query OK, 0 rows affected (Elapsed: 00:00:00.03)
gbase> DELIMITER ;
gbase> call "GenerateTestData"(10);
Query OK, 0 rows affected (Elapsed: 00:00:00.48)
gbase> select * from test;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
10 rows in set (Elapsed: 00:00:00.01)
gbase> create view v_test as select * from test;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)
3、创建库级镜像同步
gbase> alter database vc1.primarydb create mirror to vc2;
Query OK, 1 row affected (Elapsed: 00:00:00.31)
4、检查是否同步
表结构、数据、存储过程都可以同步,但视图除外。
gbase> desc vc2.primarydb.test;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (Elapsed: 00:00:00.00)
gbase> desc vc2.primarydb.v_test;
ERROR 1146 (42S02): Table 'vc2.primarydb.v_test' doesn't exist
gbase> SELECT ROUTINE_VC,ROUTINE_NAME FROM vc2.information_schema.Routines;
+------------+------------------------------------------------------------------+
| ROUTINE_VC | ROUTINE_NAME |
+------------+------------------------------------------------------------------+
| vc1 | GenerateTestData |
| vc2 | GenerateTestData |
+------------+------------------------------------------------------------------+
2 rows in set (Elapsed: 00:00:00.00)
gbase> select * from vc2.primarydb.test;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+------+
10 rows in set (Elapsed: 00:00:00.01)
5、测试增量数据是否相互同步
gbase> insert into vc1.primarydb.test values(11);
Query OK, 1 row affected (Elapsed: 00:00:00.08)
gbase> select * from vc2.primarydb.test;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
+------+
11 rows in set (Elapsed: 00:00:00.02)
gbase> insert into vc2.primarydb.test values(12);
Query OK, 1 row affected (Elapsed: 00:00:00.15)
gbase> select * from vc1.primarydb.test;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
+------+
12 rows in set (Elapsed: 00:00:00.01)
6、数据字典表查看镜像关系
gbase> select vc_id,index_name,mirror_vc_id from gbase.table_distribution where mirror_vc_id is not null and dbname='primarydb' order by vc_id;
+---------+----------------+--------------+
| vc_id | index_name | mirror_vc_id |
+---------+----------------+--------------+
| vc00001 | primarydb.test | vc00002 |
| vc00002 | primarydb.test | vc00001 |
+---------+----------------+--------------+
2 rows in set (Elapsed: 00:00:00.00)
7、删除库级镜像关系
gbase> ALTER DATABASE VC1.primarydb DELETE MIRROR;
Query OK, 1 row affected (Elapsed: 00:00:00.14)
gbase> select vc_id,index_name,mirror_vc_id from gbase.table_distribution where mirror_vc_id is not null and dbname='primarydb' order by vc_id;
Empty set (Elapsed: 00:00:00.00)
8、强制建立同步关系
上面我们已经删除了同步关系,但数据对象并没有删除,两个库中存在相同数据及对象。这是我们再创建镜像关系会出现警告。
gbase> alter database vc1.primarydb create mirror to vc2;
Query OK, 1 row affected, 2 warnings (Elapsed: 00:00:00.04)
gbase> show warnings;
+---------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 4294967295 | vc00002.primarydb.test exists. |
| Warning | 1703 | [192.168.142.10:5258](GBA-02AD-0005)Failed to query in gnode:
DETAIL: gcluster procedure error: PROCEDURE GenerateTestData already exists.
SQL: CREATE PROCEDURE primarydb.GenerateTestData(num int) begin
declare tempval int;
set tempval = 1;
set autocommit = off;
label: loop
insert into test values(tempval);
if tempval >= num then
leave label;
else
set tempval = tempval + 1;
end if;
end loop label;
commit;
end |
+---------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (Elapsed: 00:00:00.00)
gbase> select * from gbase.table_distribution where mirror_vc_id is not null and dbname='primarydb' order by vc_id;
Empty set (Elapsed: 00:00:00.00)
也就是说如果需要同步的库中有相同名字的对象,则不会创建复制关系。我们加上强制关键字force看看。
gbase> alter database vc1.primarydb create mirror to vc2 force;
Query OK, 1 row affected (Elapsed: 00:00:00.32)
gbase> select * from gbase.table_distribution where mirror_vc_id is not null and dbname='primarydb' order by vc_id;
+----------------+-----------+--------+-------------+-------------+------------------+--------------------+-------------+----------------------+---------+--------------+
| index_name | dbName | tbName | isReplicate | hash_column | lmt_storage_size | table_storage_size | is_nocopies | data_distribution_id | vc_id | mirror_vc_id |
+----------------+-----------+--------+-------------+-------------+------------------+--------------------+-------------+----------------------+---------+--------------+
| primarydb.test | primarydb | test | NO | NULL | NULL | NULL | NO | 1 | vc00001 | vc00002 |
| primarydb.test | primarydb | test | NO | NULL | NULL | NULL | NO | 2 | vc00002 | vc00001 |
+----------------+-----------+--------+-------------+-------------+------------------+--------------------+-------------+----------------------+---------+--------------+
2 rows in set (Elapsed: 00:00:00.00)
force关键字会强行将目标表重建,如果目标表存在数据会造成丢失,建议谨慎使用。
9、创建库级的默认镜像关系
gbase> ALTER DATABASE VC1.primarydb create mirror to vc2;
Query OK, 1 row affected, 2 warnings (Elapsed: 00:00:00.05)
gbase> ALTER DATABASE VC1.primarydb create mirror to vc2 force;
Query OK, 1 row affected (Elapsed: 00:00:00.37)
gbase> show mirror databases;
+--------------------+-----+-----------+
| Database | VC | MIRROR_VC |
+--------------------+-----+-----------+
| information_schema | | |
| performance_schema | | |
| gbase | | |
| gctmpdb | | |
| gclusterdb | vc2 | |
| primarydb | vc2 | vc1 |
| standbydb | vc2 | |
+--------------------+-----+-----------+
7 rows in set (Elapsed: 00:00:00.00)
gbase> use vc vc1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)
gbase> show mirror databases;
+--------------------+-----+-----------+
| Database | VC | MIRROR_VC |
+--------------------+-----+-----------+
| information_schema | | |
| performance_schema | | |
| gbase | | |
| gctmpdb | | |
| czg | vc1 | |
| gclusterdb | vc1 | |
| primarydb | vc1 | vc2 |
+--------------------+-----+-----------+
7 rows in set (Elapsed: 00:00:00.00)
10、删除库级的默认镜像关系
gbase> ALTER DATABASE VC1.primarydb DELETE MIRROR;
Query OK, 1 row affected (Elapsed: 00:00:00.16)
gbase> ALTER DATABASE VC1.primarydb SET DEFAULT MIRROR = NULL;
Query OK, 1 row affected (Elapsed: 00:00:00.01)
gbase> show mirror databases;
+--------------------+-----+-----------+
| Database | VC | MIRROR_VC |
+--------------------+-----+-----------+
| information_schema | | |
| performance_schema | | |
| gbase | | |
| gctmpdb | | |
| czg | vc1 | |
| gclusterdb | vc1 | |
| primarydb | vc1 | |
+--------------------+-----+-----------+
7 rows in set (Elapsed: 00:00:00.00)
三、表级镜像同步
1、同时创建表及其镜像表
gbase> CREATE TABLE vc1.primarydb.test1(a int) MIRROR TO VC2;
Query OK, 0 rows affected (Elapsed: 00:00:00.16)
2、源端有表目的端没有表
gbase> CREATE TABLE vc1.primarydb.test3(a int);
Query OK, 0 rows affected (Elapsed: 00:00:00.13)
gbase> alter table vc1.primarydb.test3 create mirror to vc2;
Query OK, 1 row affected (Elapsed: 00:00:00.19)
3、源端有表目的端有表
gbase> CREATE TABLE vc1.primarydb.test4(a int);
Query OK, 0 rows affected (Elapsed: 00:00:00.13)
gbase> CREATE TABLE vc2.primarydb.test4(a int);
Query OK, 0 rows affected (Elapsed: 00:00:00.14)
gbase> alter table vc1.primarydb.test4 create mirror to vc2;
ERROR 1707 (HY000): gcluster command error: vc00002.primarydb.test4 exists.
gbase> alter table vc1.primarydb.test4 create mirror to vc2 force;
Query OK, 1 row affected (Elapsed: 00:00:00.23)
4、测试DDL是否同步
gbase> desc vc1.primarydb.test3;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (Elapsed: 00:00:00.00)
gbase> desc vc2.primarydb.test3;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (Elapsed: 00:00:00.00)
gbase> alter table vc1.primarydb.test3 add column b int;
Query OK, 0 rows affected (Elapsed: 00:00:00.21)
Records: 0 Duplicates: 0 Warnings: 0
gbase> desc vc1.primarydb.test3;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | YES | | NULL | |
| b | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.00)
gbase> desc vc2.primarydb.test3;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | YES | | NULL | |
| b | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (Elapsed: 00:00:00.00)
5、删除表级镜像关系
gbase> alter table vc1.primarydb.test4 create mirror to vc2 force;
Query OK, 1 row affected (Elapsed: 00:00:00.38)
gbase> select * from gbase.table_distribution where mirror_vc_id is not null and dbname='primarydb' order by vc_id;
+-----------------+-----------+--------+-------------+-------------+------------------+--------------------+-------------+----------------------+---------+--------------+
| index_name | dbName | tbName | isReplicate | hash_column | lmt_storage_size | table_storage_size | is_nocopies | data_distribution_id | vc_id | mirror_vc_id |
+-----------------+-----------+--------+-------------+-------------+------------------+--------------------+-------------+----------------------+---------+--------------+
| primarydb.test4 | primarydb | test4 | NO | NULL | NULL | NULL | NO | 1 | vc00001 | vc00002 |
| primarydb.test4 | primarydb | test4 | NO | NULL | NULL | NULL | NO | 2 | vc00002 | vc00001 |
+-----------------+-----------+--------+-------------+-------------+------------------+--------------------+-------------+----------------------+---------+--------------+
2 rows in set (Elapsed: 00:00:00.00)
gbase> alter table vc1.primarydb.test4 delete mirror ;
Query OK, 1 row affected (Elapsed: 00:00:00.14)
gbase> select * from gbase.table_distribution where mirror_vc_id is not null and dbname='primarydb' order by vc_id;
Empty set (Elapsed: 00:00:00.00)
四、总结
测试对象 | 是否支持同步 |
---|---|
表结构 | √ |
表数据 | √ |
存储过程 | √ |
视图 | × |