1. 先通过navicat在mysql上建表结构。
使用navicat连接oracle和mysql后,工具 —> 数据传输,源端选择oracle,目标端选择mysql。
2. 通过datax工具迁移数据
2.1 下载安装datax
所需环境:jdk1.8以上,python
下载datax:
wget http://datax-opensource.oss-cn-hangzhou.aliyuncs.com/datax.tar.gz -C /opt/
tar -xf /opt/datax.tar.gz
cd /opt/datax
2.2 datax相关文件
其中bin/datax.py为程序主文件,不需要修改
job下json文件需要手动修改配置,可参考官方文档:https://github.com/alibaba/DataX/blob/master/
2.3 oracle TO mysql 单表迁移
[root@host124 datax]# cat job/oracle2mysql.json
{
"job": {
"setting": {
"speed": {
"byte":10485760
},
"errorLimit": {
"record": 0,
"percentage": 0.02
}
},
"content": [
{
"reader": {
"name": "oraclereader",
"parameter": {
"column": ["*"],
"connection": [
{
"jdbcUrl": ["jdbc:oracle:thin:@192.168.9.221:1521:orcl"],
"table": ["test_table"]
}
],
"password": "123456",
"username": "user"
}
},
"writer": {
"name": "mysqlwriter",
"parameter": {
"column": ["*"],
"connection": [
{
"jdbcUrl": "jdbc:mysql://192.168.9.124:3306/test?useUnicode=true&characterEncoding=utf8",
"table": ["test_table"]
}
],
"password": "123456",
"username": "root",
"writeMode": "insert"
}
}
}
],
"setting": {
"speed": {
"channel": "3"
}
}
}
}
./bin/datax.py job/oracle2mysql.json
2.3 oracle TO mysql 多表迁移
[root@host124 datax]# cat job/oracle2mysql.json
{
"job": {
"setting": {
"speed": {
"byte":10485760
},
"errorLimit": {
"record": 0,
"percentage": 0.02
}
},
"content": [
{
"reader": {
"name": "oraclereader",
"parameter": {
"column": ["*"],
"connection": [
{
"jdbcUrl": ["jdbc:oracle:thin:@192.168.9.221:1521:orcl"],
"table":["${tablename}"]
}
],
"password": "123456",
"username": "user"
}
},
"writer": {
"name": "mysqlwriter",
"parameter": {
"column": ["*"],
"connection": [
{
"jdbcUrl": "jdbc:mysql://192.168.9.124:3306/test?useUnicode=true&characterEncoding=utf8",
"table": ["${tablename}"]
}
],
"password": "123456",
"username": "root",
"writeMode": "insert"
}
}
}
],
"setting": {
"speed": {
"channel": "3"
}
}
}
}
可以用命令直接执行:
python2 /opt/datax/bin/datax.py -p "-Dtablename=test_table" /opt/datax/job/oracle2mysql.json
也可以通过脚本调用:
[root@host124 datax]# cat tablename.txt
test_table1
test_table2
test_table3
[root@host124 datax]# cat run.sh
#!/bin/bash
for tablename in `cat tablename.txt` ; do
python2 /opt/datax/bin/datax.py -p "-Dtablename=${tablename}" /opt/datax/job/oracle2mysql.json
done
./run.sh > run.log 执行脚本,以下是部分日志输出。