环境准备

1）Centos7.9；4G内存；2CPU; IP 192.168.56.121

​ MySQL 5.7.44

2）Rocky；16G内存；8CPU; IP 192.168.56.210

​ OceanBase 3.1.5

使用 mysqldump 迁移数据

1、导出指定数据库的表结构，只导出表结构不导表数据，添加“-d”命令参数

[root@rocky8 ~]# mysqldump -h 192.168.56.121 -uroot -p123456 -d sbtest --compact > sbtest_ddl.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
-- Warning: column statistics not supported by the server.

[root@rocky8 ~]# more sbtest_ddl.sql
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `sbtest2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_2` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

2、导出指定数据库的数据，不导表结构添加“-t”命令参数

[root@rocky8 ~]# mysqldump -h 192.168.56.121 -uroot -p123456 sbtest -t > sbtest_data.sql

3、oceanbase加载数据

[admin@rocky8 ~]$ ll
total 622152
-rw-rw-r--  1 admin admin  13728920 Oct 11 16:18 obclient-2.2.3-1.el8.x86_64.rpm
drwxr-xr-x  3 admin admin        17 Mar 16 22:54 obproxy-3.2.3.5
-rw-r--r--  1 admin admin   9964012 Sep  5  2022 obproxy-ce-3.2.3.5-2.el8.x86_64.rpm
drwxr-xr-x  8 admin admin        74 Mar 16 22:37 oceanbase
drwxr-xr-x. 6 admin admin        88 Feb 28 17:41 oceanbase-all-in-one
-rw-r--r--. 1 admin admin 523556648 Mar 11 16:58 oceanbase-all-in-one-4.2.2.0-100010012024022719.el8.x86_64.tar.gz
-rw-r--r--  1 admin admin  50074000 Sep 12  2023 oceanbase-ce-3.1.5-100020022023091114.el8.x86_64.rpm
-rw-r--r--  1 admin admin    163116 Sep 12  2023 oceanbase-ce-libs-3.1.5-100020022023091114.el8.x86_64.rpm
-rw-r--r--  1 admin admin  39580810 Mar 17 14:13 sbtest_data.sql
-rw-r--r--  1 admin admin      1183 Mar 17 14:12 sbtest_ddl.sql
[admin@rocky8 ~]$

使用 Datax 数据迁移

• 下载DataX

从http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz下载Datax并解压

tar -zxvf datax.tar.gz

#安装openjdk1.8
[root@rocky8 ~]# yum install java-1.8.0-openjdk.x86_64 -y

#安装python2
[root@rocky8 ~]# yum install python2 -y

• 删除 plugin 文件夹下的隐藏文件

cd /home/admin/datax/plugin/writer && rm -rf .*
cd /home/admin/datax/plugin/reader && rm -rf .*

验证

[admin@rocky8 ~]$ cd /home/admin/datax/bin
[admin@rocky8 bin]$ python2 datax.py ../job/job.json
2024-03-17 19:35:21.360 [job-0] INFO  JobContainer -
任务启动时刻                    : 2024-03-17 19:35:08
任务结束时刻                    : 2024-03-17 19:35:21
任务总计耗时                    :                 12s
任务平均流量                    :          253.91KB/s
记录写入速度                    :          10000rec/s
读出记录总数                    :              100000
读写失败总数                    :                   0

生成MySQL导出模板

$ python2 bin/datax.py -r mysqlreader -w oceanbasev10writer > mysql2objob.json 
$ vi mysql2objob.json
{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "mysqlreader",
                    "parameter": {
                        "column": ["id","name"],
                        "connection": [
                            {
                                "jdbcUrl": ["jdbc:mysql://192.168.56.121:3306/sbtest?useUnicode=true&characterEncoding=utf8"],
                                "table": ["a"]
                            }
                        ],
                        "password": "123456",
                        "username": "root",
                        "where": ""
                    }
                },
                "writer": {
                    "name": "oceanbasev10writer",
                    "parameter": {
                        "column": ["id","name"],
                        "preSql": [
                            "truncate table a"
                        ],
                        "connection": [
                            {
                                "jdbcUrl": "||_dsc_ob10_dsc_||mysqlTest:sys||_dsc_ob10_dsc_||jdbc:oceanbase://192.168.56.210:2883/testdb",
                                "table": ["a"]
                            }
                        ],
                        "obWriteMode": "insert",
                        "password": "user1",
                        "username": "user1"
                    }
                }
            }
        ],
        "setting": {
            "speed": {
                "channel": "1"
            }
        }
    }
}

执行任务

[root@rocky8 datax]#python2 bin/datax.py /home/admin/datax/mysql2objob.json

https://open.oceanbase.com/blog/10900157

https://open.oceanbase.com/blog/10900182?currentPage=1