MyCat 学习笔记第十六篇 . Oracle 数据迁移 MySql 初体验

最新推荐文章于 2020-11-28 04:26:03 发布

HKaye

最新推荐文章于 2020-11-28 04:26:03 发布

阅读量356

点赞数

分类专栏： MySql MyCat 文章标签： mysql 数据迁移 oracle

本文链接：https://blog.csdn.net/kaye0110/article/details/50985167

版权

MySql 同时被 2 个专栏收录

44 篇文章 0 订阅

订阅专栏

MyCat

27 篇文章 1 订阅

订阅专栏

前言

这篇文章验证的功能，可能在目前大部分日常工作中遇不到，就权当作是体验性的DEMO。部分功能如果后面来得急的话，可以做一个工具给出来。

Oracle 数据迁移至 Mysql 时，需要注意几点东西：

数据类型的转换，类似 DATE、TIMESTAMP 这类
存储过程，mysql 里面不好使~~
大数据的存储

第一点这个还好做，网上大把工具（或者自己写个工具）可以把数据导成 INSERT 脚本，留言下字符集的问题就 OK。
第二点的话，从数据库层面，无解。把存储过程改为业务逻辑方法吧。
第三点的话，利用 mycat 做个数据分片，当然要做好数据迁移前的测试。

运行环境
VM mysql 192.168.13.186:3307
VM mycat 192.168.13.187:8066/9066
VM oracle 192.168.13.175:1521

模拟3个数据表（T_USER、T_ORDER、T_ORDER_FEE），由于根据字段值分片，所有表上都有一个 user_id 字段。

create table T_USER
(
  user_id     NUMBER,
  user_name   VARCHAR2(50),
  user_passwd VARCHAR2(50)
)

create table T_ORDER
(
  order_id   NUMBER,
  order_time DATE,
  order_amt  NUMBER(15,2),
  user_id    NUMBER
)

create table T_ORDER_FEE
(
  fee_id     NUMBER,
  order_id   NUMBER,
  fee_status CHAR(1),
  order_amt  NUMBER(15,2),
  user_id    NUMBER
)

数据模拟及导出准备
生成1000个用户，每个用户15条交易记录和15条交易支付流水记录。类似 intelligent-converters 这种工具可以导出数据。
这里自己做了一个小工具在做数据导出，默认全部数据都是字符 INSERT 脚本，要注意字符集。
花了2个时写了一个小程序，做为一个原型抛出来大家看下，以后再改改一个更好的工具~~

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import kaye.etl.model.Column;
import kaye.etl.model.Table;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;

public class SimpleTableExporter {

    public SimpleTableExporter(JdbcTemplate jdbcTemplate, String tableName) {

        this.tableName = tableName;

        this.jdbcTemplate = jdbcTemplate;

    }

    public void exportTo(String fileName) {
        generateInsertSql();
        generateSelectSql();

        File f = new File(fileName);
        FileOutputStream fos = null;
        try {
            f.createNewFile();

            fos = new FileOutputStream(f);

            List<Map<String, Object>> data = this.jdbcTemplate
                    .queryForList(this.tableSelectSql);

            for (Map<String, Object> row : data) {
                String rowDataStr = "";

                for (Column col : this.tableObj.getCols()) {
                    switch (col.getType()) {
                    case "DATE":
                        rowDataStr += " str_to_date('" + row.get(col.getName())
                                + "','%Y-%m-%d %H:%i:%s'), ";
                        break;

                    default:
                        rowDataStr += "'" + row.get(col.getName()) + "',";
                        break;
                    }

                }

                // Iterator<String> colName = row.keySet().iterator();
                // while (colName.hasNext()) {
                // String cname = colName.next();
                // rowDataStr += "'" + row.get(cname) + "',";
                // }

                rowDataStr = rowDataStr.substring(0, rowDataStr.length() - 1);
                rowDataStr = String.format(tableInsertSql, rowDataStr) + "\r\n";
                System.out.print(rowDataStr);
                fos.write(rowDataStr.getBytes());

            }

            fos.flush();

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fos != null) {
                try {
                    fos.flush();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                try {
                    fos.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }

    }

    public void generateInsertSql() {

        if (tableObj == null) {
            this.loadTableInfo();
        }
        if (this.tableObj == null) {
            return;
        }

        String sql = "insert into " + this.tableName + " (";// tablename;

        int i = 1;
        for (Column col : this.tableObj.getCols()) {
            sql += col.getName() + " ";
            if (i < this.tableObj.getCols().size()) {
                sql += ", ";
            }

            i++;
        }
        sql += ") values ( %s ) ;"; // 在实际读取数据的时候拼装

        this.tableInsertSql = sql;
    }

    public void generateSelectSql() {

        if (tableObj == null) {
            this.loadTableInfo();
        }
        if (this.tableObj == null) {
            return;
        }

        String sql = " select  ";
        int i = 1;
        for (Column col : this.tableObj.getCols()) {

            switch (col.getType()) {
            case "DATE":
                sql += " to_char(" + col.getName()
                        + ",'yyyy-mm-dd hh24:mi:ss') as " + col.getName() + "";
                break;
            default:
                sql += col.getName() + " ";

            }

            if (i < this.tableObj.getCols().size()) {
                sql += ", ";
            }

            i++;
        }
        sql += " from " + this.tableName;
        this.tableSelectSql = sql;
    }

    private void loadTableInfo() {
        String param[] = { this.tableName };
        List<Column> tableCols = this.jdbcTemplate.query(QUERY_TABLE_COL,
                param, new RowMapper<Column>() {
                    public Column mapRow(ResultSet rs, int rowNum)
                            throws SQLException {
                        Column col = new Column();
                        col.setName(rs.getString("CNAME"));
                        col.setType(rs.getString("COLTYPE"));
                        col.setWidth(rs.getInt("WIDTH"));
                        col.setScale(rs.getInt("SCALE"));
                        col.setPrecision(rs.getInt("PRECISION"));
                        return col;
                    }
                });

        if (tableCols == null || tableCols.isEmpty()) {
            return;
        }

        this.tableObj = new Table();
        tableObj.setName(this.tableName);
        tableObj.setCols(tableCols);
    }

    private Table tableObj;

    private String tableInsertSql;

    private String tableSelectSql;

    private static final String QUERY_TABLE_COL = "select cname,coltype,width,scale,precision from col where tname = ? order by colno";

    private String tableName;
    private JdbcTemplate jdbcTemplate;

方法入口

import org.junit.Test;

import kaye.etl.oracle.SimpleTableExporter;
import kaye.utils.OracleTestCase;

public class DataExportTest extends OracleTestCase {

    @Test
    public void exportData() {
        exportUser();
        exportOrder();
        exportOrderFee();
    }

    public void exportUser() {

        String tableName = "T_USER";
        String fileName = "/Users/xxxx/Desktop/ora_export/user_export.sql";

        SimpleTableExporter ste = new SimpleTableExporter(
                super.jdbc.getJdbcTemplate(), tableName);

        ste.exportTo(fileName);
    }

    public void exportOrder() {
        String tableName = "T_ORDER";
        String fileName = "/Users/xxxx/Desktop/ora_export/order_export.sql";

        SimpleTableExporter ste = new SimpleTableExporter(
                super.jdbc.getJdbcTemplate(), tableName);

        ste.exportTo(fileName);
    }

    public void exportOrderFee() {
        String tableName = "T_ORDER_FEE";
        String fileName = "/Users/xxx/Desktop/ora_export/fee_export.sql";

        SimpleTableExporter ste = new SimpleTableExporter(
                super.jdbc.getJdbcTemplate(), tableName);

        ste.exportTo(fileName);
    }
}

导出来的数据片断

insert into T_USER (USER_ID , USER_NAME , USER_PASSWD ) values ( '11109','bysgyzhp','6zumqh10yr95h8a5' ) ;
insert into T_USER (USER_ID , USER_NAME , USER_PASSWD ) values ( '11110','duzjqrgi','r0vr2msm97y49m4w' ) ;
insert into T_USER (USER_ID , USER_NAME , USER_PASSWD ) values ( '11111','ypqrnczm','dl877wnnp6l374mq' ) ;
insert into T_USER (USER_ID , USER_NAME , USER_PASSWD ) values ( '11112','sxzyvevp','b09thy59pz1d3kd5' ) ;

。。。

insert into T_ORDER (ORDER_ID , ORDER_TIME , ORDER_AMT , USER_ID ) values ( '4', str_to_date('2016-03-25 10:46:21','%Y-%m-%d %H:%i:%s'), '6339797439','10104' ) ;
insert into T_ORDER (ORDER_ID , ORDER_TIME , ORDER_AMT , USER_ID ) values ( '5', str_to_date('2016-03-25 10:46:21','%Y-%m-%d %H:%i:%s'), '2175899216','10104' ) ;
insert into T_ORDER (ORDER_ID , ORDER_TIME , ORDER_AMT , USER_ID ) values ( '6', str_to_date('2016-03-25 10:46:21','%Y-%m-%d %H:%i:%s'), '3123860168','10104' ) ;
insert into T_ORDER (ORDER_ID , ORDER_TIME , ORDER_AMT , USER_ID ) values ( '7', str_to_date('2016-03-25 10:46:21','%Y-%m-%d %H:%i:%s'), '3733375584','10104' ) ;

。。。

insert into T_ORDER_FEE (FEE_ID , ORDER_ID , FEE_STATUS , ORDER_AMT , USER_ID ) values ( '7','7','1','3733375584','10104' ) ;
insert into T_ORDER_FEE (FEE_ID , ORDER_ID , FEE_STATUS , ORDER_AMT , USER_ID ) values ( '8','8','1','4321309919','10104' ) ;
insert into T_ORDER_FEE (FEE_ID , ORDER_ID , FEE_STATUS , ORDER_AMT , USER_ID ) values ( '9','9','1','4308238369','10104' ) ;
insert into T_ORDER_FEE (FEE_ID , ORDER_ID , FEE_STATUS , ORDER_AMT , USER_ID ) values ( '10','10','1','5953411441','10104' ) ;
。。。

数据导入
本次验证使用 mycat 在做数据分片，因此数据导入时必须走mycat：8066端口，OK再来看下 mycat 的配置

rule.xml

    <tableRule name="mod-long-userId">
        <rule>
            <columns>user_id</columns>
            <algorithm>mod-long</algorithm>
        </rule>
    </tableRule>


    <function name="mod-long" class="org.opencloudb.route.function.PartitionByMod">
        <!-- how many data nodes -->
        <property name="count">3</property>
    </function>

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">

    <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100">
        <table name="t_user" dataNode="dn1,dn2,dn3" rule="mod-long-userId" />
        <table name="t_order" dataNode="dn1,dn2,dn3" rule="mod-long-userId" />
        <table name="t_order_fee" dataNode="dn1,dn2,dn3" rule="mod-long-userId" />

    </schema>

    <dataNode name="dn1" dataHost="13_183" database="ds1" />
    <dataNode name="dn2" dataHost="13_183" database="ds2" />
    <dataNode name="dn3" dataHost="13_183" database="ds3" />

    <dataHost name="13_183" maxCon="1000" minCon="10" balance="0"
        writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="host_183" url="192.168.13.186:3307" user="root" password="root123" />
    </dataHost>



</mycat:schema>

查看 mycat 日志

03/26 08:16:20.046  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=4, lastTime=1458951380021, user=root, schema=ds3, old shema=ds3, borrowed=true, fromSlaveDB=false, threadId=22, charset=utf8, txIsolation=3, autocommit=true, attachment=dn3{insert into T_USER (USER_ID , USER_NAME , USER_PASSWD ) values ( '10547','laghsrrg','qgy3er12f1uh522x' )}, respHandler=SingleNodeHandler [node=dn3{insert into T_USER (USER_ID , USER_NAME , USER_PASSWD ) values ( '10547','laghsrrg','qgy3er12f1uh522x' )}, packetId=0], host=192.168.13.186, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
03/26 08:16:20.046  DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=4, lastTime=1458951380021, user=root, schema=ds3, old shema=ds3, borrowed=true, fromSlaveDB=false, threadId=22, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.13.186, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
03/26 08:16:20.057  DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection [id=2, schema=TESTDB, host=192.168.13.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]insert into T_USER (USER_ID , USER_NAME , USER_PASSWD ) values ( '10548','ahzinzvk','sqsj5g975929exv9' )
03/26 08:16:20.058  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=2, schema=TESTDB, host=192.168.13.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]insert into T_USER (USER_ID , USER_NAME , USER_PASSWD ) values ( '10548','ahzinzvk','sqsj5g975929exv9' ), route={
   1 -> dn1{insert into T_USER (USER_ID , USER_NAME , USER_PASSWD ) values ( '10548','ahzinzvk','sqsj5g975929exv9' )}
} rrs 
03/26 08:16:20.077  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=8, lastTime=1458951380047, user=root, schema=ds1, old shema=ds1, borrowed=true, fromSlaveDB=false, threadId=23, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{insert into T_USER (USER_ID , USER_NAME , USER_PASSWD ) values ( '10548','ahzinzvk','sqsj5g975929exv9' )}, respHandler=SingleNodeHandler [node=dn1{insert into T_USER (USER_ID , USER_NAME , USER_PASSWD ) values ( '10548','ahzinzvk','sqsj5g975929exv9' )}, packetId=0], host=192.168.13.186, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
03/26 08:16:20.078  DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=8, lastTime=1458951380047, user=root, schema=ds1, old shema=ds1, borrowed=true, fromSlaveDB=false, threadId=23, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.13.186, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
03/26 08:16:20.079  DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection [id=2, schema=TESTDB, host=192.168.13.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]insert into T_USER (USER_ID , USER_NAME , USER_PASSWD ) values ( '10549','goywggex','f9t6j336ixve9tnr' )
03/26 08:16:20.080  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=2, schema=TESTDB, host=192.168.13.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]insert into T_USER (USER_ID , USER_NAME , USER_PASSWD ) values ( '10549','goywggex','f9t6j336ixve9tnr' ), route={
   1 -> dn2{insert into T_USER (USER_ID , USER_NAME , USER_PASSWD ) values ( '10549','goywggex','f9t6j336ixve9tnr' )}
} rrs

数据查询验证

连接mycat进行数据查询

mysql> select count(1) from t_user;
+--------+
| COUNT0 |
+--------+
|   1000 |
+--------+
1 row in set (0.00 sec)

mysql> select count(1) from t_order;
+--------+
| COUNT0 |
+--------+
|  15000 |
+--------+
1 row in set (0.03 sec)

mysql> select count(1) from t_order_fee;
+--------+
| COUNT0 |
+--------+
|  15000 |
+--------+
1 row in set (0.01 sec)

直联服务器查询

mysql> select count(1) from ds1.t_order_fee;
+----------+
| count(1) |
+----------+
|     5000 |
+----------+
1 row in set (0.01 sec)

mysql> select count(1) from ds2.t_order_fee;
+----------+
| count(1) |
+----------+
|     4990 |
+----------+
1 row in set (0.01 sec)

mysql> select count(1) from ds3.t_order_fee;
+----------+
| count(1) |
+----------+
|     5010 |
+----------+
1 row in set (0.00 sec)

再从不同的数据源中连表查询交易信息


mysql> select u.user_name , o.order_id,o.order_time,o.order_amt from t_user u,t_order o where u.user_id = o.user_id;
+-----------+----------+---------------------+---------------+
| user_name | order_id | order_time          | order_amt     |
+-----------+----------+---------------------+---------------+
| bysgyzhp  |     5012 | 2016-03-25 10:47:54 | 3425473156.00 |
| bysgyzhp  |     5013 | 2016-03-25 10:47:54 | 4005873987.00 |
| bysgyzhp  |     5014 | 2016-03-25 10:47:54 | 8294275086.00 |
| bysgyzhp  |     5015 | 2016-03-25 10:47:54 | 3967944695.00 |
| bysgyzhp  |     5016 | 2016-03-25 10:47:54 | 8927796847.00 |
| bysgyzhp  |     5017 | 2016-03-25 10:47:54 | 9148603719.00 |
| bysgyzhp  |     5018 | 2016-03-25 10:47:54 | 9921759682.00 |
| bysgyzhp  |     5019 | 2016-03-25 10:47:54 |  601943046.00 |
| bysgyzhp  |     5020 | 2016-03-25 10:47:54 | 3148833940.00 |
| bysgyzhp  |     5021 | 2016-03-25 10:47:54 | 6004514647.00 |
| sxzyvevp  |     5042 | 2016-03-25 10:47:54 | 4732536069.00 |
| sxzyvevp  |     5043 | 2016-03-25 10:47:54 | 5242974090.00 |
| sxzyvevp  |     5044 | 2016-03-25 10:47:54 | 5035814107.00 |
| sxzyvevp  |     5045 | 2016-03-25 10:47:54 | 1548121856.00 |
| sxzyvevp  |     5046 | 2016-03-25 10:47:54 | 8621324790.00 |
| sxzyvevp  |     5047 | 2016-03-25 10:47:54 | 9555229584.00 |
| sxzyvevp  |     5048 | 2016-03-25 10:47:54 | 8771175373.00 |

。。。。

| dhmtvega  |    14973 | 2016-03-25 10:48:43 | 1814099503.00 |
| dhmtvega  |    14974 | 2016-03-25 10:48:43 | 9215783450.00 |
| dhmtvega  |    14975 | 2016-03-25 10:48:43 | 2777394548.00 |
| dhmtvega  |    14976 | 2016-03-25 10:48:43 | 1785040710.00 |
| dhmtvega  |    14977 | 2016-03-25 10:48:43 | 1622068755.00 |
| dhmtvega  |    14978 | 2016-03-25 10:48:43 | 6468674259.00 |
| dhmtvega  |    14979 | 2016-03-25 10:48:43 | 5668626424.00 |
| dhmtvega  |    14980 | 2016-03-25 10:48:43 | 5490617670.00 |
| dhmtvega  |    14981 | 2016-03-25 10:48:43 |  380347593.00 |
+-----------+----------+---------------------+---------------+
10000 rows in set (0.21 sec)

mycat 日志

03/26 08:53:11.924  DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection [id=8, schema=TESTDB, host=192.168.13.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select u.user_name , o.order_id,o.order_time,o.order_amt from t_user u,t_order o where u.user_id = o.user_id
03/26 08:53:11.925  DEBUG [$_NIOREACTOR-0-RW] (EnchachePool.java:70) -SQLRouteCache hit cache ,key:TESTDBselect u.user_name , o.order_id,o.order_time,o.order_amt from t_user u,t_order o where u.user_id = o.user_id
03/26 08:53:11.925  DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:113) -ServerConnection [id=8, schema=TESTDB, host=192.168.13.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select u.user_name , o.order_id,o.order_time,o.order_amt from t_user u,t_order o where u.user_id = o.user_id, route={
   1 -> dn1{select u.user_name , o.order_id,o.order_time,o.order_amt from t_user u,t_order o where u.user_id = o.user_id}
   2 -> dn2{select u.user_name , o.order_id,o.order_time,o.order_amt from t_user u,t_order o where u.user_id = o.user_id}
   3 -> dn3{select u.user_name , o.order_id,o.order_time,o.order_amt from t_user u,t_order o where u.user_id = o.user_id}
} rrs 
03/26 08:53:11.925  DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:82) -execute mutinode query select u.user_name , o.order_id,o.order_time,o.order_amt from t_user u,t_order o where u.user_id = o.user_id
03/26 08:53:11.925  DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -select read source host_183 for dataHost:13_183
03/26 08:53:11.926  DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -select read source host_183 for dataHost:13_183
03/26 08:53:11.927  DEBUG [$_NIOREACTOR-0-RW] (PhysicalDBPool.java:452) -select read source host_183 for dataHost:13_183
03/26 08:53:12.102  DEBUG [$_NIOREACTOR-0-RW] (MultiNodeQueryHandler.java:241) -on row end reseponse MySQLConnection [id=8, lastTime=1458953591906, user=root, schema=ds1, old shema=ds1, borrowed=true, fromSlaveDB=false, threadId=23, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{select u.user_name , o.order_id,o.order_time,o.order_amt from t_user u,t_order o where u.user_id = o.user_id}, respHandler=org.opencloudb.mysql.nio.handler.MultiNodeQueryHandler@4e708fc0, host=192.168.13.186, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

OK，初体验告一段落，后期有机会的话，考虑把Oracle 或 DB2的数据迁移至 mycat mysql 做个好点的解决方案，图形界面一键迁移数据~~
另外关于中文数据迁移的时候，记得要在那个simpleTableExporter中做好字符集转换并写出的问题。

本篇完

HKaye

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MyCat 学习笔记第十六篇 . Oracle 数据迁移 MySql 初体验

前言这篇文章验证的功能，可能在目前大部分日常工作中遇不到，就权当作是体验性的DEMO。部分功能如果后面来得急的话，可以做一个工具给出来。Oracle 数据迁移至 Mysql 时，需要注意几点东西：数据类型的转换，类似 DATE、TIMESTAMP 这类存储过程，mysql 里面不好使~~大数据的存储第一点这个还好做，网上大把工具（或者自己写个工具）
复制链接

扫一扫