实践练习四(必选):迁移 MySQL 数据到 OceanBase 集群

一、系统环境

系统配置:4C16G500G

操作系统:centOS 7.9.2009

 

二、mysqldump 迁移数据

1、导出数据

mysqldump -h192.168.3.208 -uroot -P3306 -p123456 -d zjpt --compact > /home/zjpt.sql;

mysqldump -h 192.168.3.208 -uroot -P3306 -p123456 -t zjpt > /home/zjpt_data.sql;

 

2、导出数据内容

表结构

 

表数据

 

 

3、导入数据

3.1链接oceanbase

obclient -h127.1 -uroot@sys -P2881 -proot123 -c -A oceanbase

 

3.2创建数据库

create database zjpt;

 

3.3切换数据库

use zjpt

 

3.4导入表结构

source /home/zjpt.sql;

 

3.5导入表数据

Source /home/zjpt_data.sql;

 

3.6总结:

脚本包含以--及/ *!* / 的注释内容,删除即可,不删除导入的话数据可以正常导入,但是会提示错误。

三、DataX迁移数据

1. DataX准备工作

1.1 下载DataX

下载地址:https://github.com/alibaba/DataX

Google浏览器无法下载,使用火狐或者Edge下载即可

1.2检查安装环境

jdk版本:java -version

python版本:python -v

maven版本:mvn -v

 

1.3下载并安装maven

系统不存在maven,需自行下载,下载方式如下:

wget http://mirrors.tuna.tsinghua.edu.cn/apache/maven/maven-3/3.3.9/binaries/apache-maven-3.3.9-bin.tar.gz

 

 

解压并复制到/home/目录下

tar -zxvf apache-maven-3.3.9-bin.tar.gz -c /home/

 

配置Maven的环境变量

vim /etc/profile

M2_HOME=/home/apache-maven-3.3.9

export PATH=${M2_HOME}/bin:${PATH}

重新加载配置文件

source /etc/profile

检查Maven安装是否成功

mvn -v

 

2. DataX安装

2.1解压DataX

tar -zxvf datax.tar.gz

2.2自检

进入bin目录,开始自检,自建报错,直接rm -rf 删除隐藏文件即可

rm -rf /home/datax/plugin/reader/._*er

rm -rf /home/datax/plugin/writer/._*er

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

 

3. 修改配置文件

column表示字段名称,*表示全部字段,部分字段需写明字段名称。

jdbcUrl: _dsc_ob10_dsc_固定格式,obdemo表示集群名称,sys表示租户名称,192.168.3.232表示ip,2883端口,zjpt表示数据库名称。

"obWriteMode": "insert"表示插入,如果修改修改成update

"preSql": [ "truncate table business"],表示迁移前删除目标表记录。

 

{

    "job": {

        "setting": {

            "speed": {

                "channel": 4

            },

            "errorLimit": {

                "record": 0,

                "percentage": 0.1

            }

        },

        "content": [

            {

                "reader": {

                    "name": "mysqlreader",

                    "parameter": {

                        "username": "root",

                        "password": "123456",

                        "column": [

                            "*"

                        ],

                        "connection": [

                            {

                                "table": [

                                    "business"

                                ],

                                "jdbcUrl": ["jdbc:mysql://192.168.3.208:3306/zjpt?useUnicode=true&characterEncoding=utf8"]

                            }

                        ]

                    }

                },

 

                "writer": {

                    "name": "oceanbasev10writer",

                    "parameter": {

                        "obWriteMode": "insert",

                        "column": [

                            "*"

                        ],

                        "preSql": [

                            "truncate table business"

                        ],

                        "connection": [

                            {

                                "jdbcUrl": "||_dsc_ob10_dsc_||obdemo:sys||_dsc_ob10_dsc_||jdbc:oceanbase://192.168.3.232:2883/zjpt",

                                "table": [

                                    "business"

                                ]

                            }

                        ],

                        "username": "root",

                        "password":"root123",

                        "writerThreadCount":10,

                        "batchSize": 1000,

                        "memstoreThreshold": "0.9"

                    }

                }

            }

        ]

    }

}

4.执行数据迁移

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

 

 

 

学习了!