【项目实战经验】DataKit迁移MySQL到openGauss（下）_网站优化分享

您的位置：上海毫米网络优化公司 > 网站优化分享 >

9. 离线迁移

【项目实战经验】DataKit迁移MySQL到openGauss（下）,第1张

【项目实战经验】DataKit迁移MySQL到openGauss（下）,第2张

【项目实战经验】DataKit迁移MySQL到openGauss（下）,第3张

【项目实战经验】DataKit迁移MySQL到openGauss（下）,第4张

【项目实战经验】DataKit迁移MySQL到openGauss（下）,第5张

9.1. 迁移插件安装

中断安装，比如 kill 掉java进程（安装失败也要等待300s）

update tb_migration_host_portal_install set install_status=10;

【项目实战经验】DataKit迁移MySQL到openGauss（下）,第6张

下载安装包准备上传

【项目实战经验】DataKit迁移MySQL到openGauss（下）,第7张

缺少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

安装成功后的截图

【项目实战经验】DataKit迁移MySQL到openGauss（下）,第8张

主机上有对应的进程

[root@mysqldb alternatives]# jps
19073 QuorumPeerMain
19122 SupportedKafka
4874 Jps
19487 SchemaRegistryMai

10. 全量迁移

10.1. 选中主机，启动迁移

【项目实战经验】DataKit迁移MySQL到openGauss（下）,第9张

10.2. 迁移中

【项目实战经验】DataKit迁移MySQL到openGauss（下）,第10张

10.3. 迁移结束

【项目实战经验】DataKit迁移MySQL到openGauss（下）,第11张

【项目实战经验】DataKit迁移MySQL到openGauss（下）,第12张

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. 创建在线迁移任务

【项目实战经验】DataKit迁移MySQL到openGauss（下）,第13张

11.3. 启动

【项目实战经验】DataKit迁移MySQL到openGauss（下）,第14张

全量迁移完成并校验成功后进入增量迁移

【项目实战经验】DataKit迁移MySQL到openGauss（下）,第15张

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

【项目实战经验】DataKit迁移MySQL到openGauss（下）,第16张

上面一直卡住，再起一个的时候报错（内存不足）：

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

【项目实战经验】DataKit迁移MySQL到openGauss（下）,第17张

停止增量

【项目实战经验】DataKit迁移MySQL到openGauss（下）,第18张

12. 反向迁移

【项目实战经验】DataKit迁移MySQL到openGauss（下）,第19张

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

【项目实战经验】DataKit迁移MySQL到openGauss（下）,第20张

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)

【项目实战经验】DataKit迁移MySQL到openGauss（下）,第21张

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)

至此，迁移部分实践分享结束，欢迎大家一起交流学习。

网络营销教学网站销售型公司深圳网站开发培训网站建设与开发大作业 b2c网站建设费用宣传片制作公司资质

上一篇：SpringBoot部署一 Windows服务器部署

下一篇：springboot 查看和修改内置 tomcat 版本