Build a MySQL Server Load balancer and high-availability environment

Use rhel5.8, mysql, keepalived, and haproxy to build a cluster with high availability and load balancing. Also, use mysql binary replication to maintain data consistency. Avoid

Use rhel5.8, mysql, keepalived, and haproxy to build a cluster with high availability and load balancing. Also, use mysql binary replication to maintain data consistency. Avoid

Abstract: rhel5.8, mysql, keepalived, and haproxy are used to build a cluster with high availability and load balancing. mysql binary replication is also used to maintain data consistency. Single Node failure is avoided, it also provides device utilization. Although this experiment only has two devices, it can be used as an example to understand the concept and then zoom in to other environments;

Objective: To use two hosts to achieve Mysql load balancing and redundancy, and achieve dual-master-slave mutual backup;
Environment: rhel5.8, mysql-5.0.77, keepalived-1.2.7, haproxy-1.4.20;
Note:
Keepalived achieves host redundancy through vrrp protocol by switching a virtual IP address (or floating IP address) between the master and slave devices;
The client accesses the virtual IP address to obtain the service;
Haproxy is a server Load balancer software that forwards requests to different hosts according to policies to achieve load balancing;
Can realize judgment and allocation at the network layer or application layer;

Mysql enables binary logs to achieve bidirectional replication between databases and maintain data consistency;

# Remind me that no one in the actual production environment will set up this way. We can only use this experiment to understand these concepts and hope that readers can get them;

Serverslave IP = 192.168.5.11
Server2_ip = 192.168.5.12
Server_vip = 192.168.5.111
========================================================== ==========
# Disable the firewall here. If the firewall is enabled, configure the corresponding rules.
Service iptables stop
Chkconfig iptables off

Ls/opt/soft/# extract preparation software here
Haproxy-1.4.20.tar.gz keepalived-1.2.7.tar.gz

Mkdir/opt/keepalived
Mkdir/opt/scripts/# script directory used in this experiment
Mkdir/opt/log/# This experiment log folder
========================================================== ==========

[Install_mysql]
# Install mysql on two hosts. Here, the rpm package is used for installation, and the yum environment can be set to the local source;
Yum install-y mysql-server
Service mysqld start
========================================================== ====================
[Create_mysql_test_table]
# Create a table with the same name and field, and insert different values to test whether ha and round robin are successful;
# Create an identical user

# Server1
Mysql
> Use test;
> Create table mywait (name char (9), phone char (14 ));
> Insert into mywait (name, phone) value ('wait', 15000000000 );

# Server2
> Use test;
> Create table mywait (name char (9), phone char (14 ));
> Insert into mywait (name, phone) values ('chen', 15611111111 );

[New_mysql_test_user]
> Mysql
> Grant all on test. * to diaosi @ '%' identified by '20140901 ';
> Flush privileges;
========================================================== ====================
# Test it on the client;
Mysql-udiaosi-p123456-h 192.168.5.11-e "select * from test. mywait ;"
Mysql-udiaosi-p123456-h 192.168.5.12-e "select * from test. mywait ;"

# So far, the basic mysql environment has been set up;
========================================================== ====================
[Install_keepalived]

Yum install-y libnl-devel # resolve Dependencies

Tar xf/opt/soft/keepalived-1.2.7.tar.gz-C/opt/soft
Keepalived-1.2.7/cd/opt/soft/
# Use the TAB key to complete the kernel version specified by with-kernel based on the local operating system.
./Configure -- prefix =/opt/keepalived -- with-kernel-dir =/usr/src/kernels/2.6.18-308. el5-i686/
Make & make install

[Keepalived_config]
# Because it is not installed in the/directory, You need to copy the startup and configuration files;

Cp/opt/keepalived/sbin/keepalived/usr/sbin/
Cp/opt/keepalived/etc/rc. d/init. d/keepalived/etc/init. d/
Cp/opt/keepalived/etc/sysconfig/
Mkdir/etc/keepalived
Cp/opt/keepalived/etc/keepalived. conf/etc/keepalived/

Chkconfig keepalived on # Set startup
========================================================== ====================
Vim/etc/keepalived. conf # Master configuration file of keepalived
# The following configurations are applicable to the situation where keepalived itself is a service provider;

! Configuration File for keepalived

# Simple header. Here, you can set email notification alarms. This is not configured for the moment;
Global_defs {
Notificationd LVS_DEVEL
}

# Define a script in advance to facilitate subsequent calls or multiple definitions for easy selection;
Vrrp_script mysql_chk {
Script "/opt/scripts/mysql_chke.sh"
Interval 2 # script cycle interval
Weight 2 # The Script result leads to priority change, successful + 2
}
# VRRP vro redundancy protocol Configuration
Vrrp_instance VI_1 {# VI_1 is a custom name;
State MASTER # indicates that this is a MASTER device and the standby device is BACKUP
Interface eth0 # specify the physical Nic to be bound to the VIP
Virtual_router_id 11 # VRID vro ID, also known as the group name. The devices in this group must be the same
Priority 150 # define the priority of this device 1-254;
Advert_int 1 # interval for sending multicast information during survival detection, consistent in the group

Authentication {# set verification information, consistent in the group
Auth_type PASS # There are two types: PASS and AH.
Auth_pass 111 # Password
}

Virtual_ipaddress {# specify the VIP address, which is consistent in the group. You can set multiple IP addresses.
192.168.5.111/24
}

Track_script {# use a pre-defined script in this domain
Mysql_chk
}