High-performance MySQL cluster (ii)

I. Build a high-availability cluster system with MySQL dual master mode via keepalived

1.MySQL Replication Introduction:

MySQL replication is a master-slave replication feature provided by MySQL itself, which is a process by which a MySQL server (called slave) replicates logs from another MySQL server (called master) and then parses the logs and applies them to itself. MySQL replication is a one-way, asynchronous replication, with the basic replication process: The master server first writes the update to the binary log file and maintains an index of the file to track the loop of the log that can be sent to the slave server for updates. When a slave server connects to the master server, it reads the location of the last successful update from the master server log. The slave server then starts receiving all updates that occurred since the last update was completed, and all updates are completed, waiting for the primary server to notify the new updates.

MySQL replication supports chained replication, which means that the slave server can also be linked slave server, while slave server can also act as the master server role. It is important to note that in MySQL master-slave replication, all table updates must be made on the master server, and the slave server can only provide query operations.

MySQL replication technology based on one-way replication has the following advantages:

-Added robustness to the MySQL application, and if there is a problem with the master server, you can switch to the slave server at any time to continue serving.

MySQL read and write operations can be separated, the write operation is completed only on the master server, the read operation can be done on multiple slave servers, because the master server and the slave server is keeping the data synchronized, 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 separation of reading and writing.

-In a better network environment, the traffic is not very large environment, slave server synchronization data is very fast, basically can achieve real-time synchronization, and the 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. The following are described separately.

(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 it is not accurate to replicate.

(2) Row-based replication

The basic approach is to copy the changed content from the master server to the past, rather than having the SQL statement run from the server again, and support row-based replication starting with MySQL5.0.

(3) Mixed-type replication

In fact, it is the combination of the above two types, the default is statement-based replication, if you find that statement-based replication can not be done accurately, you will take a row-based replication.

2.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 IO thread on the master side, The other two threads (SQL thread and IO thread) are 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. A more detailed procedure is as follows.

1) First the IO line on the slave is thread attached connected to master, then requests the log content from the specified location of the log file or from the beginning of the log location.

2) master, after receiving the IO thread request from Slave, reads the log information after the specified log location through its IO thread, and returns to the IO thread on the slave side, based on the request information. In addition to the information contained in the log, the returned information includes the name of the binary log file on the master side of the returned information and its location in binary log.

3) after the slave IO thread receives the information, the retrieved log content is written to the slave end of the relay log file (similar to mysql-relay-bin.xxxxxx), 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 quickly locate where the log information will begin to be read back.

4) Slave SQL thread will parse the contents of the relay log file, parse the log content into an SQL statement, and then execute the SQL on its own after it detects the new additions to the relay log file. Since the same SQL operation was performed on the master and slave ends, the data on both ends is exactly the same. This completes the entire replication process.

3.MySQL Replication Common Architecture

MySQL replication Technology has a variety of implementation architectures in practical applications, common:

☆ One master at a time, that is, a master server and a slave server. This is the most common architecture.

★ A master multi-slave, that is, a master server and two or more slave servers, often used in write operations infrequently, the query volume is larger than the business environment.

☆ Master Master Mutual Preparation, also known as dual-master mutual preparation, that is, two MySQL server each other as their own master, and also as the other side of the slave to replicate. It is mainly used in the environment of high requirement for MySQL write operation, which avoids MySQL single point failure.

★ Double Master more from, in fact, is the dual master mutual preparation, and then add more than one slave server. Mainly used for MySQL write operation requirements are relatively high, while the query volume is larger than the environment.

In fact, the master/slave structure can be flexibly changed according to the specific situation, but original aim, before the various deployment of MySQL replication, the following rules must be followed:

◇ only one master server can write at the same time.

A master server can have more than one slave server.

◇ whether it is the master server or the slave server, we must ensure that the respective ServerID are unique, otherwise there will be problems with the two-master mutual preparation.

An slave server can pass the update information it obtains from the master server to other slave servers.

4. MySQL Master Master Interop mode architecture

Enterprise MySQL clusters are highly available, scalable, manageable, and cost-efficient. The following will introduce a solution that is often applied in the enterprise environment, that is, MySQL's dual-Master interoperability architecture, the main design idea is to use MySQL replication technology to each other as their own master, And also as the other side of the slave to replicate. This enables data synchronization in a highly available architecture, while keepalived is used to automate MySQL failover. In this architecture, although the two MySQL servers are mainly from each other, but only one MySQL server can read and write at the same time, and the other MySQL server can only read operations, so as to ensure data consistency. The entire architecture, such as:

5.MySQL Primary Master Interop mode configuration

The configuration of MySQL master-slave replication is still relatively simple, just need to modify the MySQL configuration file, here to configure the main master Interop mode, but the configuration process and a master one from the structure is exactly the same, the configuration environment is as follows:

Host name OS version mysql version host IP mysql VIP

DB1 (Master) CentOS release 6.7 mysql-5.1.73 10.0.0.35 10.0.0.40

DB2 (Slave) CentOS release 6.7 mysql-5.1.73 10.0.0.36

1. Modify the MySQL configuration file

by default, the MySQL configuration file is/etc/my.cnf, first modify the DB1 host's configuration file, in the/etc/my.cnf file in the "[Mysqld]" section to add the following:

Server-id=1

Log-bin=mysql-bin

Relay-log=mysql-relay-bin

Replicate-wild-ignore-table=mysql.%

replicate-wild-ignore-table=test.%

replicate-wild-ignore-table=information_schema.%

Then modify the configuration file for the DB2 host and add the following in the "[mysqld]" segment in the/etc/my.cnf file:

server-id=2

Log-bin=mysql-bin

Relay-log=mysql-relay-bin

Replicate-wild-ignore-table=mysql.%

replicate-wild-ignore-table=test.%

replicate-wild-ignore-table=information_schema.%

where Server-id is the node identifier, the primary and slave nodes cannot be the same and must be globally unique. Log-bin is the Binlog log feature that turns on MySQL. "Mysql-bin" indicates the name format of the log file and generates a log file named mysql-bin.000001,mysql-bin.000002, and so on. Relay-log is used to define the naming format for relay-log log files. Replicate-wild-ignore-table is a replication filtering option that filters out databases or tables that do not need to be copied, such as "mysql.%" to indicate that all objects under the MySQL library are not copied, and so on. This corresponds to the replicate-wild-do-table option, which specifies the database or table that needs to be replicated.

It is important to note that you should not use the BINLOG-DO-DB or BINLOG-IGNORE-DB option on the main library, or use the replicate-do-db or REPLICATE-IGNORE-DB option from the library. This can cause problems with cross-Library update failures. It is recommended to use the replicate-wild-do-table and replicate-wild-ignore-table two options from the library to resolve replication filtering issues.

2. Manually synchronizing the database

High-performance MySQL cluster (ii)