【MySql】利用DataX同步mysql数据，多数据源数据同步方案

清疚 2024-04-17 11:22 188阅读 0赞

--------------------

你说你知道他们的世界  
悲歌三首买一切  
买昆仑落脚 蓬莱放思想  
买人们的争执酿酒汤  
买公主坟的乌鸦  
事发之木和东窗之麻  
买胭脂河里船行渔歌  
黄金世界中万物法则  
你我都一样 将被遗忘 郭源潮  
你的病也和我的一样 风月难扯 离合不骚  
层楼终究误少年 自由早晚乱余生  
你我山前没相见 山后别相逢  
? 宋冬野《郭源潮》

--------------------

在数据驱动的时代，数据同步在数据仓库、数据湖和其他数据存储解决方案中扮演着至关重要的角色。DataX，作为一款高效的开源数据同步工具，它支持包括MySQL在内的多种数据源之间的高性能数据同步。本文将通过一个实际案例，指导您如何使用DataX来同步两个MySQL数据库之间的数据。

### 安装和配置DataX ###

1.  下载DataX：访问[DataX GitHub][]页面或其官方网站下载最新版本。
2.  解压缩：将下载的DataX包解压到您选择的目录中。
3.  配置MySQL数据源：您需要为源数据库和目标数据库创建JSON配置文件。DataX提供了模板来帮助您开始。

### 创建配置文件 ###

在DataX安装目录下，创建一个新的JSON配置文件，例如mysql\_to\_mysql.json，并按照以下结构配置您的源数据库和目标数据库信息：

{
        
      "job": {
        
        "setting": {
        
          "speed": {
        
            "channel": 1
          }
        },
        "content": [
          {
        
            "reader": {
        
              "name": "mysqlreader",
              "parameter": {
        
                "username": "source_username",
                "password": "source_password",
                "connection": [
                  {
        
                    "jdbcUrl": ["jdbc:mysql://source_host:source_port/source_db?characterEncoding=utf8"],
                    "table": ["source_table"]
                  }
                ]
              }
            },
            "writer": {
        
              "name": "mysqlwriter",
              "parameter": {
        
                "username": "target_username",
                "password": "target_password",
                "writeMode": "insert",
                "connection": [
                  {
        
                    "jdbcUrl": "jdbc:mysql://target_host:target_port/target_db?characterEncoding=utf8",
                    "table": ["target_table"]
                  }
                ]
              }
            }
          }
        ]
      }
    }

在此配置文件中，您需要替换`source_username`、`source_password`、`source_host`、`source_port`、`source_db`和`source_table`为您的源数据库配置。同样，将`target_username`、`target_password`、`target_host`、`target_port`、`target_db`和`target_table`替换为目标数据库的配置。

### 执行数据同步 ###

配置好JSON文件后，打开终端或命令提示符，导航到DataX的bin目录，执行以下命令来启动数据同步：

python datax.py /path/to/your/mysql_to_mysql.json

请将`/path/to/your/mysql_to_mysql.json`替换为您配置文件的实际路径。DataX将开始同步过程，并在终端中显示进度和日志。

### 同步完成 ###

当同步完成后，DataX会显示一个任务总结报告，包括同步的记录数、速度和是否有错误发生。此时，您可以登录到目标数据库检查数据是否如预期那样同步。

### 总结 ###

DataX提供了一个强大且灵活的平台，用于在不同的数据库和数据仓库之间同步数据。通过本文的指导，您应该能够理解如何使用DataX同步两个MySQL数据库之间的数据。DataX的配置和使用非常直观，使得即使是数据同步的新手也能轻松上手。

### [DataX数据源参考指南][DataX] ###

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>数据源</th> 
   <th align="center">Reader(读)</th> 
   <th align="center">Writer(写)</th> 
   <th align="center">文档</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>RDBMS 关系型数据库</td> 
   <td>MySQL</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/mysqlreader/doc/mysqlreader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/mysqlwriter/doc/mysqlwriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>Oracle</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/oraclereader/doc/oraclereader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/oraclewriter/doc/oraclewriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>OceanBase</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://open.oceanbase.com/docs/community/oceanbase-database/V3.1.0/use-datax-to-full-migration-data-to-oceanbase" rel="nofollow">读</a> 、<a href="https://open.oceanbase.com/docs/community/oceanbase-database/V3.1.0/use-datax-to-full-migration-data-to-oceanbase" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>SQLServer</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/sqlserverreader/doc/sqlserverreader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/sqlserverwriter/doc/sqlserverwriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>PostgreSQL</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/postgresqlreader/doc/postgresqlreader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/postgresqlwriter/doc/postgresqlwriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>DRDS</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/drdsreader/doc/drdsreader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/drdswriter/doc/drdswriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>Kingbase</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/drdsreader/doc/drdsreader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/drdswriter/doc/drdswriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>通用RDBMS(支持所有关系型数据库)</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/rdbmsreader/doc/rdbmsreader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/rdbmswriter/doc/rdbmswriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td>阿里云数仓数据存储</td> 
   <td>ODPS</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/odpsreader/doc/odpsreader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/odpswriter/doc/odpswriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>ADB</td> 
   <td align="center"></td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/adbmysqlwriter/doc/adbmysqlwriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>ADS</td> 
   <td align="center"></td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/adswriter/doc/adswriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>OSS</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/ossreader/doc/ossreader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/osswriter/doc/osswriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>OCS</td> 
   <td align="center"></td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/ocswriter/doc/ocswriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>Hologres</td> 
   <td align="center"></td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/hologresjdbcwriter/doc/hologresjdbcwriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>AnalyticDB For PostgreSQL</td> 
   <td align="center"></td> 
   <td align="center">√</td> 
   <td align="center">写</td> 
  </tr> 
  <tr> 
   <td>阿里云中间件</td> 
   <td>datahub</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center">读 、写</td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>SLS</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center">读 、写</td> 
  </tr> 
  <tr> 
   <td>图数据库</td> 
   <td>阿里云 GDB</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/gdbreader/doc/gdbreader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/gdbwriter/doc/gdbwriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>Neo4j</td> 
   <td align="center"></td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/neo4jwriter/doc/neo4jwriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td>NoSQL数据存储</td> 
   <td>OTS</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/otsreader/doc/otsreader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/otswriter/doc/otswriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>Hbase0.94</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/hbase094xreader/doc/hbase094xreader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/hbase094xwriter/doc/hbase094xwriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>Hbase1.1</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/hbase11xreader/doc/hbase11xreader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/hbase11xwriter/doc/hbase11xwriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>Phoenix4.x</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/hbase11xsqlreader/doc/hbase11xsqlreader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/hbase11xsqlwriter/doc/hbase11xsqlwriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>Phoenix5.x</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/hbase20xsqlreader/doc/hbase20xsqlreader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/hbase20xsqlwriter/doc/hbase20xsqlwriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>MongoDB</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/mongodbreader/doc/mongodbreader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/mongodbwriter/doc/mongodbwriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>Cassandra</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/cassandrareader/doc/cassandrareader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/cassandrawriter/doc/cassandrawriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td>数仓数据存储</td> 
   <td>StarRocks</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center">读 、<a href="https://github.com/alibaba/DataX/blob/master/starrockswriter/doc/starrockswriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>ApacheDoris</td> 
   <td align="center"></td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/doriswriter/doc/doriswriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>ClickHouse</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/clickhousereader/doc/clickhousereader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/clickhousewriter/doc/clickhousewriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>Databend</td> 
   <td align="center"></td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/databendwriter/doc/databendwriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>Hive</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/hdfsreader/doc/hdfsreader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/hdfswriter/doc/hdfswriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>kudu</td> 
   <td align="center"></td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/hdfswriter/doc/hdfswriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>selectdb</td> 
   <td align="center"></td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/selectdbwriter/doc/selectdbwriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td>无结构化数据存储</td> 
   <td>TxtFile</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/txtfilereader/doc/txtfilereader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/txtfilewriter/doc/txtfilewriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>FTP</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/ftpreader/doc/ftpreader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/ftpwriter/doc/ftpwriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>HDFS</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/hdfsreader/doc/hdfsreader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/hdfswriter/doc/hdfswriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>Elasticsearch</td> 
   <td align="center"></td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/elasticsearchwriter/doc/elasticsearchwriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td>时间序列数据库</td> 
   <td>OpenTSDB</td> 
   <td align="center">√</td> 
   <td align="center"></td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/opentsdbreader/doc/opentsdbreader.md" rel="nofollow">读</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>TSDB</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/tsdbreader/doc/tsdbreader.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/tsdbwriter/doc/tsdbhttpwriter.md" rel="nofollow">写</a></td> 
  </tr> 
  <tr> 
   <td></td> 
   <td>TDengine</td> 
   <td align="center">√</td> 
   <td align="center">√</td> 
   <td align="center"><a href="https://github.com/alibaba/DataX/blob/master/tdenginereader/doc/tdenginereader-CN.md" rel="nofollow">读</a> 、<a href="https://github.com/alibaba/DataX/blob/master/tdenginewriter/doc/tdenginewriter-CN.md" rel="nofollow">写</a></td> 
  </tr> 
 </tbody> 
</table>

[DataX GitHub]: https://github.com/alibaba/DataX/blob/master/README.md
[DataX]: https://github.com/alibaba/DataX/wiki/DataX-all-data-channels