迁移 MySQL 数据到 OceanBase 集群（使用mysqldump、datax等工具）

通过本实验，可以掌握从 MySQL 向 OceanBase 迁移数据的基本方法：mysqldump、datax 、canal 等。

1、相关准备工作

1.1 数据库环境

1套MySQL数据库，本次使用mysql5.7.36，部署在虚拟机（OS：CentOS7.9）
1套OceanBase集群，本次使用的是三副本集群，部署方式参考：https://blog.youkuaiyun.com/gdrs206/article/details/122933818

1.2 准备测试数据

## 使用tpcc-mysql生成测试数据
## 安装tpcc-mysql
ln -s /mysql/app/mysql/lib/libmysqlclient.so.20.3.23 /usr/local/lib/libmysqlclient.so.20

unzip -q /soft/tpcc-mysql-master.zip -d /mysql/app/
cd /mysql/app/tpcc-mysql-master/src
make

## 创建测试库，导入tpcc建表脚本
## 当前测试库为MySQL 5.7.36
mysql -uroot -p -h192.168.79.190 -P3308
create database oadb;
use oadb;
source /mysql/app/tpcc-mysql-master/create_table.sql;

## 导入测试数据
/mysql/app/tpcc-mysql-master/tpcc_load -uroot -proot -h192.168.79.190 -P3308 -d oadb -w 1

## 查看数据表的相关记录情况
SELECT TABLE_NAME,DATA_LENGTH,INDEX_LENGTH,(DATA_LENGTH+INDEX_LENGTH) as length,TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2), 'MB') as total_size FROM information_schema.TABLES WHERE TABLE_SCHEMA='oadb' order by length desc; 

2、使用mysqldump 将 mysql的表结构和数据同步到 OceanBase 的MySQL 租户中

2.1 MySQL源端导出数据

## 导出表结构
mysqldump -h192.168.79.190 -uroot -p -P3308 -d oadb --compact > /mysql/backup/oadb_ddl.sql

### 检查文件中是否存在特殊语法、变量等
grep -Ei "SQL_NOTES|DEFINER|MAX_ROWS" /mysql/backup/oadb_ddl.sql

## 导出表数据
mysqldump -h192.168.79.190 -uroot -p -P3308 --single-transaction -t oadb --compact > /mysql/backup/oadb_data.sql

2.2 导入数据到OceanBase

## 先建一个数据库oadb
obclient -h192.168.79.200 -uroot@myoadb#myob -P2883 -p -c -A
create database oadb;

## 导入表结构
use oadb;
source /mysql/backup/oadb_ddl.sql

### 导入表数据前先禁用外键检查约束
show global variables like '%foreign%';
set global foreign_key_checks=off;
show global variables like '%foreign%';

### 退出重新登陆，再进行导数
use oadb;
source /mysql/backup/oadb_data.sql

3、使用 datax 配置至少一个表的 MySQL 到 OceanBase 的 MySQL 租户的离线同步

3.1 安装配置datax

安装参考datax文档：
https://github.com/alibaba/DataX/blob/master/userGuid.md

## 下载安装包：
离线上传，或者：
wget http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz

tar -zxf /soft/datax.tar.gz -C /mysql/app/
cd /mysql/app/

## 删除datax中的隐藏文件
find /mysql/app/datax/plugin -name ".*" | xargs rm -f

## 如无Python则先安装
yum install -y python java

3.2 创建和调整作业的配置文件

## 生成模板文件
python /mysql/app/datax/bin/datax.py -r mysqlreader -w oceanbasev10writer > /mysql/app/datax/job/mysql2ob.json

## 根据实际情况调整模板文件
vi /mysql/app/datax/job/mysql2ob.json

{    
"job": {
        "setting": {
            "speed": {
                "channel": 2,
            },
            "errorLimit": {
                "record": 10
            }
        },
        "content": [
            {
                "reader": {
                    "name": "mysqlreader",
                    "parameter": {
                        "username": "root",
                        "password": "root",
                        "splitPk": "c_id",
                        "column": ["*"],
                        "connection": [
                            {
                                "jdbcUrl": [
                                    "jdbc:mysql://192.168.79.190:3308/oadb"
                                ],
                                "table": [
                                    "customer"
                                ]
                            }
                        ]
                    }
                },
                "writer": {
                    "name": "oceanbasev10writer",
                    "parameter": {
                        "writeMode": "insert",
                        "username": "root",
                        "password": "root",
                        "writerThreadCount": 5,
                        "column": [
                            "*"
                        ],
                        "connection": [
                           {
                                "jdbcUrl": "||_dsc_ob10_dsc_||myob:myoadb||_dsc_ob10_dsc_||jdbc:oceanbase://192.168.79.200:2883/oadb?useUnicode=true&characterEncoding=utf-8",
                                "table": [
                                    "customer"
                                ]
                            }
                        ]
                    }
                }
            }
        ]
    }
}

## 启动同步作业
## obclient -h192.168.79.200 -uroot@myoadb#myob -P2883 -p -c -A
## truncate oadb.customer;
python /mysql/app/datax/bin/datax.py /mysql/app/datax/job/mysql2ob.json

## 检查同步情况 
select count(*) from oadb.customer;
select * from oadb.customer limit 1;

4、使用 datax 配置至少一个表的 OceanBase 到 CSV 以及 CSV 到 OceanBase 的离线同步

4.1 OceanBase to CSV

## 生成模板文件
python /mysql/app/datax/bin/datax.py -r mysqlreader -w oceanbasev10writer > /mysql/app/datax/job/ob2csv.json

## 调整模板文件
vi /mysql/app/datax/job/ob2csv.json
{
   "job": {
       "setting": {
           "speed": {
               "channel": 8
           },
           "errorLimit": {
               "record": 0,
               "percentage": 0.1
           }
       },
       "content": [
           {
               "reader": {
                   "name": "oceanbasev10reader",
                   "parameter": {
				       "timeout":10000,
                       "readBatchSize":10000,
                       "readByPartition":"true",
                       "column": ["*"],
                       "connection": [
                           {
                               "jdbcUrl": ["||_dsc_ob10_dsc_||myob:myoadb||_dsc_ob10_dsc_||jdbc:oceanbase://192.168.79.200:2883/oadb?useUnicode=true&characterEncoding=utf-8"],
                                "table": ["orders"]
                           }
                       ],
                       "password": "root",
                       "username": "root",
                       "where": ""
                   }
               },
               "writer": {
                    "name": "txtfilewriter",
                    "parameter": {
                       "path": "/mysql/backup/datax",
                       "fileName": "orders",
                       "encoding": "UTF-8",
                       "writeMode": "truncate",
                       "dateFormat": "yyyy-MM-dd hh:mm:ss" ,
                       "nullFormat": "\\N" ,
                       "fileFormat": "csv" ,
                       "fieldDelimiter": ","
                    }
               }
           }
       ]
   }
}

## 启动同步作业
python /mysql/app/datax/bin/datax.py /mysql/app/datax/job/ob2csv.json

4.2 CSV to OceanBase

## 生成模板文件
python /mysql/app/datax/bin/datax.py -r mysqlreader -w oceanbasev10writer > /mysql/app/datax/job/csv2ob.json

## 调整模板文件
vi /mysql/app/datax/job/csv2ob.json
{
   "job": {
       "setting": {
           "speed": {
               "channel": 8
           },
           "errorLimit": {
               "record": 0,
               "percentage": 0.1
           }
       },
       "content": [
           {
               "reader": {
                   "name": "txtfilereader",
                   "parameter": {
                       "path": ["/mysql/backup/datax"],
                       "fileName": "orders",
                       "encoding": "UTF-8",
                       "column": ["*"],
                       "dateFormat": "yyyy-MM-dd hh:mm:ss" ,
                       "nullFormat": "\\N" ,
                       "fieldDelimiter": ","
                   }
               },
               "writer": {
                   "name": "oceanbasev10writer",
                   "parameter": {
                       "obWriteMode": "insert",
                       "column": ["*"],
                       "preSql": [
                           "truncate table orders"
                       ],
                       "connection": [
                           {
                               "jdbcUrl": "||_dsc_ob10_dsc_||myob:myoadb||_dsc_ob10_dsc_||jdbc:oceanbase://192.168.79.200:2883/oadb?useUnicode=true&characterEncoding=utf-8",
                                "table": [
                                   "orders"
                               ]
                           }
                       ],
                       "username": "root",
                       "password": "root",
                       "writerThreadCount": 10,
                       "batchSize": 1000,
                       "memstoreThreshold": "0.9"
                   }
               }
           }
       ]
   }
}

## 启动同步作业
python /mysql/app/datax/bin/datax.py /mysql/app/datax/job/csv2ob.json

## 检查同步情况
obclient -h192.168.79.200 -uroot@myoadb#myob -P2883 -p -c -A -e "select count(*) from oadb.orders;select * from oadb.orders limit 1;"

相关连接：
https://open.oceanbase.com/answer/detail?id=20400030