使用DMHS同步mysql5.7
- 这里是centos7默认安装了mariadb,为避免后续安装冲突,先卸载mariadb
[root@mysql57 tmp]# rpm -qa | grep -i mariadb
- 卸载mariadb
[root@mysql57 tmp]# rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64
setenforce 0 && sed -i 's/SELINUX\=enforcing/SELINUX\=disabled/' /etc/selinux/config
[root@mysql57 tmp]# systemctl stop firewalld && systemctl disable firewalld
[root@mysql57 tmp]# yum -y install autoconf libaio net-tools numactl
-
- RPM安装MYSQL
- 解压安装介质
[root@mysql57 tmp]# tar -xvf mysql-5.7.37-1.el7.x86_64.rpm-bundle.tar
- RPM安装, 安装顺序common→libs→client→server
[root@mysql57 tmp]# rpm -ivh mysql-community-common-5.7.37-1.el7.x86_64.rpm mysql-community-libs-5.7.37-1.el7.x86_64.rpm mysql-community-client-5.7.37-1.el7.x86_64.rpm mysql-community-server-5.7.37-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.37-1.el7.x86_64.rpm
- 启动MYSQL
安装完mysql-community-server-5.7.37-1.el7.x86_64.rpm后,会生成mysql配置文件/etc/my.cnf
[root@mysql57 tmp]# systemctl start mysqld
- 搜索临时密码
[root@mysql57 tmp]# grep -i password /var/log/mysqld.log
- 通过临时密码登录MYSQL
[root@mysql57 tmp]# mysql -uroot -p
- 修改初始密码
mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'Root123456!';
Query OK, 0 rows affected (0.00 sec)
- 创建root@'%'用户并授权
mysql> create user 'root'@'%' identified with mysql_native_password by 'Root123456!';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to 'root'@'%';
Query OK, 0 rows affected (0.00 sec)
- 创建userdmhs@'%'用户并授权
mysql> create user 'userdmhs'@'%' identified with mysql_native_password by 'Root123456!';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to 'userdmhs'@'%';
Query OK, 0 rows affected (0.00 sec)
- 刷新权限
mysql> flush privileges;
- 创建数据库datadmhs 源库字符集utf8mb3 目的库utf8mb4
- mysql> create database datadmhs charset utf8mb3;
Query OK, 1 row affected (0.01 sec)
- mysql> create database datadmhs charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
MYSQL5.7默认未开启binlog,需设置相关binlog相关参数,并重启数据库
- vi /etc/my.cnf 【mysqld】部分增加3个参数,开启binlog
[mysqld]
server_id=1 #源库,目标库 server_id最好不要一样
log-bin=mysql-binlog
binlog_format=row #row模式
- 增加本机免密码登录
[mysql]
user=root
password=Root123456!
- my.cnf
[root@mysql57 tmp]# more /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysql]
user=root
password=Root123456!
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server_id=1
log-bin=mysql-binlog
binlog_format=row
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
- 重启MYSQL,令参数生效
[root@mysql57 tmp]# systemctl restart mysqld
- 部署DMHS相关环境
- 安装编译器
yum -y install gcc gcc-c++
- 解压包
tar -zxvf unixODBC-2.3.9.tar.gz
- 设置语言环境
export LANG=en_US.UTF-8
- 安装
cd unixODBC-2.3.9
./configure --enable-drivers=no --with-iconv-char-enc=GB18030 --enable-gui=no --enable-iconv=yes
make && make install
- 查看配置文件
[root@mysql57 unixODBC-2.3.9]# ls -lsa /usr/local/etc/odbc*
0 -rw-r--r--. 1 root root 0 Aug 1 11:20 /usr/local/etc/odbc.ini
0 -rw-r--r--. 1 root root 0 Aug 1 11:20 /usr/local/etc/odbcinst.ini
- 查看ODBC
[root@mysql57 mysql-connector-odbc-5.3.13-linux-glibc2.12-x86-64bit]# odbcinst -j
unixODBC 2.3.9
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
-
-
- 安装ODBC驱动
-
- tar -zxvf mysql-connector-odbc-5.3.13-linux-glibc2.12-x86-64bit.tar.gz
- cd mysql-connector-odbc-5.3.13-linux-glibc12-x86-64bit
- cp lib/* /usr/local/lib/
- cp bin/* /usr/local/bin/
- cp /usr/local/lib/libodbc.so.2.0.0 /usr/lib64/libodbc.so.2
- cp /usr/local/lib/libodbcinst.so.2.0.0 /usr/lib64/libodbcinst.so.2
- myodbc-installer -a -d -n "MySQ LODBC 5.3 Driver" -t "Driver=/usr/local/lib/libmyodbc5w.so"
- myodbc-installer -a -d -n "MySQL ODBC 5.3 Driver" -t "Driver=/usr/local/lib/libmyodbc5a.so"
- 验证安装情况
[root@mysql57 tmp]# myodbc-installer -d -l
[root@mysql57 tmp]# myodbc-installer -d -l
MySQL ODBC 5.3 Driver
- 配置odbc.ini
cat >> /usr/local/etc/odbc.ini << EOF
[MYSQL]
Description = MYSQL ODBC DSN
Driver = MySQL ODBC 5.3 Driver
SERVER = 192.168.218.208
PORT = 3306
UID = userdmhs
PWD = Root123456!
EOF
- 测试连接
[root@mysql57 tmp]# isql MYSQL
- 设置文件权限
chmod 775 dmhs_V4.2.90_mysql8_rev110096_rh6_64_veri_20220617.bin
- 命令行安装DMHS
./dmhs_V4.2.90_mysql8_rev110096_rh6_64_veri_20220617.bin -i
[root@mysql57 tmp]# ./dmhs_V4.2.90_mysql8_rev110096_rh6_64_veri_20220617.bin -i
Extract install files.........
1.英文(English)
2.简体中文(简体中文)
请选择安装语言[2.简体中文(简体中文)]:2
/tmp/DMHSInstall/install.log
1.免费试用达梦数据实时同步
2.使用已申请的Key文件
验证许可证文件[1.免费试用达梦数据实时同步]:2
Key文件路径:/tmp/dmhs9F01077495.key
Key文件限制信息如下:
有效日期:2023-06-25
用户名称:达梦公司产品试用
服务器颁布类型:试用版
发布类型:企业版
Key类型:企业版
授权用户数:无限制
并发连接数:无限制
1.精简版
2.完整版(web客户端)
3.自定义
安装类型[1.精简版]:1
1.实时同步软件服务器
2.远程部署工具
3.实时同步软件配置助手
4.手册
所需磁盘空间:559 MB
安装目录: [/opt/dmhs]
1.统一部署
2.现在初始化
是否初始化达梦数据实时同步系统[1.统一部署]:1
正在安装
default start ... default finished.
server start ... server finished.
hs_agent start ... hs_agent finished.
hsca start ... hsca finished.
doc start ... doc finished.
postinstall start ... postinstall finished.
正在创建快捷方式
安装成功
远程部署工具配置
远程部署工具名称[HsAgent]:
主机Ip(外网)[192.168.218.208](192.168.218.208):
远程部署工具管理端口[5456](1000-65535):
内置数据库轮询间隔[3](1-60):
内置数据库IP[]:192.168.218.208
内置数据库端口[15236](1000-65535):
内置数据库用户名[SYSDBA]:
内置数据库密码[SYSDBA]:
服务脚本环境变量设置
依赖库路径
提示:此配置项供用户配置源或目的数据库依赖库路径和odbc依赖库路径, 多个路径以":"隔开(例:/opt/dmdbms/bin:/usr/local/lib),此配置项会添加到服务脚本的NEED_LIB_PATH的变量值中。
请配置依赖库路径:/usr/local/mysql/bin:/usr/local/lib:/opt/dmhs/bin
远程控制服务
1.自动
2.手动
启动方式:[2.手动]
正在创建远程控制服务
达梦数据实时同步V4.0安装完成
更多安装信息,请查看安装日志文件:
/opt/dmhs/log/install.log
- 查看执行模块链接是否正常验证
[root@mysql57 tmp]# cd /opt/dmhs/bin
[root@mysql57 bin]# ldd libcpt_mysql.so
发现有libodbc.so.1 => not found,需处理
- find / -name libodbc.so*
- 建立软链接
ln -s /usr/local/lib/libodbc.so.2.0.0 /usr/local/lib/libodbc.so.1
- 配置动态库环境变量, 加入/opt/dmhs/bin:/usr/local/lib/
[root@mysql57 bin]# more ~/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
export DMHS_HOME=/opt/dmhs
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/dmhs/bin:/usr/local/lib/
- 环境变量生效
[root@mysql57 bin]# source ~/.bash_profile
- 查看执行模块链接情况
[root@mysql57 bin]# ldd libcpt_mysql.so
- 在dmhs的bin目录下创建dmhs.hs配置文件
<?xml version="1.0" encoding="GB2312"?>
<dmhs>
<base>
<lang>en</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>
<driver>MySQL ODBC 5.3 Driver</driver>
<char_code>PG_UTF8</char_code>
<db_server>192.168.218.208</db_server>
<db_port>3306</db_port>
<db_name>datadmhs</db_name>
<db_user>userdmhs</db_user>
<db_pwd>Root123456!</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.218.209</ip>
<mgr_port>6345</mgr_port>
<data_port>6346</data_port>
<filter>
<enable>
<item>datadmhs.*</item>
</enable>
</filter>
</send>
</cpt>
</dmhs>
[root@mysql572 bin]# more dmhs.hs
<?xml version="1.0" encoding="GB2312"?>
<dmhs>
<base>
<lang>en</lang>
<mgr_port>6345</mgr_port>
<ckpt_interval>45</ckpt_interval>
<siteid>2</siteid>
<version>2.0</version>
</base>
<exec>
<recv>
<data_port>6346</data_port>
</recv>
<db_type>mysql</db_type>
<driver>MySQL ODBC 5.3 Driver</driver>
<char_code>PG_UTF8</char_code>
<db_server>127.0.0.1</db_server>
<db_port>3306</db_port>
<db_name>datadmhs</db_name>
<db_user>userdmhs</db_user>
<db_pwd>Root123456!</db_pwd>
</exec>
</dmhs>
mysql> use datadmhs
Database changed
mysql> create table tab_test1(id int,col_a varchar(50));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into tab_test1 values(1,'a');
Query OK, 1 row affected (0.01 sec)
mysql> insert into tab_test1 values(2,'张三');
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
-
-
- 启动dmhs_server(源端与目的端)
-
[root@mysql572 bin]# ./dmhs_server
- 源端启动dmhs_server
- 目的端启动dmhs_server
目的端运行 DMHS 软件工具 dmhs_console,启动执行器
[root@mysql572 ~]# cd /opt/dmhs/tool/
[root@mysql572 tool]# ls
configuration dmhs_console dmhs_hsca dropins hsca.bmp hsservice log4j.xml plugins
[root@mysql572 tool]# ./dmhs_console
DMHS console tool: V4.1.4-Build(2022.06.15-110096trunc)_64_2206
Copyright (c) 2020, DMHS. All rights reserved.
Type ? or "help" for help, type "quit" to quit console.
Connected to DMHS: 127.0.0.1:6345
execute success
Dameng HS Server V4.1.4-Build(2022.06.15-110096trunc)_64_2206
DMHS> start exec
execute success
DMHS>
源端运行 DMHS 软件工具 dmhs_console,连接dmhs服务,执行以下命令:
COPY 0 "sch.name='datadmhs'" CLEAR|DICT|CREATE|INDEX|LSN|COMMENT|INSERT|THREAD|8
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| datadmhs |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.07 sec)
mysql> use datadmhs
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-----------------------+
| Tables_in_datadmhs |
+-----------------------+
| DMHS_CHECKPOINT_TABLE |
| DMHS_DPC_SYNC_TABLE |
| DMHS_DTYPE_MAP |
| DMHS_ERROR_TABLE |
| DMHS_ERROR_TSK_TABLE |
| DMHS_TABLE_SEQID |
| DMHS_TRXID_TABLE |
| tab_test1 |
+-----------------------+
8 rows in set (0.00 sec)
mysql> select * from tab_test1;
+------+--------+
| id | col_a |
+------+--------+
| 1 | a |
| 2 | 张三 |
+------+--------+
2 rows in set (0.00 sec)
设置日志捕获模块起始 LSN
DMHS> clear exec lsn
DMHS> start cpt
[root@mysql57 bin]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.37-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| datadmhs |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.06 sec)
mysql> use datadmhs -A
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+--------------------+
| Tables_in_datadmhs |
+--------------------+
| tab_test1 |
+--------------------+
1 row in set (0.00 sec)
mysql> create table tab_test2(id int,col_aa varchar(20));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into tab_test2 values(10,'qwer');
Query OK, 1 row affected (0.01 sec)
mysql> insert into tab_test2 values(99,'李四');
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
创建tab_test2表,写入数据
查看目的端数据
-
-
- dmhs监控
-
- CPT:日志捕获模块,日志发送模块
- EXEC:日志执行模块,日志接收模块
日志中包含【INFO】【WARN】【ERROR】【FATAL】四类信息
tail -330f dmhs_202208.log |grep ERROR