Build MySQL dual Master mode high-availability cluster system through keepalived

Build MySQL dual Master mode high-availability cluster system through keepalived

I. MYSQL Replication Introduction:

mysql replication is a master-slave replication feature provided by MySQL itself, which is a MySQL server (slave) that replicates logs from another MySQL server (master) and then parses the log application process to itself. MYSQL replication is a one-way, asynchronous replication.

MYSQL replication supports chained replication, which means that the slave server can also be linked to slave servers, while slave servers can also act as master roles. In MySQL master-slave replication, all table updates must be run on the master server, and the slave server can only provide query operations.

Advantages:

1, increased the robustness of the MySQL application, if the master server problems, you can switch to the slave server at any time, continue to provide services

2, can be MySQL read and write separation, write operations only on the master server, read operations can be done on multiple slave servers, because the master server and slave server is to maintain data synchronization, so there is no impact on the front-end business system. At the same time, the running load of MySQL can be greatly reduced by the reading and writing separation.

3, in a good network environment, small business environment, slave server synchronization data quickly, the basic can achieve real-time synchronization, and slave server in the synchronization process will not interfere with the master server.

MYSQL replication supports multiple types of replication, common with statement-based replication, row-based replication, and mixed-type replication.

1, statement-based replication:

MySQL uses statement-based replication by default and is highly efficient. The basic approach is to execute the SQL statement on the master server and execute the same statement again on the slave server. Row-based replication is automatically selected when the exact copy is not found

2, row-based replication:

The basic approach is to copy the changed content from the master server to the past, rather than the SQL statement from the server.

3, mixed-type replication:

is based on the above two types of hair combinations, the default is statement-based replication, if the discovery of statement-based replication can not be done accurately, the use of row-based replication

Two. MYSQL Replication Implementation principle:

MYSQL replication is an asynchronous process that replicates from master to one or more slave, where the entire replication process between master and slave is done primarily by three threads, one of which IO threads On the master side, another two threads (SQL thread and IO thread) on the slave side

To implement MySQL replication, first open the MySQL binary log (generate binary logfile) function on the master server, because the entire replication process is actually slave to get the log from the master side, The binaries are then parsed into SQL statements on their own and the various operations recorded by the SQL statements are executed in full order.

Detailed

1, first slave on the IO line thread attached connect to master, then request from the specified log file at the specified location or from the beginning of the log location after the contents of the log

After receiving the IO thread request from slave, 2,master reads the log information after the specified log location according to the request information through its IO thread, and returns the IO thread to the slave end, in addition to the information contained in the log. It also includes the name of the binary log file and the location in binary log of the returned information on the master side.

After the 3,slave IO thread receives the information, the log content is fetched to the slave end of the relay log file (like mysql-relay-bin.xxxxx) at the end, and the binary that will be read to the master side Log's file name and location are recorded in a file named Master-info so that the next read can be quickly positioned to begin reading the log information.

When the 4,slave SQL thread detects a new addition to the relay log file, it immediately resolves the contents of the relay log file, resolves the log content to an SQL statement, and executes the SQL on its own. Since the same SQL operation was performed on both the master and the slave side, the data on both ends is exactly the same, and at this point the entire replication process is complete.

This article from "High Good bright" blog, reproduced please contact the author!

Build MySQL dual Master mode high-availability cluster system through keepalived