MySQL master-slave configuration and cluster load balancing

• Read Catalogue

  1. Introduction
  2. Basic Environment
  3. Configuring MySQL primary master replication
  4. Brief introduction of Middleware
  4.1, Haproxy introduction
  4.2, Keepalived Introduction
  5, the installation and configuration of middleware (Haproxy, keepalived)
  5.1, installation Haproxy
  1), compile and install Haproxy
  2), provide startup script
  3), provide configuration files
  4), start log
  5), start Haproxy
  6), test haproxy
  5.2, installation keepalived
  1), resolve the missing software library files
  2), compile and install keepalived software
  3), create the configuration file
  4), create script file
  6. Function Test
  6.1. Process brief
  6.2, Test haproxy monitoring front-end Port 3306
  6.3, test high-availability +keepalived do not preempt VIP
  6.4. Test Load Balancing
  7. Summary and Suggestions

1. Introduction

With the use of MySQL over time, the number of users and the gradual increase in the amount of data, access is increased, and eventually will bring MySQL to a bottleneck, then MySQL performance will be greatly reduced. This result is also not conducive to the promotion of software.

So how do you cross this bottleneck and increase the concurrency of MySQL? There are many methods, such as distributed database, read/write separation, high available load balancing, increasing cache server and so on. The previous article has introduced a read-write separation scheme, and next I will explain MySQL high-availability load balancing method.

There are many ways to achieve high-availability load balancing, such as lvs+keepalived combination implementations, haproxy+keepalived combination implementations, and so on, where we use the haproxy+keepalived combination to achieve MySQL's high-availability load balancing technology.

Back to top 2, basic environment

Four Linux virtual hosts

Linux version CentOS6.6

MySQL 5.5 (already installed)

haproxy-1.5.14

keepalived-1.2.19

ip:192.168.95.11 (MYSQL1), 192.168.95.12 (MYSQL2), 192.168.95.13 (haproxy+keepalived), 192.168.95.14 (haproxy+ keepalived), 192.168.95.55 (VIP)

Back to top 3, configure MySQL Master master replication

Detailed configuration steps can refer to this article:

Http://www.cnblogs.com/phpstudy2015-6/p/6485819.html#_label7

The following is a brief introduction to MySQL's primary master replication:

What is primary master replication? That is, two MySQL can read and write, data records through the binary communication to the other side to maintain the consistency of the data.

(192.168.95.11 master-slave replication +192.168.95.12 master-slave replication ==192.168.95.11 , 192.168.95.12 primary master replication)

So the thing that must be resolved in the primary master replication is the self-increment primary key issue. If the MYSQL1 primary key ID is increased to 12, at this time the binary data has not reached Mysql2, then the mysql2 just to insert data, then the new data primary key ID is 12, that is not a mess! To solve this problem we can directly change the configuration file in MySQL.

1 ), change the configuration file

--192.168.95.11:mysqlserver-id=11   #任意自然数n, just ensure that the two MySQL hosts do not repeat. Log-bin=mysql-bin   #开启二进制日志auto_increment_increment =2   #步进值auto_imcrement. Generally have n main MySQL to fill nauto_increment_offset=1   #起始值. Generally fill the nth master MySQL. This is the first master mysqlbinlog-ignore=mysql   #忽略mysql库 "I don't usually write" Binlog-ignore=information_schema   #忽略information_ Schema Library "I don't usually write" Replicate-do-db=aa   #要同步的数据库, default all libraries--192.168.95.12:mysqlserver-id=12log-bin=mysql-binauto_ Increment_increment=2auto_increment_offset=2replicate-do-db=aa

Restart MySQL after configuration.

2 ), configure 192.168.95.11 master-slave replication

1. Create a MySQL user in 192.168.95.12 that can log in to the 192.168.95.11 host

Users: Mysql11

Password: mysql11

Mysql>grant REPLICATION SLAVE on * * to ' mysql11 ' @ ' 192.168.95.11 ' identified by ' mysql11 '; mysql>flush privileges;

2. View 192.168.95.12 binary Log

Mysql> Show master status;

3, inform the binary file name and location

mysql> Change Master    to-master_host= ' 192.168.95.11 ',    master_user= ' Mysql11 ',    ->master_ Password= ' Mysql11 ',    ->master_log_file= ' mysql-bin.000097 ',    master_log_pos=107;

4. View Results

mysql> slave start;mysql> Show Slave status\g

Configure master-slave replication success

3 ), configure 192.168.95.12 master-slave replication

Ditto

Configuration Master-slave replication succeeded.

Back to top 4, middleware brief back to top 4.1 , Haproxy Introduction

Haproxy is an open source high performance reverse proxy or one of the load Balancer services software that supports dual standby, virtual hosting, TCP and HTTP application-based proxies. Its configuration is simple, and has a good health check on the server node (equivalent to keepalived health check), when its agent's back-end server failure, Haproxy will automatically remove the fault server, when the server failed to recover Haproxy will automatically be the RS server.

Haproxy is especially useful for Web sites that are heavily loaded, and often require session-hold or seven-tier processing. The haproxy runs on the current hardware and can support tens of thousands of concurrent connections. and its operating mode makes it easy and safe to integrate into your current architecture, while protecting your Web server from being exposed to the web.

Haproxy Software introduces the functionality of Frontend,backend, frontend (ACL rule matching) can make a rule match based on any HTTP request header, and then directs the request to the associated backend (server Pools waiting for the front end to turn the request over to the server group). Through frontend and backup, we can easily implement the Haproxy 7-layer agent function, Haproxy is a rare excellent agent service software.

Back to Top 4.2 , keepalived Introduction

Keepalived is based on the VRRP protocol, the VRRP full name virtual Router Redundancy Protocol, that is, the VPN routing redundancy protocol.

Virtual Routing Redundancy Protocol, can be considered to implement a high-availability router protocol, will be n units to provide the same functionality of a router group, the group has a master and multiple backup, Master has an external service VIP (which is the default route for other machines in the LAN), Master sends multicast, and when backup does not receive the VRRP package, it thinks Master is down. It is then necessary to elect a backup master based on the priority of the VRRP. This will ensure that the router is highly available.

Keepalived has three main modules, namely core, check and VRRP. The core module is the kernel of keepalived, which is responsible for initiating, maintaining, and loading and parsing the global configuration file for the main process. Check is responsible for health checks, including a variety of common inspection methods. The VRRP module is to implement the VRRP protocol.

Back to top 5, middleware installation and configuration (Haproxy, keepalived)

Baidu Cloud Download: Http://pan.baidu.com/s/1qYoCjDE Password: 7CEF

Back to top 5.1, install Haproxy

Installation of Haproxy (identical installation) in 192.168.95.13, 192.168.95.14

Back to top 1), compile and install Haproxy

# TAR-ZXVF haproxy-1.5.14.tar.gz# cd haproxy-1.5.14# make target=linux26 arch=x86_64 # make install Sbindir=/usr/sbin/ma ndir=/usr/share/man/docdir=/usr/share/doc/

Attention:

1, why do not configure, please see. Haproxy-1.5.14 already exists makefile file.

2. When make, target and arch need to be set according to their own Linux host

3. I added some extra options when make install. This can be added without your own configuration, if not added will be installed by default path, please see.

Back to top 2), provide startup script

Put haproxy this startup script under the/etc/init.d/folder so we can start it directly with the service

"Note": This startup script is only suitable for my installation path. If the installation path is different, you need to make the appropriate modifications to use.

View Code

Back to top 3), provide configuration file

Build the appropriate directory and configuration file according to the startup script above

# mkdir/etc/haproxy# mkdir/var/lib/haproxy# useradd-r haproxy       #建立脚本启动用户 # vi/etc/haproxy/haproxy.cfg

"Configuration File"

#这里的配置文件仅仅只是贴出来进行解析说明.

#如果需要这个配置文件最好将注释解析全部删除掉, because I am using the process, because there is a comment parsing and error, after the deletion will be able to run normally.

#可以下载这个配置文件进行使用, consistent with the configuration file posted below, except that there is no comment parsing

#百度云下载该配置文件 (without comments): Link: http://pan.baidu.com/s/1gfOMtKB Password: zl9o

Global log 127.0.0.1 local2//Log definition level Chroot/var/lib/haproxy//Current working directory pidfile/v AR/RUN/HAPROXY.PID//Process ID maxconn 4000//Maximum number of connections user Haproxy//Run Program User Group Haproxy daemon//background run stats Socket/var/lib/haproxy/statsdefau LTS mode TCP//haproxy operating mode (HTTP | TCP |     Health) Log global option Dontlognull option Redispatch     ServerID the corresponding server is hung, force directed to other healthy servers retries 3//Three connections failed server without timeout Http-request 10s             Timeout Queue 1m timeout connect 10s//Connection time-out timeout client 1m            Client time-out Timeout server 1m//server timeout timeout http-keep-alive 10s timeout check 10s Heartbeat detection Maxconn 600//MAX connections Listen sTats//Configure Haproxy Status page (used to view the page) mode HTTP bind:8888 stats enable stats Hide-versio N//Hide Haproxy version number stats Uri/haproxyadmin?stats//One will be used to open the Status page URI stats realm haproxy\ Statist                     ICS//Enter the account password when the prompt text stats auth admin:admin//user name: Password frontend main bind 0.0.0.0:3306 Use port 3306.     Listening front-end port "means that any IP access 3306 port will turn data into a MySQL server group" default_backend MySQL/backend server set name backend MySQL balance Leastconn//Dispatch server MYSQL1 192.168.95.11:3306 Check Port 3306 maxconn 1 with least connection 92.168.95.12:3306 Check Port 3306 maxconn 300

Back to top 4), start log

#service Rsyslog Restart

Back to top 5), start Haproxy

# service Haproxy Start

Back to top 6), test haproxy

The appropriate test for the photo configuration file

Open Browser Input 192.168.95.13:8888/haproxyadmin?stats

After logging in as shown below, it indicates that the installation of Haproxy is successful.

Back to top 5.2, install keepalived

Official website Download: http://www.keepalived.org/download.html

Install keepalived in 192.168.95.13, 192.168.95.14

Back to top 1), resolve missing software library files

"This step depends on the specific Linux version, and some of them already have OpenSSL installed. Specific circumstances can be implemented./configure is able to determine the lack of software library files "

First we unpack the keepalived-1.2.19.tar.gz and then go to the directory./configure view

# TAR-ZXVF keepalived-1.2.19.tar.gz#/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.el6.x86_64/

Installation of the visible keepalived requires first installing the software OpenSSL

Missing header files, just install OpenSSL and openssl-devel

The simplest method is: yum-y install OpenSSL Openssl-devel

No net friends do not fear, the next step is to introduce the rpm method installation:

#挂载光盘, find the software on the disc. If the disc cannot be found, download it directly and then pass it on to Linux for installation.

# mount/dev/cdrom  /home/suifeng2/rom/# cd rom/# cd packages/# ls |grep OpenSSL

There are various dependencies when installing the Keepalived software, which is a dependency graph that I have compiled after I installed the software:

Now that you know the dependencies of each software, you can start the installation by following the latest software:

(You can also start from the previous installation, step-by-step view of the various dependencies)

1 , installing OpenSSL

# RPM-IVH openssl-1.0.1e-30.el6.x86_64.rpm

Install OpenSSL successfully

2 , installation Openssl-devel

Install Libsepol-devel:

# RPM-IVH libsepol-devel-2.0.41-4.el6.x86_64.rpm

Install Pkgconfig (LIBSEPOL):

# RPM-IVH pkgconfig-0.23-9.1.el6.x86_64.rpm

Install Libselinux-devel:

# RPM-IVH libselinux-devel-2.0.94-5.8.el6.x86_64.rpm

Install Keyutils-libs-devel:

# RPM-IVH keyutils-libs-devel-1.4-5.el6.x86_64.rpm

Install Libcom_err-devel:

# RPM-IVH libcom_err-devel-141.12-21.el6.x86_64.rpm

Install Krb5-devel:

# RPM-IVH krb5-devel-1.10.3-33.el6.x86_64.rpm

Install Zlib-devel:

# RPM-IVH zlib-devel-1.2.3-29.el6.x86_64.rpm

Installing Openssl-devel :

# RPM-IVH openssl-devel-1.0.1e-30.el6.x86_64.rpm

Back to top 2), compile and install keepalived software

# 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.el6.x86_64/# make && make Install
# chkconfig--add keepalived  #添加开机自启 (I haven't added it yet) # Chkconfig keepalived on

"Note":

1, installation./configure in the-prefix after a few options optional, choose to use the service to start directly. The best advice is to add.

2 、--with-kernel-dir This option is based on your Linux version (use the command uname–a in Linux to find out)

Back to top 3), create configuration file

/etc/keepalived/folder already exists keepalived.conf file, we renamed it to Keepalived.conf.back, and then set up our own keepalived.conf configuration file.

Vi/etc/keepalived/keepalived.conf (13 and 14 configuration file paths consistent)

"The following is a simple configuration file, it is best to remove the comments when using"

Configuration file Download (without comments):

192.168.95.13 configuration file:

! Configuration File for keepalived# simple head, here is the main can do email notification alarm settings, which is not configured here; global_defs {Notificationd lvs_devel} #预先定义一个脚本, Convenient to call later, can also define multiple, convenient choice; Vrrp_script chk_haproxy {script "/etc/keepalived/chk.sh" #具体脚本路径 interval 2 #脚本循环运行间隔} #VRRP虚拟    Routing Redundancy Protocol configuration vrrp_instance Vi_1 {#VI_1 is a custom name, state backup #MASTER表示是一台主设备, backup is indicated as a standby device "We are set to standby because it is set to no preemption." Nopreempt #开启不抢占 interface eth0 #指定VIP需要绑定的物理网卡 virtual_router_id #VRID虚拟路由标识, also called the group name, the devices within the group need the same PRI Ority #定义这台设备的优先级 1-254; no preemption is turned on, so the priority must be higher than another Advert_int 1 #生存检测时的组播信息发送间隔, consistent authentication {#设置验证信息 in the group, Group consistent Auth_type Pass #有PASS and AH Two, common pass Auth_pass ASD #密码} virtual_ipaddress {192.168 .95.55 #指定VIP地址, consistent within the group, can set multiple IPs} track_script {#使用在这个域中使用预先定义的脚本, defined above Chk_haproxy} NOTIFY_BA Ckup "/etc/init.d/haproxy Restart" #表示当切换到backup状态时, script to execute Notify_fault "/etc/init.d/haproxy stop" #故障时执行的脚本}

192.168.95.14 configuration file:

Configuration file is almost the same as above, just change the priority 120 "just a little more than the above"

Back to top 4), create script file

Script files required to create the above configuration file (13, 141 samples)

(Detect Haproxy If there is no fault, failure will keepalived stop, let out VIP)

# vi/etc/keepalived/chk.sh#!/bin/bash#if [$ (ps-c haproxy--no-header | wc-l)-EQ 0]; Then       /etc/init.d/keepalived STOPFI

Give execute permission # chmod +x/etc/keepalived/chk.sh

Start keepalived:# service keepalived start

Installation keepalived Successful!

Back to top 6, functional testing

Set up a MySQL user in MYSQL1 and MYSQL2 before testing, this user can allow 13, 14linux host Login:

User: Jack

Password: 321

host:192.168.95.%

Mysql> GRANT All on * * to ' jack ' @ ' 192.168.95.% ' identified by ' 321 ';mysql> FLUSH privileges;

Back to top 6.1, Process brief

About the overall operating process:

First two 11,12 MySQL and 13, 14 Haproxy, keepalived all start;

Keepalived gets the virtual IP in the keepalived group and detects if the haproxy is killed;

Haproxy is responsible for forwarding incoming data to 11 or 12 of MySQL.

I draw a simple understanding of the diagram: (relatively concise, ah, do not do AH)

Next we will test each function to verify.

Back to top 6.2, test haproxy monitoring front-end Port 3306

1, frontend monitoring port 3306, the MySQL, Haproxy, keepalived all open

2, use any one MySQL client login user jack

Login successful (windowns on MySQL)

3, change the frontend listening port to 3307, continue to operate the login test

Login failed

Results: Explains the usefulness of the frontend listening port and helps us understand haproxy usage.

Back to top 6.3, test high availability +keepalived not preemption VIP

You can find out who gets the VIP through the Haproxy monitoring page

1, start 13, then 14 keepalived, Haproxy (start keepalived will automatically open Haproxy)

2. Visit http://192.168.95.55:8888/haproxyadmin?stats

13 Gets the VIP

3, # kill-9 8923

Refresh Http://192.168.95.55:8888/haproxyadmin?stats

14 Get the VIP, the machine works properly

Result: Proven high availability, hung up on one another to continue working

4, restart 13 of Haproxy and keepalived

and Refresh Http://192.168.95.55:8888/haproxyadmin?stats

Result: At this time VIP still in 14 hands, proved that keepalived configuration does not preempt VIP, do not waste resources to get VIP.

Back to top 6.4, test load Balancer

1, all normal start, at this time VIP in 14 hands

2, respectively, in 11, 12 to open the clutch

# tcpdump-n-I eth0 host 192.168.95.11 and 192.168.95.14# tcpdump-n-i eth0 host 192.168.95.12 and 192.168.95.14

3, using different client login jack user, constantly add data to the database

Result: At this point 14 to 11, 12 have sent data, this time proof load balancing

"The Balance method we set up in the Haproxy is the least connected, and if the test results will be more pronounced with the Roundrobin method"

Attention:

When a single MySQL is hung, Haproxy will kick it out of the MySQL server group.

When a command comes in, it is forwarded to the normal server.

When the problem of MySQL resumes, Haproxy will automatically put it back into the MySQL server group, and automatically synchronize the data that is not synchronized

Test:

1, all normal start

MYSQL1 and Mysql2 are normal.

2. Turn off the MYSQL2

Mysql2 a problem, kick it out of the MySQL group

3. Start MYSQL2

Mysql2 restored and then put it back in the MySQL group

"When MYSQL2 is hung up, if there is data inserted, it will be forwarded to MYSQL1, and when MySQL resumes, it will synchronize the data to Mysql2"

Back to top 7, summary and suggestions

In this blog post we are not just focusing on the implementation of this entire MySQL high-availability load balancer, but we should also understand how haproxy and keepalived work. Haproxy and keepalived are powerful tools to understand how they are implemented, so they can be combined with other servers to build a robust service cluster. For example, it can be combined with Apache to form a highly available load-balanced Web cluster.

This article simply builds a MySQL high-availability load-balanced environment that is really applied to the production environment and needs to be modified according to the specific project.

Finally, my suggestion is to read this blog to learn more about the Haproxy and keepalived configuration, as well as learn the same as the Haproxy function of LVS.

(These are some of their own views and summary, if there is insufficient or wrong place please point out)

MySQL master-slave configuration and cluster load balancing