迁移 MySQL 数据到 OceanBase 集群(mysqldump+datax)
文章目录
环境介绍
操作系统：redhat7.9
mysql数据库版本：5.7.26
mysql测试数据来源：GitHub - Percona-Lab/tpcc-mysql
oceanbase集群版本：3.1.0
准备tpcc测试数据
编译tpcc-mysql之前，需要配置mysql_config命令至PATH中
su - mysql
vi ~/.bash_profile
export PATH=$PATH:/mysql5.7.26/bin
source ~/.bash_profile
获取tpcc-mysql工具包并编译
su - mysql
wget https://github.com/Percona-Lab/tpcc-mysql/archive/refs/heads/master.zip
unzip master.zip
cd tpcc-mysql-master/src/
#编译
[mysql@rac03 src]$ make
cc -w -O3 -g -I. `mysql_config --include` -c load.c
cc -w -O3 -g -I. `mysql_config --include` -c support.c
cc load.o support.o `mysql_config --libs_r` -lrt -o ../tpcc_load
cc -w -O3 -g -I. `mysql_config --include` -c main.c
cc -w -O3 -g -I. `mysql_config --include` -c spt_proc.c
cc -w -O3 -g -I. `mysql_config --include` -c driver.c
cc -w -O3 -g -I. `mysql_config --include` -c sequence.c
cc -w -O3 -g -I. `mysql_config --include` -c rthist.c
cc -w -O3 -g -I. `mysql_config --include` -c sb_percentile.c
cc -w -O3 -g -I. `mysql_config --include` -c neword.c
cc -w -O3 -g -I. `mysql_config --include` -c payment.c
cc -w -O3 -g -I. `mysql_config --include` -c ordstat.c
cc -w -O3 -g -I. `mysql_config --include` -c delivery.c
cc -w -O3 -g -I. `mysql_config --include` -c slev.c
cc main.o spt_proc.o driver.o support.o sequence.o rthist.o sb_percentile.o neword.o payment.o ordstat.o delivery.o slev.o `mysql_config --libs_r` -lrt -o ../tpcc_start
创建tpcc数据
#创建tpcc1数据库
[mysql@rac03 src]$ mysqladmin create tpcc1 -uroot -p
Enter password:
#创建表结构
[mysql@rac03 tpcc-mysql-master]$ pwd
/software/mysql/tpcc-mysql-master
[mysql@rac03 tpcc-mysql-master]$ ll
total 304
-rw-rw-r-- 1 mysql mysql 1621 Jan 21 2017 add_fkey_idx.sql
-rw-rw-r-- 1 mysql mysql 317 Jan 21 2017 count.sql
-rw-rw-r-- 1 mysql mysql 3105 Jan 21 2017 create_table.sql
-rw-rw-r-- 1 mysql mysql 194 Jan 21 2017 Dockerfile
-rw-rw-r-- 1 mysql mysql 763 Jan 21 2017 drop_cons.sql
-rw-rw-r-- 1 mysql mysql 1079 Jan 21 2017 load_multi_schema.sh
-rw-rw-r-- 1 mysql mysql 573 Jan 21 2017 load.sh
-rw-rw-r-- 1 mysql mysql 2302 Jan 21 2017 README.md
drwxrwxr-x 2 mysql mysql 92 Jan 21 2017 schema2
drwxrwxr-x 5 mysql mysql 4096 Jan 21 2017 scripts
drwxrwxr-x 2 mysql mysql 4096 Mar 10 22:57 src
-rwxrwxr-x 1 mysql mysql 80968 Mar 10 22:57 tpcc_load
-rwxrwxr-x 1 mysql mysql 188336 Mar 10 22:57 tpcc_start
[mysql@rac03 tpcc-mysql-master]$
[mysql@rac03 tpcc-mysql-master]$ mysql tpcc1 -uroot -p < create_table.sql
Enter password:
#创建索引和外键等约束（为了提高数据插入效率，该步骤可以插入数据之后再执行）
[mysql@rac03 tpcc-mysql-master]$ mysql tpcc1 -uroot -p < add_fkey_idx.sql
Enter password:
#配置mysql用户的LD_LIBRARY_PATH
vi /home/mysql/.bash_profile
export LD_LIBRARY_PATH=/mysql/lib
#插入数据到tpcc1库中，这里只生成1个仓库（-w参数）
[mysql@rac03 tpcc-mysql-master]$ ./tpcc_load -h 127.0.0.1 -d tpcc1 -u root -p 'root' -w 1
*************************************
*** TPCC-mysql Data Loader ***
*************************************
option h with value '127.0.0.1'
option d with value 'tpcc1'
option u with value 'root'
option p with value 'root'
option w with value '1'
<Parameters>
[server]: 127.0.0.1
[port]: 3306
[DBname]: tpcc1
[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
Item Done.
Loading Warehouse
Loading Stock Wid=1
.................................................. 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.
校验数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| tpcc1 |
+--------------------+
5 rows in set (0.00 sec)
mysql> use tpcc1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-----------------+
| Tables_in_tpcc1 |
+-----------------+
| customer |
| district |
| history |
| item |
| new_orders |
| order_line |
| orders |
| stock |
| warehouse |
+-----------------+
9 rows in set (0.00 sec)
mysql> select count(*) from customer;
+----------+
| count(*) |
+----------+
| 30000 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from orders;
+----------+
| count(*) |
+----------+
| 30000 |
+----------+
1 row in set (0.01 sec)
使用mysqldump迁移数据
mysql数据库端导出数据
检查mysql端大小写敏感设置
mysql> show variables like '%lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
+------------------------+-------+
2 rows in set (0.00 sec)
mysql> show global variables like '%lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
+------------------------+-------+
2 rows in set (0.01 sec)
导出tpcc1数据库的表结构
[mysql@rac03 ~]$ mysqldump -h 127.0.0.1 -uroot -p -d tpcc1 --compact > tpcc1_ddl.sql
Enter password:
[mysql@rac03 ~]$ ll
total 8
-rw-rw-r-- 1 mysql mysql 7098 Mar 10 23:58 tpcc1_ddl.sql
#严格来说，导出表结构之后，需要检查sql里面是否有oceanbase-mysql不支持的语法，将其删除。
[mysql@rac03 ~]$ vi tpcc1_ddl.sql
导出tpcc1数据库的表数据
[mysql@rac03 ~]$ mysqldump -h 127.0.0.1 -uroot -p -t tpcc1 > tpcc1_data.sql
Enter password:
[mysql@rac03 ~]$ ll
total 81940
-rw-rw-r-- 1 mysql mysql 83895461 Mar 11 00:04 tpcc1_data.sql
-rw-rw-r-- 1 mysql mysql 7098 Mar 10 23:58 tpcc1_ddl.sql
[mysql@rac03 ~]$ view tpcc1_data.sql
oceanbase-mysql租户端导入数据
传输sql文件到oceanbase服务器
[mysql@rac03 ~]$ scp tpcc1_*.sql admin@11.11.11.14:/home/admin/
检查和修改大小写敏感设置
注意：如果租户的大小写设置和源库不一致，需要重新创建一个新租户，不能使用alter tenant的方式修改租户大小写设置，否则会使租户内的所有数据库无法打开。
show global variables like '%lower%';
create tenant yw charset='utf8mb4',zone_list=('zone1'),resource_pool_list=('pool1'),comment='业务租户1'
set ob_compatibility_mode=mysql,ob_tcp_invited_nodes='%',recyclebin = off,lower_case_table_names=0;
[admin@rac04 ~]$ obclient -h127.0.0.1 -P2883 -uroot@yw#obcluster -p -c -A
MySQL [(none)]> alter user root identified by 'root';
Query OK, 0 rows affected (0.016 sec)
MySQL [(none)]> show global variables like '%lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 0 |
+------------------------+-------+
1 row in set (0.015 sec)
MySQL [(none)]> show variables like '%lower%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 0 |
+------------------------+-------+
1 row in set (0.012 sec)
禁用外键约束检查（经过实践，在社区版本3.1.0中，即使关闭了外键约束检查，创建表时依赖的表不存在依然会报错，应该是bug）
MySQL [(none)]> SET GLOBAL foreign_key_checks=off;
Query OK, 0 rows affected (0.004 sec)
MySQL [(none)]> SET session foreign_key_checks=off;
Query OK, 0 rows affected (0.007 sec)
MySQL [(none)]> show global variables like '%foreign%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| foreign_key_checks | OFF |
+--------------------+-------+
1 row in set (0.006 sec)
MySQL [(none)]> show variables like '%foreign%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| foreign_key_checks | OFF |
+--------------------+-------+
1 row in set (0.006 sec)
创建tpcc1数据库
MySQL [(none)]> create database tpcc1;
Query OK, 1 row affected (0.010 sec)
导入表结构
MySQL [(none)]> use tpcc1;
Database changed
MySQL [tpcc1]> source /home/admin/tpcc1_ddl.sql
Query OK, 0 rows affected (0.005 sec)
Query OK, 0 rows affected (0.005 sec)
ERROR 1146 (42S02) at line 3 in file: '/home/admin/tpcc1_ddl.sql': Table doesn't exist
Query OK, 0 rows affected (0.004 sec)
Query OK, 0 rows affected (0.007 sec)
Query OK, 0 rows affected (0.003 sec)
ERROR 1146 (42S02) at line 32 in file: '/home/admin/tpcc1_ddl.sql': Table doesn't exist
Query OK, 0 rows affected (0.003 sec)
Query OK, 0 rows affected (0.009 sec)
Query OK, 0 rows affected (0.001 sec)
ERROR 1146 (42S02) at line 50 in file: '/home/admin/tpcc1_ddl.sql': Table doesn't exist
Query OK, 0 rows affected (0.002 sec)
Query OK, 0 rows affected (0.009 sec)
Query OK, 0 rows affected (0.002 sec)
Query OK, 0 rows affected, 1 warning (0.028 sec)
Query OK, 0 rows affected (0.002 sec)
Query OK, 0 rows affected (0.008 sec)
Query OK, 0 rows affected (0.003 sec)
ERROR 1146 (42S02) at line 78 in file: '/home/admin/tpcc1_ddl.sql': Table doesn't exist
Query OK, 0 rows affected (0.002 sec)
Query OK, 0 rows affected (0.008 sec)
Query OK, 0 rows affected (0.003 sec)
ERROR 1146 (42S02) at line 88 in file: '/home/admin/tpcc1_ddl.sql': Table doesn't exist
Query OK, 0 rows affected (0.003 sec)
Query OK, 0 rows affected (0.009 sec)
Query OK, 0 rows affected (0.001 sec)
ERROR 1146 (42S02) at line 107 in file: '/home/admin/tpcc1_ddl.sql': Table doesn't exist
Query OK, 0 rows affected (0.010 sec)
Query OK, 0 rows affected (0.010 sec)
Query OK, 0 rows affected (0.011 sec)
ERROR 1146 (42S02) at line 123 in file: '/home/admin/tpcc1_ddl.sql': Table doesn't exist
Query OK, 0 rows affected (0.011 sec)
Query OK, 0 rows affected (0.010 sec)
Query OK, 0 rows affected (0.011 sec)
Query OK, 0 rows affected, 1 warning (0.032 sec)
Query OK, 0 rows affected (0.010 sec)
MySQL [tpcc1]> show tables;
+-----------------+
| Tables_in_tpcc1 |
+-----------------+
| item |
| warehouse |
+-----------------+
2 rows in set (0.012 sec)
'
-- 经过实践，在社区版本3.1.0中，即使关闭了外键检查，有外键的表检测不到表依然会报错，
-- 最终只创建了2张表。多次调用/home/admin/tpcc1_ddl.sql可以解决。
MySQL [tpcc1]> source /home/admin/tpcc1_ddl.sql
MySQL [tpcc1]> source /home/admin/tpcc1_ddl.sql
MySQL [tpcc1]> source /home/admin/tpcc1_ddl.sql
MySQL [tpcc1]> source /home/admin/tpcc1_ddl.sql
MySQL [tpcc1]> show tables;
+-----------------+
| Tables_in_tpcc1 |
+-----------------+
| customer |
| district |
| history |
| item |
| new_orders |
| order_line |
| orders |
| stock |
| warehouse |
+-----------------+
9 rows in set (0.008 sec)
导入表数据（经过实践，ob无法识别“-- ”这样的单行注释，因此会报错ERROR 1064 (42000)，即语法错误。可以提前将这些注释删除）
MySQL [tpcc1]> source /home/admin/tpcc1_data.sql
Query OK, 0 rows affected (0.005 sec)
Query OK, 0 rows affected (0.005 sec)
ERROR 1064 (42000) at line 177 in file: '/home/admin/tpcc1_data.sql': You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near '--' at line 1
Query OK, 0 rows affected (0.003 sec)
Query OK, 0 rows affected (0.007 sec)
Query OK, 1 row affected (0.006 sec)
Query OK, 0 rows affected (0.004 sec)
……
Query OK, 0 rows affected (0.009 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.009 sec)
Query OK, 0 rows affected (0.001 sec)
检验数据（源库和ob查询比对）
select count(*) from customer ;
select count(*) from district ;
select count(*) from history ;
select count(*) from item ;
select count(*) from new_orders;
select count(*) from order_line;
select count(*) from orders ;
select count(*) from stock ;
select count(*) from warehouse ;
使用datax离线同步数据
我这里将datax服务器设在mysql数据库服务器上。将 MySQL 数据迁移到 OceanBase 数据库，如果源端和目标端不能同时跟 DataX 服务器网络联通，那么可以通过 CSV 文件中转。如果源端数据库和目标端数据库能同时跟 DataX 所在服务器联通，则可以使用 DataX 直接将数据从源端迁移到目标端。
datax运行环境要求：
环境检查
[mysql@rac03 ~]$ uname -a
Linux rac03 3.10.0-1160.el7.x86_64 #1 SMP Tue Aug 18 14:50:17 EDT 2020 x86_64 x86_64 x86_64 GNU/Linux
[mysql@rac03 ~]$ java -version
java version "1.8.0_201"
Java(TM) SE Runtime Environment (build 1.8.0_201-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.201-b09, mixed mode)
[mysql@rac03 ~]$ python --version
Python 2.7.5
下载datax
su - mysql
wget https://datax-opensource.oss-cn-hangzhou.aliyuncs.com/202309/datax.tar.gz
tar -xzf datax.tar.gz
cd datax/bin
[mysql@rac03 bin]$ ls
datax.py dxprof.py perftrace.py
编写json配置文件
vi datax_mysql-ob.json
{
"job": {
"setting": {
"speed": {
"channel": 4
},
"errorLimit": {
"record": 0,
"percentage": 0.1
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "root",//指定用户名及密码，实际调用时请删除本注释
"password": "root",
"column": [
"*"
],
"connection": [
{
"table": [
"t1"//指定表名，实际调用时请删除本注释
],
"jdbcUrl": ["jdbc:mysql://11.11.11.13:3306/tpcc1?useUnicode=true&characterEncoding=utf8"]//指定数据源，实际调用时请删除本注释
}
]
}
},
"writer": {
"name": "oceanbasev10writer",
"parameter": {
"obWriteMode": "insert",
"column": [
"*"
],
"preSql": [
"truncate table t1"//导入之前做的操作，实际调用时请删除本注释
],
"connection": [
{
"jdbcUrl": "||_dsc_ob10_dsc_||集群名:租户名||_dsc_ob10_dsc_||jdbc:oceanbase://11.11.11.14:2883/数据库名?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true",//指定数据源，实际调用时请删除本注释
"table": [
"t1"//指定表名，实际调用时请删除本注释//指定表名，实际调用时请删除本注释
]
}
],
"username": "root",//指定租户的用户名及密码，实际调用时请删除本注释
"password":"root",
"writerThreadCount": 4,
"batchSize": 1000,
"memstoreThreshold": "0.9"//为防止租户内存写满，设置写入限速
}
}
}
]
}
}
我的配置如下：
{
"job": {
"setting": {
"speed": {
"channel": 4
},
"errorLimit": {
"record": 0,
"percentage": 0.1
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "root",
"password": "root",
"column": [
"*"
],
"connection": [
{
"table": [
"t1"
],
"jdbcUrl": ["jdbc:mysql://11.11.11.13:3306/tpcc1?useUnicode=true&characterEncoding=utf8"]
}
]
}
},
"writer": {
"name": "oceanbasev10writer",
"parameter": {
"obWriteMode": "insert",
"column": [
"*"
],
"preSql": [
"truncate table t1"
],
"connection": [
{
"jdbcUrl": "||_dsc_ob10_dsc_||obcluster:yw||_dsc_ob10_dsc_||jdbc:oceanbase://11.11.11.14:2883/tpcc1?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true",
"table": [
"t1"
]
}
],
"username": "root",
"password":"root",
"writerThreadCount": 4,
"batchSize": 1000,
"memstoreThreshold": "0.9"
}
}
}
]
}
}
源库（mysql）创建表tpcc1.t1
mysql> create table tpcc1.t1(id int(10),name char(10));
mysql> insert into tpcc1.t1 values(1,'row1'),(2,'row2');
mysql> commit;
mysql> select * from tpcc1.t1;
+------+------+
| id | name |
+------+------+
| 1 | row1 |
| 2 | row2 |
+------+------+
2 rows in set (0.00 sec)
目标库（ob mysql）创建表t1，但不插入数据
MySQL [tpcc1]> create table tpcc1.t1(id int(10),name char(10));
Query OK, 0 rows affected (0.019 sec)
运行datax同步表t1
su - mysql
cd datax/
[mysql@rac03 datax]$ python bin/datax.py datax_mysql-ob.json
DataX (DATAX-OPENSOURCE-3.0), From Alibaba !
Copyright (C) 2010-2017, Alibaba Group. All Rights Reserved.
2024-03-11 13:47:22.144 [main] INFO MessageSource - JVM TimeZone: GMT+08:00, Locale: zh_CN
2024-03-11 13:47:22.146 [main] INFO MessageSource - use Locale: zh_CN timeZone: sun.util.calendar.ZoneInfo[id="GMT+08:00",offset=28800000,dstSavings=0,useDaylight=false,transitions=0,lastRule=null]
2024-03-11 13:47:22.159 [main] INFO VMInfo - VMInfo# operatingSystem class => sun.management.OperatingSystemImpl
2024-03-11 13:47:22.163 [main] INFO Engine - the machine info =>
osInfo: Linux amd64 3.10.0-1160.el7.x86_64
jvmInfo: Oracle Corporation 1.8 25.201-b09
cpu num: 4
totalPhysicalMemory: -0.00G
freePhysicalMemory: -0.00G
maxFileDescriptorCount: -1
currentOpenFileDescriptorCount: -1
GC Names [PS MarkSweep, PS Scavenge]
MEMORY_NAME | allocation_size | init_size
PS Eden Space | 256.00MB | 256.00MB
Code Cache | 240.00MB | 2.44MB
Compressed Class Space | 1,024.00MB | 0.00MB
PS Survivor Space | 42.50MB | 42.50MB
PS Old Gen | 683.00MB | 683.00MB
Metaspace | -0.00MB | 0.00MB
……
2024-03-11 13:47:32.665 [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 | 0 | 0 | 0 | 0.000s | 0.000s | 0.000s
2024-03-11 13:47:32.665 [job-0] INFO JobContainer - PerfTrace not enable!
2024-03-11 13:47:32.665 [job-0] INFO StandAloneJobContainerCommunicator - Total 2 records, 10 bytes | Speed 1B/s, 0 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 0.000s | All Task WaitReaderTime 0.000s | Percentage 100.00%
2024-03-11 13:47:32.666 [job-0] INFO JobContainer -
任务启动时刻 : 2024-03-11 13:47:22
任务结束时刻 : 2024-03-11 13:47:32
任务总计耗时 : 10s
任务平均流量 : 1B/s
记录写入速度 : 0rec/s
读出记录总数 : 2
读写失败总数 : 0
目标端（ob mysql）检查
MySQL [tpcc1]> select * from tpcc1.t1;
+------+------+
| id | name |
+------+------+
| 1 | row1 |
| 2 | row2 |
+------+------+
2 rows in set (0.001 sec)
避坑指导
- tpcc-mysql工具，依赖于mysql_config，以及MySQL的一些lib包，需要先在环境变量里面配置PATH和LD_LIBRARY_PATH。
- 在ob mysql端导入sql文件之前，一定要检查大小写敏感设置是否与源库一致，不一致就必须新建一个租户。不能使用alter tenant的方式修改租户大小写设置，否则会使租户内的所有数据库无法打开。
- 关于禁用外键约束检查（经过实践，在社区版本3.1.0中，即使关闭了外键约束检查，创建表时依赖的表不存在依然会报错，应该是bug）
- 由于bug的存在，导入表结构sql文件时报错，需要多次调用/home/admin/tpcc1_ddl.sql才可以创建完整的表。
- 导入表数据sql文件的时候，ob无法识别“-- ”这样的单行注释，因此会报错ERROR 1064 (42000)，即语法错误。可以提前将这些注释删除。
- 使用datax同步表时，需要注意json配置文件中的"preSql"部分，如果有truncate表这样的操作，初次同步之前，需要先在目标端创建好对应的表结构，否则会报错表不存在。
参考链接
工具 | 如何对 MySQL 进行 TPC-C 测试？ - 知乎 (zhihu.com)
实战教程第四章4.2：如何使用 mysqldump 迁移 MySQL 表 OceanBase-数据库技术博客-OceanBase分布式数据库
OceanBase 数据库 DataX 使用示例-OceanBase 数据库-OceanBase文档中心-分布式数据库使用文档
DataX/userGuid.md at master · alibaba/DataX · GitHub
DataX/introduction.md at master · alibaba/DataX · GitHub
DataX/mysqlwriter/doc/mysqlwriter.md at master · alibaba/DataX · GitHub
DataX/mysqlreader/doc/mysqlreader.md at master · alibaba/DataX · GitHub
2188
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
