前言
这篇文章验证的功能,可能在目前大部分日常工作中遇不到,就权当作是体验性的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中做好字符集转换并写出的问题。
本篇完