• 【openGauss数据迁移系列】使用pgloader将数据从MySQL迁移到openGauss的最佳实践


    数据库迁移是实际工作中经常遇到的问题,比如由于磁盘空间、业务性能、项目改造等等原因,有从甲服务器迁移到乙服务器,从A种数据库迁移到B种数据库,从源路径迁移到另一个目标路径、同一个机器下从一个用户迁移到另一个用户等各种场景,有时需要整个数据库所有文件都迁移,有时只需要迁移部分数据文件如REDO、表空间文件等。

    一、概述

    云原生数据库openGauss也支持若干数据库迁移工具,其中的pgloader是一个数据导入工具,使用COPY命令将数据导入到PostgreSQL。pgloader有两种工作模式,一种是从文件导入,一种是迁移数据库。pgloader在两种情况下都使用PostgreSQL的COPY协议高效的传输数据。
    openGauss兼容PostgreSQL的通信协议以及绝大部分语法,本文就介绍如何使用pgloader将MySQL数据库迁移至openGauss。
    在这里插入图片描述

    整个过程可以基本分为四步:
    迁移前准备 > 相关文件配置 > 执行迁移 > 结果验证

    二、迁移前准备:

    下载pgloader

    除了 源数据库MySQL 和目标数据库openGauss,它们之间需要网络互通外。还需要关键工具pgloader。因为某些网络原因,我这里使用的是docker 版本的pgloader 下载,安装在目标库所在服务器上。
    默认pull最新版本。

    [root@pekphisprb70593 software]# docker pull dimitri/pgloader
    [root@pekphisprb70593 software]# docker run --rm --name pgloader dimitri/pgloader:latest pgloader --version
    pgloader version "3.6.7~devel"
    compiled with SBCL 2.1.1.debian
    
    • 1
    • 2
    • 3
    • 4

    需要注意的是,它只是命令行工具,所以一旦执行完毕会自动退出。故每次都需要“docker run --rm --name pgloader dimitri/pgloader:latest pgloader + 具体操作”的命令。

    修改openGauss加密方式(重要)

    用户密码存储在系统表pg_authid中,为防止用户密码泄露,openGauss对用户密码进行加密存储,所采用的加密算法由配置参数password_encryption_type决定。

    • 当参数password_encryption_type设置为0时,表示采用md5方式对密码加密。
    • 当参数password_encryption_type设置为1时,表示采用sha256和md5方式对密码加密。
    • 当参数password_encryption_type设置为2时,表示采用sha256方式对密码加密,为默认配置。
      在这里插入图片描述

    由于MD5加密算法安全性低,存在安全风险,不建议使用。openGauss 对原生PostgreSQL的通信协议进行了安全加固,这导致与PostgreSQL的默认通信协议互相不兼容了,因此,使用pgloader的PostgreSQL原生版本默认是不能连接openGauss的。会报类似下述错误:
    在这里插入图片描述

    通过修改该GUC参数修改数据库加密算法,执行如下命令将参数修改为1 ,同时支持md5 和sha256

    gs_guc reload -N all -I all -c "password_encryption_type=1"
    
    • 1

    一定要在设置完上述参数后,再新建用户。

    openGauss设置监听

    若不设置,可能会像我一样遇到“Failed to connect to pgsql at “xxx” (port 15400) as user “opengauss_test”: Database error 28000: no pg_hba.conf entry for host “172.17.0.2”.”
    在这里插入图片描述
    设置方法:

    cd /opt/huawei/install/data/dn/
    vi pg_hba.conf
    在IPv4中新增一行
    host    all    all     0.0.0.0/0                      md5
    然后重启数据库
    [omm@pekphisprb70593 dn]$ gs_om -t stop
    [omm@pekphisprb70593 dn]$ gs_om -t start
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    在这里插入图片描述

    测试用户和数据

    这一步是可选的,包括源库的数据库、用户和原始数据,目标库的数据库和用户。如果都已经有了则直接跳过看下一步,缺啥补啥。
    创建目标库openGauss的用户及database.

    [omm@pekphisprb70593 ~]$ gsql -d postgres -p 15400 
    openGauss=# CREATE USER opengauss_test identified by 'Gauss_234'; 
    openGauss=# GRANT ALL PRIVILEGES TO opengauss_test; 
    openGauss=# CREATE DATABASE opengauss_db with owner opengauss_test ENCODING 'utf8' template = template0;  
    
    • 1
    • 2
    • 3
    • 4

    新建了一个空表test2。

    [omm@pekphisprb70593 dn]$ gsql -d opengauss_db -p 15400
    opengauss_db=# create table if not exists test2(id integer);
    CREATE TABLE
    
    • 1
    • 2
    • 3

    创建源数据库和用户,刷新系统授权表。

    CREATE DATABASE IF NOT EXISTS mysql_db1 default charset utf8;
    CREATE USER mysql_t1 identified by 'Mysql_234' ;
    GRANT ALL PRIVILEGES ON *.* TO 'mysql_t1' WITH GRANT OPTION;
    FLUSH PRIVILEGES;
    
    • 1
    • 2
    • 3
    • 4

    构造数据

    mysql@siaphisprk01345:~> mysql -u mysql_t1 -p
    mysql> use mysql_db1;
    create table if not exists test1 (id int(10),name char(20),salary float,time datetime(6),addr varchar(200),primary key(id)) ;
    insert into test1 values (1,'zhangsan',2000.00,'2018-06-01 00:00:00','beijing');
    insert into test1 values (2,'李四',2000.89,'2018-06-01 00:00:00','西安');
    insert into test1 values (3,'Bob',2000,'2018-06-02 00:00:00','南京');
    
    #创建了一个和目标数据库表结构不同的test2。
    create table if not exists test2(id int(10),name varchar(20));
    insert into test2 values (1234567,'Kevin');
    #创建空表test3。
    create table if not exists test3(id int(10));
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    三、配置文件

    pgloader提供丰富的配置项,我们可以自由定义迁移时的各类动作,如通过include drop,删除目标数据库中名称出现在MySQL数据库中的所有表,以允许连续多次使用同一命令,从干净的环境自动启动。
    在pgloader所在服务器任意路径下创建一个配置文件,文件可以命名为opengauss.loader。下面是一个配置文件示例,里面的值需要修改为自己环境的实际值。

    LOAD DATABASE 
      
    FROM mysql://mysql_t1:Mysql_234@1.1.1.1:3306/mysql_db1 
      
    INTO postgresql://opengauss_test:Gauss_234@2.2.2.2:15400/opengauss_db 
      
    WITH include drop, create tables, create indexes, reset no sequences, 
      
    workers = 8, concurrency = 1, 
      
    multiple readers per thread, rows per range = 50000 
      
    CAST 
      
    type varchar when(= 1 precision) to "boolean" drop typemod keep default keep not null;
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    几个配置项简单介绍下:

    • FROM:源库,格式:
      mysql://用户名:密码@IP:端口/数据库名称
    • INTO:目标库,格式:
      postgresql://用户名:密码@IP:端口/数据库名称
    • WITH:从MySQL数据库加载时的选项。有include drop、create tables、create indexes等选项。
    • CAST:因为两种数据库的数据类型并不完全一致,工具允许用户覆盖已有的默认转换规则或者使用特殊情况修改它们,自定义类型转换规则。
      还有个比较重要的功能是部分迁移:用户可以通过 including only table names matching 和 excluding table names matching 实现只迁移特定的表或者在迁移过程中排除特定的表。

    更详细的配置项解读,可查看官网的说明:https://pgloader.readthedocs.io/en/latest/ref/mysql.html
    Migratinga MySQL Database to PostgreSQL — pgloader 3.4.1 documentation

    四、执行迁移

    启动docker

    docker run -tid --name pgloader_test dimitri/pgloader 
    
    • 1

    复制配置文件到docker:

    docker cp ./openGauss.loader pgloader_test:/ 
    
    • 1

    进入docker环境:

    docker exec -it pgloader_test /bin/bash
    
    • 1

    启动迁移

    root@548498fb64a6:/# pgloader opengauss.loader
    
    • 1

    在这里插入图片描述

    如果遇到问题可以先按照上面的配置步骤去检查下。

    五、结果验证

    现在来看下迁移结果与预期是否一致:
    在目标库进行查询:

    [omm@pekphisprb70593 dn]$ gsql -d opengauss_db -p 15400
    
    • 1

    可以看出表 test1和test3包括数据都已经迁移过来。test2由于迁移前后的数据库结构不一致并没有迁移成功。与预期一致。
    但是之前mysql定义的float类型,被强制转成double precision。其他的一些不同和限制有兴趣或者必须要迁移的同学自行探索下。

    总结:使用pgloader进行数据库的迁移过程还是比较简单的,有兴趣的同学可以尝试下。

    openGauss: 一款高性能、高安全、高可靠的企业级开源关系型数据库。

    🍒如果您觉得博主的文章还不错或者有帮助的话,请关注一下博主,如果三连点赞评论收藏就更好啦!谢谢各位大佬给予的支持!

  • 相关阅读:
    python对异常的处理
    Power BI 如何使用Tooltip创建悬浮报表页 (自定义工具提示)
    数据库SqlServer笔试题
    计算机毕业论文java毕业设计论文题目springboot ssm框架项目源代码实现的学生就业信息管理(spring boot+layui)[包运行成功]
    试试用Markdown来设计表单
    JavaScript:js实现在线五子棋人机(人人)对弈
    Idea断点调试(debug)详解
    时序分析 46 -- 时序数据转为空间数据 (五) 马尔可夫转换场 python 实践(上)
    小设计,大作用——防腐层的妙用
    SpringCloud Alibaba微服务第6章之Gateway
  • 原文地址:https://blog.csdn.net/GaussDB/article/details/126853195