完成用户表与订单表到MySQL的迁移过程,要求导数据是通过命令行连接Mycat来完成的
同时操作如下过程:
在Mycat里查询转账记录表,查询OK
在Mycat里查询用户表,查询OK
####################################
1:准备Oracle测试环境
Linux 部署安装Oracle 过程省略
创建Oracle用户:
create tablespace mycat datafile '/oracle/oradata/orcl/mycat' size 50M autoextend on;
create user mycat identified by mycat123
default tablespace mycat
temporary tablespace temp;
grant connect,resource to mycat;
确认Oracle的字符集为GBK
select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
通过mycat创建Oracle表,也可以在oracle实例上创建表:
conn mycat/mycat123
create table uc_user(id number,uc_name varchar(64),create_time date);
create table uc_order(id number,uc_id number,shop_name varchar(64),create_time date);
create table traders(id number,uc_id number,order_id number,fee number,trade_status char(1),create_time date);
2:准备mysql测试环境
建库,这里为了好提现对比,所以将mysql实例下的表加了后缀_2来区分:
去mysql实例上建库: create database powerdes;
在myacat命令行上建表,mysql相对于oracle,比如数字int、日期datetime等都描述不一样:
create table uc_user_2(id int,uc_name varchar(64),create_time datetime);
create table uc_order_2(id int,uc_id int,shop_name varchar(64),create_time datetime);
create table traders_2(id int,uc_id int,order_id int,fee int,trade_status char(1),create_time datetime);
3:mycat中配置oracle、mysql数据源
schema.xml中oracle配置:
<table name="UC_USER" primaryKey="_ID" dataNode="dn_oracle_1" needAddLimit="false"/> <table name="ORDER" primaryKey="_ID" dataNode="dn_oracle_1" needAddLimit="false"/>
<table name="TRADERS" primaryKey="_ID" dataNode="dn_oracle_1" needAddLimit="false"/>
<dataNode name="dn_oracle_1" dataHost="dn_oracle_1" database="orcl" />
<dataHost name="dn_oracle_1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="oracle" dbDriver="native" 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="hostM1" url="jdbc:oracle:thin:@192.168.10.20:1521:orcl" user="mycat" password="mycat123456" >
</writeHost>
</dataHost>
schema.xml里面配置mysql源:
<table name="UC_USER_2"primaryKey="_ID" dataNode="dn_uc" needAddLimit="false"/> <table name="UC_ORDER_2"primaryKey="_ID" dataNode="dn_uc" needAddLimit="false"/>
<table name="TRADERS_2" primaryKey="_ID" dataNode="dn_uc" needAddLimit="false"/>
<dataNode name="dn_uc"dataHost="mysql1" database="powerdes" />
############################################
配置好后的schema.xml:
<?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="UC_USER" primaryKey="_ID" dataNode="dn_oracle_1" needAddLimit="false"/>
<table name="ORDER" primaryKey="_ID" dataNode="dn_oracle_1" needAddLimit="false"/>
<table name="TRADERS" primaryKey="_ID" dataNode="dn_oracle_1" needAddLimit="false"/>
<table name="UC_USER_2" primaryKey="_ID" dataNode="dn_uc" needAddLimit="false"/>
<table name="UC_ORDER_2" primaryKey="_ID" dataNode="dn_uc" needAddLimit="false"/>
<table name="TRADERS_2" primaryKey="_ID" dataNode="dn_uc" needAddLimit="false"/>
</schema>
<!--######### TESTDB脢戮萁诘茫############-->
<dataNode name="dn_oracle_1" dataHost="dn_oracle_1" database="orcl" />
<dataNode name="dn_uc" dataHost="mysql1" database="powerdes" />
<!--######### TESTDB脢戮脻麓 ########-->
<dataHost name="dn_oracle_1" maxCon="1000" minCon="1" balance="0" writeType="0" dbType="oracle" dbDriver="jdbc">
<heartbeat>select 1 from dual</heartbeat>
<connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>
<writeHost host="oracle1" url="jdbc:oracle:thin:@192.168.10.20:1521:orcl" user="mycat" password="mycat123">
</writeHost>
</dataHost>
<dataHost name="mysql1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
<heartbeat>show slave status</heartbeat>
<writeHost host="mysql1" url="192.168.10.23:3306" user="root" password="oracle">
</writeHost>
</dataHost>
</mycat:schema>
加载oracle的lib包,默认在/oracle/product/11.2.0/db_1/jdbc/lib下,ojdbc6.jar文件。
需要把oracle的jar包放到mycat安装目录下,一般是用这个ojdbc6.jar,放到/usr/mysql/mycat/lib目录下面,然后重新加载下mycat生效。
4:录入oracle数据
在oracle实例窗口录入数据:
用Pl/SQL录入用户数据:
INSERT INTO mycat.UC_USER(ID,UC_NAME,CREATE_TIME)VALUES(1,'张三',SYSDATE);
INSERT INTO mycat.UC_USER(ID,UC_NAME,CREATE_TIME)VALUES(2,'李四',SYSDATE);
录入订单数据:
INSERT INTO mycat.UC_ORDER(ID,UC_ID,SHOP_NAME,CREATE_TIME)VALUES(1,1,'mycat技术权威指南书籍',SYSDATE);
INSERT INTO mycat.UC_ORDER(ID,UC_ID,SHOP_NAME,CREATE_TIME)VALUES(2,2,'mysql高性能第三版',SYSDATE);
录入转账数据:
INSERT INTO mycat.TRADERS(ID,UC_ID,ORDER_ID,FEE,TRADE_STATUS,CREATE_TIME)VALUES(1,1,1,59,1,SYSDATE);
INSERT INTO mycat.TRADERS(ID,UC_ID,ORDER_ID,FEE,TRADE_STATUS,CREATE_TIME)VALUES(2,2,2,119,1,SYSDATE);
防止乱码配置,在server.xml里面配置:
<property name="charset">utf8</property>
5:在mycat平台上从oracle迁移到mysql中文乱码怎么破?
5.1,从oracle迁移出来
在mycat命令行迁移出来:
mysql -h192.168.10.21 -P8066 -uroot -poracle -DTESTDB -s -N -e "select * from UC_USER" > /tmp/UC_USER.txt
[root@mycat1 conf]# mysql -h192.168.10.21 -P8066 -uroot -poracle -DTESTDB -s -N -e "select * from UC_USER" > /tmp/UC_USER.txt
[root@mycat1 conf]# more /tmp/UC_USER.txt
1 ???? 2016-04-06 23:09:48
2 ???? 2016-04-06 23:11:19
5.2,导入到mysql实例中
在mycat命令行执行load data infile '/tmp/UC_USER.txt' into table UC_USER_2;:
load data infile '/tmp/UC_USER.txt' into table UC_USER_2;
[root@mycat1 conf]# mysql -h192.168.10.21 -P8066 -uroot -poracle -DTESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.8-mycat-1.5-alpha-20151221110028 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2013, 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> load data infile '/tmp/UC_USER.txt' into table UC_USER_2;
Query OK, 2 rows affected (0.24 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from uc_user_2;
+------+---------+---------------------+
| id | uc_name | create_time |
+------+---------+---------------------+
| 1 | ???? | 2016-04-06 23:09:48 |
| 2 | ???? | 2016-04-06 23:11:19 |
+------+---------+---------------------+
6:在mycat平台上从oracle迁移到mysql中文正常
指导思想:
Mycat对于数据库而言就是客户端,一般来讲客户端字符编码与数据库服务端一致就不会有什么问题,或者是严格超集,保证插入到数据库不因转码而丢失。
客户端、系统终端、数据库三者字符庥得协调,否则不一致的话,会有字符集转换的问题,有时候,显示正确的,但不一定在数据库就保存正确,
或者保存到数据库的数据编码正确,没准显示为乱码。这就意味着,在oracle里面是gbk或者latin1啥都没有关系,只要做好以下3点:
(1),在迁移到mysql里面,在mycat平台上从oracle导出时候字符集设置为utf8
(2),在mycat平台上导入到mysql字符集也要设置为utf8
(3),然后在mycat平台上查看数据也要设置字符集为utf8
做好了以上3点,三而合一,这样就能正常显示中文了。
6.1:从oracle导出设定好字符集utf8
设定好utf8,--default-character-set=utf8:
mysql -h192.168.10.21 -P8066 -uroot -poracle -DTESTDB -s -N --default-character-set=utf8 -e "select * from UC_USER" > /tmp/UC_USER_2.txt
mysql -h192.168.10.21 -P8066 -uroot -poracle -DTESTDB -s -N --default-character-set=utf8 -e "select * from UC_ORDER" > /tmp/UC_ORDER_2.txt
mysql -h192.168.10.21 -P8066 -uroot -poracle -DTESTDB -s -N --default-character-set=utf8 -e "select * from TRADERS" > /tmp/TRADERS_2.txt
mysql> set names utf8; Query OK, 0 rows affected (0.00 sec)
mysql> load data infile '/tmp/UC_USER_2.txt' into table UC_USER_2;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
mysql> load data infile '/tmp/UC_ORDER_2.txt' into table UC_ORDER_2;
Query OK, 2 rows affected (0.02 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0
mysql> load data infile '/tmp/TRADERS_2.txt' into table TRADERS_2;
load data infile '/tmp/UC_USER_2.txt' into table UC_USER_2;