迁移 MySQL 数据到 OceanBase 集群

1、准备mysql环境和测试数据

使用现有的mysql测试环境

安装sysbench

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench

用sysbench创建2张测试表

sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=10.102.23.43 --mysql-port=3306 --mysql-db=sbtest --mysql-user= --mysql-password= --table_size=2000 --tables=2 --threads=150 --report-interval=10 --rand-type=uniform --time=60 prepare

2、使用mysqldump导出sbtest库下的表结构和数据

mysqldump -usouce_mysql -p'1qaz2wsx' --databases sbtest --set-gtid-purged=OFF >  /tmp/sbtest.sql

3、将mysqldump文件导入oceanbase

连接oceanbase业务租户

obclient -h 127.1 -uroot@obmysql -P2883 -p'Hello123' -c

source /tmp/sbtest.sql

4、使用datax将mysql数据导入到oceanbase

下载datax

wget https://datax-opensource.oss-cn-hangzhou.aliyuncs.com/202210/datax.tar.gz

tar -zxvf datax.tar.gz

测试可以正常使用

python datax.py ../job/job.json

修改配置文件，配置数据源和数据目标

mysql2ob.json

{

    "job": {

        "setting": {

            "speed": {

                "channel": 4

            },

            "errorLimit": {

                "record": 0,

                "percentage": 0.1

            }

        },

        "content": [

            {

                "reader": {

                    "name": "mysqlreader",

                    "parameter": {

                        "username": "souce_mysql",

                        "password": "1qaz2wsx",

                        "column": ["*"],

                        "connection": [

                            {

                                "table": ["sbtest1"],

                                "jdbcUrl": ["jdbc:mysql://127.0.0.1:3306/sbtest?useUnicode=true&characterEncoding=utf8&useSSL=false"]

                            }

                        ]

                    }

                },

                "writer": {

                    "name": "oceanbasev10writer",

                    "parameter": {

                        "obWriteMode": "insert",

                        "column": ["*"],

                        "preSql": ["truncate table sbtest1"],

                        "connection": [

                            {

                                "jdbcUrl": "jdbc:oceanbase://127.0.0.1:2883/sbtest?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true",

                                "table": ["sbtest1"]

                            }

                        ],

                        "username": "root@obmysql",

                        "password":"Hello123",

                        "writerThreadCount":10,

                        "batchSize": 1000,

                        "memstoreThreshold": "0.9"

                    }

                }

            }

        ]

    }

}

执行离线同步

python /data/datax/bin/datax.py /data/datax/job/mysql2ob.json

目标库同步成功