Shulou(Shulou.com)05/31 Report--

This article focuses on "how to build a Mysql high-availability cluster system". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to build a Mysql high availability cluster system.

I. the implementation principle of MySQL replication

MySQL supports one-way, two-way replication and asynchronous replication, in which one server acts as a master server and one or more other servers act as slave servers. The primary 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 a slave server connects to the master server, the log file informs the master server of the location of the last successful update read by the slave server in the log. Next, enter the update operation from the location where the server was last successfully updated. After the update is completed, it starts to enter the waiting state from the server and waits for subsequent updates from the main server.

It is important to note that when replicating, all updates to the tables in replication must be made on the primary server. Otherwise, a conflict may occur between updates to the table on the master server and updates to the table on the slave server.

One-way replication is good for robustness, speed and system management.

The master / slave server setting adds robustness. When there is a problem with the master server, you can switch to the slave server.

Better customer response time can be achieved by splitting the load of processing customer queries between the master server and the slave server. SELECT queries can be sent to the slave server to reduce the query processing load on the master server. However, statements that modify the data should still be sent to the master server so that the master server and the slave server are synchronized.

MySQL provides the synchronization function of database, which is of great help to the realization of database redundancy, backup, recovery, load balancing and so on.

In general, in Mysql replication, the master server is also called master, and the two-slave server is called slave, so binary logging must be enabled on master to enable the synchronization mechanism. Each slave receives an update operation recorded in the binary log on the master, and on the slave it is equivalent to a copy of the operation.

2. MySQL synchronization details

The MySQL synchronization function is implemented by three threads (two on a binlog dump,slave on master, namely the Sql process and the IO process). After the "START SLAVE" statement is executed, slave creates an iThread O thread. The Icano thread connects to the master and requests the 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 slave reads the statements sent by the Binlog Dump thread of master and copies them to the relay log (relay logs) under its data directory. The third is the SQL thread, which salve uses to read the relay logs and then execute them to update the data.

The advantage of using two threads on slave is to separate log reading and execution into two separate tasks. If the execution of the task is slow, the log reading task will not slow down. For example, if slave is stopped for a period of time, the SQL O thread can read all the logs from the master soon after the master starts, although the SQL thread may lag several hours behind the master thread. If slave stops before the SQL thread has finished executing, but the SQL O thread has read and saved all the update logs in the local relay log, it will continue to execute them after slave starts again. This allows the binary log to be cleared on master because slave no longer needs to go to master to read the update log.

The environment described in this article is: a master master node node1 of mysql, three Slave nodes of Mysql, and all three Slave nodes synchronize data in real time from the primary node. The information of Mysql highly available cluster hosts is shown in figure 1:

Install MYSQL on node1, slave1, slave2 and slave3

There are several ways to install MySQL, and only the rpm installation is described here.

[root@node1~] # yum-y install mysql-server mysql-devel mysql mysql-bench mysql-test

After the installation is complete, start the mysql service using the following command:

[root@node1 ~] # / etc/init.d/mysqld start

To ensure data security, it is recommended to put the data on professional storage devices or disk array partitions. Here, it is assumed that the partition of the disk array is / data directory, and then put the data files in 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 to the [mysqld] group:

Datadir = / data/mysql

Finally, restart the MySQL service.

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 to [mysqld]:

Server-id = 1 # server ID. There can be no duplicate ID between servers. The general master is 1.

Log-bin=mysql-bin # enables the binlog function of mysql. The following name can be specified by yourself. If the name is not changed, it will be named after the host by default.

Binlog-do-db=ixdba # ixdba is the name of the database that needs to be backed up. If you back up multiple databases, you can set this option repeatedly.

Binlog-ignore-db=mysql # does not need the name of the database to be backed up. If you need to ignore backing up multiple databases, you can set this option again.

5. Establish replication users on the master node node1

Mysql > GRANT REPLICATION SLAVE ON *. * TO 'repl_user'@'%' IDENTIFIED BY' repl_password'

Make sure that the repl_user user is set so that any remote node can log in.

VI. Back up Master data

To back up the data on Master, first execute 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, or the lock will be invalidated; if you do not exit the terminal, open another terminal to package the compressed data file directly or use the mysqldump tool to export the data.

[root@node1 ~] # cd / var/lib/ # enter the data directory of mysql, 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 transfer the packaged data to several other Slave machines.

After the data transfer is complete, execute on the command terminal above:

Mysql > UNLOCK TABLES

7. Set up the Slave host

Edit the / etc/my.cnf file and add the following to [mysqld]:

Server-id = 2

Log-bin=mysql-bin

Binlog-do-db=ixdba

Binlog-ignore-db=mysql

Other Slave and so on, to ensure that the server-id is globally unique.

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

Execute after execution:

Mysql > slave start

Query OK, 0 rows affected (0.00 sec)

Mysql > show slave status\ G

You can see from the output that if both Slave_IO_Running and Slave_SQL_Running are Yes, the configuration is successful.

IX. Several problems needing attention

(1) if log-bin and relay-log parameters are defined in my.cnf, make sure that the definition has nothing to do with hostname, because if the file names of these two types of log are related to hostnames, the switching process will cause the problem that slave hosts cannot continue to synchronize. For example, you can make the following settings:

Log-bin = mysql-bin

Relay-log = mysql-relay-bin

Make sure the names of the two files are the same on both hosts.

(2) it is best to put the my.cnf file in the data directory of the partition where the disk array is located.

At this point, I believe you have a deeper understanding of "how to build a Mysql high availability cluster system". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.