datax安装使用(sqlserver同步到mysql)

环境:

[root@host135 ~]# java -version
java version "1.8.0_361"
Java(TM) SE Runtime Environment (build 1.8.0_361-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.361-b09, mixed mode)
[root@host135 ~]# python -V
Python 3.6.5

 

 

 

1.下载

https://github.com/alibaba/DataX

选择Quick Start连接入口进行下载编译好的

 

2.解压
tar -xvf datax.tar.gz
mv datax /opt/

 

3.编辑json文件

cd /opt/datax/script
vi sqlserver2mysql.json

{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "sqlserverreader",
                    "parameter": {
                        "connection": [
                            {
                                "jdbcUrl": ["jdbc:sqlserver://192.168.2.84:1433;DatabaseName=mydb"],
                                "table": ["${readTable}"]
                            }
                        ],
                        "password": "123456",
                        "username": "sa",
                        "column": ["*"]
                    }
                },
                "writer": {
                    "name": "mysqlwriter",
                    "parameter": {
                        "column": ["*"],
                        "connection": [
                            {
                                "jdbcUrl": "jdbc:mysql://192.168.1.135:3306/db_hxl",
                                "table": ["${writeTable}"]
                            }
                        ],
                        "password": "mysql",
                        "preSql": ["truncate table ${writeTable}"],
                        "session": [],
                        "username": "root",
                        "writeMode": "insert"
                    }
                }
            }
        ],
        "setting": {
            "speed": {
                "channel": "5"
            }
        }
    }
}

 

4.编写shell执行文件

cd /opt/datax/script/
vi sqlserver2mysql.sh
python /opt/datax/bin/datax.py /opt/datax/script/sqlserver2mysql.json -p "-DreadTable=$1 -DwriteTable=$2" --jvm="-Xms8G -Xmx8G"

[root@host135 script]# chmod u+x sqlserver2mysql.sh

 

5.执行(需要在目标端创建表)
sh ./sqlserver2mysql.sh tb_test tb_test

报错误:

错误:“The server selected protocol version TLS10 is not accepted by client preferences [TLS13, TLS12]”

 

解决办法:
find / -name java.security
/usr/local/java/jdk1.8.0_361/jre/lib/security/java.security
打开 java.security 文件之后，修改 jdk.tls.disabledAlgorithms= 后面的值，删除 TLSv1, TLSv1.1 和 3DES_EDE_CBC 即可

 

6.使用select方式获取源数据

[root@host135 script]# more sqlserver2mysql01.json 
{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "sqlserverreader",
                    "parameter": {
                        "connection": [
                            {
                                "jdbcUrl": ["jdbc:sqlserver://192.168.2.84:1433;DatabaseName=db_test"],
                                "querySql": ["select * from tb_test"]
                            }
                        ],
                        "password": "123456",
                        "username": "sa",
                        "column": ["*"]
                    }
                },
                "writer": {
                    "name": "mysqlwriter",
                    "parameter": {
                        "column": ["*"],
                        "connection": [
                            {
                                "jdbcUrl": "jdbc:mysql://192.168.1.135:3306/db_hxl",
                                "table": ["tb_test"]
                            }
                        ],
                        "password": "mysql",
                        "preSql": ["truncate table tb_test"],
                        "session": [],
                        "username": "root",
                        "writeMode": "insert"
                    }
                }
            }
        ],
        "setting": {
            "speed": {
                "channel": "5"
            }
        }
    }
}