navicat+datax迁移oracle2mysql

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 执行脚本，以下是部分日志输出。