共计 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 信息
| <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 端验证
4.3 ORACLE 端验证
可以看到, 出现了乱码的情况
解决方案:
| # 设置客户端字符集 | |
| export NLS_LANG=AMERICAN_AMERICA.UTF8 | |
| # 设置 secureCRT 字符集为 UTF-8 |
乱码情况已经解决
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 |
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 |
解决方案: 重新导出数据, 去掉列名
| # 重新导出 | |
| 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 |
| 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); |
6.3 mycat 端验证数据
本文提到的文档下载：
高性能 MySQL（第 3 版）中文 PDF 带目录清晰版 下载见 http://www.linuxidc.com/Linux/2014-10/108464.htm
Mycat 权威指南 完整 PDF 清晰版 下载见 http://www.linuxidc.com/Linux/2017-12/149843.htm
MySQL 排错指南 PDF 清晰完整版 下载见 http://www.linuxidc.com/Linux/2017-12/149844.htm
本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-12/149842.htm
正文完
星哥玩云-微信公众号
