作者简介：邹阳，数据库工程师。
本实践练习实现导出导入数据。
一、环境信息
服务器信息
机器和角色划分
二、创建租户
su - admin
obclient -h 172.16.100.21 -P 2881 -uroot@sys -p -c -A
2.1 创建资源单元规格（RESOURCE UNIT）
CREATE resource unit S1 max_cpu=1, min_cpu=1, max_memory='2G', min_memory='2G', max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='128G';
select * from __all_unit_config;
2.2创建资源池（RESOURCE POOL)
create resource pool pool_1 unit='S1' , unit_num=1, zone_list=('zone1' ,'zone2' ,'zone3') ; select t1.name resource_pool_name, t2.`name` unit_config_name, t2.max_cpu, t2.min_cpu, t2.max_memory/1024/1024/1024 max_mem_gb, t2.min_memory/1024/1024/1024 min_mem_gb, t3.unit_id, t3.zone, concat(t3.svr_ip,':',t3.`svr_port`) observer,t4.tenant_id, t4.tenant_name from __all_resource_pool t1 join __all_unit_config t2 on (t1.unit_config_id=t2.unit_config_id) join __all_unit t3 on (t1.`resource_pool_id` = t3.`resource_pool_id`) left join __all_tenant t4 on (t1.tenant_id=t4.tenant_id) order by t1.`resource_pool_id`, t2.`unit_config_id`, t3.unit_id ; select a.zone,concat(a.svr_ip,':',a.svr_port) observer, cpu_total, cpu_assigned, (cpu_total-cpu_assigned) cpu_free, mem_total/1024/1024/1024 mem_total_gb, mem_assigned/1024/1024/1024 mem_assign_gb, (mem_total-mem_assigned)/1024/1024/1024 mem_free_gb from __all_virtual_server_stat a join __all_server b on (a.svr_ip=b.svr_ip and a.svr_port=b.svr_port) order by a.zone, a.svr_ip ;
2.3创建租户（TENANT）
create tenant obmysql resource_pool_list=('pool_1'), primary_zone='RANDOM',comment 'mysql tenant/instance', charset='utf8' set ob_tcp_invited_nodes='%' ; select * from gv$tenant;
2.4创建业务数据库及用户
obclient -h172.16.100.21 -uroot@obmysql -P2881 -p -c -A oceanbase
create database tpch;
use tpcc;
2.5创建普通数据库用户
create user tpcc1 identified by tpcc1; GRANT ALL PRIVILEGES ON *.* TO tpcc1;
三、使用 mysqldump 迁移 MySQL 表 OceanBase
3.1 MYSQL端导出表结构、表数据
mysqldump -h 127.1 -uroot -P3306 -p -d TPCH --compact > tpch_ddl.sql mysqldump -uroot -p -t -T /var/lib/mysql-files/ tpch bmsql_oorder --fields-terminated-by=','
3.2 oceanbase 中导入
将数据从mysql 服务器拷贝至OB环境
obclient -h172.16.100.51 -uroot@obmysql -P2881 -p -c -A oceanbase use tpch; source /soft/tpch_ddl.sql; load data infile '/soft/bmsql_oorder.txt' into table bmsql_oorder fields terminated by ',';
四、安装DATAX软件包
DataX 简介
DataX 是阿里云 DataWorks 数据集成 的开源版本，主要就是用于实现数据间的离线同步。 DataX 致力于实现包括关系型数据库（MySQL、Oracle 等）、HDFS、Hive、ODPS、HBase、FTP 等 各种异构数据源（即不同的数据库） 间稳定高效的数据同步功能。为了解决异构数据源同步问题，DataX 将复杂的网状同步链路变成了星型数据链路，DataX 作为中间传输载体负责连接各种数据源；当需要接入一个新的数据源时，只需要将此数据源对接到 DataX，便能跟已有的数据源作为无缝数据同步。
安装要在 ob01(172.16.100.21) 及 mysql (172.16,100.57)上分别进行安装
4.1 安装JDK
安装 JDK cd /soft/ tar zxf jdk-8u181-linux-x64.tar.gz mv jdk1.8.0_211/ /usr/local/java cat <<END >> /etc/profile export JAVA_HOME=/usr/local/java export PATH=$PATH:$JAVA_HOME/bin END source /etc/profile java –version
4.2 安装DATAX
wget http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz tar zxf datax.tar.gz -C /usr/local/ rm -rf /usr/local/datax/plugin/*/._* # 需要删除隐藏文件 (重要)
验证
cd /usr/local/datax/bin python datax.py ../job/job.json # 用来验证是否安装成功
五、DATZX mysql 端导出数据
5.1创建导出脚本
{
"job": {
"setting": {
"speed": {
"channel": 1
},
"errorLimit": {
"record": 0,
"percentage": 0.1
}
},
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"username": "tpcc",
"password": "tpcc",
"column": [
"*"
],
"connection": [
{
"table": [
"bmsql_oorder"
],
"jdbcUrl": ["jdbc:mysql://127.0.0.1:3306/tpccdb?useUnicode=true&characterEncoding=utf8"]
}
]
}
},
"writer": {
"name": "txtfilewriter",
"parameter": {
"path": "/tmp/tpcc/bmsql_oorder",
"fileName": "bmsql_oorder",
"encoding": "UTF-8",
"writeMode": "truncate",
"nullFormat": "\\N" ,
"fileFormat": "csv" ,
"fieldDelimiter": ","
}
}
}
]
}
}
查看导出的JSON文件
5.2执行导出命令
bin/datax.py job/bmsql_oorder_mysql2csv.json
六、DATAX oceanbase 端导入数据
6.1 创建导入脚本
{
"job": {
"setting": {
"speed": {
"channel": 4
},
"errorLimit": {
"record": 0,
"percentage": 0.1
}
},
"content": [
{
"reader": {
"name": "txtfilereader",
"parameter": {
"path": ["/tmp/tpcc/bmsql_oorder"],
"fileName": "bmsql_oorder",
"encoding": "UTF-8",
"column": ["*"],
"nullFormat": "\\N" ,
"fieldDelimiter": ","
}
},
"writer": {
"name": "oceanbasev10writer",
"parameter": {
"obWriteMode": "insert",
"column": [
"*"
],
"preSql": [
"truncate table bmsql_oorder"
],
"connection": [
{
"jdbcUrl": "||_dsc_ob10_dsc_||obdemo:my_tenant1||_dsc_ob10_dsc_||jdbc:oceanbase://127.0.0.1:2883/tpcc?useLocalSessionState=true&allowBatch=true&allowMultiQueries=true&rewriteBatchedStatements=true",
"table": [
"bmsql_oorder"
]
}
],
"username": "root",
"password":"password",
"writerThreadCount":10,
"batchSize": 1000,
"memstoreThreshold": "0.9"
}
}
}
]
}
}
6.2 执行导入
[admin@ob01 datax]$ bin/datax.py ./job/bmsql_oorder_csv2ob.json
执行结果如下：
OceanBase社区版入门到实战教程直播正在进行中~
快和小伙伴一起进群交流学习吧~
加入直播群方式一：
钉钉群号 3582 5151
加入直播群方式二：
扫码下方二维码加入
