九有数据库数据迁移

需求：

16.169:5678/dataunify --> dataunify169

16.94:3306/ap-dataunify --> 16.169:5678/apdataunify

说明：

本人也是第一次使用闭源的九有数据库，有感兴趣的小伙伴可以到官网查询下，使用单位的库、表等命名存在一系列问题，在此略过，仅发出数据迁移整个过程。不修改的情况下，UDB TX数据库的端口是5678

操作步骤：

1，在16.169:5678 创建 ap-dataunify

2，#进入管理和监控节点 登录 16.160，运行udb-loader

docker exec -it loader /bin/bash

root@udb-16160:/# cd /data/

root@udb-16160:/data# cat mtu-d.conf

LOAD DATABASE

FROM mysql://xxxv:xxx@10.1.16.94:3306/ap-dataunify

INTO pgsql://unvdb:xxx@10.1.16.169:5678/ap-dataunify

WITH include drop, create tables, create indexes, reset sequences, workers = 16, concurrency = 4

SET Mysql PARAMETERS

net_read_timeout = '600',

net_write_timeout = '600'

CAST type datetime to timestamp drop default drop not null using zero-dates-to-null,

type date to date drop default drop not null using zero-dates-to-null,

type tinyint to smallint drop typemod

ALTER SCHEMA 'ap-dataunify' RENAME TO 'public';

udb-loader mtu-d.conf #开始导入

3，登录169，执行sql更改数据库名称

SELECT pg_terminate_backend(pg_stat_activity.pid)

FROM pg_stat_activity

WHERE datname='dataunify' AND pid<>pg_backend_pid();

ALTER DATABASE "dataunify" RENAME TO dataunify169;

SELECT pg_terminate_backend(pg_stat_activity.pid)

FROM pg_stat_activity

WHERE datname='ap-dataunify' AND pid<>pg_backend_pid();

ALTER DATABASE "ap-dataunify" RENAME TO dataunify;

4，建议对dataunify和dataunify169执行统计分析，否则性能可能会有影响。

分别选择dataunify和dataunify169库，执行 VACUUM analyze

root@udb-16160 test]# docker images |grep loader

udbloader-x86_64 latest 5caccdb6edfe 3 months ago 349MB

[root@udb-16160 test]# docker ps -a

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

8b3d6d827x udbloader-x86_64 "/bin/bash" 3 hours ago Up 3 hours loader

9fb7d972297 docker.elastic.co/kibana/kibana:7.14.0 "/bin/tini -- /usr/l…" 7 days ago Up 6 days kibana01

b3333fc11 docker.elastic.co/elasticsearch/elasticsearch:7.14.0 "/bin/tini -- /usr/l…" 7 days ago Up 6 days es01

64571633c7 docker.elastic.co/beats/filebeat:7.17.2 "/usr/bin/tini -- /u…" 7 days ago Up 7 days filebeat

5c57043fa49 grafana/grafana-oss:9.5.6 "/run.sh" 7 days ago Up 6 days grafana

becdb3809bab bitnami/prometheus:2.45.0 "/opt/bitnami/promet…" 7 days ago Up 7 days prometheus

e18e397e960c prom/alertmanager:v0.25.0 "/bin/alertmanager -…" 7 days ago Exited (0) 6 days ago alertmanager

058a85752221 python:3.8.16 "/bin/bash -c 'cd /d…" 4 weeks ago Up 7 days aiops-worker

3afc8c0f404c python:3.8.16 "/bin/bash -c 'cd /d…" 4 weeks ago Up 7 days aiops

[root@udb-16160 test]# docker exec -it loader /bin/bash

root@udb-16160:/# cd /data

root@udb-16160:/data# ls -la

total 4

drwxr-xr-x 2 root root 24 Nov 24 08:25 .

drwxr-xr-x 1 root root 41 Nov 24 06:45 ..

-rw-r--r-- 1 root root 592 Nov 24 08:25 mtu-d.conf

root@udb-16160:/data# udb-loader mtu-d.conf

2023-11-24T10:03:06.002414Z LOG udb-loader version "22.4.0~devel"

2023-11-24T10:03:06.023473Z LOG Migrating from #<MYSQL-CONNECTION mysql://xx@10.1.16.94:3306/appeal-dataunify #x3020022BB76D>

2023-11-24T10:03:06.023883Z LOG Migrating into #<PGSQL-CONNECTION pgsql://unvdb@10.1.16.169:5678/ap-dataunify #x302002ffA9AD>

2023-11-24T10:20:38.880531Z LOG report summary reset

table name errors rows bytes total time

--------------------------------- --------- --------- --------- --------------

fetch meta data 0 94 0.039s

Create Schemas 0 0 0.001s

Create SQL Types 0 0 0.001s

Create tables 0 46 0.119s

Set Table OIDs 0 23 0.001s

--------------------------------- --------- --------- --------- --------------

public.unify_wo_proc_log 0 1428601 473.5 MB 2m43.007s

public.unify_work_order_dispose 0 1144986 344.9 MB 1m42.353s

public.unify_work_extend_value 0 50358768 4.6 GB 15m44.299s

public.unify_work_order_finsh 0 286903 71.1 MB 2m11.657s

public.unify_work_order 0 290903 219.6 MB 1m18.983s

public.unify_work_order_allocate 0 101551 38.2 MB 41.115s

public.unify_wo_proc_log_COPY2 0 50016 23.7 MB 11.505s

public.unify_wo_proc_log_copy1 0 49300 22.0 MB 5.495s

public.unify_sys_oss 0 10609 2.6 MB 3.678s

public.unify_sys_files 0 9227 2.7 MB 0.942s

public.unify_work_order_copy1 0 5406 5.2 MB 1.627s

public.unify_work_order_addr_test 0 5397 1.5 MB 1.141s

public.unify_bas_order_duty 0 818 453.2 kB 0.401s

public.unify_work_extend_model 0 721 90.1 kB 0.161s

public.unify_work_extend_log 0 718 264.7 kB 0.167s

public.unify_wo_order_satisfy 0 732 156.8 kB 0.108s

public.unify_wo_leader_opt 0 208 39.6 kB 0.079s

public.unify_app_auth 0 0 0.058s

public.unify_app_info 0 3 0.1 kB 0.049s

public.unify_app_info_auth 0 0 0.050s

public.unify_sys_oss_config 0 0 0.052s

public.unify_wait_sign_work_order 0 0 0.049s

public.unify_work_order_log 0 0 0.033s

--------------------------------- --------- --------- --------- --------------

COPY Threads Completion 0 16 15m44.222s

Create Indexes 0 71 3m50.828s

Index Build Completion 0 71 1m47.924s

Reset Sequences 0 0 0.056s

Primary Keys 0 22 0.023s

Create Foreign Keys 0 0 0.000s

Create Triggers 0 0 0.000s

Install Comments 0 540 0.297s

--------------------------------- --------- --------- --------- --------------

Total import time ✓ 53744867 5.8 GB 21m23.351s

root@udb-16160:/data#