你好,游客 登录 注册 搜索
背景:
阅读新闻

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

[日期:2017-12-26] 来源:Linux社区  作者:chinesern [字体: ]

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



--oraclemysql 具体搭建,之前都有 
现有环境,直接测试功能,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端验证



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

linux
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款