Easily build MySQL high-availability cluster system

First, the implementation of MySQL replication principle

MySQL supports one-way, bidirectional replication, asynchronous replication, one server acting as the primary server during the replication process, and one or more other servers acting as a server. The master server writes the update to a binary log file and creates an index file to track the log loop. These logs can record updates sent to the server. When a primary server is connected from a server, the log file notifies the primary server of the location of the last successful update that was read from the server in the log. Next, start the update operation from the server at the location where the last successful update was made. After the update completes, the wait state is started from the server and waits for subsequent updates from the primary server.

It is important to note that all updates to the tables in replication must be done on the primary server while replication is in progress. Otherwise, there may be a conflict between updates to the tables on the primary server and updates to the tables on the server.

One-way replication facilitates robustness, speed, and system management.

The primary server/server settings increase robustness. When there is a problem with the primary server, you can switch to from the server.

Better customer response times can be obtained by splitting the load from the primary server and from the server to handle the customer query. A select query can be sent to a server to reduce the query processing load on the primary server. However, the statement that modifies the data should still be sent to the primary server, so that the primary server and the server remain synchronized.

MySQL provides the database synchronization function, which is very helpful to the implementation of database redundancy, backup, recovery, load balancing and so on.

In general, in MySQL replication, the primary server is also called Master, and the second from the server is called slave, therefore, to enable synchronization mechanism, you must enable the binary log on master. Each slave accepts update operations that are recorded in the binary log from master, and is equivalent to performing a copy of this operation on slave.

Second, MySQL synchronization details

The MySQL synchronization feature is implemented by 3 threads (1 binlog dump,slave on master, respectively, SQL processes and IO processes). After the "START SLAVE" statement is executed, SLAVE creates an I/O thread. The I/O line Chenglian to master and requests master to send the statements in the binary log. Master creates a thread to send the contents of the log to the slave.

The I/O thread on the slave reads the statements sent by Master's Binlog dump thread and copies them to the relay log (relay logs) in its data directory. The third is the SQL thread, which salve use to read the relay logs and then execute them to update the data.

The advantage of using 2 threads on Slave is that it separates read logs from execution into 2 separate tasks. If you perform a task slowly, the read log task will not slow down. For example, if slave stops for a while, the I/O thread can quickly read all the logs from Master after the slave startup, even though the SQL thread may be behind the I/O thread for several hours. If the slave is stopped when the SQL thread is not fully executed, the I/O thread has read all the update logs and saved them in the local relay log, so they will continue to execute after slave starts again. This allows the binary log to be purged on master because slave no longer has to read the update log to master.

This article explains the environment: a MySQL Master master node Node1, three MySQL slave node, three slave nodes from the primary node for real-time synchronization data, MySQL high availability cluster host information as shown in Figure 1:

Figure 1