Configure high-availability load balancing for MySQL with keepalived and Haproxy

Http://www.cnblogs.com/tae44/p/4717334.html

Experimental system: CentOS 6.6_x86_64 (2.6.32-504.30.3.el6.x86_64)

Lab Prerequisites: Firewall and SELinux are off

The experiment shows that there are 4 hosts in this experiment, such as the topology of IP assignment

Experimental software: keepalived-1.2.19 haproxy-1.5.14 mariadb-10.0.20

: Http://pan.baidu.com/s/1bnnYiMr

Experimental topology:

　　　　

First, install the MARIADB

1. Install on two database servers:

Tar XF mariadb-10.0.20-linux-x86_64.tar.gz  -c/usr/local/cd/usr/local/ln-sv mariadb-10.0.20-linux-x86_64 Mysqluseradd-r mysqlmkdir-pv/mydata/datachown-r mysql.mysql/mydata/data/cd mysql/chown-r root.mysql. Scripts/mysql_ install_db--user=mysql--DATADIR=/MYDATA/DATA/CP SUPPORT-FILES/MY-LARGE.CNF/ETC/MY.CNFCP support-files/ Mysql.server/etc/init.d/mysqldchkconfig--add mysqldchkconfig mysqld on

2. Configure Primary master replication:

19.74:

VIM/ETC/MY.CNF----------------------------------------------->[mysqld]server-id = 1datadir =/mydata/ Datalog-bin =/mydata/data/mysql1-binbinlog_format = Rowrelay_log =/mydata/data/relay-logauto-increment-increment = 2auto-increment-offset = 1sync_binlog = 1sync_master_info = 1sync_relay_log = 1sync_relay_log_info = 1

19.76:

VIM/ETC/MY.CNF----------------------------------------------->[mysqld]server-id = 2datadir =/mydata/ Datalog-bin =/mydata/data/mysql2-binbinlog_format = Rowrelay_log =/mydata/data/relay-logauto-increment-increment = 2auto-increment-offset = 2sync_binlog = 1sync_master_info = 1sync_relay_log = 1sync_relay_log_info = 1

3. Create a user with copy permissions:

19.74:

Service mysqld start/usr/local/mysql/bin/mysql------------------------------------------>grant REPLICATION Slave,replication CLIENT on * * to ' master ' @ ' 192.168.19.76 ' identified by ' 123456 '; FLUSH privileges;

19.76:

Service mysqld start/usr/local/mysql/bin/mysql------------------------------------------>grant REPLICATION Slave,replication CLIENT on * * to ' master ' @ ' 192.168.19.74 ' identified by ' 123456 '; FLUSH privileges;

4. Look at the binary location:

19.74:

SHOW MASTER LOGS;

　　　　

Use the same command on 19.76:

　　　　

5. Configure dual-Master:

19.74:

Change MASTER to master_host= ' 192.168.19.76 ', master_user= ' master ', master_password= ' 123456 ', master_log_file= ' Mysql2-bin.000001 ', master_log_pos=1112; START SLAVE;

19.76:

Change MASTER to master_host= ' 192.168.19.74 ', master_user= ' master ', master_password= ' 123456 ', master_log_file= ' Mysql1-bin.000001 ', master_log_pos=1112; START SLAVE;

Second, compile and install Haproxy

1. Compile the installation haproxy on 19.66 and 19.79:

Tar XF haproxy-1.5.14.tar.gz cd haproxy-1.5.14make target=linux2628 arch=x86_64        //Set make install according to your own host sbindir=/ usr/sbin/mandir=/usr/share/man/docdir=/usr/share/doc/

2. Provide a startup script:

Vim/etc/init.d/haproxy--------------------------------------------------->#!/bin/sh## haproxy## chkconfig:-85 15# Description:haproxy is a free, very fast and reliable solution # offering high availability, load Bala Ncing, and # proxying for TCP and http-based applications# processname:haproxy# config:/etc/haproxy/ haproxy.cfg# pidfile:/var/run/haproxy.pid# Source function Library. /etc/rc.d/init.d/functions# Source Networking configuration: /etc/sysconfig/network# Check that networking are up. ["$NETWORKING" = "no"] && exit 0exec= "/usr/sbin/haproxy" prog=$ (basename $exec) [-e/etc/sysconfig/$prog] && amp;. /etc/sysconfig/$progcfgfile =/etc/haproxy/haproxy.cfgpidfile=/var/run/haproxy.pidlockfile=/var/lock/subsys/ Haproxycheck () {$exec-c-v-f $cfgfile $OPTIONS}start () {$exec-c-q-f $cfgfile $OPTIONS if [$?-ne 0]; the        n echo "Errors in the configuration file, check with $prog check."  Return 1 fi  Echo-n $ "Starting $prog:" # start it up here, usually something like "daemon $exec" daemon $exec-D-F $cfgfile    -P $pidfile $OPTIONS retval=$? echo [$retval-eq 0] && touch $lockfile return $retval}stop () {echo-n $ "stopping $prog:" # Stop I    T here, often "Killproc $prog" Killproc $prog retval=$?    echo [$retval-eq 0] && rm-f $lockfile return $retval}restart () {$exec-c-q-f $cfgfile $OPTIONS If [$?-ne 0];        Then echo "Errors in the configuration file, check with $prog check." Return 1 fi Stop start}reload () {$exec-c-q-f $cfgfile $OPTIONS if [$?-ne 0]; then echo "Error        s in the configuration file, check with $prog check. " Return 1 fi echo-n $ "reloading $prog:" $exec-D-F $cfgfile-P $pidfile $OPTIONS-SF $ (cat $pidfile) retval=    $? echo return $retval}force_reload () {restart}fdr_status () {status $prog}case "$" in Start|stop|restart|reloa    D    $;;    Force-reload) force_reload;;    check) check;;    status) Fdr_status;; Condrestart|try-restart) [!-F $lockfile] | |    restart;; *) echo $ "Usage: $ start|stop|status|restart|try-restart|reload|force-reload}" Exit 2esac
<---------------------------------------------------
Chkconfig--add Haproxy
Chkconfig Haproxy on
chmod +x/etc/init.d/haproxy

3. Provide the configuration file:

Mkdir/etc/haproxy
Mkdir/var/lib/haproxy
Useradd-r haproxyvim/etc/haproxy/ Haproxy.cfg----------------------------------------------------------------------->
Global log 127.0.0.1 local2 chroot/var/lib/haproxy pidfile/var/run/haproxy.pid maxconn                    4000 User haproxy Group Haproxy daemon Stats socket/var/lib/haproxy/statsdefaults mode TCP//haproxy Run mode log global option Dontlognull Opti    On Redispatch Retries 3 timeout Http-request 10s Timeout queue 1m    Timeout Connect 10s Timeout client 1m timeout server 1m timeout http-keep-alive 10s Timeout Check 10s maxconn 600//maximum number of connections

Listen stats//configuration Haproxy Status page
Mode http
bind:6677//Find a more specific port
Stats enable
Stats hide-version//Hide Haproxy version number
Stats uri/haproxyadmin?stats//A URI to open the status page
Stats Realm haproxy\ Statistics//Prompt text when entering account password
Stats auth admin:admin//user name: Password
Stats admin If TRUE//Open Status page management function
Frontend main *:3306//Here for experimental convenience, use 3306-port default_backend MySQL/backend server group name backend my SQL balance Leastconn//scheduling with least connection
Server M1 192.168.19.74:3306 Check Port 3306 maxconn 300
Server M2 192.168.19.76:3306 Check port 3306 maxconn 300

4. Start the log:

Vim/etc/rsyslog.conf-----------------------------------------------------># provides UDP syslog reception                / /Remove the following two lines of comments, turn on UDP monitor $modload imudp$udpserverrun 514local2.*             /var/log/haproxy.log      //Add this line
<-----------------------------------------------------
Service Rsyslog Restart

5. Start the test haproxy:

Service Haproxy Start
Netstat-tnlp

　

6. Create a Telnet account on 19.74:

GRANT all on * * to ' jason ' @ ' 192.168.19.% ' identified by ' 123456 '; FLUSH privileges;

7. Log in to MySQL on 19.66 and 19.79 respectively, and continue down if you can connect successfully:

Yum-y install MySQL                            //Run this command if there is no MySQL client
mysql-ujason-p123456-h192.168.19.66          //log on at 19.66
mysql-ujason-p123456-h192.168.19.79          //log on at 19.79

Third, installation keepalived

1. Compile the installation keepalived on 19.66 and 19.79:

Tar XF keepalived-1.2.19.tar.gz cd keepalived-1.2.19./configure--prefix=/usr/local/keepalived--sbindir=/usr/sbin/- -sysconfdir=/etc/--mandir=/usr/local/share/man/--with-kernel-dir=/usr/src/kernels/2.6.32-504.30.3.el6.x86_64/      //kernel version replaced with own host make && make Installchkconfig--add keepalivedchkconfig keepalived on

2. Configure on 19.66:

vim/etc/keepalived/keepalived.conf----------------------------------------------------->
! Configuration File for Keepalived

Global_defs {//This paragraph is temporarily skipped
Notification_email {
[Email protected]
[Email protected]
[Email protected]
}
Notification_email_from [email protected]
Smtp_server 192.168.200.1
Smtp_connect_timeout 30
router_id Lvs_devel
}
Vrrp_script chk_haproxy {Script "/etc/keepalived/chk.sh"//check Haproxy scripts interval 2 Check once every two seconds}vrrp_instance vi_1 {State BACKUP//defined as Backup node nopreempt//Open no preemption interface eth0 virtual_router_id Prior ity 100//No preemption is enabled, so the priority must be higher than the other Advert_int 1 authentication {Auth_typ    E PASS auth_pass ABCD} virtual_ipaddress {192.168.19.150//configure VIP }
Track_script {
Chk_haproxy//Invoke Check Script
}
Notify_backup "/etc/init.d/haproxy restart" Notify_fault "/etc/init.d/haproxy Stop"}

3. Configure on 19.79:

vim/etc/keepalived/keepalived.conf----------------------------------------------------->
! Configuration File for Keepalived

Global_defs {
Notification_email {
[Email protected]
[Email protected]
[Email protected]
}
Notification_email_from [email protected]
Smtp_server 192.168.200.1
Smtp_connect_timeout 30
router_id Lvs_devel
}
Vrrp_script chk_haproxy {script "/etc/keepalived/chk.sh" interval 2}vrrp_instance vi_1 {State BACKUP Interfa Ce eth0 virtual_router_id advert_int 1 Authentication {auth_type PASS Auth_pass ABCD} virtual_ipaddress {192.168.19.150}
Track_script {
Chk_haproxy
} notify_backup "/etc/init.d/haproxy restart" Notify_fault "/etc/init.d/haproxy Stop"}

4. Create the chk.sh file on both machines:

vim/etc/keepalived/chk.sh------------------------------------------------>
#!/bin/bash
#
If [$ (ps-c haproxy--no-header | wc-l)-EQ 0]; Then
/etc/init.d/keepalived stop
Fi
<------------------------------------------------
chmod +x/etc/keepalived/chk.sh

5. Test on 19.66 and 19.79:

Service keepalived Start

Both hosts are configured as backup, so which one runs KEEPALIVED,VIP first. I just started here. VIP runs on 19.66 and then tests for connectivity:

　　　　

mysql-ujason-p123456-h192.168.19.150
------------------------------------------->
CREATE DATABASE Bokeyuan;

Back-End Database server Capture package:

　　　　

Stop the 19.66 keepalived service and let the VIP move to 19.79, then test:

Service keepalived Stop                   //stop 19.66 of keepalived services
mysql-ujason-p123456-h192.168.19.150
------------------------------------------->
SHOW DATABASES;

Back-End Database server Capture package:

　　　　

6. In the browser open http://192.168.19.150:6677/haproxyadmin?stats, open the Haproxy status page:

Turning off the MySQL service on 19.74, you can see that haproxy is very quick to detect back-end servers:

Service Mysqld Stop

7. Additional Instructions:

To continue the previous experiment, the 19.66 keepalived service is started again, you can find that the VIP is still on the 19.79, this is why before the configuration is not preemption reasons. If you configure 19.66 as Master in the normal configuration, when it restarts the keepalived service, it will be sure to grab the VIP back. But in fact we do not want this, because 19.79 is still working, 19.66 there is no reason to rob resources, resulting in unnecessary resource switching. The experimental demo is here, thank you!

Configure high-availability load balancing for MySQL with keepalived and Haproxy