一、目标二、环境准备三、实施
1、使用OBD安装Oceanbase2、安装mysql实例3、sysbench数据准备4、使用mysqldump离线同步数据5、使用datax (离线)从MySQL同步表数据到 Oceanbase6、(可选)使用datax配置Oceanbase和CSV之间的同步7、(可选)使用canal 配置 MySQL到 Oceanbase 的增量同步
参考：https://open.oceanbase.com/docs/tutorials/quickstart/V1.0.0/4-2
https://www.modb.pro/db/222647等
1.使用 mysqldump 将 mysql的表结构和数据同步到 Oceanbase 的MySQL 租户中。
2.使用 datax 配置至少一个表的 MySQL 到 Oceanbase 的 MySQL 租户的离线同步。
3.(可选)使用 datax 配置至少一个表的 Oceanbase 到 CSV 以及 CSV 到 Oceanbase 的离线同步。
4.(可选)使用 canal 配置 MySQL 的增量同步到 Oceanbase 的 MySQL 租户。
| Host | Cpu | Memory | Disk | Os version |
|---|---|---|---|---|
| Oceanbase01 | 4 | 16G | 50G | CentOS Linux release 7.9.2009 (Core) |
创建admin用户
[admin@oceanbase01 ~]$ useradd admin
[admin@oceanbase01 ~]$ passwd admin
安装OBD
yum install -y yum-utils
yum-config-manager --add-repo https://mirrors.aliyun.com/oceanbase/Oceanbase.repo
yum install -y ob-deploy
配置yaml文件
[admin@oceanbase01 soft]$ vi single.yaml
oceanbase-ce:
servers:
- 172.17.0.15
global:
home_path: /home/admin/oceanbase-ce
data_dir: /data
redo_dir: /redo
devname: eth0
mysql_port: 2881 # External port for Oceanbase Database. The default value is 2881.
rpc_port: 2882 # Internal port for Oceanbase Database. The default value is 2882.
zone: zone1
cluster_id: 1
memory_limit: 8G # The maximum running memory for an observer
system_memory: 3G # The reserved system memory. system_memory is reserved for general tenants. The default value is 30G.
stack_size: 512K
cpu_count: 16
cache_wash_threshold: 1G
__min_full_resource_pool_memory: 268435456
workers_per_cpu_quota: 10
schema_history_expire_time: 1d
net_thread_count: 4
major_freeze_duty_time: Disable
minor_freeze_times: 10
enable_separate_sys_clog: 0
enable_merge_by_turn: FALSE
datafile_size: 10G
syslog_level: WARN # System log level. The default value is INFO.
enable_syslog_wf: false # Print system logs whose levels are higher than WARNING to a separate log file. The default value is true.
enable_syslog_recycle: true # Enable auto system log recycling or not. The default value is false.
max_syslog_file_count: 10 # The maximum number of reserved log files before enabling auto recycling. The default value is 0.
root_password: ‘Welcome123$’ # root user password, can be empty
obproxy:
depends:
- oceanbase-ce
servers:
- 172.17.0.15
global:
listen_port: 2883 # External port. The default value is 2883.
prometheus_listen_port: 2884 # The Prometheus port. The default value is 2884.
home_path: /home/admin/obproxy
enable_cluster_checkout: false
cluster_name: obcluster
skip_proxy_sys_private_check: true
使用OBD安装集群
[admin@oceanbase01 soft]$ obd cluster deploy obce-single -c single.yaml
登录数据库创建资源和租户
[admin@oceanbase01 ~]$ obclient -h127.1 -uroot@sys -P5883 -p -c -A oceanbase
Enter password: Welcome123$
Welcome to the Oceanbase. Commands end with ; or g.
Your MySQL connection id is 6
Server version: 5.6.25 Oceanbase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)
Copyright © 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.
MySQL [oceanbase]> show databases;
±-------------------+
| Database |
±-------------------+
| oceanbase |
| information_schema |
| mysql |
| SYS |
| LBACSYS |
| ORAAUDITOR |
| test |
±-------------------+
7 rows in set (0.003 sec)
MySQL [oceanbase]> alter resource unit sys_unit_config min_cpu=2;
Query OK, 0 rows affected (0.007 sec)
MySQL [oceanbase]> CREATE resource unit S2C1G max_cpu=2, min_cpu=2, max_memory=‘1G’, min_memory=‘1G’, max_iops=10000, min_iops=1000, max_session_num=10000, max_disk_size=‘10G’;
Query OK, 0 rows affected (0.011 sec)
MySQL [oceanbase]> CREATE resource pool my_pool unit = ‘S2C1G’, unit_num = 1;
Query OK, 0 rows affected (0.021 sec)
MySQL [oceanbase]> create tenant obmysql resource_pool_list=(‘my_pool’), primary_zone=‘RANDOM’,comment ‘mysql tenant/instance’, charset=‘utf8’ set ob_tcp_invited_nodes=’%’, ob_compatibility_mode=‘mysql’;
Query OK, 0 rows affected (1.229 sec)
创建数据库
2、安装mysql实例[admin@oceanbase01 ~]$ obclient -h127.1 -uroot@sys -P2881 -p -c -A oceanbase
Enter password:
Welcome to the Oceanbase. Commands end with ; or g.
Your MySQL connection id is 3221487985
Server version: 5.7.25 Oceanbase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)
Copyright © 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.
MySQL [oceanbase]> show parameters like ‘cluster’;
±------±---------±------------±---------±--------±----------±----------±--------------------±---------±--------±--------±------------------+
| zone | svr_type | svr_ip | svr_port | name | data_type | value | info | section | scope | source | edit_level |
±------±---------±------------±---------±--------±----------±----------±--------------------±---------±--------±--------±------------------+
| zone1 | observer | 172.17.0.15 | 2882 | cluster | NULL | obcluster | Name of the cluster | OBSERVER | CLUSTER | DEFAULT | DYNAMIC_EFFECTIVE |
±------±---------±------------±---------±--------±----------±----------±--------------------±---------±--------±--------±------------------+
1 row in set (0.003 sec)
[admin@oceanbase01 ~]$ obclient -h127.1 -uroot@obmysql#obcluster -P5883 -c -A test
MySQL [test]> create database obmysql1;
使用系统自带的mariadb建立mysql测试库
3、sysbench数据准备[root@oceanbase01 ~]# yum -y install mysql
[root@oceanbase01 ~]# systemctl start mariadb.service
[root@oceanbase01 ~]# systemctl status mariadb.service
本次使用sysbench作为数据初始化工具。
下载sysbench并上传到mysql数据库服务器上
https://codeload.github.com/Percona-Lab/tpcc-mysql/zip/master
解压编译安装
[root@oceanbase01 ~]# unzip sysbench-master.zip
./autogen.sh
./configure --with-mysql-includes=/usr/include/mysql --with-mysql-libs=/usr/lib64/mysql --with-mysql
make
make install
mysql中建立测试库
[root@oceanbase01 sysbench-master]# mysql
MariaDB [(none)]> create database migration;
加载测试数据
sysbench ./src/lua/oltp_common.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-db=migration --tables=10 --table-size=10000 --db-driver=mysql --report-interval=10 --threads=10 --time=120 prepare
检查表的数据量
查看表数据量
4、使用mysqldump离线同步数据SELECt TABLE_NAME,DATA_LENGTH,INDEX_LENGTH,(DATA_LENGTH+INDEX_LENGTH) as length,TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2), ‘MB’) as total_size
FROM information_schema.TABLES
WHERe TABLE_SCHEMA=‘migration’
order by length desc;
将表结构和数据分开导出(MySQL源端)
[root@db01 dmp]# mysqldump -h 127.0.0.1 -uroot -proot -P3306 -d migration > exp_migration_ddl.sql
[root@db01 dmp]# mysqldump -h127.0.0.1 -uroot -P3306 -proot -t migration > exp_migration_data.sql
这个导出来的脚本有几个特征：
• 视图的定义也会在里面，但是会以注释 /!/。视图我们不关注，这部分内容可以删除。
• 会有一些特别的语法 Oceanbase MYSQL 会不支持，但是不影响，需要替换掉其中部分。比如说变量 SQL_NOTES，DEFINER 语句等。
下面这个示例就是导出的脚本里有一个 MAX_ROWS= 的设置，这个是 MySQL 特有的，Oceanbase MySQL 没有这个问题，也不需要这个设置，不支持这个语法，会报错。
;
CREATE TABLE NATION (
N_NATIONKEY int(11) NOT NULL,
N_NAME char(25) COLLATE utf8_unicode_ci NOT NULL,
N_REGIONKEY int(11) NOT NULL,
N_COMMENT varchar(152) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (N_NATIONKEY)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci MAX_ROWS=4294967295;
需要把所有 MAX_ROWS= 以及后面部分注释掉。使用批量替换技术。如在 vim 中使用 :%s/MAX_ROWS=/; – MAX_ROWS=/g 。
注意：上面导出的 SQL 中表名是大写，说明源端 MySQL 里设置表名默认很可能是大小写敏感。因此目标 Oceanbase MySQL 租户也要设置。
在导出的表结构语句里，可能包含外键。在导入 Oceanbase MySQL 里时，如果外键依赖的表没有创建时，导入脚本会报错。因此导入之前需要将外键检查约束先禁用掉。
MySQL [oceanbase]> set global foreign_key_checks=off;
Query OK, 0 rows affected (0.01 sec)
MySQL [oceanbase]> show global variables like ‘%foreign%’;
±-------------------±------+
| Variable_name | Value |
±-------------------±------+
| foreign_key_checks | OFF |
±-------------------±------+
1 row in set (0.00 sec)
修改后，退出会话，重新登录。 在 obclient 客户端里通过 source 命令可以执行外部 SQL 脚本文件。
检查文件中是否存在特殊语法|变量等,并在导入前将表中了latin1字符集的建表语句修改掉。
grep -Ei “SQL_NOTES|DEFINER|MAX_ROWS” exp_migration_ddl.sql
sed -i ‘s/CHARACTER SET latin1//g’ exp_migration_ddl.sql
或者使用vim进行替换
导入表结构
[root@oceanbase01 ~]# obclient -h127.1 -uroot@obmysql#obcluster -P5883 -proot -c -A obmysqll
Welcome to the Oceanbase. Commands end with ; or g.
Your MySQL connection id is 12
Server version: 5.6.25 Oceanbase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29)
Copyright © 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.
MySQL [obmysqll]> set global foreign_key_checks=off;
Query OK, 0 rows affected (0.002 sec)
MySQL [obmysqll]> source exp_migration_ddl.sql
导入数据
MySQL [obmysqll]> source exp_migration_data.sql
MySQL [obmysqll]> set global foreign_key_checks=on;
数据导入过程中在alter table disable keys和enable keys语句中有一些报错，不影响结果。
检查导入结果
数据完全一致。
部署datax软件
wget http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz
解压
tar -xf datax.tar.gz
cd datax
删除datax中的隐藏文件
find ./plugin -name “.*” | xargs rm -f
生成模板文件
python ./bin/datax.py -r mysqlreader -w oceanbasev10writer > job/my2ob.json
根据实际环境修改参数文件
vim job/my2obce.json
[root@oceanbase01 datax]# cat job/my2ob.json
{
“job”: {
“content”: [
{
“reader”: {
“name”: “mysqlreader”,
“parameter”: {
“column”: ["*"],
“connection”: [
{
“jdbcUrl”:[ “jdbc:mysql://127.0.0.1:3306/migration?useUnicode=true&characterEncoding=utf8”
],
“table”: [“sbtest1”]
}
],
“password”: “root”,
“username”: “root”,
“where”: “”
}
},
“writer”: {
“name”: “oceanbasev10writer”,
“parameter”: {
“column”: ["*"],
“connection”: [
{
“jdbcUrl”: “||dsc_ob10_dsc||obcluster:obmysql||dsc_ob10_dsc||jdbc:oceanbase://127.0.0.1:5883/obmysql1”,
“table”:[“sbtest1”]
}
],
“obWriteMode”: “insert”,
“password”: “root”,
“username”: “root”
}
}
}
],
“setting”: {
“speed”: {
“channel”: 4
}
}
}
}
清理之前导入的数据
[root@oceanbase01 datax]# obclient -h127.1 -uroot@obmysql#obcluster -P5883 -proot -c -A obmysql1
MySQL [obmysql1]> truncate table sbtest1;
启动同步作业
python ./bin/datax.py ./job/my2ob.json
数据同步成功。
6、(可选)使用datax配置Oceanbase和CSV之间的同步生成ob2csv配置文件
[root@oceanbase01 datax]# python ./bin/datax.py -r oceanbasev10reader -w txtfilewriter > job/ob2csv.json
修改配置文件
[root@oceanbase01 datax]# vim ob2csv.json
{
“job”:{
“setting”:{
“speed”:{
“channel”:10
},
“errorLimit”:{
“record”:0, “percentage”: 0.02
}
},
“content”:[
{
“reader”:{
“name”:“oceanbasev10reader”,
“parameter”:{
“where”:"",
“column”: [
“*”
],
“connection”:[
{
“jdbcUrl”:["||dsc_ob10_dsc||obcluster:obmysql||dsc_ob10_dsc||jdbc:oceanbase://127.0.0.1:5883/obmysql1"],
“table”:[“sbtest2”]
}
],
“username”: “root”,
“password”: “root”
}
},
“writer”:{
“name”: “txtfilewriter”,
“parameter”: {
“path”: “/tmp/”,
“fileName”: “sbtest2”,
“writeMode”: “truncate”,
“dateFormat”: “yyyy-MM-dd hh:mm:ss”,
“charset”: “UTF-8”,
“nullFormat”: “N”,
“fileDelimiter”: “,”
}
}
}
]
}
}
运行导出表sbtest2到csv
[root@oceanbase01 datax]# python ./bin/datax.py job/ob2csv.json
查看生成的csv文件
导出成功。
实现 CSV 到 Oceanbase 导入
清理数据
生成导入配置
[root@oceanbase01 datax]# python ./bin/datax.py -r txtfilereader -w oceanbasev10writer > ./job/csv2ob.json
修改配置文件
[root@oceanbase01 datax]# vim csv2ob.json
{
“job”: {
“content”: [
{
“reader”: {
“name”: “txtfilereader”,
“parameter”: {
“fileName”: “sbtest2”,
“column”: [“id”,“k”,“c”,“pad”],
“encoding”: “UTF-8”,
“fieldDelimiter”: “,”,
“path”: ["/tmp"]
}
},
“writer”: {
“name”: “oceanbasev10writer”,
“parameter”: {
“column”: [“id”,“k”,“c”,“pad”],
“connection”: [
{
“jdbcUrl”: “||dsc_ob10_dsc||obcluster:obmysql||dsc_ob10_dsc||jdbc:oceanbase://127.0.0.1:5883/obmysql1”,
“table”: [“sbtest2”]
}
],
“obWriteMode”: “insert”,
“password”: “root”,
“username”: “root”
}
}
}
],
“setting”: {
“speed”: {
“channel”: “4”
}
}
}
}
导入数据
7、(可选)使用canal 配置 MySQL到 Oceanbase 的增量同步[root@oceanbase01 datax]# python ./bin/datax.py ./job/csv2ob.json
