南大通用数据库-Gbase-8a-学习-31-VC间镜像同步
admin
2024-05-05 15:28:00
0次
一、环境
| 名称 | 值 |
|---|---|
| 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 existgbase> 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) begindeclare tempval int;set tempval = 1;set autocommit = off;label: loopinsert into test values(tempval);if tempval >= num then leave label;elseset 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: 0gbase> 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)四、总结
| 测试对象 | 是否支持同步 |
|---|---|
| 表结构 | √ |
| 表数据 | √ |
| 存储过程 | √ |
| 视图 | × |
相关内容
热门资讯
打发时间一元一分红中麻将跑得快...
认准微——as099055或as022055——客服扣675434346免押%D%A
（盘点十款）1元1分正规麻将群...
正规广东红中癞子麻将，15张跑得快，一元一分群，24小时不熄火
哪里有熊猫一元一分广东麻将上下...
＋薇:mj08522或hz05832游戏类型：单挑，多人，亲友圈模式、秒上下，所有用户都是微信实名制...
“发慌！””清友会管清友“是陷...
网恋有风险，恋爱需谨慎！“他们”通过网页，社交软件，等形式发布有色广告，诱骗点击链接下载APP，随即...