Easily build a MySQL high-availability cluster system

 

From: http://ixdba.blog.51cto.com/2895551/583232
Author:Technical Achievements

I. Implementation Principles of MySQL Replication
MySQL supports unidirectional, bidirectional, and asynchronous replication. One server acts as the master server during the replication process, and one or more other servers act as slave servers. The master server writes updates to a binary log file and creates an index file to track log loops. These logs can record updates sent to the slave server. When an slave server connects to the master server, the log file notifies the master server of the last successful update location read from the log from the server. Next, start the update operation from where the server was last updated. After the update is complete, the server enters the waiting state, waiting for subsequent updates to the master server.
Note: during replication, all updates to the tables in the replication must be performed on the master server. Otherwise, conflicts may occur between updates to tables on the master server and those on the slave server.
One-way replication facilitates robustness, speed, and system management.
The master server/slave server settings increase robustness. If the master server encounters a problem, you can switch to the slave server.
By splitting the customer query load between the master server and slave server, you can get a better response time. Select queries can be sent to the slave server to reduce the query processing load of the master server. However, the statement for modifying data should still be sent to the master server, so that the master server and slave server can be synchronized.
MySQL provides the Database Synchronization function, which is of great help for database redundancy, backup, recovery, and load balancing.
Generally, in MySQL replication, the master server is also called the master server and the slave server is called the slave server. Therefore, to enable the synchronization mechanism, the binary log must be enabled on the master server. Each slave accepts the update operation recorded in the binary log on the master, and the slave is equivalent to a copy of the operation.

Ii. MySQL synchronization details
The MySQL synchronization function is implemented by three threads (one BINLOG dump on the master and two on the slave, namely the SQL process and IO process. After the "Start slave" statement is executed, slave creates an I/O thread. The I/O thread connects to the master and requests the master to send statements in binary logs. The master creates a thread to send the log content to the slave.
The I/o thread on the slave reads the statements sent by the BINLOG dump thread of the master and copies them to the relay logs in the data directory. The third is the SQL thread, which salve uses to read relay logs and then execute them to update data.
The advantage of using two threads on slave is to split the read log and execution into two independent tasks. If the task is executed slowly, the log reading task will not be slowed down. For example, if slave stops for a period of time, the I/O thread can read all the logs from the master quickly after the slave is started, although the SQL thread may lag behind the I/O thread for several hours. If slave stops running all the SQL threads, but the I/O thread has read all the update logs and saved them in the local relay log, therefore, after the slave is started again, it will continue to execute them. This allows you to clear the binary log on the master, because the slave does not need to read the Update log from the master.

 

The environment described in this article is: one MySQL master node node1, three MySQL slave nodes, and all three slave nodes synchronize data in real time from the primary node, mySQL high-availability cluster host information 1:

 

Figure 1

3. Install MySQL on node1, slave1, slave2, and slave3

There are multiple ways to install mysql. Here, only the RPM installation is described.
[Root @ node1 ~] # Yum-y install mysql-server mysql-devel MySQL mysql-slave mysql-test
After the installation is complete, run the following command to start the MySQL service:
[Root @ node1 ~] #/Etc/init. d/mysqld start
To ensure data security, we recommend that you store data in a professional storage device or disk array partition. Assume that the partition of the disk array is the/data directory, and then put the data file under the/data directory:
[Root @ node1 ~] # Cp-r/var/lib/MySQL/data/MySQL
[Root @ node1 ~] # Chown-r MYSQL: MySQL/data/MySQL
Modify the/etc/My. CNF file and add the following configuration in the [mysqld] group:
Datadir =/data/MySQL
Finally, restart the MySQL service.

4. Edit the configuration file my. CNF on the MySQL master node node1.

Edit the configuration file/etc/My. CNF of the master server and add the following content to [mysqld:
Server-id = 1 # server ID. Duplicate IDs are not allowed between servers. Generally, the master is 1.
Log-bin = mysql-bin # enable the BINLOG function of MySQL. You can specify the name after it. If you do not change the name, the host name is used by default.
BINLOG-do-DB = ixdba # ixdba is the name of the database to be backed up. If multiple databases are backed up, set this option again.
BINLOG-ignore-DB = MySQL # Name of the database that does not need to be backed up. If you need to ignore backing up multiple databases, set this option again.

5. Create a replication application on the master node node1.User
Mysql> grant replication slave on *. * To 'repl _ user' @ '% 'identified by 'repl _ password ';
The repl_user user must be set to be able to log on to any remote node.

6. Back up Master Data
To back up data on the master, run the following SQL statement:
Mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 Sec)
Mysql> Reset master;
Query OK, 0 rows affected (0.00 Sec)
Do not exit the terminal. Otherwise, the lock becomes invalid. If you do not exit the terminal, open another terminal to directly package and compress the data file or use the mysqldump tool to export the data.
[Root @ node1 ~] # Cd/var/lib/# enter the MySQL data directory, depending on your situation.
[Root @ node1 lib] # tar zcvf mysql.tar.gz MySQL
[Root @ node1 lib] # SCP mysql.tar.gz 192.168.12.231/232/233:/var/lib/
Use the SCP command to upload the packaged data to several other slave machines.
After the data transmission is complete, run the following command on the command terminal:
Mysql> unlock tables;

7. Set the slave host
Edit the/etc/My. CNF file and add the following content to [mysqld:
Server-id = 2
Log-bin = mysql-bin
BINLOG-do-DB = ixdba
BINLOG-ignore-DB = MySQL
Make sure that the server-ID is globally unique.

8. Execute the following command on slave
Mysql> change master to master_host = '192. 168.12.135 ',
-> Master_user = 'repl _ user ',
-> Master_password = 'repl _ password ',
-> Master_log_file = 'mysql-bin.000001 ',
-> Master_log_pos = 98;
Run the following command after execution:
Mysql> slave start;
Query OK, 0 rows affected (0.00 Sec)
Mysql> show slave status \ G
The output shows that if slave_io_running and slave_ SQL _running are both yes, the configuration is successful.

IX. Notes
(1) If. the log-bin and relay-log parameters are defined in CNF, so ensure that the definition is irrelevant to the hostname, because if the names of these two types of logs are related to the Host Name, the switching process will cause the slave host to fail to synchronize. For example, you can make the following settings:
Log-bin = mysql-bin
Relay-log = mysql-relay-bin
Ensure that the names of the two files are the same on the two hosts.
(2) it is best to put the my. CNF file into the data directory of the partition where the disk array is located.