Use keepalived to build highly available MySQL-HA

Use keepalived to build highly available MySQL-HA

 

This article describes how to use keepalived to build highly available MySQL-HA to ensure data consistency between the two MySQL instances. Then, keepalived is used to implement virtual IP addresses and automatic switch during MySQL fault is achieved through the service monitoring function provided by keepalived.

AD:

 

There are many solutions for MySQL-HA, such as heartbeat, drbd, mmm, and shared storage, but they have their own advantages and disadvantages. The heartbeat and drbd configurations are complex. You need to write your own scripts to achieve automatic MySQL switching. For those who do not know the scripting language, this is undoubtedly a split-brain problem. For mmm, few people use the production environment, and the mmm management end needs to run a server separately. To achieve high availability, You need to implement HA on the mmm management end, this will undoubtedly increase hardware spending. For shared storage, I personally think that MySQL Data is safer to be stored locally. After all, there is a single point of failure in storage devices. Using MySQL dual master + keepalived is a very good solution. In the MySQL-HA environment, MySQL is mutually master-slave, which ensures data consistency between the two MySQL databases, then, use keepalived to implement virtual IP addresses, and use the service monitoring function provided by keepalived to automatically switch MySQL during failure.

Next, I will share with you the architecture in a production environment to be launched. Let's take a look at how MySQL-HA is implemented in this architecture. The environment topology is as follows:

MySQL-VIP: 192.168.1.90

MySQL-master1: 192.168.1.91

MySQL-master2: 192.168.1.92

 

OS Version: CentOS 5.4

MySQL version: 5.0.89

Keepalived version: 1.1.20

1. MySQL master-master Configuration

[root@localhost ~]# mysql -urootWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.0.77 Source distributionType 'help;' or '\h' for help. Type '\c' toclear the buffer.mysql> GRANT ALL PRIVILEGES ON *.* TOroot@'%';Query OK, 0 rows affected (0.01 sec)mysql> use mysql;Reading table information for completion oftable and column namesYou can turn off this feature to get aquicker startup with -ADatabase changedmysql> update user setPassword=password('bee') where user='root';Query OK, 4 rows affected (0.01 sec)Rows matched: 4 Changed: 4 Warnings: 0mysql> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB |Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| MySQL-bin.000001 | 328 | | |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)

1. Modify the MySQL configuration file

[mysqld]log-bin=MySQL-bindatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysqlserver-id = 2replicate-same-server-id = 0auto-increment-increment = 2auto-increment-offset = 2replicate-ignore-db=testreplicate-ignore-db=mysqlslave-skip-errors=all# Default to using old password format forcompatibility with mysql 3.x# clients (those using the mysqlclient10compatibility package).old_passwords=1# Disabling symbolic-links is recommended toprevent assorted security risks;# to do so, uncomment this line:# symbolic-links=0[replication]master-host=192.168.1.91master-user=replicationmaster-password=replicationmaster-port=3306master-connect-retry=60[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

To enable the binlog function for both MySQL instances, enable the function by adding log-bin = MySQL-bin in the [MySQLd] section of the MySQL configuration file.

The server-ID of the two MySQL instances cannot be the same. By default, the serverID of the two MySQL instances is 1 and you need to change one of them to 2.

2. Set 192.168.1.201 to the master server of 192.168.1.202.

Create an authorized user on 192.168.1.201

MySQL> grant replication slave on *.* to 'replication'@'%' identified by 'replication';Query OK, 0 rows affected (0.00 sec)MySQL> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| MySQL-bin.000003 | 374 | | |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)

Set 192.168.1.201 as the master server on 192.168.1.202.

MySQL> change master to master_host='192.168.1.201',master_user='replication',master_password='replication',master_log_file='MySQL-bin.000003',master_log_pos=374;Query OK, 0 rows affected (0.05 sec)MySQL> start slave;Query OK, 0 rows affected (0.00 sec)MySQL> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.1.201Master_User: replicationMaster_Port: 3306Connect_Retry: 60Master_Log_File: MySQL-bin.000003Read_Master_Log_Pos: 374Relay_Log_File: MySQL-master2-relay-bin.000002Relay_Log_Pos: 235Relay_Master_Log_File: MySQL-bin.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 374Relay_Log_Space: 235Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 01 row in set (0.00 sec)

3. Set 192.168.1.202 to the master server of 192.168.1.201.

Create an authorized user on 192.168.1.202

MySQL> grant replication slave on *.* to 'replication'@'%' identified by 'replication';Query OK, 0 rows affected (0.00 sec)MySQL> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| MySQL-bin.000003 | 374 | | |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)

On 192.168.1.201, set 192.168.1.202 as your master server.

MySQL> change master to master_host='192.168.1.202',master_user='replication',master_password='replication',master_log_file='MySQL-bin.000003',master_log_pos=374;Query OK, 0 rows affected (0.05 sec)MySQL> start slave;Query OK, 0 rows affected (0.00 sec)MySQL> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.1.202Master_User: replicationMaster_Port: 3306Connect_Retry: 60Master_Log_File: MySQL-bin.000003Read_Master_Log_Pos: 374Relay_Log_File: MySQL-master1-relay-bin.000002Relay_Log_Pos: 235Relay_Master_Log_File: MySQL-bin.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 374Relay_Log_Space: 235Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 01 row in set (0.00 sec)

4. MySQL synchronization test

If the above configurations are correct, data update on any MySQL instance will be synchronized to another MySQL instance. MySQL synchronization will not be demonstrated here

Ii. install and configure keepalived

1. install and configure keepalived on 192.168.1.201 Server

Install keepalived

#tar zxvf keepalived-1.1.20.tar.gz#cd keepalived-1.1.20#./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.18-164.el5-i686#make && make install[root@master2 ~]#/usr/local/keepalived/sbin/keepalived -D[root@master2 ~]# ps -aux|grep keepalivedWarning: bad syntax, perhaps a bogus '-'?See /usr/share/doc/procps-3.2.7/FAQroot 4101 0.3 0.1 35828 632 ? Ss 15:41 0:00/usr/local/keepalived/sbin/keepalived -Droot 4102 0.7 0.4 39988 1620 ? S 15:41 0:00/usr/local/keepalived/sbin/keepalived -Droot 4103 0.5 0.3 39988 1092 ? S 15:41 0:00/usr/local/keepalived/sbin/keepalived -Droot 4106 0.0 0.2 61136 716 pts/0 R+ 15:41 0:00 grep keepalived

Configure keepalived

Ln-sv/usr/src/kernels/2.6.18-194. el5-x86_64 // usr/src/linux

We create a configuration file by ourselves. By default, when keepalived is started, we will find the configuration file in the/etc/keepalived directory.

# Mkdir/etc/keepalived # vi/etc/keepalived. conf! Configuration File for detail {icationication_email {luwenju@live.cn} icationication_email_from luwenju@live.cnsmtp _ server 127.0.0.1smtp _ connect_timeout 30router_id MySQL-ha} vrrp_instance VI_1 {state BACKUP # both configurations here are BACKUPinterface priority 51 priority 100 # priority, change the other one to 90advert_int 1 nopreempt # Do not preemptible. You can only set it on a machine with a higher priority, authentication {auth_type authentication 1111} virtual_ipaddress {192.168.1.200} virtual_server 192.168.1.200 3306 {delay_loop 2 # Check real_server status lb_algo wrr # LVS algorithm lb_kind # LVS mode persistence_timeout 60 # session persistence time protocol TCPreal_server 192.168.1.201 3306 {weight 3notify_down/usr/local/MySQL/bin/MySQL. sh # script executed after the service is down {connect_timeout 10 # connection timeout nb_get_retry 3 # reconnection times delay_before_retry 3 # reconnection interval connect_port 3306 # Health Check port }}

Compile the script to be executed after the detection service is down

#vi /usr/local/MySQL/bin/MySQL.sh#!/bin/shpkill keepalived#chmod +x /usr/local/MySQL/bin/MySQL.sh

Note: This script is used by the notify_down option in the configuration file above. keepalived uses the notify_down option to check the service status of real_server. This script is triggered when the real_server service is found to be faulty; we can see that the script is just a command that forces the keepalived process to be killed through pkillkeepalived, thus realizing the automatic failover of MySQL. In addition, we do not have to worry that two MySQL databases will provide data update operations at the same time, because the keepalived configuration on each MySQL instance only contains the IP address + VIP address of the local MySQL instance, instead of the IP address + VIP address of the two MySQL instances.

Start keepalived

#/Usr/local/keepalived/sbin/keepalived-D

# Ps-aux | grep keepalived

Test

Find a lan pc and ping the VIP address of MySQL. At this time, the VIP address of MySQL can be pinged.

Stop the MySQL service and check whether the keepalived health check program will trigger the script we wrote.

2. install and configure keepalived on 192.168.1.202

Install keepalived

#tar zxvf keepalived-1.1.20.tar.gz#cd keepalived-1.1.20#./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.18-164.el5-i686#make && make install

Configure keepalived

This configuration is basically the same as above, but there are three differences: priority is 90, no preemption settings, real_server is the local IP

#mkdir /etc/keepalived#vi /etc/keepalived/keepalived.conf! Configuration File for keepalivedglobal_defs {notification_email {luwenju@live.cn}notification_email_from luwenju@live.cnsmtp_server 127.0.0.1smtp_connect_timeout 30router_id MySQL-ha}vrrp_instance VI_1 {state BACKUPinterface eth0virtual_router_id 51priority 90advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.1.200}}virtual_server 192.168.1.200 3306 {delay_loop 2lb_algo wrrlb_kind DRpersistence_timeout 60protocol TCPreal_server 192.168.1.202 3306 {weight 3notify_down /usr/local/MySQL/bin/MySQL.shTCP_CHECK {connect_timeout 10nb_get_retry 3delay_before_retry 3connect_port 3306}}

Compile the script to be executed after the detection service is down

# Vi/usr/local/MySQL/bin/MySQL. sh #! /Bin/shpkill keepalived # chmod + x/usr/local/MySQL/bin/MySQL. sh start keepalived #/usr/local/keepalived/sbin/keepalived-D # ps-aux | grep keepalived

Test

Stop the MySQL service and check whether the keepalived health check program will trigger the script we wrote.

Iii. Test

MySQL remote logon Test

We need to find a windows server with a MySQL client installed, and then log on to the VIP to check whether the server can be logged on. We need to authorize remote logon when logging on to both MySQL servers.

MySQL> grant all privileges on *.* to 'root'@'%' identified by '123456';Query OK, 0 rows affected (0.00 sec)MySQL> flush privileges;Query OK, 0 rows affected (0.00 sec)

Use the client to log on to the VIP for testing

C:\MySQL\bin>MySQL.exe -uroot -p123456 -h192.168.1.200 -P3306Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 224Server version: 5.0.89-log Source distributionType 'help;' or '\h' for help. Type '\c' to clear the current input statement.MySQL>

● Keepalived failover Test

※In the windows client, ping the VIP and disable keepalived on 192.168.1.201. Normally, the VIP will switch to 192.168.1.202.

※Enable keepalived on 192.168.1.201 and disable keepalived on 192.168.1.202 to check whether automatic failover is enabled. Under normal circumstances, the VIP will belong to 192.168.1.201.

Note: The keepalived switching speed is still very low. The entire switching process only takes 1-3 seconds.

● MySQL failover Test

※Disable the MySQL service on 192.168.1.201 to check whether the VIP will switch to 192.168.1.202.

※Enable MySQL and keepalived on 192.168.1.201, disable MySQL on 192.168.1.202, and check whether the VIP will switch to 192.168.1.201.

The following is the VIP address of MySQL connected with a windows client. During the switchover, I executed a MySQL query command, from executing show databases to displaying the result, the time is 3-5 seconds (you can see an error prompt on it, but don't worry, because our keepalived switch is about 3 seconds, no one belongs to the VIP in about 3 seconds)

MySQL> show databases;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 592Current database: *** NONE ***+--------------------+| Database |+--------------------+| information_schema || MySQL || test |+--------------------+

3 rows in set (9.01 sec)

Afterwards: everything in the world is not absolutely perfect. Just like MySQL-HA above, keepalived can only perform 3306 health checks, however, the Server Load balancer-SQL and Server Load balancer-IO processes such as MySQL replication cannot be checked. Therefore, to achieve some meticulous health check, you must use additional monitoring tools, such as nagios, and then use nagios to enable SMS and email alarms, so as to effectively solve the problem.

 

Mysql> grant replication slave on *.*

-> TO 'repl' @ '% .mydomain.com' identified by 'slavepass ';

If you have not backed up the master server, here is a fast program for creating backups. All steps should be performed on the master server host.

 

1. Issue the statement:

 

Mysql> flush tables with read lock;

2. Execute this command (or its variant) when the lock is still applied ):

 

Shell> tar zcf/tmp/backup.tar.gz/var/lib/mysql

3. Issue the statement and ensure that the output used later is recorded:

 

Mysql> show master status;

4. Release the lock:

 

Mysql> unlock tables;

An optional method is to dump the master server's SQL statement instead of the binary copy in the previous step. To do this, you can use mysqldump -- master-data on the master server and load the SQL to your slave server.