Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to replicate across databases between MySQL and PostgreSQL". In daily operations, I believe many people have doubts about how to replicate across databases between MySQL and PostgreSQL. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "how to replicate across databases between MySQL and PostgreSQL". Next, please follow the editor to study!

Databases that involves cross-database replication is generally referred to as heterogeneous databases. This is a good way to copy data from one RDBMS server to another server.

PostgreSQL and MySQL are both traditional RDBMS databases, but they also provide the capabilities of NoSQL. This paper mainly discusses the replication between PostgreSQL and MySQL from the point of view of RDBMS. The internal mechanism of replication is not described in detail, only some basic elements, how to configure, some points, limitations, and some use cases are described.

Usually, binary mode or query mode is used to replicate between two master and slave of the same kind. The purpose of replication is to get the real-time backup data of the master on the standby, thus forming an active-passive mode (because replication is configured with only one-way replication). Of course, it can also be configured to synchronize to build active-active mode.

The above two modes can be configured between two different database server, and one database server can be configured to receive replica data from the other completely different database server and maintain real-time snapshots of the replica data. MySQL and PostgreSQL complete the pattern mentioned above through native mechanisms or third-party plug-ins, including binlog methods, block methods, statement-based and row-based methods.

Because MySQL and PostgreSQL use different replication protocols, they cannot interact with each other. For the purpose of traffic flow, an open source software pg_chameleon can be used.

Pg_chameleon background

Pg_chameleon is a replication tool for MySQL to PG developed by python3. The plug-in also uses an open source library for mysql-replication, which is also developed by Python3. Pull the row mirror image from the MySQL table, store it as JSONB, and then synchronize it to the PG database. The PG database is parsed and played back through pl/pgsql.

Pg_chameleon characteristics

1. Multiple MySQL schema in the same cluster can be replicated to a single PG database, forming a many-to-one replication mode.

2. The source and destination schema names can be different.

3. Replication data can be pulled from mysql cascaded replicas.

4. Tables that fail to replicate and tables that produce errors during replication are excluded.

5. Each replication function is managed through the daemon

6. Configuration parameters and configuration files are controlled by yaml structure.

Demo

Host

Vm1

Vm2

Operating system

Centos linux release 7.6 x86_64

Centos linux release 7.5 x86_64

Database version

MySQL5.7.26

PostgreSQL10.5

Database port number

3306

5433

IP address

192.168.56.102

192.168.56.106

The first thing you need to install is Python, which he uses when creating a virtual environment and activating it.

$> wget $> tar-xJf Python-3.6.8.tar.xz$ > cd Python-3.6.8 $>. / configure-- enable-optimizations$ > make altinstall

After a successful installation, you need to create and activate the virtual environment. You also need to upgrade the pip module to the latest version. The latest version of pg_chameleon is 2.0.10. In order not to introduce a new bug, version 2.0.9 is recommended.

$> python3.6-m venv venv$ > source venv/bin/activate (venv) $> pip install pip-- upgrade (venv) $> pip install pg_chameleon==2.0.9

The next step is to enable pg_chameleon through the set_configuration_files configuration and create a default path and configuration file:

(venv) $> chameleon set_configuration_filescreating directory / root/.pg_chameleoncreating 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.yml

At this point, create a config-example.yml file as the default configuration file. A simple example of configuration is as follows:

Cat default.yml---#global settingspid_dir:'~ / .pg_chameleon/pid/'log_dir:'~ / .pg_chameleon/logs/'log_dest: filelog_level: infolog_days_keep: 10rollbar_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 connectionpg_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 files used in this article are modified from the sample files provided by pg_chameleon to adapt to the source and target environments. The following is a summary of the profile modification.

By default, .yml files have a "global settings" section to control details such as lock file location, log location, log retention period, and so on. Then comes the "type override" section, which is a collection of overridden types during replication. By default, sample type rewriting rules are used to convert tinyint (1) to a Boolean value. Then there is "pg_conn", which is the details of the target database connection. The last part is the source database information, which controls the connection of the source database, the direct schema mapping between the source and the target, the need to skip non-replicated tables, time-out, memory and other configurations. Note that "sources" indicates that there can be multiple sources.

The demo used in this article has a "world_x" database that includes four tables, and the MySQL community provides the download location: https://dev.mysql.com/doc/index-other.html.

In both MySQL and PostgreSQL, you need to create a dedicated user "usr_replica" for replication. In MySQL, the user needs to give additional permissions to access the tables that need to be replicated:

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

The PostgreSQL section creates a "db_replica" database to receive MySQL data. The "usr_replica" user in PG is automatically configured as the owner of two schemas (pgworld_x and sch_chameleon). These two schema contain the actual replication table and the catalog table. Automatically configure through the create_replica_schema parameter:

Postgres=# CREATE USER usr_replica WITH PASSWORD 'pass123';CREATE ROLEpostgres=# CREATE DATABASE db_replica WITH OWNER usr_replica;CREATE DATABASE

The MySQL configuration is as follows, and the service needs to be restarted to take effect:

$> vi / etc/my.cnfbinlog_format= ROWbinlog_row_image=FULLlog-bin = mysql-binserver-id = 1

At this point, you need to test whether the connection is normal to ensure that there are no problems when executing the pg_chameleon command:

PostgreSQL side:

$> mysql-u usr_replica-Ap'admin123'-h 192.168.56.102-D world_x

MySQL side:

Psql-p 5433-U usr_replica-h 192.168.56.106 db_replica

The following three commands for pg_chameleon are executed when setting up the environment, adding a source and initializing a standby. "create_replica_schema" creates the default schema (sch_chameleon) and copied schema (pgworld_x). "add_source" adds source database by reading the configuration file information, and in this article, "mysql". "init_replica" is initialized based on the configuration file.

Chameleon create_replica_schema-- debug$ > chameleon add_source-- config default-- source mysql-- debug$ > chameleon init_replica-- config default-- source mysql-- debug

After the above three commands are executed successfully, obvious success messages are output respectively. Any errors and grammatical errors will be clearly output.

The final step is to start replication through "start_replica":

$> chameleon start_replica-config default-source mysqloutput: Starting the replica process for source mysql

Display replication status through show_status:

$> chameleon show_status-source mysql OUTPUT: Source id Source name Type Status Consistent Read lag Last read Replay lag Last replay-- -1 mysql mysql running No Nash A NAccord A = = Schema mappings = = Origin schema Destination schema--world_x pgworld_x = = Replica status = =- -Tables not replicated 0Tables replicated 4All tables 4Last maintenance N/ANext maintenance N/AReplayed rowsReplayed DDLSkipped rows--$> chameleon show_errors-config default output: There are no errors in the log

View the daemon through the ps command:

$> ps-ef | grep chameleonroot 763 10 19:20? 00:00:00 / u01/media/mysql_samp_dbs/world_x-db/venv/bin/python3.6 / u01/media/mysql_samp_dbs/world_x-db/venv/bin/ chameleon start_replica-- config default-- source mysqlroot 764 763 0 19:20? 00:00:01 / u01/media/mysql_samp_dbs/world_x-db / venv/bin/python3.6 / u01/media/mysql_samp_dbs/world_x-db/venv/bin/ chameleon start_replica-- config default-- source mysqlroot 765 7630 19:20? 00:00:00 / u01/media/mysql_samp_dbs/world_x-db/venv/bin/python3.6 / u01/media/mysql_samp_dbs/world_x-db/venv/bin/ chameleon start_replica-- config default-- source mysql

The copy will not be completed until the real-time playback build. Involves creating tables and inserting data into the MySQL database; PG's sync_tables command updates the daemon and copies table records to PG:

Mysql > create table T1 (N1 int primary key, N2 varchar (10)); Query OK, 0 rows affected (0.01 sec) mysql > insert into T1 values; Query OK, 1 row affected (0.00 sec) mysql > insert into T1 values; Query OK, 1 row affected (0.00 sec) $> chameleon sync_tables-tables world_x.t1-config default-source mysqlSync tables process for source mysql started.

The test confirms that the replication is normal:

$> psql-p 5433-U usr_replica-d db_replica-c "select * from pgworld_x.t1"; N1 | N2 Musashi-1 | one 2 | two

If it is a migration requirement, execute the following command to mark the end of the migration. Execute these commands after all the tables that need to be copied have been copied:

$> chameleon stop_replica-config default-source mysql$ > chameleon detach_replica-config default-source mysql-debug

The following commands are optional:

$> chameleon drop_source-config default-source mysql-debug$ > chameleon drop_replica_schema-config default-source mysql-debugPros of Using pg_chameleon

It is easy to install and configure

The error log is easy to understand

There is no need to change any configuration, additional replication tables can be added after initialization

Can be configured for multi-source replication

You can specify which tables are not replicated

Cons of Using pg_chameleon

Replication from MySQL5.5 and above to Pg9.5 and above is only supported.

Each replicated table needs to have a primary or unique key

Only MySQL to PG

At this point, the study on "how to replicate across databases between MySQL and PostgreSQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.