MYSQL 环境准备
查看mysql 字符集
查看字符集
mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
安装mysql odbc驱动
使用源码安装unix odbc驱动
省略安装部署，unixodbc安装检测如下
[root@DM02 unixODBC-2.3.0]
unixODBC 2.3.0
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
采取rmp包安装mysql-connector-odbc
[root@DM02 mysql]
warning: mysql-connector-odbc-5.3.13-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...
1:mysql-connector-odbc
Success: Usage count is 1
Success: Usage count is 1
创建dmhs 同步用户 并授权
mysql> create user hstest@"%" identified by "dmhs";
Query OK, 0 rows affected (0.00 sec)
mysql> grant select on *.* to hstest@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave,replication client on *.* to hstest@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> grant select,create,lock tables on *.* to hstest@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT Index, Insert, Select ON `hstest`.* TO `hstest`@`%`;
Query OK, 0 rows affected (0.00 sec)
开启 binlog日志
[root@DM02 usr]
Shutting down MySQL.... [ OK ]
Starting MySQL.... [ OK ]
[root@DM02 usr]
[mysqld]
character_set_server=utf8
collation-server=utf8_general_ci
log-bin=mysql-bin
binlog_format=row
安装mysql 端dmhs
dmhs安装包需要符合mysql 版本、操作系统、CPU
配置ODBC
[root@DM02 unixODBC-2.3.0]
[mysql]
Driver=MySQL ODBC 5.1 Driver
DATABASE=mysql
DESCRIPTION=MYSQL ODBC 5.1 DRIVER
SERVER=127.0.0.1
UID=root
PASSWORD=njnu123456
[root@DM02 unixODBC-2.3.0]
[MySQL ODBC 5.3 Unicode Driver]
Driver=/usr/lib64/libmyodbc5w.so
UsageCount=1
[MySQL ODBC 5.1 Driver]
Driver=/usr/lib64/libmyodbc5w.so
UsageCount=1
测试odbc
[dmdba@DM02 bin]$ isql -v mysql
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
配置dmhs.hs文件
<?xml version="1.0" encoding="GB2312"?>
<dmhs>
<base>
<lang>ch</lang>
<mgr_port>5345</mgr_port>
<ckpt_interval>45</ckpt_interval>
<siteid>1</siteid>
<version>2.0</version>
</base>
<cpt>
<db_type>mysql</db_type>
<db_server>127.0.0.1</db_server>
<db_port>3306</db_port>
<db_name>hstest</db_name>
<db_user>hstest</db_user>
<db_pwd>dmhs</db_pwd>
<ddl_mask>op:obj</ddl_mask>
<arch>
<clear_interval>600</clear_interval>
<clear_flag>0</clear_flag>
<dir>/var/lib/mysql</dir>
</arch>
<send>
<ip>192.168.142.237</ip>
<mgr_port>5345</mgr_port>
<data_port>5346</data_port>
<filter>
<enable>
<item>hstest.*</item>
</enable>
</filter>
<map>
<item>hstest.*==HSTEST.*</item>
</map>
</send>
</cpt>
<exec>
<recv>
<data_port>5346</data_port>
</recv>
<db_type>mysql</db_type>
<db_server>127.0.0.1</db_server>
<db_port>3306</db_port>
<db_name>hstest</db_name>
<db_user>hstest</db_user>
<db_pwd>dmhs</db_pwd>
</exec>
</dmhs>
达梦端环境配置
初始化实例
dminit path=/dmdata/ DB_NAME=MYSQL_HS INSTANCE_NAME=MYSQL_HS PAGE_SIZE=32 EXTENT_SIZE=32 CHARSET=1 LENGTH_IN_CHAR=1
启动实例并开启归档和逻辑附加日志
alter DATABASE MOUNT;
alter database archivelog;
alter database add archivelog 'DEST=/dmdata/dmarch, TYPE=LOCAL, FILE_SIZE=2048, SPACE_LIMIT=102400';
alter DATABASE OPEN;
SP_SET_PARA_VALUE(1,'RLOG_IGNORE_TABLE_SET', 1);
安装dm 端dmhs
dmhs安装包需要符合dm版本、操作系统、CPU
配置dmhs.hs文件
<?xml version="1.0" encoding="GB2312"?>
<dmhs>
<base>
<siteid>2</siteid>
<mgr_port>5345</mgr_port>
<chk_interval>3</chk_interval>
<ckpt_interval>60</ckpt_interval>
<lang>ch</lang>
<version>2.0</version>
</base>
<cpt>
<db_type>DM8</db_type>
<db_server>dm</db_server>
<db_user>SYSDBA</db_user>
<db_pwd>SYSDBA</db_pwd>
<db_port>5236</db_port>
<idle_time>300</idle_time>
<ddl_mask>OBJ:OP</ddl_mask>
<dict_dir/>
<arch>
<clear_interval>43200</clear_interval>
<clear_flag>0</clear_flag>
</arch>
<send>
<ip>192.168.142.236</ip>
<mgr_port>5345</mgr_port>
<data_port>5346</data_port>
<net_pack_size>256</net_pack_size>
<net_turns>0</net_turns>
<crc_check>0</crc_check>
<trigger>1</trigger>
<constraint>1</constraint>
<identity>0</identity>
<filter>
<enable>
<item>HSTEST.*</item>
</enable>
<disable/>
</filter>
<map>
<item>HSTEST.*==hstest.*</item>
</map>
</send>
</cpt>
<exec>
<recv>
<data_port>5346</data_port>
</recv>
<db_type>DM8</db_type>
<db_server>192.168.142.237</db_server>
<db_user>SYSDBA</db_user>
<db_pwd>SYSDBA</db_pwd>
<db_port>5236</db_port>
<level>0</level>
<exec_mode>1</exec_mode>
<exec_thr>4</exec_thr>
<exec_sql>1024</exec_sql>
<exec_trx>5000</exec_trx>
<exec_rows>250</exec_rows>
<case_sensitive>1</case_sensitive>
<toggle_case>0</toggle_case>
<exec_policy>0</exec_policy>
<commit_policy>1</commit_policy>
<enable_merge>1</enable_merge>
</exec>
</dmhs>
启动dmhs
启动mysql ->单向同步
启动dm exec
MGR[INFO]: 站点 1 的主机节点登记为 192.168.142.236, unique_sign: 1634727541993
EXE[INFO]: site id:1 ignore_trigger:FALSE ignore_constraint:FALSE ignore_identity:FALSE host_db_type:MYSQL version:11 char_code:PG_GB18030
REV[INFO]: 针对192.168.142.236:1站点的日志接收线程成功创建, 开始接收日志...
REV[INFO]: 192.168.142.236:1站点要求重置执行模块
EXE[INFO]: 需要重置站点: 1
EXE[INFO]: 正在等待站点:1 上异步消息投递完成...
EXE[INFO]: 正在保存最小的事务执行号......
REV[INFO]: MAP:192.168.142.236:1 hstest.*==HSTEST.*
REV[INFO]: 192.168.142.236:1 站点获取当前执行模块中的最小LSN...
EXE[INFO]: 正在等待站点:1 上异步消息投递完成...
REV[INFO]: 192.168.142.236:1站点获取当前执行模块中的最小LSN: 0
EXE[INFO]: 正在清空站点 1 未提交的缓存消息
EXE[INFO]: DDL:SITEID:1 SEQID:0 TRXID:2 schema.lock DROP
EXE[ERROR]: SITEID:1 SEQID:0 TRXID:0 ROWID:NULL SQL_ERROR = SQL:--dmhs ddl, siteid:1, level:2, mask:1
DROP TABLE "HSTEST"."test01" CASCADE
EXE[WARN]: SITEID:1 SEQID:0 TRXID:0 STATE:42S02 CODE: -2106 ROWID:NULL ERR:Error in line: 2
Invalid table or view name [test01] SQL:--dmhs ddl, siteid:1, level:2, mask:1
DROP TABLE "HSTEST"."test01" CASCADE
EXE[INFO]: DDL:SITEID:1 SEQID:0 TRXID:4 schema.lock CREATE
EXE[WARN]: CHECKPOINT WAIT: SITEID:1 TRXID:0 schema.lock
EXE[INFO]: DDL:SITEID:1 SEQID:0 TRXID:12 schema.lock ALTER
EXE[INFO]: DDL:SITEID:1 SEQID:0 TRXID:14 schema.lock DROP
EXE[INFO]: DDL:SITEID:1 SEQID:0 TRXID:16 schema.lock CREATE
EXE[INFO]: 正在等待站点:1 上已经提交的 5 个事务执行完成...
EXE[INFO]: DDL: SITEID:1 SEQID:0 TRXID:4 SQL:--dmhs ddl, siteid:1, level:2, mask:1
CREATE TABLE "HSTEST"."test01"("id" BIGINT NOT NULL,"name" VARCHAR(400) NULL)
EXE[INFO]: 设置表.HSTEST.test01的起始LSN为: 0 LFS: 0
EXE[WARN]: 设置表.HSTEST.test01的起始LSN为: 8589936030 LFS: 0 装载行数:0
EXE[INFO]: 正在等待站点:1 上已经提交的事务执行完成百分比：40%
EXE[INFO]: 正在等待站点:1 上异步消息投递完成...
EXE[INFO]: 正在等待站点:1 上已经提交的事务执行完成百分比：60%
EXE[INFO]: DDL: SITEID:1 SEQID:0 TRXID:12 SQL:--dmhs ddl, siteid:1, level:2, mask:1
ALTER TABLE "HSTEST"."test01" ADD CONSTRAINT "test01_key_test01" PRIMARY KEY("id")
EXE[ERROR]: SITEID:1 SEQID:0 TRXID:0 ROWID:NULL SQL_ERROR = SQL:--dmhs ddl, siteid:1, level:2, mask:1
DROP TABLE "HSTEST"."staff_info" CASCADE
EXE[WARN]: SITEID:1 SEQID:0 TRXID:0 STATE:42S02 CODE: -2106 ROWID:NULL ERR:Error in line: 2
Invalid table or view name [staff_info] SQL:--dmhs ddl, siteid:1, level:2, mask:1
DROP TABLE "HSTEST"."staff_info" CASCADE
EXE[INFO]: DDL: SITEID:1 SEQID:0 TRXID:16 SQL:--dmhs ddl, siteid:1, level:2, mask:1
CREATE TABLE "HSTEST"."staff_info"("staff_id" VARCHAR(1020) NULL,"staff_name" VARCHAR(1020) NULL,"position" VARCHAR(1020) NULL,"work_type" VARCHAR(1020) NULL,"group_name" VARCHAR(1020) NULL)
EXE[INFO]: 设置表.HSTEST.staff_info的起始LSN为: 0 LFS: 0
EXE[WARN]: 设置表.HSTEST.staff_info的起始LSN为: 8589936030 LFS: 0 装载行数:13
EXE[INFO]: 正在等待站点:1 上已经提交的事务执行完成百分比：100%
EXE[INFO]: 正在等待站点:1 上事务提交完成...
EXE[INFO]: 正在清空站点 1 未提交的缓存消息
REV[INFO]: 针对192.168.142.236:1站点的日志接收线程已经退出...
MGR[INFO]: 来自 192.168.142.236 的连接已经断开!
装载
copy 0 "sch.name in ('hstest')" DICT|DROP|CREATE|INSERT|INDEX|COMMENT
启动mysql端cpt
[dmdba@DM02 bin]$ sudo ./dmhs_server
MGR[INFO]: DMHS start up, current version: V4.1.48-Build(2021.09.09-102088trunc)_D64 (The beta)(Enterprise Edition)
MGR[WARN]: License will expire on 2021-12-09
MGR[INFO]: 成功加载配置文件,站点号:1, 管理端口:5345, 轮询间隔:3
MGR[INFO]: 管理 服务正在监听管理端口:5345
start cpt
MGR[INFO]: 正在初始化分析模块...
PUB[INFO]: set enable_directio = 0
MGR[INFO]: 正在加载MYSQL日志分析模块...
CPT[INFO]: MYSQL_V3.1.2_D64
CPT[INFO]: MySQL lower_case_table_names参数值为：0
MGR[INFO]: 正在设置目的端192.168.142.237:5345网匝模式:DISABLE
MGR[INFO]: 发送命令到站点192.168.142.237:5345成功
MGR[INFO]: 正在获取站点192.168.142.237:5345上的最小LSN...
MGR[INFO]: 成功获取站点192.168.142.237:5345上的最小LSN: 0...
SND[INFO]: 正在加载过滤信息:
SND[INFO]: ENABLE: hstest.*
SND[INFO]: 正在加载列映射信息:
SND[INFO]: 分析模块192.168.142.237:5346的日志发送线程已经被创建
SND[INFO]: 分析模块1正在连接站点192.168.142.237:5346...
CPT[INFO]: 起始LSN值:0 定位在日志文件: /var/lib/mysql/mysql-bin.000001(pos:0)
CPT[INFO]: 切换到日志文件: /var/lib/mysql/mysql-bin.000001
CPT[INFO]: 切换到日志文件: /var/lib/mysql/mysql-bin.000002
MGR[INFO]: 日志分析启动成功
ERR:x
命令执行完成
CPT[WARN]: 不支持的DDL：grant select on *.* to hstest@'%'
SND[INFO]: 分析模块1正在重置站点192.168.142.237:5346的执行模块...
ERR:
CPT[WARN]: 不支持的DDL：grant replication slave,replication client on *.* to hstest@'%'
ERR:?
CPT[WARN]: 不支持的DDL：flush privileges
ERR:?
CPT[WARN]: 不支持的DDL：grant select,create,lock tables on *.* to hstest@'%'
SND[INFO]: 分析模块1正在发送映射规则...
SND[INFO]: hstest.*==HSTEST.*...
SND[INFO]: 分析模块1正在获取站点192.168.142.237:5346上的最小LSN...
SND[INFO]: 分析模块1成功获取LSN:0 LFS:0
ERR:x
CPT[WARN]: 不支持的DDL：flush privileges
cpt
启动 dm-> mysql 同步
启动mysql端exec
dm端装载
copy 0 "sch.name in ('HSTEST')" DICT
启动dm端cpt
测试
- 同步速度
- 中文是否乱码
更多资讯请上达梦技术社区了解： https://eco.dameng.com
|