实践练习四(必选):迁移 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 可执行二进制文件:
-
运行以下命令,下载源代码。
git clone https://github.com/alibaba/DataX.git
-
注释
DataX/pom.xml
文件中的以下内容。<module>oscarwriter</module> <module>mongodbreader</module> <module>mongodbwriter</module>
-
运行以下命令,使用 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 2月 17 11:03 bin
drwxrwxr-x. 2 aicenter aicenter 4096 2月 17 11:03 conf
drwxrwxr-x. 2 aicenter aicenter 4096 2月 17 11:03 job
drwxrwxr-x. 2 aicenter aicenter 4096 2月 17 11:03 lib
drwxrwxr-x. 4 aicenter aicenter 4096 2月 17 11:03 plugin
drwxrwxr-x. 2 aicenter aicenter 4096 2月 17 11:03 script
drwxrwxr-x. 2 aicenter aicenter 4096 2月 17 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)
表数据迁移成功!