使用DataX同步MySQL数据

经常需要从不同环境同步数据库表数据，在这里推荐使用DataX。

下载DataX

https://datax-opensource.oss-cn-hangzhou.aliyuncs.com/202308/datax.tar.gz

部署DataX

将datax.tar.gz上传到服务器，解压到/data/public下，解压后目录为/data/public/datax/

编写同步配置

{
    "job": {
        "setting": {
            "speed": {
                "channel": 1
            },
            "errorLimit": {
                "record": 0,
                "percentage": 0
            }
        },
        "content": [{
                "reader": {
                    "name": "mysqlreader",
                    "parameter": {
                        "username": "root",
                        "password": "********",
                        "column": ["id", "user_name", "password", "create_time"],
                        "connection": [{
                                "querySql": ["SELECT * FROM t_user"],
                                "jdbcUrl": ["jdbc:mysql://172.17.9.100:3306/test?useSSL=false"]
                            }
                        ]
                    }
                },
                "writer": {
                    "name": "mysqlwriter",
                    "parameter": {
                        "print": true,
                        "username": "root",
                        "password": "********",
                        "column": ["id", "user_name", "password", "create_time"],
                        "preSql": ["DELETE FROM t_user"],
                        "writeMode": "insert",
                        "connection": [{
                                "jdbcUrl": "jdbc:mysql://172.17.10.200:3306/test2?useSSL=false",
                                "table": ["t_user"]
                            }
                        ]
                    }
                }
            }
        ]
    }
}

同步数据

执行如下命令

[root@dev datax]# python ./bin/datax.py my2my.json

执行结果如下，表示同步正常：

2025-06-09 17:00:05.535 [job-0] INFO  JobContainer - jobContainer starts to do split ...
2025-06-09 17:00:05.536 [job-0] INFO  JobContainer - Job set Channel-Number to 1 channels.
2025-06-09 17:00:05.538 [job-0] INFO  JobContainer - DataX Reader.Job [mysqlreader] splits to [1] tasks.
2025-06-09 17:00:05.538 [job-0] INFO  JobContainer - DataX Writer.Job [mysqlwriter] splits to [1] tasks.
2025-06-09 17:00:05.563 [job-0] INFO  JobContainer - jobContainer starts to do schedule ...
2025-06-09 17:00:05.566 [job-0] INFO  JobContainer - Scheduler starts [1] taskGroups.
2025-06-09 17:00:05.568 [job-0] INFO  JobContainer - Running by standalone Mode.
2025-06-09 17:00:05.573 [taskGroup-0] INFO  TaskGroupContainer - taskGroupId=[0] start [1] channels for [1] tasks.
2025-06-09 17:00:05.581 [taskGroup-0] INFO  Channel - Channel set byte_speed_limit to -1, No bps activated.
2025-06-09 17:00:05.582 [taskGroup-0] INFO  Channel - Channel set record_speed_limit to -1, No tps activated.
2025-06-09 17:00:05.647 [taskGroup-0] INFO  TaskGroupContainer - taskGroup[0] taskId[0] attemptCount[1] is started
2025-06-09 17:00:05.654 [0-0-0-reader] INFO  CommonRdbmsReader$Task - Begin to read record by Sql: [SELECT * FROM t_user
] jdbcUrl:[jdbc:mysql://172.17.9.100:3306/test?useSSL=false&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewri    teBatchedStatements=true].
2025-06-09 17:00:05.669 [0-0-0-reader] INFO  CommonRdbmsReader$Task - Finished read record by Sql: [SELECT * FROM t_user
] jdbcUrl:[jdbc:mysql://172.17.10.200:3306/test?useSSL=false&yearIsDateType=false&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&rewri    teBatchedStatements=true].
2025-06-09 17:00:05.748 [taskGroup-0] INFO  TaskGroupContainer - taskGroup[0] taskId[0] is successed, used[101]ms
2025-06-09 17:00:05.748 [taskGroup-0] INFO  TaskGroupContainer - taskGroup[0] completed it's tasks.
2025-06-09 17:00:20.040 [job-0] INFO  StandAloneJobContainerCommunicator - Total 8 records, 146 bytes | Speed 10B/s, 0 records/s | Error 0 re    cords, 0 bytes |  All Task WaitWriterTime 0.000s |  All Task WaitReaderTime 0.000s | Percentage 100.00%
2025-06-09 17:00:20.040 [job-0] INFO  AbstractScheduler - Scheduler accomplished all tasks.
2025-06-09 17:00:20.041 [job-0] INFO  JobContainer - DataX Writer.Job [mysqlwriter] do post work.
2025-06-09 17:00:20.041 [job-0] INFO  JobContainer - DataX Reader.Job [mysqlreader] do post work.
2025-06-09 17:00:20.041 [job-0] INFO  JobContainer - DataX jobId [0] completed successfully.
2025-06-09 17:00:20.042 [job-0] INFO  HookInvoker - No hook invoked, because base dir not exists or is a file: /data/public/datax/hook
2025-06-09 17:00:20.043 [job-0] INFO  JobContainer -
         [total cpu info] =>
                averageCpu                     | maxDeltaCpu                    | minDeltaCpu
                -1.00%                         | -1.00%                         | -1.00%


         [total gc info] =>
                 NAME                 | totalGCCount       | maxDeltaGCCount    | minDeltaGCCount    | totalGCTime        | maxDeltaGCTime         | minDeltaGCTime
                 PS MarkSweep         | 1                  | 1                  | 1                  | 0.032s             | 0.032s                 | 0.032s
                 PS Scavenge          | 1                  | 1                  | 1                  | 0.009s             | 0.009s                 | 0.009s

2025-06-09 17:00:20.044 [job-0] INFO  JobContainer - PerfTrace not enable!
2025-06-09 17:00:20.044 [job-0] INFO  StandAloneJobContainerCommunicator - Total 8 records, 146 bytes | Speed 10B/s, 0 records/s | Error 0 re    cords, 0 bytes |  All Task WaitWriterTime 0.000s |  All Task WaitReaderTime 0.000s | Percentage 100.00%
2025-06-09 17:00:20.045 [job-0] INFO  JobContainer -
任务启动时刻                    : 2025-06-09 17:00:02
任务结束时刻                    : 2025-06-09 17:00:20
任务总计耗时                    :                 17s
任务平均流量                    :               10B/s
记录写入速度                    :              0rec/s
读出记录总数                    :                   8
读写失败总数                    :                   0