Use rds_dbsync to migrate or synchronize data from a MySQL database to an AnalyticDB for PostgreSQL database - AnalyticDB

rds_dbsync is an open source tool for migrating or synchronizing data from MySQL to AnalyticDB for PostgreSQL (and other PostgreSQL-compatible databases). The mysql2pgsql feature connects directly to both databases, reads data from MySQL, and loads it into the destination using a COPY statement — no intermediate storage required. Data is imported in parallel across multiple worker threads, each handling a specific set of tables.

Prerequisites

Before you begin, ensure that you have:

Read permission on all user tables in the source MySQL database
Write permission on the destination tables in the AnalyticDB for PostgreSQL database
The mysql2pgsql binary downloaded from the rds_dbsync releases page, or compiled from source

Quick start

To migrate all tables from MySQL to AnalyticDB for PostgreSQL:

Configure the source and destination connections in my.cfg (see Configure connections).
Generate the DDL for the destination tables:
```
./mysql2pgsql -d
```
Run the generated DDL in AnalyticDB for PostgreSQL to create the tables. Specify a distribution key for each table.
Import all data:
```
./mysql2pgsql
```

This runs the migration using five concurrent threads by default.

Configure connections

Edit my.cfg to set the connection details for your source and destination databases.

Source MySQL database — the [src.mysql] section:

[src.mysql]
host = "192.168.1.1"
port = "3306"
user = "test"
password = "test"
db = "test"
encodingdir = "share"
encoding = "utf8"

Destination database — the [desc.pgsql] section (supports PostgreSQL, PPAS, and AnalyticDB for PostgreSQL):

[desc.pgsql]
connect_string = "host=192.168.1.2 dbname=test port=3432 user=test password=pgsql"

Run mysql2pgsql

./mysql2pgsql -l <tables_list_file> -d -n -j <thread_count> -s <schema>

All parameters are optional:

Parameter	Description	Default	Example
`-l <file>`	Path to a text file listing the tables to synchronize. If omitted, all tables in the source database are synchronized.	All tables	`-l tab_list.txt`
`-d`	Generate only the DDL statement for creating the destination table. Data is not synchronized.	Off	`-d`
`-n`	Exclude partition definitions from the DDL statement. Use with `-d`.	Off	`-d -n`
`-j <n>`	Number of concurrent threads for data import.	`5`	`-j 8`
`-s <schema>`	Schema of the destination table. Set to `public` for AnalyticDB for PostgreSQL.	—	`-s public`

Tables list file format

When using -l, create a plain text file where each line specifies a table name and an optional filter condition:

table1 : select * from table_big where column1 < '2016-08-05'
table2 :
table3
table4: select column1, column2 from tableX where column1 != 10
table5: select * from table_big where column1 >= '2016-08-05'

Lines with no condition (e.g., table3) migrate all rows.
Lines with a SELECT condition migrate only the matching rows.

Common scenarios

Full database migration

Generate the DDL statement:
```
./mysql2pgsql -d
```
Run the DDL in AnalyticDB for PostgreSQL to create the tables. Specify a distribution key for each table.
Migrate all data:
```
./mysql2pgsql
```
By default, five concurrent threads read and import the data.

Partial table migration

Create a file named tab_list.txt and list the tables to migrate. Add a filter condition to migrate only a subset of rows:
```
t1
t2 : select * from t2 where c1 > 138888
```
This migrates all rows from t1 and only rows where c1 > 138888 from t2.
Run the migration:
```
./mysql2pgsql -l tab_list.txt
```

AnalyticDB:Use rds_dbsync to migrate or synchronize data from a MySQL database to an AnalyticDB for PostgreSQL database