MySQL Master server replication principle

In the actual enterprise application environment, the single MySQL database is insufficient to meet the future business requirements. For example, if a server fails and there is no backup server to provide the service, the business will have to stop. In this case, let's learn MySQL master-slave replication.
The distribution of MySQL data across multiple systems is done by copying data from one of the MySQL hosts to the other host (slaves) and re-executing it again. One server acts as the primary server during replication, while one or more other servers act as slave servers. The primary server writes the update to the binary log file and maintains an index of the file to track the log loop. These logs can record updates that are sent to the slave server. When a primary server is connected from the server, it notifies the primary server where the last successful update was read from the server in the log. Receive any updates from the server from then on, and then block and wait for the primary server to notify the new updates.
Note that when you make a copy, all updates to the tables in the replication must be made on the primary server. Otherwise, you must be careful to avoid conflicts between updates to tables on the primary server and updates made to tables on the server.

1.mysql Supported Replication types:
1) Statement-based replication: SQL statements executed on the primary server execute the same statement from the server. MySQL uses statement-based replication by default and is more efficient.
Row-based replication is automatically selected as soon as it is found that it cannot be copied accurately.
2) Row-based replication: Copy the changed content past, rather than executing the command on the server. Support Starting from mysql5.0
3) Mixed-type replication: statement-based replication is used by default, and row-based replication occurs when a statement-based, inaccurate copy is found.

2. Replication-Resolved issues
MySQL replication technology has some of the following features:
Data distribution (distribution)
Load balancing (Load Balancing)
Backup (Backups)
High-availability and fault-tolerant rows of availability and failover

3. The benefits of using MySQL master-slave replication are:
1) with the master-slave server This architecture, stability can be improved. If the primary server fails, we can use the slave server to provide the service.
2) processing the user's request separately on the master-slave server can improve the data processing efficiency.
3) Copy the data on the master server to the slave server to protect the data from accidental loss.

4. How replication works
As a whole, there are 3 steps to replication:
1) Master will change the record to binary log (these are called binary logs events, binary log event);
2) slave copies the binary log events of master to its trunk logs (relay log);
3) Slave Redo the event in the trunk log and change the data to reflect its own.
Describes the process of replication:

The first step: the master records the binary log.
Master records These changes in binary logging before each transaction updates the data. MySQL writes the transaction serially to the binary log, even if the statements in the transaction are cross-executed. After the event is written to the binary log, master notifies the storage engine to commit the transaction.
The second step is that slave copies the binary log of master to its own trunk logs.
1) slave start a worker thread--i/o thread. The I/O thread opens a normal connection on master and then starts Binlog dump process. Binlog dump process reads the event from the binary log of master, and if it has been followed by master, it sleeps and waits for master to produce a new event.
2) I/O threads write these events to the relay log.
Step three: SQL slave thread (SQL slave thread) handles the last step of the process.
The SQL thread reads events from the log and replays the events in them to update the slave data so that it is consistent with the data in master. As long as the thread is consistent with the I/O thread, the trunk log is typically located in the OS cache, so the overhead of the trunk log is minimal.

Note: In addition, there is a worker thread in master: As with other MySQL connections, slave opening a connection in master will also cause master to start a thread. The replication process has a very important limitation-replication is serialized on slave, meaning that parallel update operations on Master cannot operate concurrently on slave.

MySQL Master server replication principle