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 

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 

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同步任务

查询ob端的数据