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

This article introduces you how to achieve multi-master replication in the MySQL database, the content is very detailed, interested friends can refer to, hope to be helpful to you.

MySQL manages each replication channel through a Master_info class (defined in sql/rpl_mi.h). Functions such as start_slave/change_master / stop_slave/show_slave/end_slave need to pass a Master_info pointer, which makes it very convenient for us to modify multi-Master feeds. Basically, you only need to pass the corresponding Master_info for each replication channel.

In addition to finding the function entry, you also need to make the syntax support multi-master, otherwise the CHANGE MASTER TO statement cannot support multi-master. I modified sql_yacc.yy to support the following syntax:

CHANGE MASTER 'Channel ID' TO,START SLAVE 'Channel ID', STOP SLAVE 'Channel ID', SHOW SLAVE 'Channel ID' STATUS.

In this way, the syntax of multi-Master is supported.

Another problem is how to save the information of multiple channels. In the case of a single channel by default, master.info is used to store Master information and relay-log.info is used to copy applications. So the name of the storage file also needs to be changed. My way is that master.info and relay-log.info are suffixed with the channel ID, such as the channel named "plx", which is saved as master.info.plx and relay-log.info.plx. Because Relay Log has a sequence, it adds "- channel identification" before the sequence.

Another problem is that the operation commands use the channel identity to determine a channel, so you must persist the channel name you are using, and you can use the channel name to get the corresponding Master_info after establishing the channel. So I created a new MASTER_INFO_INDEX class (in sql/rpl_mi.h), which contains a HASH table corresponding to the channel ID and Master_info pointer, as well as the IO_CACHE needed for persistence, which stores the existing channel identity through the master.info.index file.

The named examples are as follows:

Double-click the code 1 2 3 4 5 6 7 8 9 10 11 mysql 10 Feb 13 20:40 master.info.index-rw-rw-- 1 mysql mysql 76 Feb 14 17:27 master.info.plx1-rw-rw-- 1 mysql mysql 71 Feb 14 17:27 master.info.plx2-rw-rw-- 1 mysql mysql 90 Feb 14 17:25 relay-log.info.plx1-rw-rw-- 1 mysql mysql 90 Feb 14 17:27 relay-log.info. Plx2-rw-rw-- 1 mysql mysql 160Feb 14 10:16 mysql-relay-bin-plx1.000011-rw-rw-- 1 mysql mysql 83765425 Feb 14 17:27 mysql-relay-bin-plx1.000012-rw-rw-- 1 mysql mysql 106Feb 14 10:16 mysql-relay-bin-plx1.index-rw-rw-- 1 mysql mysql Feb 14 10:16 mysql-relay-bin-plx2.000014-rw-rw-- 1 mysql mysql 83455792 Feb 14 17:27 mysql-relay-bin-plx2. 000015-rw-rw-- 1 mysql mysql 106 Feb 14 10:16 mysql-relay-bin-plx2.index

Download Patch here: http://bugs.mysql.com/file.php?id=18020

What can we do with more Master? Here are two application scenarios.

The first is a backup with more than one backup. Because of the sub-repository strategy, we will have many instances in a cluster, and there will be several Schema in each instance, but there will certainly be no repetition. For example, the first example is Schema 1-3. The second example is 44th 6 Schema, so the application of binlog together does not conflict with the data. This is the online backup scheme we tested.

The second is HA across computer rooms. In order to recover from disaster or accelerate, many companies deploy databases in different computer rooms, so data synchronization is involved. In order to ensure that the data generated by each computer room do not conflict, generally speaking, we use the two parameters auto_increment_increment,auto_increment_offset, which can control the step. For example, for double MAster, we will configure ID with odd sequence in the main database and ID with even sequence in the slave database, so that even if a small amount of binlog has not been applied, it will not cause data conflicts. After crossing the data center, for example, if there is a double Master in both data centers, and the data between the two data centers needs to be synchronized, in the past, you need to use a third-party script or program to build multiple Master as follows, and set the step to 4 to ensure that each data center has a double MAster HA, and the data between the data rooms can be synchronized.

Known defects:

1. I have not done the reset slave 'channel identification' command, that is, the copy channel can not be reset, can only be changed by CHANGE MASTER, it is not impossible, because we do not have this requirement for the time being, we will consider this detail when it is stable.

two。 Data conflicts were not detected. This cannot be solved. I simply call the function that starts Slave to start multiple replication threads. When binlog is fetched to the local application, any data conflict cannot be detected in advance, and it will be reported only when it is executed. You can set skip-slave-error, which is valid for the whole world. Other replication-related issues are also globally valid.

On how to achieve multi-master replication in the MySQL database is shared here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.