实践练习四（必选）：迁移 MySQL 数据到 OceanBase 集群

实验目录：
（必选）使用 mysqldump 将 mysql的表结构和数据同步到 OceanBase 的MySQL 租户中。
（必选）使用 datax 配置至少一个表的 MySQL 到 OceanBase 的 MySQL 租户的离线同步。
（可选）使用 datax 配置至少一个表的 OceanBase 到 CSV 以及 CSV 到 OceanBase 的离线同步。
（可选）使用 canal 配置 MySQL 的增量同步到 OceanBase 的 MySQL 租户。

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

测试用例数据

# MySQL官方测试库Employees Sample Database
https://github.com/datacharmer/test_db
wget https://github.com/datacharmer/test_db/archive/refs/heads/master.zip
unzip master.zip
cd test_db-master
mysql -uroot -p -e "source employees.sql"
# 导入数据量如下
[root@Node1 employees]# ll -h
总用量 179M
-rw-r----- 1 mysql mysql 128K  3月 14 16:12 departments.ibd
-rw-r----- 1 mysql mysql  25M  3月 14 16:13 dept_emp.ibd
-rw-r----- 1 mysql mysql 128K  3月 14 16:13 dept_manager.ibd
-rw-r----- 1 mysql mysql  22M  3月 14 16:13 employees.ibd
-rw-r----- 1 mysql mysql 104M  3月 14 16:13 salaries.ibd
-rw-r----- 1 mysql mysql  27M  3月 14 16:13 titles.ibd

使用mysqldump导出数据

mysqldump -uroot -p --databases employees > /data/mysql-files/employees-20240314.sql

导入oceanbase

obclient -h192.168.111.111 -P2883 -uroot -p -A
Enter password: 
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 7
Server version: OceanBase_CE 4.2.2.0 (r100010012024022719-c984fe7cb7a4cef85a40323a0d073f0c9b7b8235) (Built Feb 27 2024 19:21:00)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

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

obclient [(none)]> source employees-20240314.sql

比对数据

# 使用官方校验脚本
obclient [(none)]> use employees
Database changed
obclient [employees]> source test_employees_md5.sql
# 关键输出信息如下
+--------------+------------------+----------------------------------+
| table_name   | expected_records | expected_crc                     |
+--------------+------------------+----------------------------------+
| departments  |                9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_emp     |           331603 | ccf6fe516f990bdaa49713fc478701b7 |
| dept_manager |               24 | 8720e2f0853ac9096b689c14664f847e |
| employees    |           300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| salaries     |          2844047 | fd220654e95aea1b169624ffe3fca934 |
| titles       |           443308 | bfa016c472df68e70a03facafa1bc0a8 |
+--------------+------------------+----------------------------------+
6 rows in set (0.003 sec)
+--------------+---------------+-----------+
| table_name   | records_match | crc_match |
+--------------+---------------+-----------+
| departments  | OK            | ok        |
| dept_emp     | OK            | ok        |
| dept_manager | OK            | ok        |
| employees    | OK            | ok        |
| salaries     | OK            | ok        |
| titles       | OK            | ok        |
+--------------+---------------+-----------+
6 rows in set (0.132 sec)
# 迁移前后一致

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

datax部署

项目地址：https://github.com/alibaba/DataX

wget https://datax-opensource.oss-cn-hangzhou.aliyuncs.com/202308/datax.tar.gz
tar zxvf datax.tar.gz -C /usr/local/

开始迁移（表结构）

datax 仅迁移表数据，需要提前在目标端创建好对应的表对象结构。

# 源库导出表结构
mysqldump --set-gtid-purged=OFF --no-data -uroot -p --databases employees > /data/mysql-files/employees-schemma.sql

# 目标库清理之前的数据，并重建表结构
obclient -h127.0.0.1 -P2883 -uroot -p -A
Enter password: 
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 13
Server version: OceanBase_CE 4.2.2.0 (r100010012024022719-c984fe7cb7a4cef85a40323a0d073f0c9b7b8235) (Built Feb 27 2024 19:21:00)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

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

obclient [(none)]> drop database employees;
Query OK, 10 rows affected (0.534 sec)

obclient [(none)]> source employees-schemma.sql

# 开启数据同步前临时关闭外键检查，避免单表迁移数据迁移过程中约束检查失败
obclient [(none)]> set global foreign_key_checks = 0;
Query OK, 0 rows affected (0.160 sec)

开始迁移（数据）

创建作业的配置文件（以dept_emp表为例）

# vim /usr/local/datax/job/mysql-oceanbase.json
{
    "job": {
        "setting": {
            "speed": {
                "channel": 4 
            },
            "errorLimit": {
                "record": 0,
                "percentage": 0.1
            }
        },
        "content": [
            {
                "reader": {
                    "name": "mysqlreader",
                    "parameter": {
                        "username": "root",
                        "password": "********",
                        "column": ["*"],
                        "connection": [
                            {
                                "table": ["dept_emp"],
                                "jdbcUrl": ["jdbc:mysql://127.0.0.1:3333/employees?useUnicode=true&characterEncoding=utf8"]
                            }
                        ]
                    }
                },
                "writer": {
                    "name": "oceanbasev10writer",
                    "parameter": {
                        "obWriteMode": "insert",
                        "column": ["*"],
                        "connection": [
                            {
                                "jdbcUrl": "jdbc:oceanbase://127.0.0.1:2883/employees?",
                                "table": ["dept_emp"]
                            }
                        ],
                        "username": "root",
                        "password":"********",
                        "writerThreadCount":10,
                        "batchSize": 1000,
                        "memstoreThreshold": "0.9"
                    }
                }
            }
        ]
    }
}

启动迁移任务

cd /usr/local/datax/
python ./bin/datax.py ./job/mysql-oceanbase.json
## 终端主要输出如下
2024-03-14 17:27:01.191 [job-0] INFO  StandAloneJobContainerCommunicator - Total 331603 records, 8522209 bytes | Speed 832.25KB/s, 33160 records/s | Error 0 records, 0 bytes |  All Task WaitWriterTime 1.824s |  All Task WaitReaderTime 0.663s | Percentage 100.00%
2024-03-14 17:27:01.192 [job-0] INFO  JobContainer - 
任务启动时刻                    : 2024-03-14 17:26:50
任务结束时刻                    : 2024-03-14 17:27:01
任务总计耗时                    :                 11s
任务平均流量                    :          832.25KB/s
记录写入速度                    :          33160rec/s
读出记录总数                    :              331603
读写失败总数                    :                   0

参考：https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000033178