【项目实战经验】DataKit迁移MySQL到openGauss（下）
上一篇我们分享了安装、设置、链接、启动等步骤，本篇我们将继续分享迁移、启动~
目录
中断安装，比如 kill 掉java进程（安装失败也要等待300s）
mysql如果是二进制安装的话，我这个版本是没有18这个lib包的
9. 离线迁移
9.1. 迁移插件安装
中断安装，比如 kill 掉java进程（安装失败也要等待300s）
update tb_migration_host_portal_install set install_status=10;下载安装包准备上传
缺少mysqlclient lib包
-
mysql如果是二进制安装的话，我这个版本是没有18这个lib包的
[root@mysqldb lib]# ls -ltrh /usr/local/mysql/lib
total 1001M
-rw-r--r-- 1 mysql mysql 392M Jun 21 2023 libmysqld-debug.a
-rw-r--r-- 1 mysql mysql 43K Jun 21 2023 libmysqlservices.a
-rwxr-xr-x 1 mysql mysql 11M Jun 21 2023 libmysqlclient.so.20.3.30
-rw-r--r-- 1 mysql mysql 26M Jun 21 2023 libmysqlclient.a
-rw-r--r-- 1 mysql mysql 574M Jun 21 2023 libmysqld.a
lrwxrwxrwx 1 mysql mysql 25 Jun 21 2023 libmysqlclient.so.20 -> libmysqlclient.so.20.3.30
lrwxrwxrwx 1 mysql mysql 20 Jun 21 2023 libmysqlclient.so -> libmysqlclient.so.20
drwxr-xr-x 2 mysql mysql 28 Jan 10 13:36 pkgconfig
drwxr-xr-x 4 mysql mysql 28 Jan 10 13:36 mecab
drwxr-xr-x 3 mysql mysql 4.0K Jan 10 13:36 plugin
lrwxrwxrwx 1 root root 25 Jan 10 14:41 libmysqlclient.so.18 -> libmysqlclient.so.20.3.30-
在porta安装日志下面，会有如下报错
[root@mysqldb logs]# cat /ops/portal/error.log
/ops/portal/tools/chameleon/chameleon-5.1.0
install.sh: /ops/portal/tools/chameleon/chameleon-5.1.0/venv/bin/chameleon: /venv/bin/python3.6: bad interpreter: No such file or directory
Traceback (most recent call last):
File "/ops/portal/tools/chameleon/chameleon-5.1.0/venv/lib/python3.6/site-packages/MySQLdb/__init__.py", line 18, in <module>
from . import _mysql
ImportError: libmysqlclient.so.18: cannot open shared object file: No such file or directory
During handling of the above exception, another exception occurred:-
查看到符合当前mysql的版本，通过yum安装即可
Installed:
mysql-community-libs-compat.x86_64 0:5.7.44-1.el7
Complete!
[root@datakit bin]# rpm -ql mysql-community-libs-compat-5.7.44-1.el7.x86_64
/etc/ld.so.conf.d/mysql-x86_64.conf
/usr/lib64/mysql
/usr/lib64/mysql/libmysqlclient.so.18
/usr/lib64/mysql/libmysqlclient.so.18.1.0
/usr/lib64/mysql/libmysqlclient_r.so.18
/usr/lib64/mysql/libmysqlclient_r.so.18.1.0
/usr/share/doc/mysql-community-libs-compat-5.7.44
/usr/share/doc/mysql-community-libs-compat-5.7.44/LICENSE
/usr/share/doc/mysql-community-libs-compat-5.7.44/README-
其他有用命令
# 重新加载lib库
/sbin/ldconfig -v
# 查看位置
locate libmysql
# 手动配置lib库
vi /etc/ld.so.conf.d/mysql.conf
# 查看是否有对应的lib库
ldconfig -p|grep mysql-
ldconfig，此时安装迁移插件应该没有问题
[root@mysqldb lib]# ldconfig -p|grep mysql
libmysqlclient.so.20 (libc6,x86-64) => /usr/local/mysql/lib/libmysqlclient.so.20
libmysqlclient.so.20 (libc6,x86-64) => /usr/lib64/mysql/libmysqlclient.so.20
libmysqlclient.so.18 (libc6,x86-64) => /usr/lib64/mysql/libmysqlclient.so.18
libmysqlclient.so (libc6,x86-64) => /usr/local/mysql/lib/libmysqlclient.so-
如果是在线安装，会遇到403错误，现在要登陆了才能下载
download portal package failed:
--2024-01-09 12:11:24-- https://opengauss.obs.cn-south-1.myhuaweicloud.com/latest/tools/PortalControl-5.1.0.tar.gz
Resolving opengauss.obs.cn-south-1.myhuaweicloud.com (opengauss.obs.cn-south-1.myhuaweicloud.com)... 122.9.127.163, 122.9.127.162
Connecting to opengauss.obs.cn-south-1.myhuaweicloud.com (opengauss.obs.cn-south-1.myhuaweicloud.com)|122.9.127.163|:443... connected.
HTTP request sent, awaiting response... 403 Forbidden
2024-01-09 12:11:25 ERROR 403: Forbidden.-
出现如下提示最终还是能成功安装的：
/ops/portal/tools/chameleon/chameleon-5.1.0
install.sh: /ops/portal/tools/chameleon/chameleon-5.1.0
/venv/bin/chameleon: /venv/bin/python3.6: bad interpreter: No such file or directory安装成功后的截图
主机上有对应的进程
[root@mysqldb alternatives]# jps
19073 QuorumPeerMain
19122 SupportedKafka
4874 Jps
19487 SchemaRegistryMai10. 全量迁移
10.1. 选中主机，启动迁移
10.2. 迁移中
10.3. 迁移结束
10.4. 日志所在目录
[root@mysqldb datacheck]# pwd
/ops/portal/workspace/2/logs/datacheck
[root@mysqldb datacheck]# ls -ltrh
total 36K
-rw-rw-r-- 1 appadm appadm 2.2K Jan 10 15:31 business-source.log
-rw-rw-r-- 1 appadm appadm 2.1K Jan 10 15:31 business-sink.log
-rw-rw-r-- 1 appadm appadm 282 Jan 10 15:31 business-check.log
-rw-rw-r-- 1 appadm appadm 3.1K Jan 10 15:31 source.log
-rw-rw-r-- 1 appadm appadm 3.3K Jan 10 15:31 sink.log
-rw-rw-r-- 1 appadm appadm 422 Jan 10 15:31 kafka-sink.log
-rw-rw-r-- 1 appadm appadm 422 Jan 10 15:31 kafka-source.log
-rw-rw-r-- 1 appadm appadm 3.3K Jan 10 15:31 check.log
-rw-rw-r-- 1 appadm appadm 2.1K Jan 10 15:31 kafka-check.log
[root@mysqldb datacheck]# ls -l /ops/portal/workspace/2/logs/
total 24
drwxrwxr-x 2 appadm appadm 204 Jan 10 15:30 datacheck
drwxrwxr-x 2 appadm appadm 51 Jan 10 15:30 debezium
-rw-rw-r-- 1 appadm appadm 162 Jan 10 15:31 error.log
-rw-rw-r-- 1 appadm appadm 17400 Jan 10 15:31 full_migration.log
[root@mysqldb datacheck]# find /ops -name schema-registry.log
/ops/portal/workspace/2/logs/debezium/schema-registry.log
/ops/portal/tools/debezium/confluent-5.5.1/logs/schema-registry.log11. 增量迁移
11.1. PG里面创建第二个库
create database world2 with dbcompatibility='b';11.2. 创建在线迁移任务
11.3. 启动
-
全量迁移完成并校验成功后进入增量迁移
11.4. 在mysql端进行DDL和DML
mysql 端进行了5个事务
root@localhost 16:08:00 [world]> create table t1(id int primary key,name varchar(32));
Query OK, 0 rows affected (0.01 sec)
root@localhost 16:08:31 [world]> insert into t1 values(1,'zhangsan');
Query OK, 1 row affected (0.01 sec)
root@localhost 16:08:45 [world]> insert into t1 values(2,'22'),(3,'33');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
root@localhost 16:09:00 [world]> create table city_copy like city;
Query OK, 0 rows affected (0.03 sec)
root@localhost 16:09:22 [world]> insert into city_copy select * from city;
Query OK, 4079 rows affected (0.06 sec)
Records: 4079 Duplicates: 0 Warnings: 0上面一直卡住，再起一个的时候报错（内存不足）：
OpenJDK 64-Bit Server VM warning: INFO: os::commit_memory(0x0000000680000000,中间还有一次翻车了
py_opengauss.exceptions.ClientCannotConnectError: could not establish connection to server
CODE: 08001
LOCATION: CLIENT
CONNECTION: [failed]
failures[0]:
socket('192.168.2.3', 5432)
py_opengauss.exceptions.InsufficientPrivilegeError: Please use the original role to connect B-compatibility database first, to load extension dolphin
CODE: 42501
LOCATION: SERVER
CONNECTOR: [IP4] pq://datakit:***@192.168.2.3:5432/world4?[sslmode]=disable
category: None
DRIVER: py_opengauss.driver.pq3.Driver第6次增量
在mysql端进行增删改和DDL
root@localhost 16:48:04 [world]> delete from t1 where id=3;
Query OK, 1 row affected (0.01 sec)
root@localhost 16:48:12 [world]> insert into t1 values(4,44);
Query OK, 1 row affected (0.01 sec)
root@localhost 16:48:24 [world]> update t1 set name=222 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost 16:48:36 [world]> update t1 set name=2223 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
root@localhost 16:49:03 [world]> create table t2 (id int primary key, name char(20));
Query OK, 0 rows affected (0.01 sec)
root@localhost 16:49:41 [world]> insert into t2 select * from t1;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
停止增量
12. 反向迁移
12.1. 在PG端进行增删改
world4=# \c world4
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "world4" as user "omm".
world4=# set search_path=world;
SET
world4=# select * from t2;
id | name
----+----------------------
1 | zhangsan
2 | 2223
4 | 44
(3 rows)
world4=# insert into t2 values(5,55);
INSERT 0 1
world4=# update t2 set name=5555 where id=5;
UPDATE 1
world4=# delete from t2 where id=1;
DELETE 1
12.2. PG端DDL
PG建表无法同步到mysql，但是继续在PG继续进行DML，原有表的数据依然能同步到mysql
orld4=# create table pg_table( id bigint primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pg_table_pkey" for table "pg_table"
CREATE TABLE
world4=# create table t3(id bigint primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t3_pkey" for table "t3"
CREATE TABLE
world4=# show tables;
Tables_in_world
-----------------
city
city_copy
country
countrylanguage
pg_table
t1
t2
t3
(8 rows)
world4=# update t2 set name=55555555 where id=5;
UPDATE 1
world4=# create table t4(id bigint);
CREATE TABLE
world4=# insert into t4 values(1),(2);
INSERT 0 2
world4=# select * from t4;
id
----
1
2
(2 rows)
root@localhost 17:01:41 [world]> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| city_copy |
| country |
| countrylanguage |
| t1 |
| t2 |
+-----------------+
6 rows in set (0.00 sec)
root@localhost 17:03:08 [world]> select * from t2;
+----+----------+
| id | name |
+----+----------+
| 2 | 2223 |
| 4 | 44 |
| 5 | 55555555 |
+----+----------+
3 rows in set (0.00 sec)至此，迁移部分实践分享结束，欢迎大家一起交流学习。
