LVS + KeepAlived to build MySQL high-availability Load Balancing

This set of solutions is also one of the solutions tested some time ago. This solution is relatively easy to deploy and maintain. In simple terms, keepalived is a mechanism specifically designed to provide high-availability features for lvs. It can realize that when there are two master-slave lvs and the master lvs is damaged, transfer the IP address and lvs to the backup lvs. Its high availability

This set of solutions is also one of the solutions tested some time ago. This solution is relatively easy to deploy and maintain. In simple terms, keepalived is a mechanism specifically designed to provide high-availability features for lvs. It can realize that when there are two master-slave lvs and the master lvs is damaged, transfer the IP address and lvs to the backup lvs. Its high availability

This set of solutions is also one of the solutions tested some time ago. This solution is relatively easy to deploy and maintain.

In simple terms, keepalived is a mechanism specifically designed to provide high-availability features for lvs. It can realize that when there are two master-slave lvs and the master lvs is damaged, transfer the IP address and lvs to the backup lvs. Its high availability is mainly based on the VRRP protocol. VRRP is an "election" protocol that dynamically assigns the responsibility of a vro to other routers in the same VRRP group, this eliminates single point of failure in static routing configuration. If a VRRP device uses the virtual router IP address as the real interface address, the device is called the IP address owner. If the IP address owner is available, it usually becomes the Master.

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/12102QZ9-0.jpg "title =" mysql.png "alt =" wKiom1Wlub-y9fKhAAEf3K-tsMA017.jpg "/>

The write requests of the client are all distributed to the Server Load balancer of both master and slave nodes, and the read requests of the client are all distributed to the Server Load balancer of both slave nodes. It is worth noting that when a master database goes down, the corresponding slave database must also be offline. For example, when master1 goes down, slave1 under it will also be offline. Otherwise, the read operation may fail to read the latest data. Special scripts are required to take the corresponding slave offline operation.

Configure MySQL Master/Slave

MySQL installation is skipped. MySQL is used in this example.

Preparations:

Modify the/etc/hosts file of the four nodes to ensure normal communication based on the host name.

[root@master1~]# cat /etc/hosts192.168.1.121   master1.node.com192.168.1.114   master2.node.com192.168.1.122   slave1.node.com192.168.1.123   slave2.node.com

Ensure that the UUID of each node is unique

[root@master1 ~]# cat /mysqldata/auto.cnf[auto]server-uuid=fa6cdd4e-337b-11e4-97e9-000c293a63cc

If two identical UUID exist, manually modify the UUID to the hexadecimal format.

Mysql Master/Slave Configuration:

1. Configure the master node:

[Mysqld] binlog-format = ROWlog-bin = master-binlog-slave-updates = truegtid-mode = on enforce-gtid-consistency = truemaster-info-repository = TABLErelay-log- info-repository = TABLEsync-master-info = 1slave-parallel-workers = 2binlog-checksum = CRC32master-verify-checksum = worker = 1server-id = 1 # report-port = 3306 port = 3306 datadir =/ mydatasocket =/tmp/mysql. sockauto_increment_increment = 2 # This configuration only needs to be configured on master1 and master2 to ensure that the auto-increment key does not conflict with auto_increment_offset = 1 # This configuration only needs to be configured on master1 and master2 to ensure the auto-increment key binlog-do-db = mydb # database to be synchronized, you can add multiple rows of configuration items to multiple databases. You need to restart the mysql instance.

Mysql has an auto-increment field. When synchronizing the master and master databases, you must set two auto-increment configurations: auto_increment_offset and auto_increment_increment.

• Auto_increment_offset indicates that the auto-increment field starts from that number. Its value range is 1 .. 65535.

• Auto_increment_increment indicates the number of auto-increment fields each time. The default value is 1 and the value range is 1 .. 65535

• When configuring the master-master synchronization, you must set the auto_increment_increment growth of both servers to 2, and configure auto_increment_offset to 1 and 2 respectively.

In this way, the value of the self-growth field conflicts between the two servers when the two servers are updated at the same time.

2. Configure the slave Node

[Mysqld] binlog-format = ROWlog-slave-updates = truegtid-mode = on enforce-gtid-consistency = truemaster-info-repository = TABLErelay-log-info-repository = TABLEsync- master-info = 1slave-parallel-workers = 2binlog-checksum = CRC32master-verify-checksum = worker = 1server-id = 11 # report-port = 3306 port = 3306log-bin = mysql-bin.logdatadir =/mydatasocket =/tmp/ mysql. sockauto_increment_increment = 2 # This configuration only needs to be configured on master1 and master2 to ensure that the auto-increment key does not conflict with auto_increment_offset = 1 # This configuration only needs to be configured on master1 and master2 to ensure the auto-increment key binlog-do-db = mydb # database to be synchronized, you can add multiple rows of configuration items to multiple databases. You need to restart the mysql instance.

The configuration information of the four nodes is similar, and the server-id must be unique.

3. Create a copy user

mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'192.168.1.114' IDENTIFIED BY 'passwd';

In this example, the replication users of all nodes are set to the same for ease of management.

4. Provide the initial dataset for the slave Node

Lock the master table, back up the data on the master node, and restore it to the slave node. If GTID is not enabled, during backup, you need to run the show master status Command on the master to view the binary log file name and event location, so that it can be used later when the slave node is started.

5. Start the replication thread of the slave Node

Master1:

mysql> CHANGE MASTER TO MASTER_HOST='master2.node.com', MASTER_USER='repl', MASTER_PASSWORD='passwd', MASTER_AUTO_POSITION=1;

Master2:

mysql> CHANGE MASTER TO MASTER_HOST='master1.node.com', MASTER_USER='repl', MASTER_PASSWORD='passwd', MASTER_AUTO_POSITION=1;

Slave1:

mysql> CHANGE MASTER TO MASTER_HOST='master1.node.com', MASTER_USER='repl', MASTER_PASSWORD='passwd', MASTER_AUTO_POSITION=1;

Slave2:

mysql> CHANGE MASTER TO MASTER_HOST='master2.node.com', MASTER_USER='repl', MASTER_PASSWORD='passwd', MASTER_AUTO_POSITION=1;

If GTID is not enabled, run the following command:

Slave> change master to MASTER_HOST = 'master1 .node.com ',-> MASTER_USER = 'repl',-> MASTER_PASSWORD = 'passwd',-> MASTER_LOG_FILE = 'master-bin.000003 ', # Run show master status on the corresponding master. View-> MASTER_LOG_POS = 1174; # Run show master status on the corresponding master. View

Check whether replication is enabled successfully on the slave node.

mysql> show slave status\G;*************************** 1. row ***************************   Slave_IO_State: Waiting for master to send event  Master_Host: master2.node.com  Master_User: repl  Master_Port: 3306Connect_Retry: 60  Master_Log_File: mygateway-bin.000025  Read_Master_Log_Pos: 231   Relay_Log_File: initiator-relay-bin.000012Relay_Log_Pos: 409Relay_Master_Log_File: mygateway-bin.000025 Slave_IO_Running: YesSlave_SQL_Running: Yes  Replicate_Do_DB:   Replicate_Ignore_DB:    Replicate_Do_Table:    Replicate_Ignore_Table:   Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:    Last_Errno: 0   Last_Error:  Skip_Counter: 0  Exec_Master_Log_Pos: 231  Relay_Log_Space: 586  Until_Condition: None   Until_Log_File: Until_Log_Pos: 0   Master_SSL_Allowed: No   Master_SSL_CA_File:    Master_SSL_CA_Path:   Master_SSL_Cert: Master_SSL_Cipher:    Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:    Last_SQL_Errno: 0   Last_SQL_Error:   Replicate_Ignore_Server_Ids:  Master_Server_Id: 11  Master_UUID: 50aef63f-ed82-11e4-94f3-000c293a63bb Master_Info_File: mysql.slave_master_infoSQL_Delay: 0  SQL_Remaining_Delay: NULL  Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it   Master_Retry_Count: 86400  Master_Bind:   Last_IO_Error_Timestamp:  Last_SQL_Error_Timestamp:    Master_SSL_Crl:    Master_SSL_Crlpath:    Retrieved_Gtid_Set: 50aef63f-ed82-11e4-94f3-000c293a63bb:1-10Executed_Gtid_Set: 50aef63f-ed82-11e4-94f3-000c293a63bb:1-10,fa6cdd4e-337b-11e4-97e9-000c293a63cc:1-30Auto_Position: 11 row in set (0.01 sec)

Slave_IO_Running: Yes \ Slave_ SQL _Running: Yes indicates that the master-slave replication thread is successfully executed.

Configure high-availability server Load balancer

After the preceding steps are completed, the MySQL Master/Slave environment is successfully set up. The keepalived + lvs high-availability server Load balancer is configured below.

Master1 and mastre2 set up write high-availability load balancing, VIP: 192.168.1.120.

Slave1 and slave2 set up read high-availability load balancing, VIP: 192.168.1.200.

To save the server load, install keeplived on the four mysql nodes. In this example, lvs is enabled in the kernel, if the kernel version is too low or the lvs module is not enabled in the kernel, you need to install lvs on your own.

1. Install keeplived with the same four nodes

[root@master1~]# yum -y install keeplived

Configuration File Location:/etc/keepalived. conf

2. Configure lvs. Almost all four nodes are the same.

Lvs script. You only need to modify the corresponding VIP.

#! /Bin/bash # description: Config realserver lo and apply noarpSNS_VIP = 192.168.1.120 # modify it to the corresponding VIP. /etc/rc. d/init. d/functionscase "$1" instart) ifconfig lo: 0 $ SNS_VIP netmask 255.255.255.255 broadcast $ SNS_VIP/sbin/route add-host $ SNS_VIP dev lo: 0 echo "1">/proc/sys/net/ipv4/conf/lo/arp_ignore echo "2">/proc/sys/net/ipv4/conf/lo/arp_announce echo "1">/proc/sys/net/ipv4/conf/all/arp_ignore echo "2">/proc/sys/net/ipv4/conf/all/arp_announce sysctl-p >/dev/null 2> & 1 echo "RealServer Start OK ";; stop) ifconfig lo: 0 down route del $ SNS_VIP>/dev/null 2> & 1 echo "0">/proc/sys/net/ipv4/conf/lo/arp_ignore echo "0">/ proc/sys/net/ipv4/conf/lo/arp_announce echo "0">/proc/sys/net/ipv4/conf/all/arp_ignore echo "0">/proc/ sys/net/ipv4/conf/all/arp_announce echo "RealServer Stoped ";; *) echo "Usage: $0 {start | stop}" exit 1 esac

Execute all four nodes:

[Root @ localhost ~] # Chmod u + x/etc/rc. d/init. d/realserver. sh [root @ localhost ~] #/Etc/rc. d/init. d/realserver. sh start # start The lvs script to set parameters and bind the VIP

3. Configure keepalived

Keepalived is a mechanism dedicated to providing high-availability features for lvs. It can transfer its IP address and lvs to the backup lvs when there are two master-slave lvs and the master lvs is damaged.

The keeplived on master1 and master2 is in mutual standby mode.

Keeplived on slave1 and slave2 is in mutual standby mode.

Content of the keepalibved configuration file:

Vrrp_instance VI_1 {state MASTER # modify it to BACKUP interface eth1 on the slave machine # VIP is bound to eth1, depending on the specific situation, enter the specific host Nic name virtual_router_id 52 priority 100 # the value of the corresponding backup server must be smaller than this value advert_int 1 authentication {auth_type PASS # the same as that on the backup server auth_pass 1111 # consistent} virtual_ipaddress {192.168.1.120/32 dev eth1 label eth1: 0 # It depends on the specific situation that the VIP is bound to eth1. Fill in the specific host Nic name, changed to the corresponding VIP} virtual_server 192.168.1.120 3306 {# changed to the corresponding VIP delay_loop 6 lb_algo rr # lvs load balancing algorithm lb_kind DR # lvs forwarding mode # nat_mask limit 255.255.0 # persistence_timeout 50 protocol TCP real_server 192.168.1.121 3306 {# change to the corresponding realserever weight 2 TCP_CHECK {connect_timeout 3 timeout 3 limit 3 connect_port 3306} real_server 192.168.1.114 3306 {# change to the corresponding realserver weight 2 TCP_CHECK 3 nb_get_retry 3 delay_before_retry 3 connect_port 3306 }}}

Start keepalibed and check the VIP binding status.

[Root @ localhost ~] #/Etc/init. d/keepalived start [root @ localhost ~] # Ip add # view IP status [root @ localhost ~] # Ipvsadm-Ln # Check The LVS status. If the tool yum-y install ipvsadm is available

Add the following operations to the real_server section after checking the health status of mysql:

Notify_up $ PATH/SCRIPT. sh # It is detected that the SCRIPT executed after the service is enabled can be an email alarm, for example, if a certain IP address or mysql fails .... Notify_down $ PATH/SCRIPT. sh # scripts executed after the service is stopped.

In practical applications, when the master fails, backup occupies resources. However, after the master node recovers, it will seize the resources, continue to act as the master node, and bind the VIP to the master node. At this time, the connected business may be interrupted. Therefore, in production, you need to set it to not preemptible (nopreempt) resources, that is, it will not take the master back after it is active, and will continue to exist as a slave machine. However, nopreempt can only be set when stat is set as BACKUP. Therefore, set stat on the master and BACKUP machines as BACKUP, set priority as high and low, and determine who is the master based on the priority.

Make a simple modification to keeplived:

State BACKUP # all changed to BACKUPvirtual_router_id 60 # by default, 51 master and slave are changed to 60 priority 100 # priority (between 1 and), and the other is changed to 90. The slave node must have a lower priority than the master node. Nopreempt # do not seize resources, that is, after it is active, it will not take the master back, the slave does not need to set a change