MySQL High Availability LVS + Keepalived + MySQL

MySQL High Availability LVS + Keepalived + MySQL
1. Architecture Diagram

 

Note:

(1) Mysql needs to remove the bind-address Configuration; otherwise, virtual ip access cannot be implemented.

(2) Disable all linux firewalls:/sbin/iptables-F (may be useless)

(3) restart networking: sudo/etc/init. d/networking restart (may be useless)

2. Architecture Mysql:

Master <= slave

10.24.6.5: 3306 (system! System) <= 10.24.6.6: 3306 (system! System)

LB

10.24.6.4 Master

10.24.6.7 BACKUP

VIP:

10.24.6.2

3. LVS Server Load balancer configuration 3.1. Lvs module Detection

Ipvs is implemented by ipvsadm. To determine whether a system has ipvs function, you only need to check whether the ipvsadm program is installed. The simplest way to view the ipvsadm program is to execute the command ipvsadm in any path. Table 6-1 shows the output comparison of ipvsadm installation and ipvsadm installation.

	Output after running ipvsadm
Ipvsadm Not Installed	Bash:/sbin/ipvsadm: No file or directory
Install ipvsadm	IP Virtual Server version 1.2.1 (size = 4096) Prot LocalAddress: Port sched1_flags -> RemoteAddress: Port Forward Weight ActiveConn InActConn

Table 6-1 Comparison of ipvsadm output

A) The system command is relatively simple: apt-get install ipvsadm

B) Check whether ipvsadm is correctly installed.

I. Execute ipvsadm to check whether there is output in column 6-1 2nd.

Ii. Check the currently loaded kernel module to see if the ip_vs module exists.

Modprobe-l | grep ipvs

Or lsmod | grep ip_vs

 

Ubuntu9.10 has used the 2.6 kernel, so you do not need to compile the kernel.

 

3. 2. Configuration

LVS is not required by default.

4. KeepAlived High Availability Configuration 4.1. Installation

 

4.2. MASTER configuration (/etc/keepalived. conf)

Global_defs {

Router_id HaMySQL_1

}

Vrrp_instance VI_MYSQL {

State MASTER

Interface eth0

Virtual_router_id 51

Priority100

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

Virtual_ipaddress {

10.24.6.2

}

}

Virtual_server 10.24.6.2 3306 {

Delay_loop 6

Lb_algo wrr

Lb_kind DR

Persistence_timeout 60

Protocol TCP

Real_server 10.24.6.5 3306 {

Weight 3

TCP_CHECK {

Connect_timeout 3

Nb_get_retry 3

Delay_before_retry 3

Connect_port 3306

}

}

Real_server 10.24.6.6 3306 {

Weight 3

TCP_CHECK {

Connect_timeout 3

Nb_get_retry 3

Delay_before_retry 3

Connect_port 3306

}

}

}

4.3. SLAVE configuration (/etc/keepalived. conf)

Global_defs {

Router_id HaMySQL_1

}

Vrrp_instance VI_MYSQL {

State BACKUP

Interface eth0

Virtual_router_id 51

Priority 50

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

Virtual_ipaddress {

10.24.6.2

}

}

Virtual_server 10.24.6.2 3306 {

Delay_loop 6

Lb_algo wrr

Lb_kind DR

Persistence_timeout 60

Protocol TCP

Real_server 10.24.6.5 3306 {

Weight 3

TCP_CHECK {

Connect_timeout 3

Nb_get_retry 3

Delay_before_retry 3

Connect_port 3306

}

}

Real_server 10.24.6.6 3306 {

Weight 3

TCP_CHECK {

Connect_timeout 3

Nb_get_retry 3

Delay_before_retry 3

Connect_port 3306

}

}

}

4.4. Log

/Var/log/syslog under Ubuntu

All daemon messages are logged through the Linux syslog. If you start Keepalived with the "dump

Configuration data "option, you shocould see in your/var/log/messages (on Debian this may be

/Var/log/daemon. log depending on your syslog configuration) something like this:

4.5. Enable and verify the Server Load balancer Service

The activation and verification of the Lvs client are described in detail in the previous section. We also mentioned earlier that the keepalived startup process does not check the syntax of the configuration file. Therefore, before starting keepalived, You need to manually perform a full syntax check on the/etc/keepalived. conf file. An easy mistake is to omit the curly braces "}", which are not paired! When the lvs client is started normally and the configuration file is checked correctly (of course, errors are fine, so you can modify them at any time !), Run the command/usr/local/keepalived/sbin/keepalived-D. Then we can check whether the system processes are three keepalived processes. If the path of the configuration file is not/etc/keepalived. conf, you must specify it with option-f at startup.

System logs are the places that best reflect the start of keepalived. After you manually execute the start operation, run the command tail-f/var/log/syslog to scroll to view the output, so that you can learn more about the running status. Figure 6-3 Start the output of keepalived in an lvs environment:

In addition, the network interface vip is enabled to reflect the normal running status of keepalived. By executing ip add, you can see that the vip has been bound to the specified network interface (Note: ifconfig cannot display the vip ). Note that the BACKUP vip is not bound at the moment. As shown in:

4.6. Vip Verification

Master

 

Slave

 

4.7. IPVSADM Verification

Master

 

Clave

 

4.8. test and verification

Entity mysql

 

Vip mysql

 

4.9. High Availability test results

LB:

10.24.6.4 after the Master is shut down, the slave node is enabled for 10.24.6.7 BACKUP.

MYSQL:

10.24.6.5: 3306

10.24.6.6: 3306

After any mysql exception occurs, you can connect to other mysql

5. Problems Encountered 5.1. Mysql connection failure (error code 111) Analysis

1. Mysql connects to vip

 

2. packet capture of Mysql vip connection

 

Through packet capture, we can see that 10.24.6.2: 3306 can be connected. It has a back-to-package, but the back-to-package status is R.

3. Analysis of RST in TCP connection:

@ 1 port is not enabled

@ 2 Request timeout

@ 3 close ahead of schedule

@ 4 receive data on a closed socket

4. When mysql returns 111, you can know that the mysql service is disabled in advance,

Google's solution to mysql111:

QSqlError (2003, "QMYSQL: Unable to connect", "Can't connect to Mysql server on '172. 18.186.244 '(111) ") error. google later found that mysql bound the hostname for security purposes.

Solution: Modify/etc/mysql/my. cnf (different Linux releases may vary. I use Ubuntu 11.04 and windows is my. INI file), there is a sentence: bind-address = 127.0.0.1 with # comment out, restart the mysql service will be OK.

Address: http://stackoverflow.com/questions/1420839/cant-connect-to-mysql-server-error-111

5. solution (remove bind-address ):

Modify the mysql configuration in 10.24.6.5:

 

Modify the mysql configuration in 10.24.6.6:

6. Summary

• Both primary mysql and slave mysql can have services at the same time.
• Keepalive selects a service access from the available service list
• Primary mysql and random selection from mysql
• Suitable for Server Load balancer and master-slave backup
• Share nothing Architecture

HAProxy + Keepalived dual-host high availability solution in Linux

Haproxy + Keepalived build Weblogic high-availability server Load balancer Cluster

Keepalived + HAProxy configure high-availability Load Balancing

Haproxy + Keepalived + Apache configuration notes in CentOS 6.3

Haproxy + KeepAlived WEB Cluster on CentOS 6

Haproxy + Keepalived build high-availability Load Balancing

This article permanently updates the link address: