使用 mysqldump 将 mysql的表结构和数据同步到 OceanBase 的MySQL 租户中
源库:MySQL 5.7,数据库:sbtest,10张表,每张表10万行数据
目标库:OB 3.1.1,数据库:sbtest,租户:obmysql
安装sysbench创造数据
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | bash
yum -y install sysbench
sysbench --version
开始
mysql -uroot -p123456Admin@123 -h192.168.1.246
创建sbtest库
create database sbtest;
将数据导出
[root@cts07 ~]# mysqldump -uroot -p123456Admin@123 -h192.168.1.246 -P3306 --single-transaction --hex-blob --routines --events --triggers --master-data=2 --set-gtid-purged=OFF --databases sbtest --default-character-set=utf8 --max_allowed_packet=512M > sbtest.sql
[root@cts07 ~]# ll -h sbtest.sql
-rw-r--r-- 1 root root 189M Feb 3 02:11 sbtest.sql
用source导入
[admin@cts07 obproxy-3.1.0]$ obclient -h 127.1 -uroot@obmysql#obdemo -P2883 -p -c -A test
Enter password:
Welcome to the OceanBase. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 5.6.25 OceanBase 3.1.0 (r-) (Built May 30 2021 11:21:29)
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 [test]> show databases;
+--------------------+
| Database |
+--------------------+
| oceanbase |
| information_schema |
| mysql |
| test |
+--------------------+
4 rows in set (0.012 sec)
MySQL [test]> source /data/sbtest.sql
Query OK, 5290 rows affected (2.499 sec)
Records: 5290 Duplicates: 0 Warnings: 0
Query OK, 5282 rows affected (1.776 sec)
Records: 5282 Duplicates: 0 Warnings: 0
Query OK, 5258 rows affected (1.868 sec)
Records: 5258 Duplicates: 0 Warnings: 0
Query OK, 5258 rows affected (2.013 sec)
Records: 5258 Duplicates: 0 Warnings: 0
Query OK, 5258 rows affected (0.807 sec)
Records: 5258 Duplicates: 0 Warnings: 0
Query OK, 5258 rows affected (0.457 sec)
Records: 5258 Duplicates: 0 Warnings: 0
Query OK, 5258 rows affected (1.392 sec)
Records: 5258 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.002 sec)
Query OK, 0 rows affected (0.002 sec)
Query OK, 0 rows affected (0.001 sec)
Query OK, 0 rows affected (0.004 sec)
Query OK, 0 rows affected (0.003 sec)
Query OK, 0 rows affected (0.003 sec)
Query OK, 0 rows affected (0.010 sec)
Query OK, 0 rows affected (0.006 sec)
Query OK, 0 rows affected (0.001 sec)
MySQL [sbtest]> show databases;
+--------------------+
| Database |
+--------------------+
| oceanbase |
| information_schema |
| mysql |
| test |
| sbtest |
+--------------------+
5 rows in set (0.025 sec)
MySQL [sbtest]> show tables;
+------------------+
| Tables_in_sbtest |
+------------------+
| sbtest1 |
| sbtest10 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
| sbtest6 |
| sbtest7 |
| sbtest8 |
| sbtest9 |
+------------------+
10 rows in set (0.008 sec)
MySQL [sbtest]> select count(1) from sbtest1;
+----------+
| count(1) |
+----------+
| 100000 |
+----------+
1 row in set (0.311 sec)
使用datax (离线)从 MySQL 同步表数据到 OceanBase
使用 datax 配置至少一个表的 MySQL 到 OceanBase 的 MySQL 租户的离线同步。
部署DATAX软件,只需改配置文件
[root@cts07 ~]# wget http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz
--2023-02-03 03:05:30-- 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)... 121.199.204.252
Connecting to datax-opensource.oss-cn-hangzhou.aliyuncs.com (datax-opensource.oss-cn-hangzhou.aliyuncs.com)|121.199.204.252|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 853734462 (814M) [application/gzip]
Saving to: ‘datax.tar.gz’
100%[============================================================================================================================================================>] 853,734,462 9.68MB/s in 90s
2023-02-03 03:07:02 (9.05 MB/s) - ‘datax.tar.gz’ saved [853734462/853734462]
[root@cts07 ~]# tar -xf datax.tar.gz -C /usr/local/
[root@cts07 ~]# cd /usr/local/datax
[root@cts07 datax]# find /usr/local/datax/plugin -name ".*" | xargs rm -f
[root@cts07 datax]# yum install -y python2 java
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirrors.aliyun.com
* extras: mirrors.huaweicloud.com
* updates: mirrors.huaweicloud.com
akopytov_sysbench/x86_64/signature
Updated:
python.x86_64 0:2.7.5-92.el7_9
Dependency Updated:
python-libs.x86_64 0:2.7.5-92.el7_9
Complete!
-- 生成模板文件
python2 /usr/local/datax/bin/datax.py -r mysqlreader -w oceanbasev10writer > /usr/local/datax/job/my2ob.json
# 根据实际环境修改
vi /usr/local/datax/job/my2ob.json
[root@cts07 datax]# cat /usr/local/datax/job/my2ob.json
{
"job": {
"setting": {
"speed": {
"channel": 2,
},
"errorLimit": {
"record": 10
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "root",
"password": "123456Admin@123",
"splitPk": "id",
"column": ["*"],
"connection": [
{
"jdbcUrl": [
"jdbc:mysql://192.168.1.246:3306/sbtest"
],
"table": [
"sbtest1"
]
}
]
}
},
"writer": {
"name": "oceanbasev10writer",
"parameter": {
"writeMode": "insert",
"username": "root",
"password": "123456Admin@123",
"writerThreadCount": 5,
"column": [
"*"
],
"connection": [
{
"jdbcUrl": "||_dsc_ob10_dsc_||obce-single:obmysql||_dsc_ob10_dsc_||jdbc:oceanbase://127.0.0.1:2883/sbtest?useUnicode=true&characterEncoding=utf-8",
"table": [
"sbtest1"
]
}
]
}
}
}
]
}
}
启动服务,可以看到日志
[root@cts07 datax]# python2 /usr/local/datax/bin/datax.py /usr/local/datax/job/my2ob.json
2023-02-03 03:38:25.502 [taskGroup-0] INFO TaskGroupContainer - taskGroup[0] taskId[9] is successed, used[3258]ms
2023-02-03 03:38:25.503 [taskGroup-0] INFO TaskGroupContainer - taskGroup[0] taskId[10] is successed, used[408]ms
2023-02-03 03:38:25.503 [taskGroup-0] INFO TaskGroupContainer - taskGroup[0] completed it's tasks.
2023-02-03 03:38:33.829 [job-0] INFO StandAloneJobContainerCommunicator - Total 100000 records, 18788895 bytes | Speed 1.08MB/s, 6000 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 12.627s | All Task WaitReaderTime 2.227s | Percentage 100.00%
2023-02-03 03:38:33.830 [job-0] INFO AbstractScheduler - Scheduler accomplished all tasks.
2023-02-03 03:38:33.830 [job-0] INFO JobContainer - DataX Writer.Job [oceanbasev10writer] do post work.
2023-02-03 03:38:33.830 [job-0] INFO JobContainer - DataX Reader.Job [mysqlreader] do post work.
2023-02-03 03:38:33.831 [job-0] INFO JobContainer - DataX jobId [0] completed successfully.
2023-02-03 03:38:33.832 [job-0] INFO HookInvoker - No hook invoked, because base dir not exists or is a file: /usr/local/datax/hook
2023-02-03 03:38:33.834 [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 | 3 | 3 | 3 | 0.507s | 0.507s | 0.507s
2023-02-03 03:38:33.834 [job-0] INFO JobContainer - PerfTrace not enable!
2023-02-03 03:38:33.835 [job-0] INFO StandAloneJobContainerCommunicator - Total 100000 records, 18788895 bytes | Speed 611.62KB/s, 3333 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 12.627s | All Task WaitReaderTime 2.227s | Percentage 100.00%
2023-02-03 03:38:33.836 [job-0] INFO JobContainer -
任务启动时刻 : 2023-02-03 03:38:01
任务结束时刻 : 2023-02-03 03:38:33
任务总计耗时 : 32s
任务平均流量 : 611.62KB/s
记录写入速度 : 3333rec/s
读出记录总数 : 100000
读写失败总数 : 0