准备MySQL环境
已有一台8.0.25，且已导入TPCC测试数据
这里建议直接使用5.7测试，后面测试DataX同步数据时8.0版本有些问题
[mysql@sd2 ~]$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 8.0.25 MySQL Community Server - GPL
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.
(root@localhost) [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| employees |
| information_schema |
| mysql |
| performance_schema |
| skye |
| sys |
| test |
| tpcc |
+--------------------+
8 rows in set (0.01 sec)
(root@localhost) [(none)]> use tpcc
Database changed
(root@localhost) [tpcc]> show tables;
+----------------+
| Tables_in_tpcc |
+----------------+
| customer |
| district |
| history |
| item |
| new_orders |
| order_line |
| orders |
| stock |
| warehouse |
+----------------+
9 rows in set (0.01 sec)
mysqldump导出tpcc数据库
[mysql@sd2 ~]$ mysqldump -uroot -pAsdf123. tpcc customer > customer.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[mysql@sd2 ~]$ scp customer.sql root@192.168.4.212:/root/
root@192.168.4.212's password:
customer.sql 100% 170MB 96.3MB/s 00:01
导入数据到OB
上面的customer.sql直接导入OB会报错，需要组一些数据清洗
1.去掉注释
2.因为只导出了一张表，该表有外键，去掉外键信息
3.去掉collate
然后再source到OB
obclient [db02]> source /root/customer.sql;
obclient [db02]> select count(*) from customer;
+----------+
| count(*) |
+----------+
| 300000 |
+----------+
1 row in set (0.410 sec)
#数据量与源库一致。
使用DataX离线迁移数据
安装DataX
[root@competet-oecanbase ~]# wget http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz
--2022-10-25 18:55:58-- 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.138
Connecting to datax-opensource.oss-cn-hangzhou.aliyuncs.com (datax-opensource.oss-cn-hangzhou.aliyuncs.com)|47.110.23.138|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 853734462 (814M) [application/gzip]
Saving to: ‘datax.tar.gz’
100%[======================================================================================================================================================================>] 853,734,462 1.14MB/s in 12m 34s
2022-10-25 19:08:35 (1.08 MB/s) - ‘datax.tar.gz’ saved [853734462/853734462]
[root@competet-oecanbase ~]# tar -zxvf datax.tar.gz -C /usr/local/
[root@competet-oecanbase ~]# cd /usr/local/datax
[root@competet-oecanbase datax]# find /usr/local/datax/plugin -name ".*" | xargs rm -f
#安装java，这里如果python没有安装也需要安装
[root@competet-oecanbase datax]# yum -y install java
修改同步配置
可以在官方文档拷贝模板：https://www.oceanbase.com/docs/community-observer-cn-10000000000449924
其中ob02:obmysql中ob02是集群名，obmysql是租户名，database和用户名都是tpcc
[root@competet-oecanbase job]# cat mytoob.json
{
"job": {
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"column": ["*"],
"connection": [
{
"jdbcUrl": ["jdbc:mysql://192.168.3.235:3305/tpcc"],
"table": ["warehouse"]
}
],
"password": "Asdf123.",
"username": "skye",
"where": ""
}
},
"writer": {
"name": "oceanbasev10writer",
"parameter": {
"column": ["*"],
"connection": [
{
"jdbcUrl": "||_dsc_ob10_dsc_||ob02:obmysql||_dsc_ob10_dsc_||jdbc:oceanbase://127.0.0.1:2883/tpcc",
"table": ["warehouse"]
}
],
"obWriteMode": "insert",
"password": "123456",
"username": "tpcc"
}
}
}
],
"setting": {
"speed": {
"channel": "2"
}
}
}
}
同步数据MySQL5.7 tpcc.warehouse 到OB tpcc.warehouse
最开始使用的MySQL 8.0版本，测试连接一直报错，使用5.7版本可行。
报错信息：java.lang.Exception: DataX无法连接对应的数据库，可能原因是：1) 配置的ip/port/database/jdbc错误，无法连接。2) 配置的username/password错误，鉴权失败。请和DBA确认该数据库的连接信息是否正确。
在ob02建表warehouse
obclient [db02]> CREATE TABLE `warehouse` (
-> `w_id` smallint NOT NULL,
-> `w_name` varchar(10) DEFAULT NULL,
-> `w_street_1` varchar(20) DEFAULT NULL,
-> `w_street_2` varchar(20) DEFAULT NULL,
-> `w_city` varchar(20) DEFAULT NULL,
-> `w_state` char(2) DEFAULT NULL,
-> `w_zip` char(9) DEFAULT NULL,
-> `w_tax` decimal(4,2) DEFAULT NULL,
-> `w_ytd` decimal(12,2) DEFAULT NULL,
-> PRIMARY KEY (`w_id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected, 1 warning (0.084 sec)
执行同步
[root@competet-oecanbase job]# python2 /usr/local/datax/bin/datax.py /usr/local/datax/job/mytoob.json
在OB确认数据
obclient [tpcc]> select * from warehouse;
+------+------------+----------------------+--------------------+---------------------+---------+-----------+-------+-----------+
| w_id | w_name | w_street_1 | w_street_2 | w_city | w_state | w_zip | w_tax | w_ytd |
+------+------------+----------------------+--------------------+---------------------+---------+-----------+-------+-----------+
| 1 | ntEtmZ | Zuo7B9I1V5EoOwaw57Cw | iREVJQP5xicqXk1g0 | 95CxR6WNBdY8eEX | hq | 033189080 | 0.18 | 300000.00 |
| 2 | WTb7Zmrs | V7ebOjg7NGUTsp5rks | nwxHPWOyIErwjwYIIF | QaEu14i6uQyYEuVVK2 | II | 143924438 | 0.15 | 300000.00 |
| 3 | ZhwXG49EG0 | 22jfXINuGgwLyJ98AX | 17yvcCxjPEjOEk8uH | H9ew3xsM5yXa | Yh | 202595578 | 0.18 | 300000.00 |
| 4 | pj3yksH | 2FuvUJU8owGdfYzEi | MiAQU0AVysNDxO | sJx9m3viIYLqXXWQuC | If | 887309627 | 0.14 | 300000.00 |
| 5 | 5G5J5njo5 | V9giU8yTRc | WAPGKr2SDgVUlp | WWKcTpl9VDFSgh49DEW | WN | 516018700 | 0.10 | 300000.00 |
| 6 | LasBZymZX | qVRMhZwK4RsOybagxcj | g4dXDCVzj2xYVMu | woVyDMMCxxqsX | vD | 046753410 | 0.14 | 300000.00 |
| 7 | 5iJB7nugp | fpcLx7FTvqPcXpw3 | vpfkuM13Tmv | ctnRT6MZLqUAD6h10i | vV | 703857739 | 0.14 | 300000.00 |
| 8 | dBxL3dfG | KlPKPmkmoN2UAwB8bf | CEroNo9RPofK6R | OoV6bHTdlcYui9 | gJ | 137300669 | 0.19 | 300000.00 |
| 9 | pHLywbbvev | C0xtp4SIj9iKUCVov | hapzvnvUNq | GUIEPB4UbKBk2VCD | y8 | 222730659 | 0.20 | 300000.00 |
| 10 | wMxSL6 | aG0cNXzDUK | F8Po6oLjEu6461O1 | h7LHNJtiqruI9IXHh | LT | 969565646 | 0.14 | 300000.00 |
+------+------------+----------------------+--------------------+---------------------+---------+-----------+-------+-----------+
10 rows in set (0.002 sec)