2.Datax数据同步之Windows下，mysql和sqlserver之间的自定义sql文数据同步

前言

上一篇文章实现了不同的mysql数据库之间的数据同步，在此基础上本篇将实现mysql和sqlserver之间的自定义sql文数据同步

准备工作：

• JDK(1.8以上，推荐1.8)
• Python(2或3都可以)
• Apache Maven 3.x (Compile DataX)
• Datax插件下载
• mysql5.7
• sql server2012

步骤操作大纲

1. mysql和sql server创建好对应的数据库和表结构
2. mysql填充数据
3. 撰写mysqlToSqlServer.json文件
4. windows终端执行同步文件
5. 查看结果
6. 撰写sqlServerToMysql.json文件
7. windows终端执行同步文件
8. 查看结果

步骤明细

mysql和sql server创建好对应的数据库和表结构如下图所示


安装sql server2012时，.NET Framework3.5必须被勾选中，否则最后一步无法完全安装成功

如果遇到了0x80080005 windows更新失败导致的net framework3.5安装失败
点击该作者博客迅速解决

mysql 至 sqlServer

撰写mysql同步至sqlServer的json文件

{
    "job": {
        "setting": {
            "speed": {
                "channel":1
            },
            "errorLimit": {
                "record": 0,
                "percentage": 0.02
            }
        },
        "content": [
            {
                "reader": {
                    "name": "mysqlreader",
                    "parameter": {
						"username": "root", 
						"password": "123456",
                        "connection":[
							{
								"jdbcUrl":["jdbc:mysql://127.0.0.1:3306/lvyy_test?useSSL=false&useUnicode=true&characterEncoding=utf-8"],
								"querySql":["select id,name,content,createdate from t_user_info where id > 1"]
							}
						]
                    }
                },
                "writer": {
                    "name": "sqlserverwriter",
                    "parameter": {
                        "username": "sa",
						"password":"123456",
						"column": ["id","name","contents","createdate"],
						"preSql":["delete from t_user_info"],
						"connection":[
							{
								"jdbcUrl":"jdbc:sqlserver://127.0.0.1:1433;DatabaseName=lvyy01",
								"table":["t_user_info"]
							}
						]
                    }
                }
            }
        ]
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

windows终端执行同步文件
如果遇到报错：The server selected protocol version TLS10 is not accepted by client preferences [TLS12]
点击该作者博客迅速解决


结果如下

sqlServer 至 mysql

撰写SqlServer同步至mysql的json文件

{
    "job": {
        "setting": {
            "speed": {
                "channel": 1
            }
        },
        "content": [
            {
				"reader": {
                    "name": "sqlserverreader",
                    "parameter": {
                        "username": "sa",
						"password": "123456",
                        "connection": [
                            {
                                "jdbcUrl": [
                                    "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=lvyy01"
                                ],
								"querySql":["select id, name, contents, createdate from t_user_info where id < 3"]
                            }
                        ]
                    }
                },
				"writer": {
                    "name": "mysqlwriter",
                    "parameter": {
                        "username": "root",
                        "password": "123456",
                        "column": [ "id", "name","content","createdate"],
                        "preSql": [
                            "delete from t_user_info"
                        ],
                        "connection": [
                            {
                                "jdbcUrl": "jdbc:mysql://127.0.0.1:3306/lvyy0102?useSSL=false&useUnicode=true&characterEncoding=utf-8",
                                "table": [
                                    "t_user_info"
                                ]
                            }
                        ]
                    }
                }
            }
        ]
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

windows终端执行同步文件


结果如下