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

使用 mysqldump 将 mysql的表结构和数据同步到 OceanBase 的MySQL 租户中
源库：MySQL 5.7，数据库：sbtest，10张表，每张表10万行数据
目标库：OB 3.1.1，数据库：sbtest，租户：obmysql

安装sysbench创造数据
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | bash
yum -y install sysbench
sysbench --version

开始

mysql -uroot -p123456Admin@123 -h192.168.1.246

创建sbtest库
create database sbtest;

将数据导出

[root@cts07 ~]# mysqldump -uroot -p123456Admin@123 -h192.168.1.246 -P3306 --single-transaction --hex-blob --routines --events --triggers --master-data=2 --set-gtid-purged=OFF --databases sbtest --default-character-set=utf8  --max_allowed_packet=512M > sbtest.sql
[root@cts07 ~]# ll -h sbtest.sql
-rw-r--r-- 1 root root 189M Feb  3 02:11 sbtest.sql

用source导入

[admin@cts07 obproxy-3.1.0]$ obclient -h 127.1 -uroot@obmysql#obdemo -P2883 -p -c -A test
Enter password:
Welcome to the OceanBase.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.6.25 OceanBase 3.1.0 (r-) (Built May 30 2021 11:21:29)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [test]> show databases;
+--------------------+
| Database           |
+--------------------+
| oceanbase          |
| information_schema |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.012 sec)

MySQL [test]> source /data/sbtest.sql
Query OK, 5290 rows affected (2.499 sec)
Records: 5290  Duplicates: 0  Warnings: 0

Query OK, 5282 rows affected (1.776 sec)
Records: 5282  Duplicates: 0  Warnings: 0

Query OK, 5258 rows affected (1.868 sec)
Records: 5258  Duplicates: 0  Warnings: 0

Query OK, 5258 rows affected (2.013 sec)
Records: 5258  Duplicates: 0  Warnings: 0

Query OK, 5258 rows affected (0.807 sec)
Records: 5258  Duplicates: 0  Warnings: 0

Query OK, 5258 rows affected (0.457 sec)
Records: 5258  Duplicates: 0  Warnings: 0

Query OK, 5258 rows affected (1.392 sec)
Records: 5258  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.002 sec)

Query OK, 0 rows affected (0.002 sec)

Query OK, 0 rows affected (0.001 sec)

Query OK, 0 rows affected (0.004 sec)

Query OK, 0 rows affected (0.003 sec)

Query OK, 0 rows affected (0.003 sec)

Query OK, 0 rows affected (0.010 sec)

Query OK, 0 rows affected (0.006 sec)

Query OK, 0 rows affected (0.001 sec)


MySQL [sbtest]>  show databases;
+--------------------+
| Database           |
+--------------------+
| oceanbase          |
| information_schema |
| mysql              |
| test               |
| sbtest             |
+--------------------+
5 rows in set (0.025 sec)

MySQL [sbtest]> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1          |
| sbtest10         |
| sbtest2          |
| sbtest3          |
| sbtest4          |
| sbtest5          |
| sbtest6          |
| sbtest7          |
| sbtest8          |
| sbtest9          |
+------------------+
10 rows in set (0.008 sec)

MySQL [sbtest]> select count(1) from sbtest1;
+----------+
| count(1) |
+----------+
|   100000 |
+----------+
1 row in set (0.311 sec)

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

部署DATAX软件，只需改配置文件

[root@cts07 ~]# wget http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz
--2023-02-03 03:05:30--  http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz
Resolving datax-opensource.oss-cn-hangzhou.aliyuncs.com (datax-opensource.oss-cn-hangzhou.aliyuncs.com)... 121.199.204.252
Connecting to datax-opensource.oss-cn-hangzhou.aliyuncs.com (datax-opensource.oss-cn-hangzhou.aliyuncs.com)|121.199.204.252|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 853734462 (814M) [application/gzip]
Saving to: ‘datax.tar.gz’

100%[============================================================================================================================================================>] 853,734,462 9.68MB/s   in 90s

2023-02-03 03:07:02 (9.05 MB/s) - ‘datax.tar.gz’ saved [853734462/853734462]

[root@cts07 ~]# tar -xf datax.tar.gz -C /usr/local/
[root@cts07 ~]# cd /usr/local/datax
[root@cts07 datax]# find /usr/local/datax/plugin -name ".*" | xargs rm -f
[root@cts07 datax]# yum install -y python2 java
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirrors.aliyun.com
 * extras: mirrors.huaweicloud.com
 * updates: mirrors.huaweicloud.com
akopytov_sysbench/x86_64/signature       
Updated:
  python.x86_64 0:2.7.5-92.el7_9

Dependency Updated:
  python-libs.x86_64 0:2.7.5-92.el7_9

Complete!
               

-- 生成模板文件
python2 /usr/local/datax/bin/datax.py -r mysqlreader -w oceanbasev10writer > /usr/local/datax/job/my2ob.json

# 根据实际环境修改
vi /usr/local/datax/job/my2ob.json

[root@cts07 datax]# cat /usr/local/datax/job/my2ob.json
{
"job": {
        "setting": {
            "speed": {
                "channel": 2,
            },
            "errorLimit": {
                "record": 10
            }
        },
        "content": [
            {
                "reader": {
                    "name": "mysqlreader",
                    "parameter": {
                        "username": "root",
                        "password": "123456Admin@123",
                        "splitPk": "id",
                        "column": ["*"],
                        "connection": [
                            {
                                "jdbcUrl": [
                                    "jdbc:mysql://192.168.1.246:3306/sbtest"
                                ],
                                "table": [
                                    "sbtest1"
                                ]
                            }
                        ]
                    }
                },
                "writer": {
                    "name": "oceanbasev10writer",
                    "parameter": {
                        "writeMode": "insert",
                        "username": "root",
                        "password": "123456Admin@123",
                        "writerThreadCount": 5,
                        "column": [
                            "*"
                        ],
                        "connection": [
                           {
                                "jdbcUrl": "||_dsc_ob10_dsc_||obce-single:obmysql||_dsc_ob10_dsc_||jdbc:oceanbase://127.0.0.1:2883/sbtest?useUnicode=true&characterEncoding=utf-8",
                                "table": [
                                    "sbtest1"
                                ]
                            }
                        ]
                    }
                }
            }
        ]
    }
}

启动服务，可以看到日志

[root@cts07 datax]# python2 /usr/local/datax/bin/datax.py /usr/local/datax/job/my2ob.json

2023-02-03 03:38:25.502 [taskGroup-0] INFO  TaskGroupContainer - taskGroup[0] taskId[9] is successed, used[3258]ms
2023-02-03 03:38:25.503 [taskGroup-0] INFO  TaskGroupContainer - taskGroup[0] taskId[10] is successed, used[408]ms
2023-02-03 03:38:25.503 [taskGroup-0] INFO  TaskGroupContainer - taskGroup[0] completed it's tasks.
2023-02-03 03:38:33.829 [job-0] INFO  StandAloneJobContainerCommunicator - Total 100000 records, 18788895 bytes | Speed 1.08MB/s, 6000 records/s | Error 0 records, 0 bytes |  All Task WaitWriterTime 12.627s |  All Task WaitReaderTime 2.227s | Percentage 100.00%
2023-02-03 03:38:33.830 [job-0] INFO  AbstractScheduler - Scheduler accomplished all tasks.
2023-02-03 03:38:33.830 [job-0] INFO  JobContainer - DataX Writer.Job [oceanbasev10writer] do post work.
2023-02-03 03:38:33.830 [job-0] INFO  JobContainer - DataX Reader.Job [mysqlreader] do post work.
2023-02-03 03:38:33.831 [job-0] INFO  JobContainer - DataX jobId [0] completed successfully.
2023-02-03 03:38:33.832 [job-0] INFO  HookInvoker - No hook invoked, because base dir not exists or is a file: /usr/local/datax/hook
2023-02-03 03:38:33.834 [job-0] INFO  JobContainer -
         [total cpu info] =>
                averageCpu                     | maxDeltaCpu                    | minDeltaCpu
                -1.00%                         | -1.00%                         | -1.00%


         [total gc info] =>
                 NAME                 | totalGCCount       | maxDeltaGCCount    | minDeltaGCCount    | totalGCTime        | maxDeltaGCTime     | minDeltaGCTime
                 PS MarkSweep         | 0                  | 0                  | 0                  | 0.000s             | 0.000s             | 0.000s
                 PS Scavenge          | 3                  | 3                  | 3                  | 0.507s             | 0.507s             | 0.507s

2023-02-03 03:38:33.834 [job-0] INFO  JobContainer - PerfTrace not enable!
2023-02-03 03:38:33.835 [job-0] INFO  StandAloneJobContainerCommunicator - Total 100000 records, 18788895 bytes | Speed 611.62KB/s, 3333 records/s | Error 0 records, 0 bytes |  All Task WaitWriterTime 12.627s |  All Task WaitReaderTime 2.227s | Percentage 100.00%
2023-02-03 03:38:33.836 [job-0] INFO  JobContainer -
任务启动时刻                    : 2023-02-03 03:38:01
任务结束时刻                    : 2023-02-03 03:38:33
任务总计耗时                    :                 32s
任务平均流量                    :          611.62KB/s
记录写入速度                    :           3333rec/s
读出记录总数                    :              100000
读写失败总数                    :                   0