I will provide an overview of cross-replication between PostgreSQL and MySQL, as well as methods for setting up cross-replication between these two database servers. Databases in cross-replication are usually referred to as homogeneous, and this is a convenient method for transitioning from one relational database server to another.
PostgreSQL and MySQL databases are commonly considered relational; however, with additional extensions, they offer NoSQL capabilities. Here, we will discuss replication between PostgreSQL and MySQL from the perspective of relational databases.
We will not describe all the intricate details, only the basic principles, so that you gain an understanding of setting up replication between database servers, its advantages, limitations, and use cases.
Typically, replication between two identical database servers is performed either in binary mode or through queries between the primary node (also known as the publisher, master, or active) and the secondary (subscriber, stand-by, or passive). The purpose of replication is to provide a real-time copy of the main database on the secondary side. The data is transmitted from the primary to the secondary, meaning from active to passive, because replication occurs only in one direction. However, it is possible to set up replication between two databases in both directions, so that data can be transmitted from the secondary to the primary in an 'active-active' configuration. All of this, including cascading replication, is possible between two or more identical database servers. The 'active-active' or 'active-passive' configuration depends on the needs, availability of such capabilities in the initial configuration, or the use of external solutions for setup and the existing trade-offs.
The described configuration is possible between different database servers. A server can be configured to receive replicated data from another database server while storing snapshots of the replicated data in real time. MySQL and PostgreSQL offer most of these configurations intrinsically or through third-party extensions, including binary log methods, disk locking, and operator- and row-based methods.
Cross-replication between MySQL and PostgreSQL is needed for one-time migration from one database server to another. These databases use different protocols, so they cannot be connected directly. To establish data exchange, an external open-source tool can be used, such as pg_chameleon.
What is pg_chameleon
pg_chameleon is a replication system from MySQL to PostgreSQL built with Python 3. It utilizes the open-source mysql-replication library, also written in Python. Row snapshots are extracted from MySQL tables and saved as JSONB objects in the PostgreSQL database, where they are later decoded by the pl/pgsql function and reproduced in the PostgreSQL database.
Capabilities of pg_chameleon
Multiple MySQL schemas from a single cluster can be replicated into a single target PostgreSQL database under a 'one to many' configuration.
The names of the source and target schemas cannot be the same.
Replication data can be extracted from a cascading MySQL replica.
Tables that cannot be replicated or cause errors are excluded.
Each replication function is managed by demons.
Control via parameters and configuration files based on YAML.
Example
Host
vm1
vm2
OS Version
CentOS Linux 7.6 x86_64
CentOS Linux 7.5 x86_64
Database Server Version
MySQL 5.7.26
PostgreSQL 10.5
Database Port
3306
5433
IP address
192.168.56.102
192.168.56.106
First, prepare all the necessary components for installing pg_chameleon. In this example, Python 3.6.8 is installed, which creates a virtual environment and activates it.
$> wget https://www.python.org/ftp/python/3.6.8/Python-3.6.8.tar.xz
$> tar -xJf Python-3.6.8.tar.xz
$> cd Python-3.6.8
$> ./configure --enable-optimizations
$> make altinstallAfter successfully installing Python 3.6, the remaining requirements must be fulfilled, such as creating and activating the virtual environment. Additionally, the pip module is updated to the latest version and used to install pg_chameleon. The commands below intentionally install pg_chameleon 2.0.9, even though the latest version is 2.0.10. This is to avoid new bugs in the updated version.
$> python3.6 -m venv venv
$> source venv/bin/activate
(venv) $> pip install pip --upgrade
(venv) $> pip install pg_chameleon==2.0.9Then we call pg_chameleon (chameleon is the command) with the argument set_configuration_files to enable pg_chameleon and create the default configuration directories and files.
(venv) $> chameleon set_configuration_files
creating directory /root/.pg_chameleon
creating directory /root/.pg_chameleon/configuration/
creating directory /root/.pg_chameleon/logs/
creating directory /root/.pg_chameleon/pid/
copying configuration example in /root/.pg_chameleon/configuration//config-example.ymlWe are now creating a copy of config-example.yml as default.yml so that it becomes the default configuration file. A sample configuration file for this example is provided below.
$> cat default.yml
---
#global settings
pid_dir: '~/.pg_chameleon/pid/'
log_dir: '~/.pg_chameleon/logs/'
log_dest: file
log_level: info
log_days_keep: 10
rollbar_key: ''
rollbar_env: ''
# type_override allows the user to override the default type conversion into a different one.
type_override:
"tinyint(1)":
override_to: boolean
override_tables:
- "*"
#postgres destination connection
pg_conn:
host: "192.168.56.106"
port: "5433"
user: "usr_replica"
password: "pass123"
database: "db_replica"
charset: "utf8"
sources:
mysql:
db_conn:
host: "192.168.56.102"
port: "3306"
user: "usr_replica"
password: "pass123"
charset: 'utf8'
connect_timeout: 10
schema_mappings:
world_x: pgworld_x
limit_tables:
# - delphis_mediterranea.foo
skip_tables:
# - delphis_mediterranea.bar
grant_select_to:
- usr_readonly
lock_timeout: "120s"
my_server_id: 100
replica_batch_size: 10000
replay_max_rows: 10000
batch_retention: '1 day'
copy_max_memory: "300M"
copy_mode: 'file'
out_dir: /tmp
sleep_loop: 1
on_error_replay: continue
on_error_read: continue
auto_maintenance: "disabled"
gtid_enable: No
type: mysql
skip_events:
insert:
- delphis_mediterranea.foo #skips inserts on the table delphis_mediterranea.foo
delete:
- delphis_mediterranea #skips deletes on schema delphis_mediterranea
update:The configuration file in this example is a sample of a pg_chameleon file with minor changes according to the source and target environments, and below is an overview of the various sections of the configuration file.
The default.yml configuration file includes a global settings section where you can manage settings such as the lock file location, log location, log retention period, etc. Next, there is a type override section, which specifies a set of rules for overriding types during replication. The default example uses a type override rule that converts tinyint(1) to a boolean value. In the next section, we specify the connection details for the target database, in our case, a PostgreSQL database referred to as pg_conn. In the final section, we outline the source data, that is, the connection parameters for the source database, schema mappings between the source and target databases, tables to skip, timeout settings, memory settings, and batch size. Note that 'sources' is indicated in plural, meaning we can add multiple source databases for a single target to configure a 'many-to-one' setup.
The world_x database in this example contains 4 tables with rows that the MySQL community provides as a sample. It can be downloaded. The sample database is provided as a tar file and a compressed archive with instructions for creating and importing rows.
In MySQL and PostgreSQL databases, a special user named usr_replica is created. In MySQL, this user is given additional rights to read all replicated tables.
mysql> CREATE USER usr_replica;
mysql> SET PASSWORD FOR usr_replica='pass123';
mysql> GRANT ALL ON world_x.* TO 'usr_replica';
mysql> GRANT RELOAD ON *.* to 'usr_replica';
mysql> GRANT REPLICATION CLIENT ON *.* to 'usr_replica';
mysql> GRANT REPLICATION SLAVE ON *.* to 'usr_replica';
mysql> FLUSH PRIVILEGES;On the PostgreSQL side, a database named db_replica is created, which will receive changes from the MySQL database. The usr_replica user in PostgreSQL is automatically set up as the owner of two schemas: pgworld_x and sch_chameleon, which contain the actual replicated tables and replication catalog tables, respectively. The automatic configuration is handled by the create_replica_schema argument, as you will see below.
postgres=# CREATE USER usr_replica WITH PASSWORD 'pass123';
CREATE ROLE
postgres=# CREATE DATABASE db_replica WITH OWNER usr_replica;
CREATE DATABASEThe MySQL database is configured with changes to some parameters to prepare it for replication, as shown below. The database server will need to be restarted for the changes to take effect.
$> vi /etc/my.cnf
binlog_format=ROW
binlog_row_image=FULL
log-bin=mysql-bin
server-id=1It is now important to check the connection to both database servers to avoid issues while executing pg_chameleon commands.
On the PostgreSQL node:
$> mysql -u usr_replica -Ap'admin123' -h 192.168.56.102 -D world_xOn the MySQL node:
$> psql -p 5433 -U usr_replica -h 192.168.56.106 db_replicaThe following three pg_chameleon (chameleon) commands prepare the environment, add the source, and initialize the replica. The create_replica_schema argument in pg_chameleon creates the default schema (sch_chameleon) and the replication schema (pgworld_x) in the PostgreSQL database, as we mentioned earlier. The add_source argument adds the source database to the configuration by reading the configuration file (default.yml), which in our case is mysql, and init_replica initializes the configuration based on the parameters in the configuration file.
$> chameleon create_replica_schema --debug
$> chameleon add_source --config default --source mysql --debug
$> chameleon init_replica --config default --source mysql --debugThe outputs of these three commands clearly indicate their successful execution. All failures or syntax errors are indicated in straightforward messages with tips for troubleshooting the issues.
Finally, let's initiate replication using start_replica and receive a message about successful execution.
$> chameleon start_replica --config default --source mysql
output: Starting the replica process for source mysqlThe replication status can be queried using the show_status argument, while errors can be viewed using the show_errors argument.
As we mentioned, each replication function is managed by demons. To view them, we can query the process table using the Linux ps command, as shown below.
Replication is not considered set up until we test it in real-time, as shown below. We create a table, insert a couple of records into the MySQL database, and call the sync_tables argument in pg_chameleon to update the demons and replicate the table with records in the PostgreSQL database.
mysql> create table t1 (n1 int primary key, n2 varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values (1,'one');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values (2,'two');
Query OK, 1 row affected (0.00 sec)$> chameleon sync_tables --tables world_x.t1 --config default --source mysql
Sync tables process for source mysql started.To confirm the test results, we query the table from the PostgreSQL database and output the rows.
$> psql -p 5433 -U usr_replica -d db_replica -c "select * from pgworld_x.t1";
n1 | n2
----+-------
1 | one
2 | twoIf we are performing a migration, the following pg_chameleon commands will complete it. These commands should be executed after we have confirmed that all target table rows have been replicated, resulting in a neatly transferred PostgreSQL database without references to the original database or replication schema (sch_chameleon).
$> chameleon stop_replica --config default --source mysql
$> chameleon detach_replica --config default --source mysql --debugIf desired, the following commands can be used to remove the original configuration and replication schema.
$> chameleon drop_source --config default --source mysql --debug
$> chameleon drop_replica_schema --config default --source mysql --debugBenefits of pg_chameleon
Easy setup and configuration.
Convenient troubleshooting and anomaly detection with clear error messages.
Additional special tables can be added to replication after initialization without changing other configurations.
You can configure multiple source databases for one target, which is very convenient if you are combining data from one or more MySQL databases into a single PostgreSQL database.
You can exclude selected tables from replication.
Disadvantages of pg_chameleon
Only supported with MySQL 5.5 and above as the source and PostgreSQL 9.5 and above as the target database.
Each table must have a primary or unique key; otherwise, the tables are initialized during the init_replica process but are not replicated.
One-way replication — only from MySQL to PostgreSQL. Therefore, it is suitable only for an 'active-passive' scheme.
The source can only be a MySQL database, and support for PostgreSQL databases as a source is only experimental and comes with limitations (learn more )
Summary on pg_chameleon
The replication method in pg_chameleon is well-suited for migrating databases from MySQL to PostgreSQL. A significant drawback is that replication is one-way only, so database specialists are unlikely to want to use it for anything other than migration. However, the problem of one-way replication can be addressed with another open-source tool — SymmetricDS.
Read more in the official documentation . Command line help can be found .
Overview of SymmetricDS
SymmetricDS is an open-source tool that replicates any database to any other mainstream database: Oracle, MongoDB, PostgreSQL, MySQL, SQL Server, MariaDB, DB2, Sybase, Greenplum, Informix, H2, Firebird, and other cloud database instances like Redshift and Azure, etc. Available features include: database and file synchronization, replication from multiple source databases, filtered synchronization, transformation, and more. It is a Java-based tool, requiring a standard JRE or JDK (version 8.0 or higher). Changes in data can be recorded through triggers in the source database and sent to the corresponding target database in the form of batches.
Features of SymmetricDS
The tool is platform-independent, meaning that two or more different databases can exchange data.
Relational databases are synchronized by recording data changes, while file-based databases use file synchronization.
Bidirectional replication using Push and Pull methods based on a set of rules.
Data transfer is possible over secure networks and networks with low bandwidth.
Automatic recovery when nodes resume operation after a failure and automatic conflict resolution.
Cloud compatibility and efficient extension APIs.
Example
SymmetricDS can be configured in one of two ways:
A master (parent) node that centrally coordinates data replication between two slave (child) nodes, with data exchange between child nodes occurring only through the parent.
An active node (node 1) can exchange data for replication with another active node (node 2) without an intermediary.
In both scenarios, data exchange occurs using Push and Pull. In this example, we will consider the "active-active" configuration. Describing the entire architecture is too lengthy, so check , to learn more about the SymmetricDS device.
Installing SymmetricDS is very simple: download the open-source version zip file and extract it wherever you like. The table below provides information about the installation location and version of SymmetricDS in this example, as well as database versions, Linux versions, IP addresses, and ports for both nodes.
Host
vm1
vm2
OS Version
CentOS Linux 7.6 x86_64
CentOS Linux 7.6 x86_64
Database Server Version
MySQL 5.7.26
PostgreSQL 10.5
Database Port
3306
5832
IP address
192.168.1.107
192.168.1.112
SymmetricDS version
SymmetricDS 3.9
SymmetricDS 3.9
Installation path of SymmetricDS
/usr/local/symmetric-server-3.9.20
/usr/local/symmetric-server-3.9.20
Name of the SymmetricDS node
corp-000
store-001
Here we install SymmetricDS in /usr/local/symmetric-server-3.9.20, and various nested directories and files will be stored here. We are interested in the nested directories samples and engines. The samples directory contains example configuration files with node properties, as well as example SQL scripts for quickly getting started with the demonstration.
In the samples directory, we see three configuration files with node properties — the name indicates the nature of the node within a specific schema.
corp-000.properties
store-001.properties
store-002.propertiesSymmetricDS has all the necessary configuration files for a basic schema of 3 nodes (option 1), and the same files can be used for a schema of 2 nodes (option 2). We copy the required configuration file from the samples directory to the engines on host vm1. It looks like this:
$ > cat engines/corp-000.properties
engine.name=corp-000
db.driver=com.mysql.jdbc.Driver
db.url=jdbc:mysql://192.168.1.107:3306/replica_db?autoReconnect=true&useSSL=false
db.user=root
db.password=admin123
registration.url=
sync.url=http://192.168.1.107:31415/sync/corp-000
group.id=corp
external.id=000This node in the SymmetricDS configuration is called corp-000, and the connection to the database is handled by the MySQL JDBC driver, which uses the connection string specified above and the login credentials. We connect to the database replica_db, and during schema creation, tables will be created. sync.url indicates the connection point with the node for synchronization.
Node 2 on host vm2 is configured as store-001, and the rest is specified in the node.properties file provided below. The store-001 node uses a PostgreSQL database, and pgdb_replica is the database for replication. registration.url enables host vm2 to contact host vm1 and obtain configuration details from it.
$> cat engines/store-001.properties
engine.name=store-001
db.driver=org.postgresql.Driver
db.url=jdbc:postgresql://192.168.1.112:5832/pgdb_replica
db.user=postgres
db.password=admin123
registration.url=http://192.168.1.107:31415/sync/corp-000
group.id=store
external.id=001The complete example of SymmetricDS contains parameters for setting up bidirectional replication between two database servers (two nodes). The steps below are executed on host vm1 (corp-000), which will create a sample schema with 4 tables. Next, running create-sym-tables with the symadmin command creates the directory tables that will store the rules and direction of replication between the nodes. Finally, sample data is loaded into the tables.
vm1$> cd /usr/local/symmetric-server-3.9.20/bin
vm1$> ./dbimport --engine corp-000 --format XML create_sample.xml
vm1$> ./symadmin --engine corp-000 create-sym-tables
vm1$> ./dbimport --engine corp-000 insert_sample.sqlIn the example, the item and item_selling_price tables are automatically configured for replication from corp-000 to store-001, while the sale tables (sale_transaction and sale_return_line_item) are automatically set up for replication from store-001 to corp-000. Now we create the schema in the PostgreSQL database on host vm2 (store-001) to prepare it for receiving data from corp-000.
vm2$> cd /usr/local/symmetric-server-3.9.20/bin
vm2$> ./dbimport --engine store-001 --format XML create_sample.xmlBe sure to check that the MySQL database on vm1 has sample tables and SymmetricDS directory tables. Note that the SymmetricDS system tables (with the prefix sym_) are currently only available on the corp-000 node because we executed the create-sym-tables command there and will manage replication. Additionally, there will be only 4 sample tables without data in the store-001 node's database.
That's it. The environment is ready to launch sym server processes on both nodes, as shown below.
vm1$> cd /usr/local/symmetric-server-3.9.20/bin
vm1$> sym 2>&1 &Log entries are sent to the background log file (symmetric.log) in the logs folder in the directory where SymmetricDS is installed, as well as to standard output. The sym server can now be initiated on the node store-001.
vm2$> cd /usr/local/symmetric-server-3.9.20/bin
vm2$> sym 2>&1 &If you run the sym server process on the host vm2, it will create SymmetricDS catalog tables in the PostgreSQL database as well. If you run the sym server process on both nodes, they will coordinate with each other to replicate data from corp-000 to store-001. If we query all 4 tables on both sides after a few seconds, we will see that replication was successful. Alternatively, you can send an initial load to store-001 from corp-000 with the following command.
vm1$> ./symadmin --engine corp-000 reload-node 001At this stage, a new record is inserted into the item table in the MySQL database on the node corp-000 (host: vm1), and you can check its replication to the PostgreSQL database on the node store-001 (host: vm2). We can see a Pull operation moving data from corp-000 to store-001.
mysql> insert into item values ('22000002','Jelly Bean');
Query OK, 1 row affected (0.00 sec)vm2$> psql -p 5832 -U postgres pgdb_replica -c "select * from item"
item_id | name
----------+-----------
11000001 | Yummy Gum
22000002 | Jelly Bean
(2 rows)To perform a Push operation to move data from store-001 to corp-000, we insert a record into the sale_transaction table and check that replication was successful.
We can see a successful setup of bidirectional replication for the sample tables between the MySQL and PostgreSQL databases. To set up replication for new user tables, we follow these steps. We create a table t1 as an example and configure its replication rules as follows. Thus, we only configure replication from corp-000 to store-001.
mysql> create table t1 (no integer);
Query OK, 0 rows affected (0.01 sec)mysql> insert into sym_channel (channel_id,create_time,last_update_time)
values ('t1',current_timestamp,current_timestamp);
Query OK, 1 row affected (0.01 sec)mysql> insert into sym_trigger (trigger_id, source_table_name,channel_id,
last_update_time, create_time) values ('t1', 't1', 't1', current_timestamp,
current_timestamp);
Query OK, 1 row affected (0.01 sec)mysql> insert into sym_trigger_router (trigger_id, router_id,
Initial_load_order, create_time,last_update_time) values ('t1',
'corp-2-store-1', 1, current_timestamp,current_timestamp);
Query OK, 1 row affected (0.01 sec)Then the configuration receives a notification of the schema change, specifically the addition of a new table, via the symadmin command with the argument sync-triggers, which recreates triggers to match the table definitions. A send-schema operation is executed to send schema changes to node store-001, and replication of table t1 is set up.
vm1$> ./symadmin -e corp-000 --node=001 sync-triggers
vm1$> ./symadmin send-schema -e corp-000 --node=001 t1Advantages of SymmetricDS
Easy installation and configuration, including a ready-made set of parameter files for creating a schema with three or two nodes.
Cross-platform database compatibility and independence from platform, including servers, laptops, and mobile devices.
Replication of any database to any other database locally, in WAN, or in the cloud.
Optimal operation with a pair of databases or several thousand for convenient replication.
Paid version with a graphical interface and excellent support.
Disadvantages of SymmetricDS
It is necessary to manually define replication rules and directions in the command line using SQL operators to load catalog tables, which can be inconvenient.
Setting up many tables for replication can be tedious unless scripts are used to create SQL operators that define the rules and directions of replication.
The logs can contain too much information, and sometimes it is necessary to tidy up the log file to prevent it from taking up too much space.
Summary of SymmetricDS
SymmetricDS allows for configuring bidirectional replication between two, three, and even several thousand nodes to perform replication and synchronize files. This unique tool independently carries out many tasks, such as automatic data recovery after prolonged downtime on a node, secure and efficient data exchange between nodes via HTTPS, automatic conflict management based on a set of rules, etc. SymmetricDS performs replication between any databases, making it suitable for a wide range of scenarios, including migration, upgrading to a new version, distribution, filtering, and transforming data across different platforms.
The example is based on the official on SymmetricDS. In Various concepts related to configuring replication using SymmetricDS are described in detail.
Source: habr.com
