实践练习四：迁移 MySQL 数据到 OceanBase 集群

原创许玉冲 2021-12-27

1160

1,初始化测试数据使用tpcc-mysql

下载地址：https://github.com/Percona-Lab/tpcc-mysql

[root@db01]# unzip tpcc-mysql-master.zip

[root@db01 tpcc-mysql]#
[root@db01 tpcc-mysql]# ls
add_fkey_idx.sql count.sql create_table.sql Dockerfile drop_cons.sql load_multi_schema.sh load.sh README.md schema2 scripts src

mysql> create database TPCH; Query OK, 1 row affected (0.06 sec)

MySQL [(none)]> use TPCH;
Database changed
MySQL [TPCH]> source create_table.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected, 1 warning (0.07 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.08 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MySQL [TPCH]>

[root@db01 tpcc-mysql]# ./tpcc_load -h 127.0.0.1 -P 32769 -d TPCH -u root -proot -w 1
*************************************
*** TPCC-mysql Data Loader ***
*************************************
option h with value '127.0.0.1'
option P with value '32769'
option d with value 'TPCH'
option u with value 'root'
option p with value 'root'
option w with value '1'
<Parameters>
[server]: 127.0.0.1
[port]: 32769
[DBname]: TPCH
[user]: root
[pass]: root
[warehouse]: 1
TPCC Data Load Started...
Loading Item

.................................................. 5000
.................................................. 10000
.................................................. 15000
.................................................. 20000
.................................................. 25000
.................................................. 30000
.................................................. 35000
.................................................. 40000
.................................................. 45000
.................................................. 50000
.................................................. 55000
.................................................. 60000
.................................................. 65000
.................................................. 70000
.................................................. 75000
.................................................. 80000
.................................................. 85000
.................................................. 90000
.................................................. 95000
.................................................. 100000
Stock Done.
Loading District
Loading Customer for DID=1, WID=1
.......... 1000
.......... 2000
.......... 3000
Customer Done.
Loading Customer for DID=2, WID=1
.......... 1000
.......... 2000
.......... 3000
Customer Done.
Loading Customer for DID=3, WID=1
.......... 1000
.......... 2000
.......... 3000
Customer Done.
Loading Customer for DID=4, WID=1
.......... 1000
.......... 2000
.......... 3000
Customer Done.
Loading Customer for DID=5, WID=1
.......... 1000
.......... 2000
.......... 3000
Customer Done.
Loading Customer for DID=6, WID=1
.......... 1000
.......... 2000
.......... 3000
Customer Done.
Loading Customer for DID=7, WID=1
.......... 1000
.......... 2000
.......... 3000
Customer Done.
Loading Customer for DID=8, WID=1
.......... 1000
.......... 2000
.......... 3000
Customer Done.
Loading Customer for DID=9, WID=1
.......... 1000
.......... 2000
.......... 3000
Customer Done.
Loading Customer for DID=10, WID=1
.......... 1000
.......... 2000
.......... 3000
Customer Done.
Loading Orders for D=1, W= 1
.......... 1000
.......... 2000
.......... 3000
Orders Done.
Loading Orders for D=2, W= 1
.......... 1000
.......... 2000
.......... 3000
Orders Done.
Loading Orders for D=3, W= 1
.......... 1000
.......... 2000
.......... 3000
Orders Done.
Loading Orders for D=4, W= 1
.......... 1000
.......... 2000
.......... 3000
Orders Done.
Loading Orders for D=5, W= 1
.......... 1000
.......... 2000
.......... 3000
Orders Done.
Loading Orders for D=6, W= 1
.......... 1000
.......... 2000
.......... 3000
Orders Done.
Loading Orders for D=7, W= 1
.......... 1000
.......... 2000
.......... 3000
Orders Done.
Loading Orders for D=8, W= 1
.......... 1000
.......... 2000
.......... 3000
Orders Done.
Loading Orders for D=9, W= 1
.......... 1000
.......... 2000
.......... 3000
Orders Done.
Loading Orders for D=10, W= 1
.......... 1000
.......... 2000
.......... 3000
Orders Done.

...DATA LOADING COMPLETED SUCCESSFULLY.

1，使用mysqldump迁移数据

1.1 导出数据

#导出指定数据库的表结构（不包括数据）[root@db01 dmp]# mysqldump -h 127.1 -uroot -P32769 -proot  -d TPCH --compact > tpch_ddl.sql#导出指定数据库的表数据（不包括结构）
[root@db01 dmp]# mysqldump -h 127.1 -uroot -P32769 -proot -t TPCH  > tpch_data.sql

这个导出来的脚本有几个特征：
视图的定义也会在里面，但是会以注释 /!/。视图我们不关注，这部分内容可以删除。
会有一些特别的语法 OceanBase MYSQL 会不支持，但是不影响，需要替换掉其中部分。比如说变量 SQL_NOTES，DEFINER 语句等。
下面这个示例就是导出的脚本里有一个 MAX_ROWS= 的设置，这个是 MySQL 特有的，OceanBase MySQL 没有这个问题，也不需要这个设置，不支持这个语法，会报错。
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `NATION` (
  `N_NATIONKEY` int(11) NOT NULL,
  `N_NAME` char(25) COLLATE utf8_unicode_ci NOT NULL,
  `N_REGIONKEY` int(11) NOT NULL,
  `N_COMMENT` varchar(152) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`N_NATIONKEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci MAX_ROWS=4294967295;
需要把所有 MAX_ROWS= 以及后面部分注释掉。使用批量替换技术。如在 vim 中使用 :%s/MAX_ROWS=/; -- MAX_ROWS=/g 。
注意：上面导出的 SQL 中表名是大写，说明源端 MySQL 里设置表名默认很可能是大小写敏感。因此目标 OceanBase MySQL 租户也要设置。
在导出的表结构语句里，可能包含外键。在导入 OceanBase MySQL 里时，如果外键依赖的表没有创建时，导入脚本会报错。因此导入之前需要将外键检查约束先禁用掉。
MySQL [oceanbase]> set global foreign_key_checks=off;
Query OK, 0 rows affected (0.01 sec)

MySQL [oceanbase]> show global variables like '%foreign%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | OFF   |
+--------------------+-------+
1 row in set (0.00 sec)
修改后，退出会话，重新登录。在 obclient 客户端里通过 source 命令可以执行外部 SQL 脚本文件。

1.2导入数据

[root@db01 ~]# obclient -h127.1 -uroot@sys -P2881 -prootPWD123 -c -A oceanbase
Welcome to the OceanBase.  Commands end with ; or \g.
Your MySQL connection id is 3221487779
Server version: 5.7.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:33:14)

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 [oceanbase]> create database TPCH;
Query OK, 1 row affected (0.071 sec)
MySQL [oceanbase]> USE TPCH;
Database changed
MySQL [TPCH]> source tpch_ddl.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

MySQL [TPCH]> source tpch_data.sql

2，使用datax迁移数据

软件下载：
wget http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz

2.1 软件安装和配置

[root@db01 dmp]# wget http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz
--2021-12-27 19:02:31--  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)... 47.110.23.120
Connecting to datax-opensource.oss-cn-hangzhou.aliyuncs.com (datax-opensource.oss-cn-hangzhou.aliyuncs.com)|47.110.23.120|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 853734462 (814M) [application/gzip]
Saving to: ?.atax.tar.gz?

100%[=======================================================================================================================================================================>] 853,734,462 9.92MB/s   in 70s    

2021-12-27 19:03:41 (11.7 MB/s) - ?.atax.tar.gz?.saved [853734462/853734462]

#软件安装，直接解压即可：tar -xf datax.tar.gzfind ./datax/plugin -name ".*" | xargs rm -f
#配置模板[root@db01 dmp]# python ./datax/bin/datax.py -r mysqlreader -w oceanbasev10writer
#配置文件，写入配置文件，并修改[root@db01 dmp]# python ./datax/bin/datax.py -r mysqlreader -w oceanbasev10writer > mysql2ob.json
#修改文件 mysqltoob.json
#启动任务[root@db01 ~]# python ./datax/bin/datax.py mysql2ob.json 
DataX (DATAX-OPENSOURCE-3.0), From Alibaba !
Copyright (C) 2010-2017, Alibaba Group. All Rights Reserved.

2.2 配置文件参考

{
    "job": {
        "setting": {
            "speed": {
                "channel": 16 
            },
            "errorLimit": {
                "record": 0,
                "percentage": 0.1
            }
        },
        "content": [
            {
                "reader": {
                    "name": "oceanbasev10reader",
                    "parameter": {
                        "where": "",
                        "readBatchSize": 10000,
                        "column": [
                            "*"
                        ],
                        "connection": [
                            {
                                "jdbcUrl": ["||_dsc_ob10_dsc_||obdemo:oboracle||_dsc_ob10_dsc_||jdbc:oceanbase://127.0.0.1:32769/TPCH"],
                                "table": [
                                    "bmsql_oorder"
                                ]
                            }
                        ],
                        "username": "root",
                        "password":"root"
                    }
                },
                "writer": {
                    "name": "mysqlwriter",
                    "parameter": {
                        "writeMode": "replace",
                        "username": "root",
                        "password": "root",
                        "column": [
                            "*"
                        ],
                        "session": [
                         "set session sql_mode='ANSI'"
                        ],
                        "preSql": [
                            "truncate table bmsql_oorder"
                        ],
                        "batchSize": 512,
                        "connection": [
                            {
                                "jdbcUrl": "jdbc:mysql://127.0.0.1:2881/TPCH?useUnicode=true&characterEncoding=utf8",
                                "table": [
                                    "bmsql_oorder"
                                ]
                            }
                        ]
                    }
                }
            }    
        ]
    }
}

2，参考地址

https://open.oceanbase.com/docs/tutorials/quickstart/V1.0.0/4-2

https://open.oceanbase.com/docs/tutorials/quickstart/V1.0.0/4-9-how-to-migrate-data-from-oceanbase-to-mysql-or

墨力计划 oceanbase征文

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者