A Brief Introduction to MySQL multi-source replication and mysql Replication

A Brief Introduction to MySQL multi-source replication and mysql Replication

Recently, ORACLE released several new features in the latest version of Mysql5.7.2. Most of the improvements are in terms of database performance and replication-related features. This new version will bring us incredible results.

In this article, I will try to understand the working principle of this new multi-source replication and how we can perform our own tests with some simple steps. It should be noted that this is a development version, not for the production environment. Therefore, this article is intended for those who want to learn about this new feature to see how it works in the application, all of which are related operations in the temporary environment.

What is multi-source replication?

First, we need to know that multi-master and multi-source replication are not the same. Multi-Master replication is generally a circular replication. You can copy data to other hosts on any host.

Multi-source is different. mySQL fixes a replication restriction in this new version, which means that only one master site can be used for one slave site. this is a limiting factor in our design replication environment, and some geeks make it work normally. But now there is an official solution. So. To put it simply, Multi-Source means that a slave station can have more than one master station. Now, it is possible to have the same replication environment:

This will help us create some replication hierarchies, which was impossible in the past. For example, you can place a slave station in your office. Copy data from all the main sites in the office and spread it all over the world.
 
How does it work?

Now we have the concept of a communication channel. Every communication channel is a link that gets binary logs from the master server from the server. This means that each communication channel has an IO_THREAD. We need to run different "change master" commands for each MASTER server. We need to use the "for channel" parameter to provide the name of the communication link.
 

CHANGE MASTER MASTER_HOST='something', MASTER_USER=... FOR CHANNEL="name_of_channel";

It's easy. There is a single prerequisite. The slave server needs to configure the security function on mysql5.6, which means the information is usually contained on the master server. Information or logs should be in a table. Let's get started with configuration.

Here is an example!

First, you need to download the mysql test version. Click this link to download.

We need a sandbox environment for the slave server and two master servers. I will not explain in detail how to configure server_id, binary logs and replication users. I suppose it has been configured. If you do not know how to do this. You can see this link

First, we implement the accident security function on the slave server.

 

master_info_repository=TABLE;relay_log_info_repository=TABLE;

Restart the slave server. We can start to create communication channels with the names "master1" and "master2:
 

slave > change master to master_host="127.0.0.1", master_port=12047, master_user="msandbox",master_password="msandbox" for channel="master1";slave > change master to master_host="127.0.0.1", master_port=12048, master_user="msandbox",master_password="msandbox" for channel="master2";

Start the slave server according to the specified communication name.
 

slave > start slave for channel="master1";slave > start slave for channel="master2";

Now let's check the slave server status:
 

slave > show slave status\GEmpty set (0.00 sec)

Ah. Is empty. We need to write the communication name. Re-check the slave server status:
 

slave > SHOW SLAVE STATUS FOR CHANNEL="master1"\G*************************** 1. row ***************************        Slave_IO_State: Waiting for master to send event         Master_Host: 127.0.0.1         Master_User: msandbox         Master_Port: 12047        Connect_Retry: 60       Master_Log_File: mysql-bin.000002     Read_Master_Log_Pos: 232        Relay_Log_File: squeeze-relay-bin-master1.000003        Relay_Log_Pos: 395    Relay_Master_Log_File: mysql-bin.000002       Slave_IO_Running: Yes      Slave_SQL_Running: Yes[...]

We can also check the running IO_THREAD and SQL _THREADS:
 

slave > SHOW PROCESSLIST;+----+-------------+-----------------------------------------------------------------------------+| Id | User    | State                                    |+----+-------------+-----------------------------------------------------------------------------+| 2 | system user | Waiting for master to send event                      || 3 | system user | Slave has read all relay log; waiting for the slave I/O thread to update it || 4 | system user | Waiting for master to send event                      || 5 | system user | Slave has read all relay log; waiting for the slave I/O thread to update it |+----+-------------+-----------------------------------------------------------------------------+

Test:
 

master1 > create database master1;master2 > create database master2;slave > show databases like 'master%';+--------------------+| Database (master%) |+--------------------+| master1      || master2      |+--------------------+

Working properly. It looks simple!

Conclusion

This new multi-source function simplifies the replication environment that requires some complex operations in the past. Of course, your application can consider this new feature design and development. You must pay special attention to the use of multi-master and multi-source. Do not confuse your data.

In each new version, the mysql replication feature gives us more configuration, performance, and design possibilities. All these new features can be combined. Add the replication feature to your new (old) features, and your replication environment will be better. For example, you can configure GTID by using multi-threaded slave per schema or intra-database.