DataX在Windows上实现Mysql到Mysql同步数据以及配置多个job/多个表同步定时执行bat

r囧r小猫 2023-10-12 15:57 15阅读 0赞

## 场景 ##

DataX-阿里开源离线同步工具在Windows上实现Sqlserver到Mysql全量同步和增量同步:

[DataX-阿里开源离线同步工具在Windows上实现Sqlserver到Mysql全量同步和增量同步\_sqlserver数据同步工具\_霸道流氓气质的博客-CSDN博客][DataX-_Windows_Sqlserver_Mysql_sqlserver_-CSDN]

DataX-在Windows上实现postgresql同步数据到mysql:

[DataX-在Windows上实现postgresql同步数据到mysql\_postgresql 数据同步到mysql\_霸道流氓气质的博客-CSDN博客][DataX-_Windows_postgresql_mysql_postgresql _mysql_-CSDN]

在上面的基础上如何进行多个Mysql到Mysql之间的数据同步。

注：

博客：  
[霸道流氓气质的博客\_CSDN博客-C\#,架构之路,SpringBoot领域博主][CSDN_-C_SpringBoot]

## 实现 ##

多个数据同步job就新建多个json文件，每个json文件中执行一次的job

比如第一个同步的json文件内容：

{
        "job": {
            "content": [
                {
                    "reader": {
                        "name": "mysqlreader",
                        "parameter": {
                            "column": [
    						"station_id",
    						"station_number",
    						"group_id",
    						"mine_id",
    						],
                            "connection": [
                                {
                                    "jdbcUrl": [
    								"jdbc:mysql://127.0.0.1:3306/数据库名?useUnicode=true&characterEncoding=gbk"
    								],
                                    "table": [
    								"表名"
    								]
                                }
                            ],
                            "password": "密码",
                            "username": "用户名",
                            "where": ""
                        }
                    },
                    "writer": {
                        "name": "mysqlwriter",
                        "parameter": {
                            "column": [
    						"station_id",
    						"station_number",
    						"group_id",
    						"mine_id",
    						],
                            "connection": [
                                {
                                    "jdbcUrl": "jdbc:mysql://127.0.0.1:3306/数据库名?useUnicode=true&characterEncoding=gbk",
                                    "table": [
    								"表名"
    								]
                                }
                            ],
                            "password": "密码",
                            "preSql": [
    						"delete from 表名"
    						],
                            "session": [],
                            "username": "root",
                            "writeMode": "insert"
                        }
                    }
                }
            ],
            "setting": {
                "speed": {
                    "channel": "5"
                }
            }
        }
    }

注意将数据库地址、名称、表名、列名、用户名、密码等改成自己对应。

然后再新建第二个数据同步的json文件

{
        "job": {
            "content": [
                {
                    "reader": {
                        "name": "mysqlreader",
                        "parameter": {
                            "column": [
    						"id",
    						"location_number",
    						"car_num",
    						],
                            "connection": [
                                {
                                    "jdbcUrl": [
    								"jdbc:mysql://127.0.0.1:3306/数据库名?useUnicode=true&characterEncoding=gbk"
    								],
                                    "table": [
    								"表名"
    								]
                                }
                            ],
                            "password": "密码",
                            "username": "用户名",
                            "where": ""
                        }
                    },
                    "writer": {
                        "name": "mysqlwriter",
                        "parameter": {
                            "column": [
    						"id",
    						"location_number",
    						"car_num",
    						],
                            "connection": [
                                {
                                    "jdbcUrl": "jdbc:mysql://127.0.0.1:3306/数据库名?useUnicode=true&characterEncoding=gbk",
                                    "table": [
    								"表名"
    								]
                                }
                            ],
                            "password": "密码",
                            "preSql": [
    						"delete from 表名"
    						],
                            "session": [],
                            "username": "用户名",
                            "writeMode": "insert"
                        }
                    }
                }
            ],
            "setting": {
                "speed": {
                    "channel": "5"
                }
            }
        }
    }

将此两个json文件放在datax的bin目录下

并在此目录下新建bat文件

#设置编码
    chcp 65001
    @echo off
    title "自定义标题"
    set INTERVAL=3600
    timeout %INTERVAL%
     
    :Again
    
    python datax.py  ./busCarInfoViewMysql2Mysql.json
    
    ping 127.0.0.1 -n 5
    
    python datax.py  ./tBaseStationMysql2Mysql.json
    
    echo %date% %time:~0,8%
     
    timeout %INTERVAL%
     
    goto Again

这里的3600就代表每3600秒执行一次。

ping 127.0.0.1 -n 5 目的是为了中间停顿5秒。

[DataX-_Windows_Sqlserver_Mysql_sqlserver_-CSDN]: https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/130330353
[DataX-_Windows_postgresql_mysql_postgresql _mysql_-CSDN]: https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/130410056
[CSDN_-C_SpringBoot]: https://blog.csdn.net/badao_liumang_qizhi