国电南自实践：从Mysql到OceanBase数据迁移

作者简介：李寅，软件测试工程师

1. 实验环境

1）Centos7.3；64G内存；16CPU; IP 10.137.32.197；

2. 使用 mysqldump 迁移数据

1) 导出指定数据库的表结构（不包括数据）

[root@node1 ~]# mysqldump -h 127.1 -uroot -pscadacom -d tpcctest --compact > tpcctest_ddl.sql

2) 导出指定数据库的表数据（不包括结构）

3) 同步业务表结构至 OceanBase

mysql -h127.1 -uroot@sys -P2881 -c -A oceanbase
create database from_tpcc;
use from_tpcc;
source tpcctest_ddl.sql;

注意：

报错提示有未知字符集，修改：

sed -i 's/ DEFAULT CHARSET=latin1/ /g' tpcctest_ddl.sql

再次执行导入命令，未报错。

4) 同步业务表数据至 OceanBase

source tpcctest_data.sql;

3. 使用 Datax 数据迁移

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

tar -zxvf datax.tar.gz

2) 删除 plugin 文件夹下的隐藏文件

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

3) 生成 mysql 同步至 OceanBase 的配置模板

python bin/datax.py -r mysqlreader -w oceanbasev10writer

python bin/datax.py -r mysqlreader -w oceanbasev10writer > mysql2objob.json

4) 修改配置文件

vi mysql2objob.json
 
{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "mysqlreader",
                    "parameter": {
                        "column": ["id","name","age","sex"],
                        "connection": [
                            {
                                "jdbcUrl": ["jdbc:mysql://127.0.0.1:3306/mytestdb?useUnicode=true&characterEncoding=utf8"],
                                "table": ["test1"]
                            }
                        ],
                        "password": "scadacom",
                        "username": "root",
                        "where": ""
                    }
                },
                "writer": {
                    "name": "oceanbasev10writer",
                    "parameter": {
                        "column": ["id","name","age","sex"],
"preSql": [
                            "truncate table test1"
                        ],
                        "connection": [
                            {
                                "jdbcUrl": "||_dsc_ob10_dsc_||obdemo:sys||_dsc_ob10_dsc_||jdbc:oceanbase://192.168.0.197:2883/mytestdb",
                                "table": ["test1"]
                            }
                        ],
                        "obWriteMode": "insert",
"password": "scadacom",
                        "username": "root"
                    }
                }
            }
        ],
        "setting": {
            "speed": {
                "channel": 4
            }
        }
    }
}

5) 启动同步任务

[root@node1 datax]# bin/datax.py mysql2objob.json

6) 同步执行完成

4. 使用 Datax 以 csv 格式导入导出 oceanbase

4.1将数据导出 oceabase

1) 生成json配置模板

[root@node1 datax]# python bin/datax.py -r oceanbasev10reader -w txtfilewriter > oceanbase2csv.json

2) 编辑模板

vi oceanbase2csv.json
{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "oceanbasev10reader",
                    "parameter": {
                        "column": ["*"],
                        "connection": [
                            {
                                "jdbcUrl": ["||_dsc_ob10_dsc_||obdemo:sys||_dsc_ob10_dsc_||jdbc:oceanbase://127.0.0.1:2883/mytestdb"],
                                "table": ["test1"]
                            }
                        ],
                        "password": "scadacom",
                        "username": "root",
                        "where": ""
                    }
                },
                "writer": {
                    "name": "txtfilewriter",
                    "parameter": {
                        "dateFormat": "yyyy-MM-dd hh:mm:ss",
                        "fieldDelimiter": ",",
                        "fileName": "ob2csv",
                        "path": "/home/admin/datax",
                        "writeMode": "truncate"
                    }
                }
            }
        ],
        "setting": {
            "speed": {
                "channel": "4"
            }
        }
    }
}

3) 执行任务

[root@node1 datax]# python bin/datax.py oceanbase2csv.json

4) 执行结束

4.2将 csv 数据导入 oceanbase

1) 生成json配置模板

[root@node1 datax]# python bin/datax.py -w oceanbasev10writer -r txtfilereader > csv2oceanbase.json

2) 编辑模板

vi csv2oceanbase.json
{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "txtfilereader",
                    "parameter": {
                        "column": ["*"],
                        "encoding": "UTF-8",
                        "fieldDelimiter": ",",
                        "path": ["/home/admin/datax/csv"],
"fileName": "ob2csv",
                    }
                },
                "writer": {
                    "name": "oceanbasev10writer",
                    "parameter": {
                        "column": ["*"],
                        "connection": [
                            {
                                "jdbcUrl": "||_dsc_ob10_dsc_||obdemo:sys||_dsc_ob10_dsc_||jdbc:oceanbase://127.0.0.1:2883/mytestdb2",
                                "table": ["test1"]
                            }
                        ],
                        "obWriteMode": "insert",
                        "password": "scadacom",
                        "username": "root"
                    }
                }
            }
        ],
        "setting": {
            "speed": {
                "channel": "4"
            }
        }
    }
}

3) 执行任务

[root@node1 datax]# bin/datax.py /home/admin/datax/csv2oceanbase.json

4) 执行完成

·注意：

配置文件中 Path 需要给定文件的路径，该路径下不要有其他文件，否则执行会报错。

OceanBase 社区版入门到实战教程直播正在进行中~

快和小伙伴一起进群交流学习吧~

加入直播群方式一：

钉钉群号 3582 5151

加入直播群方式二：

扫码下方钉钉二维码加入