使用DataX对MySQL 8.1进行数据迁移

1. 环境准备

1.1 下载DataX

这里采用直接下载的方式：https://datax-opensource.oss-cn-hangzhou.aliyuncs.com/202308/datax.tar.gz，不过这个包是真的有点大。

1.2 安装Python

Python下载地址：https://www.python.org/downloads/
安装的时候添加到PATH，这样后面不用再配置环境变量。


安装完成之后验证下：

C:\Users\dongda>python -V
Python 3.11.5

2. 编写同步脚本Job

DataX中可以给咱们生成示例的脚本：

python datax.py -r mysqlreader -w mysqlwriter

比如执行上面这一段，会给咱们生成一段示例的脚本：

D:\alibaba\datax\bin>python datax.py -r mysqlreader -w mysqlwriter

DataX (DATAX-OPENSOURCE-3.0), From Alibaba !
Copyright (C) 2010-2017, Alibaba Group. All Rights Reserved.


Please refer to the mysqlreader document:
     https://github.com/alibaba/DataX/blob/master/mysqlreader/doc/mysqlreader.md

Please refer to the mysqlwriter document:
     https://github.com/alibaba/DataX/blob/master/mysqlwriter/doc/mysqlwriter.md

Please save the following configuration as a json file and  use
     python {DATAX_HOME}/bin/datax.py {JSON_FILE_NAME}.json
to run the job.

{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "mysqlreader",
                    "parameter": {
                        "column": [],
                        "connection": [
                            {
                                "jdbcUrl": [],
                                "table": []
                            }
                        ],
                        "password": "",
                        "username": "",
                        "where": ""
                    }
                },
                "writer": {
                    "name": "mysqlwriter",
                    "parameter": {
                        "column": [],
                        "connection": [
                            {
                                "jdbcUrl": "",
                                "table": []
                            }
                        ],
                        "password": "",
                        "preSql": [],
                        "session": [],
                        "username": "",
                        "writeMode": ""
                    }
                }
            }
        ],
        "setting": {
            "speed": {
                "channel": ""
            }
        }
    }
}

3. 替换数据库包

这里看下reader这边，默认mysql驱动包是：mysql-connector-java-5.1.47.jar，要支持8.1的话需要换成8.1的jar包：mysql-connector-j-8.1.0.jar

如果数据接收端也需要支持8.1，则相应的驱动包也要更换：

4. 运行同步作业

# 先修改下终端编码为utf8
chcp 65001

python datax.py ../job/mysql2mysql.json

其中的mysql2mysql.json示例内容如下：

{
    "job": {
        "content": [
            {
                "reader": {
                    "name": "mysqlreader",
                    "parameter": {
                        "column": [
						    "dept_id",
						    "parent_id",
						    "ancestors",
						    "dept_name",
						    "order_num",
						    "leader",
						    "phone",
						    "email",
						    "status",
						    "del_flag",
						    "create_by",
						    "create_time",
						    "update_by",
						    "update_time"
						],
                        "connection": [
                            {
                                "jdbcUrl": ["jdbc:mysql://localhost:3306/ry-cloud?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true"],
                                "table": ["sys_dept"]
                            }
                        ],
                        "password": "123456",
                        "username": "root",
                        "where": ""
                    }
                },
                "writer": {
                    "name": "mysqlwriter",
                    "parameter": {
                        "column": [
							"dept_id",
						    "parent_id",
						    "ancestors",
						    "dept_name",
						    "order_num",
						    "leader",
						    "phone",
						    "email",
						    "status",
						    "del_flag",
						    "create_by",
						    "create_time",
						    "update_by",
						    "update_time"
						],
                        "connection": [
                            {
                                "jdbcUrl": "jdbc:mysql://localhost:3306/trust-test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true",
                                "table": ["sys_dept"]
                            }
                        ],
                        "password": "123456",
                        "preSql": [
							"truncate table sys_dept"
						],
                        "session": [],
                        "username": "root",
                        "writeMode": "replace"
                    }
                }
            }
        ],
        "setting": {
            "speed": {
                "channel": "1"
            }
        }
    }
}

这里也可以不用填写所有的列，就用“*”来替代，如下：

"column": ["*"],

5. 参考

https://blog.csdn.net/tototuzuoquan/article/details/102601515