oceanabse数据迁移
Tpcc安装
https://code.launchpad.net/~percona-dev/perconatools/tpcc-mysql
目前已经移到到github上了
https://github.com/Percona-Lab/tpcc-mysql
https://github.com/Percona-Lab/tpcc-mysql.git
从guthub下载下来名为tpcc-mysql-master.zip。直接上传至服务器进行安装
# unzip ./tpcc-mysql-master.zip
# cd tpcc-mysql-master/
# cd src
# make
上级目录可以看到tpcc_load和tpcc_start两个脚本，tpcc_load用于初始化数据，tpcc_start用于基准测试。可以读取README.md的内容使用步骤信息。
初始化创建压测数据
登录数据库中创建通过tpcc测试的数据库
(root@localhost)[(none)]> create database tpcc1000;
Query OK, 1 row affected (0.00 sec)
(root@localhost)[(none)]> create user 'tpcc'@'localhost' identified by 'tpcc';
Query OK, 0 rows affected (0.00 sec)
(root@localhost)[(none)]> grant all privileges on tpcc1000.* to 'tpcc'@'localhost';
Query OK, 0 rows affected (0.00 sec)
数据库创建好之后，向里面导入对应的创建表及索引等脚本。
[root@db tpcc-mysql-master]# mysql -h localhost -utpcc -ptpcc -D tpcc1000 < /soft/tpcc-mysql-master/create_table.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@db tpcc-mysql-master]#
[root@db tpcc-mysql-master]# mysql -h localhost -utpcc -ptpcc -D tpcc1000 < /soft/tpcc-mysql-master/add_fkey_idx.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
将基本的表结构及索引等约束信息导入到测试库之后，再通过tpcc_load生成数据。
tpcc_load用法
tpcc_load [server] [DB] [user] [pass] [warehouse]
或者
tpcc_load [server] [DB] [user] [pass] [warehouse] [part] [min_wh] [max_wh]
选项 warehouse 意为指定测试库下的仓库数量。
查询tpcc加载的数据库及表信息。
将customer导出表结构及表数据或者使用--tab参数直接导出表结构及表数据
[root@db ~]# mysqldump -uroot -p123 -d tpcc1000 item > item_tab.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@db ~]# mysqldump -uroot -p123 -t tpcc1000 item > item_tab_data.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure..
OB新建租户
在oceanasbe新建mysql租户
MySQL [oceanbase]> alter resource unit sys_unit_config min_cpu=4;
Query OK, 0 rows affected (0.008 sec)
MySQL [oceanbase]> CREATE resource unit S4C1G max_cpu=4, min_cpu=4, max_memory='1G', min_memory='1G',
-> max_iops=10000, min_iops=1000, max_session_num=1000000, max_disk_size='1024G';
Query OK, 0 rows affected (0.007 sec)
MySQL [oceanbase]> CREATE resource pool my_pool unit = 'S4C1G', unit_num = 1;
Query OK, 0 rows affected (0.013 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.595 sec)
使用普通租户连接并创建业务用户
[admin@69d3f6987ff3 ~]$ obclient -h 127.1 -uroot@obmysql#obce-single -P2883 -p -c -A test
MySQL [test]> create user user1 identified by 'user1';
Query OK, 0 rows affected (0.049 sec)
MySQL [test]> GRANT ALL PRIVILEGES ON *.* TO user1@'%';
Query OK, 0 rows affected (0.021 sec)
通过普通用户登录之后，导入表结构及表数据。
[root@db ~]# mysql -uuser1@obmysql#obce-single -h172.17.0.2 -P2883 -p test
mysql> source item_tab.sql;
mysql> source item_tab_data.sql;
mysql> select count(*) from item;
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.08 sec)
DATAX安装及使用
-- 大小：820MB
wget http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz
tar -zvxf datax.tar.gz
# 删除datax中的隐藏文件
find /opt/datax/plugin -name ".*" | xargs rm -f
-- 生成模板文件
python2 /opt/datax/bin/datax.py -r mysqlreader -w oceanbasev10writer > /opt/datax/job/my2ob.json
# 根据实际环境修改
vi /opt/datax/job/my2ob.json
下表信息为需要数据同步的表信息。
配置json参数文件的参数内容
[root@db job]# cat my2ob.json
{
"job": {
"content": [
{
"reader": {
"name": "mysqlreader",
"parameter": {
"column": ["*"],
"connection": [
{
"jdbcUrl": ["jdbc:mysql://10.10.10.198:3306/test"],
"table": ["userInfo"]
}
],
"password": "123",
"username": "root"
}
},
"writer": {
"name": "oceanbasev10writer",
"parameter": {
"column": ["*"],
"connection": [
{
"jdbcUrl": "||_dsc_ob10_dsc_||obce-single:obmysql||_dsc_ob10_dsc_||jdbc:oceanbase://172.17.0.2:2883/test?useUnicode=true&characterEncoding=utf-8",
"table": ["userInfo"]
}
],
"obWriteMode": "insert",
"password": "user1",
"username": "user1"
}
}
}
],
"setting": {
"speed": {
"channel": 4
}
}
}
}
执行datax同步任务
python2 /opt/datax/bin/datax.py /opt/datax/job/my2ob.json
查询ob端的数据
