【项目实战经验】DataKit迁移MySQL到openGauss（下）
上一篇我们分享了安装、设置、链接、启动等步骤，本篇我们将继续分享迁移、启动~
目录
9. 离线迁移
9.1. 迁移插件安装
中断安装，比如 kill 掉java进程（安装失败也要等待300s）
下载安装包准备上传
缺少mysqlclient lib包
mysql如果是二进制安装的话，我这个版本是没有18这个lib包的
安装成功后的截图
主机上有对应的进程
10. 全量迁移
10.1. 选中主机，启动迁移
10.2. 迁移中
10.3. 迁移结束
10.4. 日志所在目录
11. 增量迁移
11.1. PG里面创建第二个库
11.2. 创建在线迁移任务
11.3. 启动
11.4. 在mysql端进行DDL和DML
mysql 端进行了5个事务
第6次增量
停止增量
12. 反向迁移
12.1. 在PG端进行增删改
12.2. PG端DDL
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 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 SchemaRegistryMai
10. 全量迁移
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.log
11. 增量迁移
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)
至此，迁移部分实践分享结束，欢迎大家一起交流学习。
-
-
-
-
-
-
-
免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理! 部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理! 图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们，邮箱：ciyunidc@ciyunshuju.com。本站只作为美观性配图使用,无任何非法侵犯第三方意图,一切解释权归图片著作权方,本站不承担任何责任。如有恶意碰瓷者,必当奉陪到底严惩不贷!
