MySQL Database Architecture and Synchronous Replication Process

In a distributed system, the bottleneck that often restricts the development of the entire system is the database. Therefore, the structure and high availability of the database and the segmentation of the database are worthy of our efforts to learn.

First, let's talk about the database architecture.

1. mysql master-slave architecture

This architecture is basically a database architecture that more than 90% will use. Advantages of this architecture: 1. Simple database architecture 2. Convenient maintenance Disadvantages: 1. The master has a single point problem and cannot be shut down for maintenance. 2. If there are too many slaves, the master-slave synchronization will affect the performance of the master.

2. Dual Master replication architecture
The above master-slave architecture has an obvious single-point master problem, and the master cannot be shut down for maintenance, then there is a dual-master architecture for the next monkey.

In this dual-master architecture, the database has two masters, and there is two-way replication between the master and the master, but our application must not have a write port, and two masters cannot write at the same time, so as to avoid possible occurrences In the case of data inconsistency, the other host depends on the situation. How to read the pressure is high, the other host can do the read operation, the reading pressure is not high, and the other host is purely to do more work as the main backup. Three-party tools do the main and backup switching, such as keepalived. The advantages of this dual-master architecture: 1. There is no single-point problem with the master. Disadvantages: 1. When the reading pressure is high, it can’t handle it.

3. Cascade replication architecture (Master --- Slaves --- Slaves...)...)
The cascading replication architecture is to solve the problem of a large number of slaves connecting to the master causing the performance of the master to degrade. The slave synchronizes the data to another slave, so that fewer slaves are connected to the master node. Advantages: 1. Reduce the pressure of master copying data. Disadvantages 1. There is a problem of data delay

4. Dual Master and cascade replication combined architecture (Master-Master-Slaves)
The previous dual-master and cascading replication have problems. Now this architecture combines these two architectures. This architecture combines the advantages of the previous two architectures. 1. There is no single point of master problem. 2. It reduces the number of write nodes. Disadvantages of master's replication pressure 1. After the write node master hangs, another master becomes a write node. After the hung master starts, all slaves must be switched to the started master for data synchronization.

I mentioned 4 kinds of databases before, and a lot of them mentioned the concept of database synchronization, so let's talk about the mysql database synchronization process.
mysql defaults to the asynchronous replication mode, and the process is as follows: master 1. user submit 2. write binlog log 3. engine level submit 4. result return to client program

The slave side 1, the master side event monitors the binlog log changes and informs the dump process on the master side 2, the dump process informs the IO process on the slave side 3, the IO process obtains the files and file locations that need to be synchronized from the master-info 4, the IO process Notify the master dump process of the binlog file and pos location. 5. The master prepares the data and synchronizes the data to the slave. 6. The slave receives the data and writes the data to the relay log. 7. After the slave relay log is written successfully, it gives the master a successful response.

mysql asynchronous replication configuration master side my.cnf configuration server-id=135 #Enable replication function log-bin=mysql-bin auto_increment_increment=2 auto_increment_offset=1 lower_case_table_names=1 #binlog-do-db=mstest //mstest database to be synchronized , To synchronize multiple databases#binlog-ignore-db=mysql //Database to be ignored

My.cnf configuration on the slave side server-id=133 log-bin=mysql-bin auto-increment-increment=2 auto-increment-offset=2 lower_case_table_names=1 #replicate-do-db = wang #Database that needs to be synchronized#binlog -ignore-db = mysql #binlog-ignore-db = information_schema

1. Add permissions GRANT REPLICATION SLAVE, FILE, REPLICATION CLIENT ON in master mysql. TO'repluser'@'%' IDENTIFIED BY '123456'; FLUSH PRIVILEGES;

2. View the binary log of the master on the master show master status;





3. Set the master information in the slave change master to master_host='192.168.88.135',master_port=3307,master_user='repluser',master_password='Jack@123456',master_log_file='mysql-bin.000001',master_log_pos= 154;

4. Turn on the slave, start SQL and IO threads start slave;

5. View the status of the slave show slave status\G

6, master slave view process information SHOW PROCESSLIST;

mysql enhanced semi-synchronous replication Enhanced semi-synchronous replication configuration: 1. Load lib, all master and slave nodes must configure the master library: install plugin rpl_semi_sync_master soname'semisync_master.so'; slave library: install plugin rpl_semi_sync_slave soname'semisync_slave.so' ; Can be installed together. It is recommended to install together, because there will be a master-slave switch scenario

2. Check to make sure that all nodes have successfully loaded show plugins;

3. Enable semi-synchronization. First enable the parameters on the slave library, and finally enable the parameters of the main library. Slave library: set global
rpl_semi_sync_slave_enabled = {0|1}; # 1: enable, 0: disable Main library: set global
rpl_semi_sync_master_enabled = {0|1}; # 1: enable, 0: disable set global
rpl_semi_sync_master_timeout = 10000; # The unit is ms

4. After the previous configuration is completed, the io_thread node must be closed on the slave node to make the semi-synchronous replication effective stop slave io_thread; start slave io_thread;

Master node log information: enable semi-synchronous, close asynchronous





Semi-synchronous replication waits when the master side submits the engine. After the slave writes the relay log successfully, the master ACK response is successful before the engine submission.