一、环境准备

先准备好MySQL环境

[root@localhost mysql]# mysql -h 127.1 -P 3306 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

使用sysbench初始化数据

[root@localhost mysql]# mysql -h 127.1 -P 3306 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> create database sbtest default charset=utf8;
Query OK, 1 row affected (0.00 sec)

初始化数据

[root@localhost sysbench]# sysbench /usr/local/share/sysbench/oltp_common.lua --time=300 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=bao --mysql-db=sbtest --table-size=100000 --tables=10 --threads=16 --events=999999999   prepare
sysbench 1.1.0-df89d34 (using bundled LuaJIT 2.1.0-beta3)

Initializing worker threads...

Creating table 'sbtest4'...
Creating table 'sbtest7'...
Creating table 'sbtest2'...
Creating table 'sbtest3'...
Creating table 'sbtest1'...
Creating table 'sbtest5'...
Creating table 'sbtest9'...
Creating table 'sbtest6'...
Creating table 'sbtest10'...
Creating table 'sbtest8'...
Inserting 100000 records into 'sbtest4'
Inserting 100000 records into 'sbtest6'
Inserting 100000 records into 'sbtest9'
Inserting 100000 records into 'sbtest1'
Inserting 100000 records into 'sbtest7'
Inserting 100000 records into 'sbtest10'
Inserting 100000 records into 'sbtest3'
Inserting 100000 records into 'sbtest5'
Inserting 100000 records into 'sbtest8'
Inserting 100000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest3'...
Creating a secondary index on 'sbtest7'...
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest9'...
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest10'...

mysql> use sbtest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1          |
| sbtest10         |
| sbtest2          |
| sbtest3          |
| sbtest4          |
| sbtest5          |
| sbtest6          |
| sbtest7          |
| sbtest8          |
| sbtest9          |
+------------------+
10 rows in set (0.00 sec)

二、迁移数据

使用mysqldump导出

[root@localhost sysbench]# mysqldump -uroot -proyce -h127.0.0.1 -P3308 --single-transaction --hex-blob --routines --events --triggers --master-data=1 --set-gtid-purged=OFF --databases sbtest --default-character-set=utf8  --max_allowed_packet=512M > /tmp/sbtest.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

导入数据

obclient [test]> source /tmp/sbtest.sql
Query OK, 0 rows affected (0.002 sec)

验证obclient [test]> use sbtest
Database changed
obclient [sbtest]>
obclient [sbtest]>
obclient [sbtest]> select count(1) from sbtest1;
+----------+
| count(1) |
+----------+
|   100000 |
+----------+

三、使用DATAX离线迁移数据

下载并安装datax

[root@localhost ~]# wget http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz
--2022-11-13 22:00:24--  http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz
Resolving datax-opensource.oss-cn-hangzhou.aliyuncs.com (datax-opensource.oss-cn-hangzhou.aliyuncs.com)... 47.110.23.138
Connecting to datax-opensource.oss-cn-hangzhou.aliyuncs.com (datax-opensource.oss-cn-hangzhou.aliyuncs.com)|47.110.23.138|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 853734462 (814M) [application/gzip]
Saving to: ‘datax.tar.gz’

100%[====================================================================================>] 853,734,462 1.35MB/s   in 15m 32s

2022-11-13 22:05:02 (894 KB/s) - ‘datax.tar.gz’ saved [853734462/853734462]

删除隐藏文件

[root@localhost ~]# tar -xf datax.tar.gz -C /usr/local/
[root@localhost ~]# cd /usr/local/datax
[root@localhost datax]# ls
bin  conf  job  lib  log  log_perf  plugin  script  tmp [root@localhost datax]# find /usr/local/datax/plugin -name ".*" | xargs rm -f
[root@localhost datax]# yum install -y python2 java

生成模板配置文件并修改

[root@localhost datax]# python2 /usr/local/datax/bin/datax.py -r mysqlreader -w oceanbasev10writer > /usr/local/datax/job/my2ob.json
[root@localhost datax]# vi /usr/local/datax/job/my2ob.json

配置文件示例{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "mysqlreader",
                    "parameter": {
                        "column": ["*"],
                        "connection": [
                            {
                                "jdbcUrl": ["jdbc:mysql://127.0.0.1:3306/sbtest"],
                                "table": ["sbtest1"]
                            }
                        ],
                        "password": "royce",
                        "username": "root",
                        "where": ""
                    }
                },
                "writer": {
                    "name": "oceanbasev10writer",
                    "parameter": {
                        "column": ["*"],
                        "connection": [
                            {
                                "jdbcUrl": "||_dsc_ob10_dsc_||obdemo:obmysql||_dsc_ob10_dsc_||jdbc:oceanbase://127.0.0.1:2883/sbtest?useUnicode=true&characterEncoding=utf-8",
                                "table": ["sbtest1"]
                            }
                        ],
                        "obWriteMode": "insert",
                        "password": "royce",
                        "username": "root"
                    }
                }
            }
        ],
        "setting": {
            "speed": {
                "channel": "2"
            }
        }
    }
}

启动同步任务

[root@localhost job]# python2 /usr/local/datax/bin/datax.py /usr/local/datax/job/my2ob.json
...任务启动时刻                    : 2023-01-14 10:07:08
任务结束时刻                    : 2023-01-14 10:07:30
任务总计耗时                    :                 22s
任务平均流量                    :          910.35KB/s
记录写入速度                    :           5000rec/s
读出记录总数                    :              100000
读写失败总数                    :                   0

确认数据是否同步过来了

obclient [test]> use sbtest
Database changed
obclient [sbtest]>
obclient [sbtest]>
obclient [sbtest]> select count(1) from sbtest1;
+----------+
| count(1) |
+----------+
|   100000 |
+----------+
1 row in set (0.118 sec)