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 that is used to migrate or synchronize data and supports simultaneous data import over multiple threads. Each worker thread imports data of specific database tables. The tool connects to the source MySQL database and the destination database, retrieves the data that you want to export from the source MySQL database, and then uses a COPY statement to import the data to the destination database. You can use the mysql2pgsql feature of the tool to migrate data from a MySQL database to an AnalyticDB for PostgreSQL database without the need to store data.

Parameter configuration

Modify the my.cfg file and configure the connection information of the source and destination databases.

Connection information of the source MySQL database:
Important
You must have the read permission on all user tables in the source MySQL database.
```
[src.mysql]
host = "192.168.1.1"
port = "3306"
user = "test"
password = "test"
db = "test"
encodingdir = "share"
encoding = "utf8"
```
Connection information of the destination database (such as PostgreSQL, PPAS, or AnalyticDB for PostgreSQL):
Important
You must have the write permission on the destination table in the destination database.
```
[desc.pgsql]
connect_string = "host=192.168.1.2 dbname=test port=3432  user=test password=pgsql"
```

Use the mysql2pgsql feature

Syntax of the mysql2pgsql feature:

./mysql2pgsql -l <tables_list_file> -d -n -j <number of threads> -s <schema of target table>

Parameters:

-l: optional. This parameter specifies a text file that contains tables whose data needs to be synchronized. If you do not specify this parameter, the data of all tables within the source database is synchronized. <tables_list_file> specifies the name of a file that contains a collection of tables that you want to synchronize and conditions for table queries. You can specify the file content in the following format:
```
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'
```
-d: optional. This parameter specifies that only the DDL statement used to create the destination table is generated and data is not synchronized.
-n: optional. This parameter specifies that the definitions of table partitions are not included in the DDL statement. You must use this parameter together with the -d parameter.
-j: optional. This parameter specifies the number of threads that are concurrently used to synchronize data. If you do not specify this parameter, five concurrent threads are used.
-s: optional. This parameter specifies the schema of the destination table. Set this parameter to public.

Common scenarios

Full database migration

To perform full database migration, perform the following steps:

Run the following command to obtain the DDL statement that is used to create a table in the destination database:
```
./mysql2pgsql -d
```
Use the DDL statement to create a table in the destination database, and then specify a distribution key for the table.
Run the following command to synchronize data of all tables:
```
./mysql2pgsql
```
The command migrates the data of all tables from the source MySQL database to the destination database. By default, five concurrent threads are used to read and import data from the tables.

Partial table migration

Create a file named tab_list.txt and enter the following content in the file:
```
t1
t2 : select * from t2 where c1 > 138888
```
Run the following command to synchronize data of the t1 and t2 tables. Only data that meets the c1 > 138888 condition is migrated for the t2 table.
```
./mysql2pgsql -l tab_list.txt
```

Download and instructions

Download the binary installation package of mysql2pgsql.
View the instructions for compiling the source code of mysql2pgsql.