使用DMHS同步mysql5.7

使用DMHS同步mysql5.7. 1

1.      安装MYSQL5.7.37. 1

1.1        卸载mariadb. 1

1.2        关闭selinux. 2

1.3        关闭防火墙... 2

1.4        安装相关的RPM包... 2

1.5        RPM安装MYSQL. 2

1.6        配置MYSQL. 3

1.7        开启binlog. 4

2       部署DMHS相关环境... 6

2.1        安装unixODBC. 6

2.2        安装ODBC驱动... 7

2.3        配置odbc.ini 8

2.4        安装DMHS. 8

2.5        源端配置dmhs.hs. 11

2.6        目标端配置dmhs.hs. 12

2.7        源端创建测试数据... 13

2.8        启动dmhs_server(源端与目的端) 13

2.9        目的端启动执行器... 14

2.10          装载数据... 14

2.11          查看数据装载情况... 15

2.12          源端设置日志起始LSN.. 16

2.13          启动源端日志捕获模块... 17

2.14          测试同步... 17

2.15      dmhs监控... 20

2.16          日志监控... 21

1. 安装MYSQL5.7.37
   1. 卸载mariadb

1. 这里是centos7默认安装了mariadb,为避免后续安装冲突，先卸载mariadb

[root@mysql57 tmp]# rpm -qa | grep -i mariadb

1. 卸载mariadb

[root@mysql57 tmp]# rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64

1. 1. 关闭selinux

setenforce  0  && sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config

1. 1. 关闭防火墙

[root@mysql57 tmp]# systemctl stop firewalld && systemctl disable firewalld

1. 1. 安装相关的RPM包

[root@mysql57 tmp]# yum -y install autoconf  libaio net-tools numactl

1. 1. RPM安装MYSQL
2. 解压安装介质

[root@mysql57 tmp]# tar -xvf mysql-5.7.37-1.el7.x86_64.rpm-bundle.tar

1. 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

1. 1. 配置MYSQL
2. 启动MYSQL

安装完mysql-community-server-5.7.37-1.el7.x86_64.rpm后,会生成mysql配置文件/etc/my.cnf

[root@mysql57 tmp]# systemctl start mysqld

1. 搜索临时密码

[root@mysql57 tmp]# grep -i password /var/log/mysqld.log

1. 通过临时密码登录MYSQL

[root@mysql57 tmp]# mysql -uroot -p

1. 修改初始密码

mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'Root123456!';

Query OK, 0 rows affected (0.00 sec)

1. 创建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)

1. 创建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)

1. 刷新权限

mysql> flush privileges;

1. 创建数据库datadmhs 源库字符集utf8mb3 目的库utf8mb4

1. mysql> create database datadmhs charset utf8mb3;

Query OK, 1 row affected (0.01 sec)

1. mysql> create database datadmhs charset utf8mb4;

Query OK, 1 row affected (0.00 sec)

1. 1. 开启binlog

MYSQL5.7默认未开启binlog,需设置相关binlog相关参数,并重启数据库

1. vi /etc/my.cnf  【mysqld】部分增加3个参数,开启binlog

[mysqld]

server_id=1                        #源库,目标库 server_id最好不要一样

log-bin=mysql-binlog       

binlog_format=row          #row模式

1. 增加本机免密码登录

[mysql]

user=root

password=Root123456!

1. 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

1. 重启MYSQL,令参数生效

[root@mysql57 tmp]# systemctl restart mysqld

1. 部署DMHS相关环境
   1. 1. 安装unixODBC

1. 安装编译器

yum -y install gcc gcc-c++

1. 解压包

tar -zxvf unixODBC-2.3.9.tar.gz

1. 设置语言环境

export LANG=en_US.UTF-8

1. 安装

cd unixODBC-2.3.9

./configure --enable-drivers=no --with-iconv-char-enc=GB18030 --enable-gui=no --enable-iconv=yes

make && make install

1. 查看配置文件

[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

1. 查看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

1. 1. 1. 安装ODBC驱动
2. tar -zxvf mysql-connector-odbc-5.3.13-linux-glibc2.12-x86-64bit.tar.gz
3. cd mysql-connector-odbc-5.3.13-linux-glibc12-x86-64bit
4. cp lib/* /usr/local/lib/
5. cp bin/* /usr/local/bin/
6. cp /usr/local/lib/libodbc.so.2.0.0        /usr/lib64/libodbc.so.2
7. cp /usr/local/lib/libodbcinst.so.2.0.0  /usr/lib64/libodbcinst.so.2
8. myodbc-installer -a -d -n "MySQ LODBC 5.3 Driver" -t "Driver=/usr/local/lib/libmyodbc5w.so"
9. myodbc-installer -a -d -n "MySQL ODBC 5.3 Driver" -t "Driver=/usr/local/lib/libmyodbc5a.so"
10. 验证安装情况

[root@mysql57 tmp]# myodbc-installer -d -l

[root@mysql57 tmp]# myodbc-installer -d -l

MySQL ODBC 5.3 Driver

1. 1. 1. 配置odbc.ini
2. 配置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

1. 测试连接

[root@mysql57 tmp]# isql MYSQL

1. 1. 1. 安装DMHS
2. 设置文件权限

chmod 775 dmhs_V4.2.90_mysql8_rev110096_rh6_64_veri_20220617.bin

1. 命令行安装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

1. 查看执行模块链接是否正常验证

[root@mysql57 tmp]# cd /opt/dmhs/bin

[root@mysql57 bin]# ldd libcpt_mysql.so

发现有libodbc.so.1 => not found,需处理

1. find / -name libodbc.so*

1. 建立软链接

ln -s /usr/local/lib/libodbc.so.2.0.0 /usr/local/lib/libodbc.so.1

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/

1. 环境变量生效

[root@mysql57 bin]# source ~/.bash_profile

1. 查看执行模块链接情况

[root@mysql57 bin]# ldd libcpt_mysql.so

1. 1. 1. 源端配置dmhs.hs
2. 在dmhs的bin目录下创建dmhs.hs配置文件

        

                   en

                   5345                 

                   45

                   1

                   2.0

        

        

                   mysql

                   MySQL ODBC 5.3 Driver

                   PG_UTF8

                   192.168.218.208  

                   3306

                   datadmhs

                   userdmhs             

                   Root123456!

                   op:obj

                  

                            600

                            0

                           

/var/lib/mysql

                  

                  

                            192.168.218.209               

                            6345

                            6346

                           

                                    

                                               datadmhs.*

                                    

                           

                  

        

1. 1. 1. 目标端配置dmhs.hs

[root@mysql572 bin]# more dmhs.hs

        

                   en

                   6345                 

                   45

                   2

                   2.0

        

        

                  

                            6346

                  

                   mysql

                   MySQL ODBC 5.3 Driver

                   PG_UTF8

                   127.0.0.1

                   3306

                   datadmhs

                   userdmhs

                   Root123456!

        

1. 1. 1. 源端创建测试数据

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)

1. 1. 1. 启动dmhs_server(源端与目的端)

[root@mysql572 bin]# ./dmhs_server

1. 源端启动dmhs_server

1. 目的端启动dmhs_server

1. 1. 1. 目的端启动执行器

目的端运行 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>

1. 1. 1. 装载数据

源端运行 DMHS 软件工具 dmhs_console,连接dmhs服务,执行以下命令:

COPY 0 "sch.name='datadmhs'" CLEAR|DICT|CREATE|INDEX|LSN|COMMENT|INSERT|THREAD|8

1. 1. 1. 查看数据装载情况

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)

1. 1. 1. 源端设置日志起始LSN

设置日志捕获模块起始 LSN

DMHS> clear exec lsn

1. 1. 1. 启动源端日志捕获模块

DMHS> start cpt

1. 1. 1. 测试同步

[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表,写入数据

查看目的端数据

1. 1. 1. dmhs监控
2. CPT:日志捕获模块，日志发送模块

1. EXEC:日志执行模块,日志接收模块

1. 1. 1. 日志监控

日志中包含【INFO】【WARN】【ERROR】【FATAL】四类信息

tail -330f dmhs_202208.log |grep ERROR

达梦数据库 - 新一代大型通用关系型数据库 | 达梦在线服务平台