Applicable scenarios of MySQL master-slave and dual-master architectures

The applicable scenario of the MySQL master-slave architecture is read/write splitting, which reads data from the master database. In most scenarios, there are many read cases, the single master multi-slave architecture can solve the problem of high read pressure well.

Ps: // oddyzfr8z.qnssl.com/wp-content/uploads/2016/09/master-slave.jpg "/>

The applicable scenario of the MySQL dual-master architecture is as follows: for example, a different server is used at the front and back ends of an application. Most databases use a master-slave structure, but the log database must share one, in this case, the log databases on both sides can be set as dual-master.

A feature of the dual-master database is that the same operation is performed on database B for any operations on database A, and the opposite is also true. Another phenomenon can be found that the incremental value of the auto-increment ID of the inserted data of databases A and B is generally 2, which avoids primary key conflicts.

3. Learn more about replication
 
3.1 Statement-based replication (Statement-BasedReplication)
 
MySQL and earlier versions only support statement-based replication (also called logical replication and logical replication), which is not common in databases. The master records the query of changed data. Then, slave reads the event from the relay log and runs it. These SQL statements are the same as the statements executed by the master.
This method is easy to implement. In addition, statement-based replication of binary logs can be well compressed, and the log data volume is small, consuming less bandwidth-for example, a query that updates GB data requires only dozens of bytes of binary logs. Mysqlbinlog is very convenient for statement-based log processing.
However, statement-based replication is not as simple as it looks, because some query statements depend on specific conditions of the master. For example, the master and slave may have different times. Therefore, the format of MySQL binary logs is not only a query statement, but also metadata information, such as the current timestamp. Even so, Some statements, such as the current user function, cannot be copied correctly. In addition, stored procedures and triggers are also a problem.
Another problem is that statement-based replication must be serialized. This requires a lot of special code, such as the next-key lock of InnoDB. Not all storage engines support statement-based replication.

3.2 record-Based Replication)
MySQL adds record-based replication and records changes in actual data in binary logs, which is similar to other DBMS implementations. This method has advantages and disadvantages. The advantage is that it can work correctly on any statement, and some statements are more efficient. The main drawback is that binary logs may be large and not intuitive. Therefore, you cannot use mysqlbinlog to view binary logs.
For some statements, record-based replication can work more effectively, such:
Mysql> insert into summary_table (col1, col2, sum_col3)
-> SELECT col1, col2, sum (col3)
-> FROM enormous_table
-> Group by col1, col2;
Assume that there are only three unique combinations of col1 and col2. However, this query scans many rows in the original table, but returns only three records. In this case, record-based replication is more efficient.
On the other hand, the following statements make statement-based replication more effective:
Mysql> UPDATE enormous_table SET col1 = 0;
In this case, the cost of using record-based replication is very high. Because the two methods cannot be well handled in all cases, MySQL 5.1 supports dynamic exchange before statement-based replication and record-based replication. You can control it by setting the binlog_format session variable.

3.3 Copy related files
In addition to binary logs and relay log files, there are other files related to replication. As follows:
(1) mysql-bin.index
Once binary logs are enabled on the server, a file with the same name as the binary log file but ended with. index is generated. It is used to track which binary log files exist on the disk. MySQL uses it to locate binary log files.
(2) mysql-relay-bin.index
The file features similar to the mysql-bin.index, but it is for relay logs, not binary logs.
(3) master.info
Saves the information required when the server is connected to the master service. Each row has one value to save the information about the master. Do not delete it. Otherwise, you cannot connect to the master after the slave is restarted.
(4) relay-log.info
Stores the copy location: the file and location of the binary log and relay log.

3.4 send replication events to other slave instances
When log_slave_updates is set, you can have slave act as the master of other slave instances. In this case, slave writes the SQL thread-executed events to its own binary log. Then, its slave can get these events and execute them. As follows:
WKioL1NOPNPSXgYeAAGOF1KjhsU124.jpg

3.5 Replication Filters)
Replication filtering allows you to repeat only a portion of the data in the *** server. There are two types of replication filtering: filtering events in binary logs on the master, and filtering events in relay logs on the slave. As follows:
WKioL1NOPYSwF1NvAAF4FoH_Luk325.jpg

4. Common topology of replication
The replication architecture has the following basic principles:
(1) each slave can have one or more Masters;
(2) each slave can have only one unique server ID;
(3) each master can have many slave instances;
(4) If log_slave_updates is set, slave can be the master of another slave, thus spreading master updates.
In the early stages of MySQL, multi-master Replication was not supported. That is, a server load balancer can have multiple master nodes. However, MariaDB currently supports multiple master nodes. However, through some simple combinations, we can build a flexible and powerful replication architecture.

4.1. Single master and multi-slave
A replication system composed of a master and a slave is the simplest case. Slave does not communicate with each other and can only communicate with the master.
In practical application scenarios, MySQL replication over 90% is an architecture mode where one Master node is replicated to one or more Slave instances. It is mainly used for database expansion solutions for applications with high read pressure. As long as the pressure on the Master and Slave is not too high (especially on the Slave end), the latency of asynchronous replication is usually very small. Especially since the replication method on the Slave end is changed to two threads for processing, the delay problem on the Slave end is reduced. The benefit is that the real-time data requirements are not especially Critical. You only need to use a cheap pcserver to expand the number of Slave and distribute the read pressure to multiple Slave machines, the read performance bottleneck of the database can be solved by dispersing the read pressure of a single database server. After all, the read pressure in most database application systems is much higher than the write pressure. This solves the database pressure bottlenecks of many small and medium-sized websites to a large extent, and even some large websites are using similar solutions to solve database bottlenecks.
As follows:
WKiom1NOQB6wKpInAACPFzueKiI373.jpg
This structure can be used if the number of write operations is small and the number of read operations is very high. You can distribute read operations to other slave instances to reduce the pressure on the master node. However, when slave increases to a certain number, slave's load on the master and network bandwidth will become a serious problem.
This structure is simple, but flexible enough to meet the needs of most applications. Some suggestions:
(1) different slave plays different roles (for example, using different indexes or different storage engines );
(2) use a server load balancer instance as the slave master and only copy the slave database;
(3) use a remote slave for disaster recovery;
It should be clear that multiple Slave nodes can be copied from a Master node. Some may wonder if the Slave node can be copied from multiple Master nodes? This can be fully implemented. Currently, multi-master replication has been implemented in MariaDB. For details, see MariaDB multi-source (master) replication.

4.2 Active Mode Master-Master (Master-Master in Active-Active Mode)
Two Master-Master replication servers are both the master and the slave of the other server. In this way, any change made by either party will be copied and applied to the database of the other party.
Some readers may have a worry. After the replication environment is set up, won't circular replication between two MySQL instances? In fact, MySQL has long thought of this, so the current MySQL server-id is recorded in MySQL BinaryLog, and this parameter must be explicitly specified when we set up MySQLReplication, in addition, the server-id parameter values of Master and Slave must be different in order to successfully build MySQL replication. Once the value of server-id is available, MySQL can easily determine which MySQLServer was originally generated for a change, so it is easy to avoid loop replication. Moreover, if we do not enable the BinaryLog option (-- log-Slave-update) for recording slave, MySQL will not record the changes in the replication process to BinaryLog, you don't have to worry about the possibility of loop replication.
As shown in the following figure:
WKioL1NOP_SAmSANAABJisdmItQ735.jpg
Active Master-Master replication has some special functions. For example, both geographically distributed parts require their own writable data copies. The biggest problem with this structure is the update conflict. Assume that a table has only one row (one column) and its value is 1. If the two servers execute the following statement simultaneously:
Run the following command on the first server:
Mysql> UPDATE tbl SET col = col + 1;
Run the following command on the second server:
Mysql> UPDATE tbl SET col = col * 2;
So what is the result? One server is 4 and the other server is 3, but this does not produce errors.
In fact, MySQL does not support multi-master Replication supported by other DBMS ), this is a huge limitation of the MySQL replication function (the difficulty of multi-master servers lies in resolving update conflicts). However, if you have such requirements, you can use MySQL Cluster, by combining Cluster and Replication, you can build a powerful and high-performance database platform. However, you can simulate the replication of multiple master servers in other ways.

Example: Dual-Master mode configuration:
Environment:
Master1/Master2 Platfrom ip app Version
Master1 CentOS6.5 _ X86-64 172.16.41.1 mariadb-10.0.10
Master2 CentOS6.5 _ X86-64 172.16.41.2 mariadb-10.0.10
1. Configure dual-master:
# Master1 configuration:
(1) modify server-id
In the main configuration file/etc/my. cnf [mysqld], modify the following lines:
Server-id = 1
(2) enable relay logs and binary logs
In the main configuration file/etc/my. cnf [mysqld], modify the following lines:
Log-bin =/mydata/binlogs/master1-bin
Relay-log =/mydata/relaylogs/relay-bin

Make sure that the relay log option is enabled
MariaDB [(none)]> show global variables like 'relay _ log ';
+ --------------- + ----------------------------- +
| Variable_name | Value |
+ --------------- + ----------------------------- +
| Relay_log |/mydata/relaylogs/relay-bin |
+ --------------- + ----------------------------- +
1 row in set (0.00 sec)

(3) add the following two items to avoid selecting non-conflicting values for the INSERT statement automatically in MySQL.
Auto-increment-offset = 1 // start value
Auto-increment = 2 // step size

Make sure that the automatic growth option is enabled:
MariaDB [(none)]> show global variables like 'auto _ inc % ';
+ -------------------------- + ------- +
| Variable_name | Value |
+ -------------------------- + ------- +
| Auto_increment_increment | 2 |
| Auto_increment_offset | 1 |
+ -------------------------- + ------- +
(4) Create a user with the copy permission. The command is as follows:
MariaDB [(none)]> create user 'luccy' @ '2017. 16.41.2 'identified BY 'qazwsx123 ';
MariaDB [(none)]> revoke all privileges, grant option from 'luccy' @ '2017. 16.41.2 ';
MariaDB [(none)]> grant reload, lock tables, replication client, replication slave on *. * TO 'luccy' @ '2017. 16.41.2 ';
MariaDB [(none)]> flush privileges;

# Master2 configuration:
(1) modify server-id #
In the main configuration file/etc/my. cnf [mysqld], modify the following lines:
Server-id = 2
(2) enable relay logs and binary logs
Log-bin =/mydata/binlogs/master2-bin
Relay-log =/mydata/relaylogs/relay-bin
Make sure that the relay log option is enabled
MariaDB [(none)]> show global variables like 'relay _ log ';
+ --------------- + ----------------------------- +
| Variable_name | Value |
+ --------------- + ----------------------------- +
| Relay_log |/mydata/relaylogs/relay-bin |
+ --------------- + ----------------------------- +
(3) add the following two items to avoid selecting non-conflicting values for the INSERT statement automatically in MySQL.
Auto-increment-offset = 2 // start value
Auto-increment = 2 // step size

Make sure that the automatic growth option is enabled:
MariaDB [(none)]> show global variables like 'auto _ inc % ';
+ -------------------------- + ------- +
| Variable_name | Value |
+ -------------------------- + ------- +
| Auto_increment_increment | 2 |
| Auto_increment_offset | 2 |
+ -------------------------- + ------- +
(4) Create a user with the copy permission. The command is as follows:
MariaDB [(none)]> create user 'Jerry '@ '2017. 16.41.1 'identified BY 'qazwsx123 ';
MariaDB [(none)]> revoke all privileges, grant option from 'Jerry '@ '2017. 16.41.1 ';
MariaDB [(none)]> grant reload, lock tables, replication client, replication slave on *. * TO 'Jerry '@ '2017. 16.41.1 ';
MariaDB [(none)]> flush privileges;
If the two servers are newly created and no other write operations are performed, each server only needs to record its own binary log file and event location, and use it as the starting position for copying another server.
# Master1:

MariaDB [(none)]> show master status;
+ -------------------- + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ -------------------- + ---------- + -------------- + ------------------ +
| Master1-bin.000001 | 969 |
+ -------------------- + ---------- + -------------- + ------------------ +
# Master2:
MariaDB [(none)]> show master status;
+ -------------------- + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ -------------------- + ---------- + -------------- + ------------------ +
| Master2-bin.000001 | 314 |
+ -------------------- + ---------- + -------------- + ------------------ +
Each server then specifies another server as its primary server
# Master1 points to Master2
MariaDB [(none)]> change master to MASTER_USER = 'luccy', MASTER_HOST = '2017. 16.41.2 ', MASTER_PASSWORD = 'qazwsx123', MASTER_LOG_FILE = 'master2-bin.000005 ', MASTER_LOG_POS = 328;

# Master2 points to Master1
MariaDB [(none)]> change master to MASTER_USER = 'Jerry ', MASTER_HOST = '2017. 16.41.1 ', MASTER_PASSWORD = 'qazwsx123', MASTER_LOG_FILE = 'master1-bin.000005 ', MASTER_LOG_POS = 1592;
Start Server replication process
# Master1
MariaDB [(none)]> start slave;
MariaDB [mysql]> show slave status \ G
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.41.2
Master_User: jerry
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master2-bin.000006
Read_Master_Log_Pos: 328
Relay_Log_File: relay-bin.000004
Relay_Log_Pos: 617
Relay_Master_Log_File: master2-bin.000006
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB:
..........
..........
# Master2
MariaDB [(none)]> start slave;

MariaDB [(none)]> show slave status \ G
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.41.1
Master_User: luccy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master1-bin.000005
Read_Master_Log_Pos: 1592
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 537
Relay_Master_Log_File: master1-bin.000005
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB:
..........
..........

Dual-master test:
(1) create a database testdb on Master1:
MariaDB [(none)]> create database testdb;
MariaDB [(none)]> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mysql |
| Performance_schema |
| Test |
| Testdb |
+ -------------------- +
View the result on Master2 is the same as that on Master1!
(2) create a database mydb on Master1:
MariaDB [(none)]> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Mydb |
| Mysql |
| Performance_schema |
| Test |
| Testdb |
+ -------------------- +
The result on Master1 is the same as that on Master2!
(3) create a table mytable1 on mydb of Master1 and insert the statement
MariaDB [(none)]> use mydb
MariaDB [mydb]> create table mytable1 (id int AUTO_INCREMENT unique key, Name CHAR (20 ));
MariaDB [mydb]> DESC mytable1;
+ ------- + ---------- + ------ + ----- + --------- + ---------------- +
| Field | Type | Null | Key | Default | Extra |
+ ------- + ---------- + ------ + ----- + --------- + ---------------- +
| ID | int (11) | NO | PRI | NULL | auto_increment |
| Name | char (20) | YES | NULL |
+ ------- + ---------- + ------ + ----- + --------- + ---------------- +
MariaDB [mydb]> insert into mytable1 (Name) VALUES ('Larry '), ('Jim'), ('Jerry ');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

MariaDB [mydb]> SELECT * FROM mytable1;
+ ---- + ------- +
| ID | Name |
+ ---- + ------- +
| 1 | larry | // The effect of automatic growth. The starting value defined on Master1 is 1 and the step diameter is 2.
| 3 | jim |
| 5 | jerry |
+ ---- + ------- +
Insert a field on Master2 (the above content has been synchronized to Master2)
MariaDB [mydb]> insert into mytable1 (Name) VALUES ('zhangsan'), ('li si'), ('Wang Wu ');
MariaDB [mydb]> SELECT * FROM mytable1;
+ ---- + ----------- +
| ID | Name |
+ ---- + ----------- +
| 1 | larry |
| 3 | jim |
| 5 | jerry |
| 6 | Zhang San |
| 8 | Li Si |
| 10 | Wang Wu |
+ ---- + ----------- +
// It seems that this is not the expected result. Although MySQL automatically selects values for INSERT, there will no conflict with each other. However, it seems that the values are not sorted by number and the solution to this problem cannot be found, unless you do not select dual-Master mode!

 

4.3 Active-Passive Mode Master-Master (Master-Master in Active-Passive Mode)
This is a change in the master-master structure, which avoids the disadvantages of the M-M, in fact, it is a fault tolerance and high availability system. The difference is that one service can only perform read-only operations. As shown in the following figure:
WKiom1NRQvyD4Q1dAABIsY-WKGc195.jpg

 

4.4 cascade replication architecture Master-Slaves
In some application scenarios, the read/write pressure may be significantly different, and the read pressure may be extremely high. A Master may need 10 or more server load balancers to support the read injection pressure. At this time, the Master will be more difficult, because there are more SlaveIO threads connected only, so when the write pressure is a little higher, the Master node consumes a lot of resources because of replication, which can easily lead to replication latency.
How can this problem be solved? At this time, we can use MySQL to record the changed BinaryLog information generated by replication on the Slave end, that is, enable the-log-slave-update option. Second-level (or more) replication reduces the pressure on the Master node due to replication. That is to say, we first use a few MySQL instances to replicate from the Master. We call these machines the first-level Slave cluster, then, other Slave instances are copied from the first-level Slave cluster. The server load balancer instance that replicates data from the first-level server load balancer instance, which is called the second-level server load balancer cluster. If necessary, we can continue to add more levels of replication. In this way, we can easily control the number of Slave attached to each MySQL. This architecture is called the Master-Slaves architecture.
This multi-layer cascade replication architecture easily solves the risk that the Master node becomes a bottleneck because there are too many Slave instances. The following figure shows the Replication architecture of multi-layer cascade Replication.
WKiom1NRQv6RNnTtAAEAh7azRwo469.jpg
Of course, if conditions permit, I would suggest you split them into multiple Replication clusters.
The above bottlenecks. After all, Slave does not reduce the write volume, and all Slave actually applies all data change operations without any write IO reduction. On the contrary, the more Slave, the more I/O writes to the entire cluster. We do not have a very obvious feeling. It is just because it is scattered across multiple machines, so it is not very easy to express.
In addition, the Cascade level of replication is increased, and the same change is required to pass to the bottom-layer Slave, which may lead to a longer delay. If we solve this problem by splitting the cluster, it may be much better. Of course, splitting the cluster also requires more complex technologies and more complex application system architecture.

4.5 Master-Master structure with slave servers (Master-Master with Slaves)
This structure provides redundancy. Geographically distributed replication structure, which does not have a single node failure problem, and can also put read-intensive requests on slave.
WKioL1NRQtPwj73_AACDaBTeaWw819.jpg
To a certain extent, Cascade replication solves the bottleneck caused by the excessive number of Slave instances attached to the Master, however, it cannot solve the problem of manual maintenance and abnormal switchover. This naturally extends the Replication architecture that combines DualMaster with Cascade Replication. I call it the Master-Slaves architecture.
Compared with the Master-Slaves architecture, the difference is only that the first-level Slave cluster is replaced with a separate Master, as a backup Master, then copy the Slave to a Slave cluster.
This architecture combines DualMaster with Cascade replication. The biggest benefit is that it can avoid the impact of write operations on the Master from the replication of the Slave cluster, at the same time, when the Master needs to be switched, Replication will basically not be redeployed. However, this architecture also has a drawback, that is, the standby Master may become a bottleneck, because if the subsequent Slave cluster is large, the standby Master may become a bottleneck due to excessive SlaveIO thread requests. Of course, when the standby Master does not provide any Read services, the bottleneck may not be very high. If a bottleneck occurs, you can perform cascade replication again after the standby Master, build a multi-layer Slave cluster. Of course, the more levels of Cascade replication, the more obvious the data delay that may occur in the Slave cluster. Therefore, before using cascade replication, you also need to evaluate the impact of data delay on the application system.