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

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

练习目的

本次练习目的掌握从 MySQL 向 OceanBase 迁移数据的基本方法:mysqldump、datax 、canal 等。

练习条件

  • 有 服务器,内存资源至少 12G*1台 ,部署有 OceanBase 集群。
  • 有 mysql 数据库,有少量业务表和数据(数据量不要求很多)。没有业务表和数据的就推荐使用 sysbenc、 tpcc 或者 tpch 的业务数据。

练习内容

请记录并分享下列内容:

  • (必选)使用 mysqldump 将 mysql的表结构和数据同步到 OceanBase 的MySQL 租户中。
  • (必选)使用 datax 配置至少一个表的 MySQL 到 OceanBase 的 MySQL 租户的离线同步。
  • (可选)使用 datax 配置至少一个表的 OceanBase 到 CSV 以及 CSV 到 OceanBase 的离线同步。
  • (可选)使用 canal 配置 MySQL 的增量同步到 OceanBase 的 MySQL 租户。

1. 使用 mysqldump 同步表结构和数据

1.1 查看实践迁移的表信息

root@kylin:~# ./getip.sh
New ip is 172.31.81.117
root@kylin:~# service mysql start
 * Starting MySQL database server mysqld                                                                                                 [ OK ]
root@kylin:~# mysql -h10.18.104.140 -P3306 -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4914874
Server version: 5.7.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show tables from dataxweb;
+---------------------+
| Tables_in_dataxweb  |
+---------------------+
| job_group           |
| job_info            |
| job_jdbc_datasource |
| job_lock            |
| job_log             |
| job_log_report      |
| job_logglue         |
| job_permission      |
| job_project         |
| job_registry        |
| job_template        |
| job_user            |
+---------------------+
12 rows in set (0.00 sec)

mysql> exit
Bye

1.2 分别导出 dataxweb 库的 Schema 和 Data

mysqldump -h10.18.104.140 -uroot -p -d dataxweb > dataxweb_schema.sql
mysqldump -h10.18.104.140 -uroot -p dataxweb > dataxweb_datas.sql 

# mysql 中如果有表用了 latin1 字符集,导入会报错。
sed -i 's/CHARACTER\ SET\ latin1//g' dataxweb_schema.sql
sed -i 's/CHARACTER\ SET\ latin1//g' dataxweb_datas.sql 

1.3 执行导入

# 这里用的是 docker 容器,查看容器 ip 用下面语句。
#  docker inspect --format '{{ .NetworkSettings.IPAddress }}' $'oceanbase-ce'

[aicenter@aicenter2 ~]$ mysql -h 172.17.0.2 -uroot@obmysql -P2883 -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:52:05)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> create database dataxweb; 
Query OK, 1 row affected (0.30 sec)

mysql> use dataxweb;
Database changed
mysql> source dataxweb_schema.sql
......省去导入过程
mysql> show tables;
+---------------------+
| Tables_in_dataxweb  |
+---------------------+
| job_group           |
| job_info            |
| job_jdbc_datasource |
| job_lock            |
| job_log             |
| job_log_report      |
| job_logglue         |
| job_permission      |
| job_project         |
| job_registry        |
| job_template        |
| job_user            |
+---------------------+
12 rows in set (0.00 sec)

mysql> source dataxweb_datas.sql;
......省去导入过程
mysql> select * from job_user;
+----+----------+--------------------------------------------------------------+------------+------------+
| id | username | password                                                     | role       | permission |
+----+----------+--------------------------------------------------------------+------------+------------+
|  1 | admin    | $2a$10$2KCqRbra0Yn2TwvkZxtfLuWuUP5KyCWsljO/ci5pLD27pqR3TV1vy | ROLE_ADMIN | NULL       |
+----+----------+--------------------------------------------------------------+------------+------------+
1 row in set (0.01 sec)

2.使用 datax 离线同步数据

2.1 编译 DataX 可执行二进制文件

按以下步骤编译 DataX 可执行二进制文件:

  1. 运行以下命令,下载源代码。

    git clone https://github.com/alibaba/DataX.git
    
  2. 注释 DataX/pom.xml 文件中的以下内容。

    <module>oscarwriter</module>
    <module>mongodbreader</module>
    <module>mongodbwriter</module>
    
  3. 运行以下命令,使用 Maven 将 DataX 打包。

    $ cd  {DataX_source_code_home}
    $ mvn -U clean package assembly:assembly -Dmaven.test.skip=true
    # Windows PowerShell 需要将 -Dmaven.test.skip=true 使用单引号引起来!
    

    本地打包成功!

    在这里插入图片描述

2.2 Tarball 上传与解压

目标机器上解压 datax 的tar包:

[aicenter@aicenter2 package]$ tar -zxf datax.tar.gz -C /data/aicenter/software/
[aicenter@aicenter2 package]$ cd ../software/datax/
[aicenter@aicenter2 datax]$ ll
总用量 28
drwxrwxr-x. 2 aicenter aicenter 4096 217 11:03 bin
drwxrwxr-x. 2 aicenter aicenter 4096 217 11:03 conf
drwxrwxr-x. 2 aicenter aicenter 4096 217 11:03 job
drwxrwxr-x. 2 aicenter aicenter 4096 217 11:03 lib
drwxrwxr-x. 4 aicenter aicenter 4096 217 11:03 plugin
drwxrwxr-x. 2 aicenter aicenter 4096 217 11:03 script
drwxrwxr-x. 2 aicenter aicenter 4096 217 11:03 tmp

2.3 迁移前确认与准备

  • 确认 mysql 写出目标及Schema导出
mysql> show tables from test;
+----------------+
| Tables_in_test |
+----------------+
| DWD_CALENDAR   |
+----------------+
1 row in set (0.04 sec)

mysql> show create table DWD_CALENDAR;
ERROR 1046 (3D000): No database selected
mysql> show create table test.DWD_CALENDAR;
+--------------+--------------------------------------------------------------------------------------------------+
| Table        | Create Table  |
+--------------+--------------------------------------------------------------------------------------------------+
| DWD_CALENDAR | CREATE TABLE `DWD_CALENDAR` (
  `SITE` varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
  `FACTORY` varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
  `PERIOD_DATE` date DEFAULT NULL,
  `PERIOD` varchar(5) COLLATE utf8mb4_bin DEFAULT NULL,
  `SHIFT_START_TIMEKEY` varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
  `SHIFT_END_TIMEKEY` varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
  `DATE_START_TIMEKEY` varchar(15) COLLATE utf8mb4_bin DEFAULT NULL,
  `DATE_END_TIMEKEY` varchar(15) COLLATE utf8mb4_bin DEFAULT NULL,
  `SHIFT_TIMEKEY` varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
  `SHIFT_NAME` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `DATE_TIMEKEY` varchar(15) COLLATE utf8mb4_bin DEFAULT NULL,
  `WEEK_TIMEKEY` varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
  `MONTH_TIMEKEY` varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
  `QUARTER_TIMEKEY` varchar(21) COLLATE utf8mb4_bin DEFAULT NULL,
  `YEAR_TIMEKEY` varchar(16) COLLATE utf8mb4_bin DEFAULT NULL,
  `INTERFACE_TIME` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+--------------+--------------------------------------------------------------------------------------------------+
1 row in set (0.17 sec)
mysql> exit
Bye

# 导出schema
[aicenter@aicenter2 ~]$ mysqldump -uroot -p -d test > dad_calendar_schema.sql

  • 确认 OceanBase 写入目标及创建数据迁移用户
# 确认 test 库中的表信息
mysql> show tables from test;
Empty set (0.01 sec)
# 创建 数据迁移用户 obuser
mysql> CREATE USER 'obuser'@'%' IDENTIFIED BY '123456';  
Query OK, 0 rows affected (0.08 sec)
# 赋权
mysql> GRANT ALL ON test.* TO 'obuser'@'%';              
Query OK, 0 rows affected (0.03 sec)
mysql> exit
Bye
# obuser 用户登录
[aicenter@aicenter2 ~]$ mysql -h 172.17.0.2 -uobuser@obmysql -P2883 -p -Dtest
Enter password: 
ERROR 1045 (42000): Access denied for user 'obuser'@'xxx.xxx.xxx.xxx' (using password: NO)
[aicenter@aicenter2 ~]$ mysql -h 172.17.0.2 -uobuser@obmysql -P2883 -p -Dtest
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:52:05)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.01 sec)


2.4 向 OceanBase test 库 导入表 schema

mysql> show tables from test;
Empty set (0.00 sec)

mysql> use test;
Database changed
mysql> source dad_calendar_schema.sql;
......此处省略导入过程
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| dwd_calendar   |
+----------------+
1 row in set (0.00 sec)

mysql> show create table ^C
mysql> show create table dwd_calendar;
+--------------+--------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                     |
+--------------+--------------------------------------------------------------------------------------------------+
| dwd_calendar | CREATE TABLE `dwd_calendar` (
  `SITE` varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
  `FACTORY` varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
  `PERIOD_DATE` date DEFAULT NULL,
  `PERIOD` varchar(5) COLLATE utf8mb4_bin DEFAULT NULL,
  `SHIFT_START_TIMEKEY` varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
  `SHIFT_END_TIMEKEY` varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
  `DATE_START_TIMEKEY` varchar(15) COLLATE utf8mb4_bin DEFAULT NULL,
  `DATE_END_TIMEKEY` varchar(15) COLLATE utf8mb4_bin DEFAULT NULL,
  `SHIFT_TIMEKEY` varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
  `SHIFT_NAME` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `DATE_TIMEKEY` varchar(15) COLLATE utf8mb4_bin DEFAULT NULL,
  `WEEK_TIMEKEY` varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
  `MONTH_TIMEKEY` varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
  `QUARTER_TIMEKEY` varchar(21) COLLATE utf8mb4_bin DEFAULT NULL,
  `YEAR_TIMEKEY` varchar(16) COLLATE utf8mb4_bin DEFAULT NULL,
  `INTERFACE_TIME` date DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = COMPACT COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+--------------+--------------------------------------------------------------------------------------------------+
1 row in set (0.19 sec)

2.5 准备 job json 及执行

[aicenter@aicenter2 job]$ touch mysql2ob-dad_calendar.json
[aicenter@aicenter2 job]$ vim mysql2ob-dad_calendar.json 
[aicenter@aicenter2 job]$ cat mysql2ob-dad_calendar.json 
{
   "job": {
       "setting": {
           "speed": {
               "channel": 4
           },
           "errorLimit": {
               "record": 0,
               "percentage": 0.1
           }
       },
       "content": [
           {
               "reader": {
                   "name": "mysqlreader",
                   "parameter": {
                       "username": "root",
                       "password": "******",
                       "column": [
                           "*"
                       ],
                       "connection": [
                           {
                               "table": [
                                   "DWD_CALENDAR"
                               ],
                               "jdbcUrl": ["jdbc:mysql://10.18.104.141:3306/test?useUnicode=yes&useCursorFetch=true&useCompression=true&characterEncoding=utf8"]
                           }
                       ]
                   }
               },

               "writer": {
                   "name": "oceanbasev10writer",
                   "parameter": {
                       "obWriteMode": "insert",
                       "column": [
                           "*"
                       ],
                       "preSql": [
                           "truncate table dwd_calendar"
                       ],
                       "connection": [
                           {
                               "jdbcUrl": "||_dsc_ob10_dsc_||obdemo:obmysql||_dsc_ob10_dsc_||jdbc:oceanbase://172.17.0.2:2883/test?useUnicode=true&characterEncoding=utf-8",
                               "table": [
                                   "dwd_calendar"
                               ]
                           }
                       ],
                       "username": "obuser",
                       "password":"123456",
                       "writerThreadCount":10,
                       "batchSize": 1000,
                       "memstoreThreshold": "0.9"
                   }
               }
           }
       ]
   }
}

[aicenter@aicenter2 job]$ cd ..
# 启动 datax 执行
[aicenter@aicenter2 datax]$ python ./bin/datax.py ./job/mysql2ob-dad_calendar.json 
......
2022-02-17 10:32:48.524 [job-0] INFO  JobContainer - PerfTrace not enable!
2022-02-17 10:32:48.525 [job-0] INFO  StandAloneJobContainerCommunicator - Total 5800 records, 471425 bytes | Speed 46.04KB/s, 580 records/s | Error 0 records, 0 bytes |  All Task WaitWriterTime 0.273s |  All Task WaitReaderTime 0.008s | Percentage 100.00%
2022-02-17 10:32:48.528 [job-0] INFO  JobContainer -
任务启动时刻                    : 2022-02-17 10:32:32
任务结束时刻                    : 2022-02-17 10:32:48
任务总计耗时                    :                 15s
任务平均流量                    :           46.04KB/s
记录写入速度                    :            580rec/s
读出记录总数                    :                5800
读写失败总数                    :                   0

2.6 验证数据导入

aicenter@aicenter2 ~]$ mysql -h 172.17.0.2 -uobuser@obmysql -P2883 -p -Dtest
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.6.25 OceanBase 3.1.1 (r4-8c615943cbd25a6f7b8bdfd8677a13a21709a05e) (Built Oct 21 2021 10:52:05)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

mysql> select count(1) from dwd_calendar;
+----------+
| count(1) |
+----------+
|     5800 |
+----------+
1 row in set (0.00 sec)

表数据迁移成功!

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值