Mycat实战之数据迁移(Oracle — MySQL)

共计 7614 个字符，预计需要花费 20 分钟才能阅读完成。

1、案例场景：

Mycat 后面接一个 Oracle 实例与一个 MySQL 实例，假设用户表，订单表，转账记录表，Oracle 字符集为 GBK 的，MySQL 字符集则要求 UTF8 的
完成用户表与订单表到 MySQL 的迁移过程，要求导数据是通过命令行连接 Mycat 来完成的
同时操作如下过程：1.  在 Mycat 里查询转账记录表，查询 OK
2.  在 Mycat 里查询用户表，查询 OK

2、环境准备

oracle 11gr2 10.10.0.23 1521
mysql  5.6   192.168.2.130 3306



--oracle 与 mysql 具体搭建，之前都有 
现有环境，直接测试功能,mysql 环境都是 ok. 配置 oracle 相关环境

2.1 oracle 环境配置

# 创建 tablespace

SQL> create tablespace mycat    DATAFILE '/home/oracle/app/oradata/orcl/mycat.dbf' 
     SIZE 200m autoextend off;

Tablespace created.


# 创建用户以及授权 
SQL> create user mycat identified by mycat default tablespace mycat;

User created.

SQL> grant resource,connect to mycat;

Grant succeeded.


# 验证数据库字符集 
SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

2.2 mysql 环境配置

mysql> show variables like '%character%';
+--------------------------+-----------------------------+
| Variable_name            | Value                       |
+--------------------------+-----------------------------+
| character_set_client     | utf8                        |
| character_set_connection | utf8                        |
| character_set_database   | utf8                        |
| character_set_filesystem | binary                      |
| character_set_results    | utf8                        |
| character_set_server     | utf8                        |
| character_set_system     | utf8                        |
| character_sets_dir       | /u01/my3306/share/charsets/ |
+--------------------------+-----------------------------+
8 rows in set (0.02 sec)

3 mycat 逻辑库配置，增加 oracle 以及 mysql 相关信息

3.1 schema.xml 增加 oracle 信息

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
            <table name="O_USER" primaryKey="ID" dataNode="ora_dn1" needAddLimit="false"/>
            <table name="O_ORDER" primaryKey="ID" dataNode="ora_dn1" needAddLimit="false"/>
            <table name="O_TRADERS" primaryKey="ID" dataNode="ora_dn1" needAddLimit="false"/>
            <table name="M_USER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
            <table name="M_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
            <table name="M_TRADERS" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
    </schema>
    <!--######### oracle 单节点  ########-->
    <dataNode name="ora_dn1" dataHost="10.10.0.23" database="orcl" />

    <!--######### mysql 三个分片 ########-->
    <dataNode name="dn1" dataHost="mysqlserver" database="db1" />
    <dataNode name="dn2" dataHost="mysqlserver" database="db2" />
    <dataNode name="dn3" dataHost="mysqlserver" database="db3" />


    <!--######### TESTDB  ########-->
    <dataHost name="oracleDB" maxCon="1000" minCon="10" balance="0"
            writeType="0" dbType="oracle" dbDriver="jdbc" switchType="1" slaveThreshold="100">
            <heartbeat>select 1 from dual</heartbeat>
            <connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>
            <writeHost host="hostO1" url="jdbc:oracle:thin:@10.10.0.23:1521:orcl" user="mycat" password="mycat >
            </writeHost>
    </dataHost>
    <dataHost name="mysqlserver" maxCon="1000" minCon="10" balance="0"
            writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
            <writeHost host="hostM1" url="192.168.2.130:3306" user="root" password="root123">
            </writeHost>
    </dataHost>
</mycat:schema>

3.2 启动 mycat

 # 加载 oracle 的 lib 包 
 # 下载 ojdbc14.jar
 # 拷贝到 /usr/local/mycat/lib
 #schema.xml 中 oralce 的 dbDriver 改成 jdbc


 # 启动 mycat 以及查看日志 
 /usr/local/mycat/bin/mycat start
 cd /usr/local/mycat/logs

3.3 验证 mycat 逻辑库中是否存在 oracle 和 mysql 的表信息

[mysql@mycat ~]$  mysql -utest -ptest  -h192.168.2.136 -P8066
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.8-mycat-1.5.1-RELEASE-20161130213509 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> 
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)

mysql> use TESTDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
 
 
mysql> show tables like 'o_%';
+------------------+
| Tables in TESTDB |
+------------------+
| o_order          |
| o_traders        |
| o_user           |
+------------------+
3 rows in set (0.00 sec)

mysql> show tables like 'm_%';
+------------------+
| Tables in TESTDB |
+------------------+
| m_order          |
| m_traders        |
| m_user           |
+------------------+
3 rows in set (0.00 sec)

4.mycat 端创建 oracle 以及 mysql 表并插入数据

4.1 mycat 端创建 oracle 表

mysql> CREATE TABLE O_USER(ID number, UC_NAME VARCHAR(64),CREATE_TIME DATE);
Query OK, 0 rows affected (0.98 sec)
 OK!

mysql> CREATE TABLE O_ORDER(ID number,UC_ID number,SHOP_NAME VARCHAR(64),CREATE_TIME DATE);
Query OK, 0 rows affected (0.06 sec)
 OK!

mysql> CREATE TABLE O_TRADERS(ID number,UC_ID number,ORDER_ID number, FEE number,TRADE_STATUS char(1),CREATE_TIME DATE);
Query OK, 0 rows affected (0.05 sec)
 OK!


#oracle 实例端验证表创建 
[oracle@localhost lib]$ sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 19 18:25:37 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn mycat/mycat
Connected.
SQL> 
SQL> 
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
O_USER
O_ORDER
O_TRADERS

4.2 mycat 端插入数据到 oracle 表

#O_USER
INSERT INTO O_USER(ID,UC_NAME,CREATE_TIME)VALUES(1,'fish',SYSDATE);
INSERT INTO O_USER(ID,UC_NAME,CREATE_TIME)VALUES(2,'chinesern',SYSDATE);
INSERT INTO O_USER(ID,UC_NAME,CREATE_TIME)VALUES(3,'hao',SYSDATE);


#O_ORDER
INSERT INTO O_ORDER(ID,UC_ID,SHOP_NAME,CREATE_TIME)VALUES(1,1,'mycat 技术权威指南书籍',SYSDATE);
INSERT INTO O_ORDER(ID,UC_ID,SHOP_NAME,CREATE_TIME)VALUES(2,2,'mysql 高性能第三版',SYSDATE);
INSERT INTO O_ORDER(ID,UC_ID,SHOP_NAME,CREATE_TIME)VALUES(3,3,'MySQL 排错指南',SYSDATE);


#O_TRADERS
INSERT INTO O_TRADERS(ID,UC_ID,ORDER_ID,FEE,TRADE_STATUS,CREATE_TIME)VALUES(1,1,1,59,1,SYSDATE);
INSERT INTO O_TRADERS(ID,UC_ID,ORDER_ID,FEE,TRADE_STATUS,CREATE_TIME)VALUES(2,2,2,119,1,SYSDATE);
INSERT INTO O_TRADERS(ID,UC_ID,ORDER_ID,FEE,TRADE_STATUS,CREATE_TIME)VALUES(3,3,3,120,1,SYSDATE);

4.2 mycat 端验证

Mycat 实战之数据迁移 (Oracle -- MySQL)

4.3 ORACLE 端验证

Mycat 实战之数据迁移 (Oracle -- MySQL)

可以看到, 出现了乱码的情况

解决方案:

  # 设置客户端字符集  
  export NLS_LANG=AMERICAN_AMERICA.UTF8
  # 设置 secureCRT 字符集为 UTF-8

Mycat 实战之数据迁移 (Oracle -- MySQL)

乱码情况已经解决

5、mycat 端导出 oracle 数据

# 导出数据 
mysql -h192.168.2.136 -P8066 -u test -ptest TESTDB -e "select * from o_user" >/tmp/o_user.txt
mysql -h192.168.2.136 -P8066 -u test -ptest TESTDB -e "select * from o_traders" >/tmp/o_traders.txt
mysql -h192.168.2.136 -P8066 -u test -ptest TESTDB -e "select * from o_order" >/tmp/o_order.txt

# 验证数据 
cat /tmp/o_user.txt && cat /tmp/o_traders.txt && cat /tmp/o_order.txt

Mycat 实战之数据迁移 (Oracle -- MySQL)

6 数据导入到 mysql

6.1 mycat 端创建 mysql 表

CREATE TABLE M_USER(ID int, UC_NAME VARCHAR(64),CREATE_TIME DATETIME);
CREATE TABLE M_ORDER(ID int,UC_ID int,SHOP_NAME VARCHAR(64),CREATE_TIME DATETIME);
CREATE TABLE M_TRADERS(ID int,UC_ID int,ORDER_ID int, FEE int,TRADE_STATUS char(1),CREATE_TIME DATETIME);

6.2 mycat 端导入数据到 mysql

load data infile '/tmp/o_user.txt' into table M_USER;
load data infile '/tmp/o_order.txt' into table M_ORDER;
load data infile '/tmp/o_traders.txt' into table M_TRADERS;

这里遇到一个问题: 分库策略需要带上字段属性，需要根据分库

mysql> load data infile '/tmp/o_user.txt' into table M_USER;
ERROR 1064 (HY000): partition table, insert must provide ColumnList

Mycat 实战之数据迁移 (Oracle -- MySQL)

解决方案: 重新导出数据, 去掉列名

# 重新导出 
mysql -h192.168.2.136 -P8066 -u test -ptest TESTDB -N -e "select * from o_user" >/tmp/o_user.txt
mysql -h192.168.2.136 -P8066 -u test -ptest TESTDB -N -e "select * from o_traders" >/tmp/o_traders.txt
mysql -h192.168.2.136 -P8066 -u test -ptest TESTDB -N -e "select * from o_order" >/tmp/o_order.txt

Mycat 实战之数据迁移 (Oracle -- MySQL)

# 导入
load data infile '/tmp/o_user.txt' into table M_USER(ID,UC_NAME,CREATE_TIME);
load data infile '/tmp/o_order.txt' into table M_ORDER(ID,UC_ID,SHOP_NAME,CREATE_TIME);
load data infile '/tmp/o_traders.txt' into table M_TRADERS(ID,UC_ID,ORDER_ID,FEE,TRADE_STATUS,CREATE_TIME);