datax数据同步更新策略-以mysql2mysql为例

最新推荐文章于 2026-06-27 17:15:42 发布

原创最新推荐文章于 2026-06-27 17:15:42 发布 · 4k 阅读

2 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

大数据

本文详细介绍了DataX在进行MySQL到MySQL数据同步时的全量抽取和增量同步策略，包括where条件和时间戳的使用，以及在配置中如何避免同时设置table和querySql的冲突。

全量抽取策略

{
    "job": {
        "setting": {
            "speed": {
                "channel": 3
            },
            "errorLimit": {
                "record": 0,
                "percentage": 0.02
            }
        },
        "content": [{
            "reader": {
                "name": "mysqlreader",
                "parameter": {
                    "username": "root",
                    "password": "APIB9hRP",
                    "column": [
                        "id",
                        "name",
			"age",
			"address",
			"salary"
                    ],
                    "splitPk": "id",
                    "connection": [{
                        "table": [
                            "company"
                        ],
                        "jdbcUrl": [
                            "jdbc:mysql://localhost:3306/datax?serverTimezone=UTC"
                        ]
                    }]
                }
            },
            "writer": {
                "name": "mysqlwriter",
                "parameter": {
                    "writeMode": "insert",
                    "username": "root",
                    "password": "APIB9hRP",
                    "column": [
                        "id",
                        "name",
			"age",
			"address",
			"salary"
                    ],
                    "session": [
                        "set session sql_mode='ANSI'"
                    ],
                    "preSql": [
                        "delete from company"
                    ],
                    "connection": [{
                        "table": [
                            "company"
                        ],
                        "jdbcUrl": "jdbc:mysql://localhost:3306/datax2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8"
                    }]
                }
            }
        }]
    }
}

增量同步

where 一般条件

{
    "job": {
        "setting": {
            "speed": {
                "channel": 3
            },
            "errorLimit": {
                "record": 0,
                "percentage": 0.02
            }
        },
        "content": [{
            "reader": {
                "name": "mysqlreader",
                "parameter": {
                    "username": "root",
                    "password": "APIB9hRP",
                    "column": [
                        "id",
                        "name",
			"age",
			"address",
			"salary"
                    ],
                    "splitPk": "id",
		    "where":"id>4",
                    "connection": [{
                        "table": [
                            "company"
                        ],
                        "jdbcUrl": [
                            "jdbc:mysql://localhost:3306/datax?serverTimezone=UTC"
                        ]
                    }]
                }
            },
            "writer": {
                "name": "mysqlwriter",
                "parameter": {
                    "writeMode": "insert",
                    "username": "root",
                    "password": "APIB9hRP",
                    "column": [
                        "id",
                        "name",
			"age",
			"address",
			"salary"
                    ],
                    "session": [
                        "set session sql_mode='ANSI'"
                    ],
                    "connection": [{
                        "table": [
                            "company"
                        ],
                        "jdbcUrl": "jdbc:mysql://localhost:3306/datax2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8"
                    }]
                }
            }
        }]
    }
}

where 时间

"where":"TO_DAYS( NOW( ) ) - TO_DAYS(createtime) <= 1",

qureySql

datax不能同时既配置table又配置querySql

{
    "job": {
        "setting": {
            "speed": {
                "channel": 3
            },
            "errorLimit": {
                "record": 0,
                "percentage": 0.02
            }
        },
        "content": [{
            "reader": {
                "name": "mysqlreader",
                "parameter": {
                    "username": "root",
                    "password": "APIB9hRP",
                    "column": [
                        "id",
                        "dept",
			"createtime"
                    ],
                    "splitPk": "id",
                    "connection": [{
			"querySql": ["select * from DEPARTMENT where id=1"],
                        "jdbcUrl": [
                            "jdbc:mysql://localhost:3306/datax?serverTimezone=UTC"
                        ]
                    }]
                }
            },
            "writer": {
                "name": "mysqlwriter",
                "parameter": {
                    "writeMode": "insert",
                    "username": "root",
                    "password": "APIB9hRP",
                    "column": [
                        "id",
                        "dept",
			"createtime"
                    ],
                    "session": [
                        "set session sql_mode='ANSI'"
                    ],
                    "connection": [{
                        "table": [
                            "DEPARTMENT"
                        ],
                        "jdbcUrl": "jdbc:mysql://localhost:3306/datax2?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8"
                    }]
                }
            }
        }]
    }
}