A brief explanation of multiple source replication _mysql in Mysql

Oracle has recently released several new features in the latest Mysql5.7.2 version, and this is the article. Most of the improvements are in database performance and replication related functions, this new version will bring us incredible results.

In this article, I'm going to use some simple steps to try to understand how this new multi-source replication works and how we do our own tests. It should be explained that this is still a development version, not for the production environment. So this article is intended for those who want to understand the new features, see how it works in the application, are in the temporary environment for related operations.

What is multiple source replication?

First, we need to be clear that multi-master is not the same as multi-source replication. multi-master replication is usually circular replication, and you can copy data to other hosts on any host.

The multi-source is different. MySQL fixes a replication limit in this new release, which is limited to a single master station from the station. This is a limiting factor in our design of the replication environment, and some geeks make it work properly. But now there is an official solution. So. Simply put, multi-source means that one can have more than one master station from the station. Now, the replication environment, like the following figure, is possible:

This will help us create some replicated hierarchies that were not possible in the past. For example, you can put one from the station in your office. Copy data from all main stations in the office and spread to all parts of the world.

How does it work?

Now that we have the concept of communication channels, each communication channel is a link from the server to get 2 of the log from the primary server. This means that every communication channel has to have a io_thread. We need to run different change master commands for each primary 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 need to configure security features on mysql5.6 from the server means that information is usually included on the primary server. Information or logs should be in a table, let's start configuring it

Here's an example!

First you need to download the beta version of MySQL. Click this link: download.

We need to have a sandbox environment from the server and two primary servers. I will not detail how to configure server_id, binary logs and replication users. I assume it's already configured. If you don't know what to do. You can look at this link

First, we implement the accident security function from the server.

master_info_repository=table;
relay_log_info_repository=table;

Let the post reboot from the server. We can start to create communication channels, with "Master1″ and" Master2″ these two names:

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 from the server according to the name of the communication you set.

Slave > Start slave for channel= "Master1";
Slave > Start slave for channel= "Master2";

Now let's check the status from the server:

Slave > Show slave status\g
Empty Set (0.00 sec)

Ah. is empty. We need to write the name of the communication. Re-check the status from server:

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 Io_thread and sql_threads that are running:

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 to see:

Master1 > CREATE database master1;
Master2 > CREATE database master2;
Slave > Show databases like ' master% ';
+--------------------+
| Database (master%) |
+--------------------+
| master1      |
| master2
| +--------------------+

Normal work. It looks very simple!

Conclusion

This new multi-source feature makes it easy to create replication environments that require some complex operations in the past. Of course, your application can consider this new feature design and development, use multi-master, multi-source all need to pay special attention to, do not mess up your data.

In each new version, MySQL's replication capabilities give us more configuration, performance and design possibilities. All of these new features can be combined. Add replication to your new (old) features and your replication environment will be even better. For example: You can configure Gtid to use multi-threaded slave per schema or intra-database.