"mysql optimization topic" High-availability, load-balanced MySQL cluster solution (12)

Why do I need a MySQL cluster?

In the performance bottleneck of a large distributed system, the most vulnerable is the connection. There are two connections, one is the client connection to the backend, and the other is the back-end connection to the database. Simple next two blue box (in fact, this picture is I in the Goku question and answer other people's time with the windows of their own drawing board, do not spray ah. ）

Copyright belongs to the author, haha

In the client and back-end can take advantage of similar nginx load Balancing solution (this topic is MySQL optimization, behind the high concurrency topic and then detailed on the connection 1 load balancing), and the database layer is the most vulnerable layer, generally in the application design in the upstream need to intercept the request, the database layer only assume the "capacity within" Access requests, so we have the lowest level of database peace of mind by introducing queues and caches at the service layer.

However, if the request surges, there is still a lot of query pressure to what to do with MySQL?

The omnipotent programmer still has to find a way to solve the MySQL bottleneck. At this point, the cluster appears. In the backend and the database we can use similar Mycat load balancer to implement MySQL cluster, improve the overall performance of MySQL. (many combinations available, such as lvs+keepalived combination, haproxy+keepalived combination)

First, the basic software version

Operating system: centos-6.6-x86_64

JDK version: jdk1.7.0_72

Haproxy version: haproxy-1.5.16.tar.gz

Mycat version: mycat-server-1.4-release-20151019230038-linux.tar.gz

MySQL version: mysql-5.6.26.tar.gz

Second, the deployment of environmental planning:

Third, the cluster deployment architecture diagram is as follows:

Schematic Description:

Haproxy is responsible for distributing the request to mycat, which acts as a load balancer, while Haproxy can detect if Mycat is alive and haproxy only forwards the request to the surviving mycat. Mycat is still available if a mycat server goes down and haporxy forwards the request without forwarding it to the mycat on the outage.

V. Deployment of MYCAT Node 2

Mycat Host 2 (edu-mycat-02,192.168.1.204) (previous master-slave replication deployment, so quickly forget to see, at the end of the link)

Note: The host name Mapping configuration is added (or updated) in edu-mycat-01 (192.168.1.203) and edu-mycat-02 (192.168.1.204).

# vi/etc/hosts

192.168.1.203 edu-mycat-01

192.168.1.204 edu-mycat-02

192.168.1.205 edu-mysql-01

192.168.1.206 edu-mysql-02

Vi. Configuring the Mycat Status Check service (configured on the MYCAT node host)

The Mycat service Host (edu-mycat-01, edu-mycat-02) needs to increase the status detection script of the Mycat service, and open the corresponding detection port to provide haproxy to the mycat of the service status of Detection and judgment. Can be implemented using XINETD, Xinetd,haproxy can use HTTPCHK to detect the survival of mycat. (xinetd is extended Internet daemon,xinetd is a new generation of network daemon services, also known as Super Internet server.) Often used to manage a variety of lightweight Internet services. XINETD provides functionality similar to Inetd+tcp_wrapper, but is more powerful and secure. XINETD basic services for Linux systems)

1, if the XINETD has not been installed, you can use the following command to install:

# yum Install xinetd

2, check/etc/xinetd.conf at the end of whether there is includedir/etc/xinetd.d, not add

# vi/etc/xinetd.conf

3, check whether the/ETC/XINETD.D directory exists, does not exist just created

# mkdir/etc/xinetd.d/

4, increase the Mycat Survival status detection service configuration

# Touch/etc/xinetd.d/mycat_status

# Vi/etc/xinetd.d/mycat_status

Add the following content:

Service Mycat_status

{

Flags = Reuse

# # Socket_type with this tag is stream, you need to set wait to No

Socket_type = stream # # packet processing mode, stream is TCP packet

Port = 48700 # # service Listening port

wait = no # # indicates that no wait is required, i.e. the service will run in a multithreaded manner

user = root # # Users executing this service process

Server =/usr/local/bin/mycat_status # # service scripts that need to be started

Log_on_failure + = USERID # # login Failure Record contents

disable = no # # to start the service, set this parameter to No

}

5. Add/usr/local/bin/mycat_status Service Script

# Touch/usr/local/bin/mycat_status

# Vi/usr/local/bin/mycat_status

Add the following content:

#!/bin/bash

#/usr/local/bin/mycat_status.sh

# This script checks if a MYCAT server was healthy running on localhost.

# It'll return:

# "http/1.x OK" (if Mycat is running smoothly)

# "http/1.x 503 Internal Server Error" (else)

mycat= '/usr/local/mycat/bin/mycat status | grep ' not running ' | Wc-l '

If ["$mycat" = "0"]; Then

/bin/echo-e "http/1.1 OK"

Else

/BIN/ECHO-E "http/1.1 503 Service Unavailable"

Fi

6. Give executable permission to new script

# chmod A+x/usr/local/bin/mycat_status

7. Join the Mycat_status service in/etc/services

# vi/etc/services

At the end, add:

Mycat_status 48700/tcp # Mycat_status

After saving, restart the XINETD service

# Service XINETD Restart

8. Verify that the Mycat_status service is successfully started

# Netstat-antup|grep 48700

You can see this message stating that the service configuration was successful.

9. Open the 48700 port on the firewall of the Mycat service host

# Vi/etc/sysconfig/iptables

Increase:

-A input-m state--state new-m tcp-p TCP--dport 48700-j ACCEPT

Restart firewall after saving

# Service Iptables Restart

Script test:

#/usr/local/bin/mycat_status

It's long! Intermission, three laps to the right three laps left. Can not look down to the first collection of attention ha.

Vii. introduction of Haproxy (official website://www.haproxy.org/)

HAProxy is a free, fast, and reliable solution that provides high availability, load balancing, and proxy software based on TCP (layer fourth) and HTTP (layer seventh) applications that support virtual hosting.

Haproxy currently has three versions: 1.4, 1.5, 1.6,centos6.6 with a RPM package of 1.5.

HAProxy1.5 version, support SSL, DDoS protection and other functions, can be crossing network description:

Version 1.5:the most featureful version, supports SSL, IPV6, keep-alive, DDoS protection, etc ...

Mycat officially recommends using Haproxy as a high-availability load Balancer agent for Mycat.

Viii. installation of Haproxy (192.168.1.191)

1, download (or upload) haproxy-1.5.16.tar.gz to/USR/LOCAL/SRC, unzip the installation

[Email protected] src]# cd/usr/local/src/

#wget//www.haproxy.org/download/1.5/src/haproxy-1.5.16.tar.gz

[Email protected] src]# TAR-ZXVF haproxy-1.5.16.tar.gz

[Email protected] src]# CD haproxy-1.5.16

2, if you need to know the installation note, you can view the Haproxy software description

[email protected] haproxy-1.5.16]# less README

3. Install the dependent packages required for compilation

# yum Install gcc gcc-c++ pcre pcre-devel zlib zlib-devel OpenSSL openssl-devel

4. Compiling

# make target=linux2628 arch=x86_64 use_pcre=1 use_openssl=1 use_zlib=1 prefix=/usr/local/haproxy

# target is the specified kernel version, above 2.6.28 recommended setting for Linux2628,linux OS kernel version view command # UNAME-R, Arch designation system architecture, OpenSSL pcre zlib These three packages need to be installed or not supported

5. Create the installation directory/usr/local/haproxy

# Mkdir/usr/local/haproxy

6. Perform the installation

[[email protected] haproxy-1.5.16]# make install Prefix=/usr/local/haproxy

Install-d "/usr/local/haproxy/sbin"

Install Haproxy "/usr/local/haproxy/sbin"

Install-d "/usr/local/haproxy/share/man"/man1

Install-m 644 doc/haproxy.1 "/usr/local/haproxy/share/man"/man1

Install-d "/usr/local/haproxy/doc/haproxy"

For x in configuration architecture Haproxy-en haproxy-fr; Do

Install-m 644 doc/$x. txt "/usr/local/haproxy/doc/haproxy";

Done

7. Create profile Directory

# mkdir-p/usr/local/haproxy/conf

# mkdir-p/etc/haproxy/

8. Copy configuration files from profile template and add profile soft Connect

# cp/usr/local/src/haproxy-1.5.16/examples/haproxy.cfg/usr/local/haproxy/conf/

# ln-s/usr/local/haproxy/conf/haproxy.cfg/etc/haproxy/haproxy.cfg

9. Copy the error page and add the directory Soft connection (HTTP mode option)

# Cp-r/usr/local/src/haproxy-1.5.16/examples/errorfiles/usr/local/haproxy/

# ln-s/usr/local/haproxy/errorfiles/etc/haproxy/errorfiles

10, copy boot file, and give executable permission

# Cp/usr/local/src/haproxy-1.5.16/examples/haproxy.init/etc/rc.d/init.d/haproxy

# chmod +x/etc/rc.d/init.d/haproxy

11. Add haproxy command Script soft connection

# ln-s/usr/local/haproxy/sbin/haproxy/usr/sbin

12, set Haproxy boot

# chkconfig--add Haproxy

# Chkconfig Haproxy on

Nine, haproxy configuration Mycat load Balancing cluster

Haproxy supports proxies for TCP (layer fourth) and HTTP (seventh tier) applications, and in this lesson we use Haproxy to do mycat load balancer proxies using TCP mode. In layer 4 mode, haproxy only forwards bidirectional traffic between the client and the server. Haproxy configuration is simple, has a very good server health check function, when its agent's back-end server fails, Haproxy will automatically remove the server, after the recovery will automatically join the server?

1. Modify the Haproxy.cfg configuration file

Refer to the official configuration document for specific parameter descriptions/usr/local/haproxy/doc/haproxy/configuration.txt

or GitHub connection://cbonte.github.io/haproxy-dconv/configuration-1.5.html

# vi/usr/local/haproxy/conf/haproxy.cfg

# # parameters in the global configuration are process-level parameters, usually related to the operating system it is running on

Global

Log 127.0.0.1 local0 Info # # defines a global syslog server that can define up to 2

# # # local0 is a log device, corresponding to the configuration in/etc/rsyslog.conf, the log level of the default recycle info

#log 127.0.0.1 Local1 Info

Chroot/usr/share/haproxy # # Modify the working directory of the Haproxy to the specified directory and execute before discarding permissions

# # # Chroot () operation to increase the security level of Haproxy

Group Haproxy # # with GID, but here is the specified user group name

User Haproxy # # with UID, but used here for username

Daemon # # Set Haproxy daemon daemon as Run

Nbproc 1 # # Specifies the number of haproxy processes started,

# # # can only be used for daemon mode haproxy; By default, 1 processes are started,

# # # Generally only use multi-process mode in a single process that can only open a few file descriptors in a farm

Maxconn 4096 # # Sets the maximum number of concurrent connections accepted per Haproxy process,

# # # is equivalent to the command line option "-N", and "ulimit-n" automatically calculates the result of the formal reference from the parameter set

# pidfile/var/run/haproxy.pid # # Process file (default path/var/run/haproxy.pid)

Node Edu-haproxy-01 # # Defines the name of the current node for multiple haproxy processes in an HA scenario when sharing the same IP address

Description EDU-HAPROXY-01 # # Description information for the current instance

# # Defaults: Used to provide default parameters for all other configuration segments, this default configuration parameter can be reset by the next "defaults"

Defaults

Log Global # # inherits the definition of log in global

Mode HTTP # # Mode: Modes handled (TCP: Layer four, Layer seven, health: status check, return OK only)

# # # TCP: the instance runs in pure TCP mode, and a full-duplex connection is established between the client and the server side.

# # # # # # # # # # # # # # 7 beginning no type of check, this is the default mode

# # # instances run in HTTP mode, and client requests are analyzed in depth before being forwarded to the backend server.

# # # All requests that are not compatible with RFC mode will be rejected

# # # Health: the instance is running in healthy mode, which responds only to "OK" information on the inbound request and closes the connection.

# # # # and no log information is logged, this mode will be used for monitoring status detection requests for the appropriate external components

Option Httplog

Retries 3

Option Redispatch # # ServerID after the corresponding server is hung, force directed to other healthy servers

Maxconn 2000 # # The maximum number of concurrent connections on the front end (default is 2000)

# # # It can't be used in the backend section, and for large sites, you can raise this value as much as possible so that Haproxy manages the connection queue.

# # # To avoid the inability to answer user requests. Of course, this maximum value cannot exceed the definition in the "global" segment.

# # # Also, be aware that Haproxy will maintain two buffers per connection, with a size of 8KB per cache,

# # # plus other data, each connection will occupy approximately 17KB of RAM space, which means that after proper optimization,

# # # 40000-50000 Concurrent connections will be maintained when there is 1GB of available RAM space.

# # # If you specify an oversized value, in an extreme scenario, it may eventually occupy more space than the current host's available memory.

# # # This may bring unexpected results, so set an acceptable value for it to be wise absolute, which defaults to 2000

Timeout Connect 5000ms # # Connection Timeout (default is milliseconds, unit can set us,ms,s,m,h,d)

Timeout Client 50000ms # # clients Timeout

Timeout Server 50000ms # # server Timeout

# # Haproxy Status Information statistics page

Listen Admin_stats

bind:48800 # # Bound Port

Stats Uri/admin-status # #统计页面

Stats Auth Admin:admin # # Set the Statistics page authentication user and password, if you want to set up more, another write a line

Mode http

Option Httplog # # enable logging HTTP requests

# # Listen: Used to define a complete proxy through the association "front End" and "back end", usually only useful for TCP traffic

Listen Mycat_servers

bind:3306 # # Bound Port

Mode TCP

Option Tcplog # # record TCP request log

Option Tcpka # # Whether to allow sending to server and client KeepAlive

Option Httpchk OPTIONS * http/1.1 host:www # # Backend Service status detection

# # # 48700 ports on the back end server (port values are configured on the back-end server via XINETD) to send an OPTIONS request

# # # (refer to the HTTP protocol for the principle), Haproxy will determine if the backend service is available based on what is returned.

# # # 2XX and 3xx response codes indicate a healthy state, and other response codes or no responses indicate a server failure.

Balance Roundrobin # # defines a load balancing algorithm that can be used in "defaults", "Listen", and "backend", by default, polling mode

Server mycat_01 192.168.1.203:8066 Check Port 48700 Inter 2000ms Rise 2 Fall 3 weight 10

Server mycat_02 192.168.1.204:8066 Check Port 48700 Inter 2000ms Rise 2 Fall 3 weight 10

# # Format: Server <name> <address>[:[port] [param*]

# # # Serser declares a server on the backend and can only be used for listen and backend segments.

# # # <name> The internal name specified for this server, which will appear in the log and warning messages

# # # <address> The IPV4 address of this server, also supports the use of resolvable host names, but need to resolve host name to response IPV4 address at startup

# # # [: [Port]] Specifies the destination port at which client connection requests are destined for this server, which is optional

# # # [param*] set a series of parameters for this server, are optional, more parameters, the following are only a few common parameters:

# # # # Weight: Weights, defaults to 1, and a maximum of 256,0 means not participating in load balancing

# # # Backup: Set as a standby server that cannot be enabled by other servers in the load Balancer scenario only

# # # Check: Initiates a monitoring status check on this server, which can be used to perform finer settings with additional parameters

# # # Inter: Sets the time interval for monitoring status checks, in milliseconds, by default of 2000,

##### can also use Fastinter and downinter to optimize this event latency based on server-side themes

# # # Rise: Sets the number of times the server needs to be checked for transition from offline to normal (not set, default is 2)

# # # Fall: Sets the number of times the server needs to be checked for transition from normal to offline status (default is 3 if not set)

# # # Cookie: Sets the cookie value for the specified server, and the value specified here will be checked when the inbound request is requested.

##### the server selected for the first time for this value will be selected by subsequent requests for the purpose of implementing a persistent connection function

# # # Maxconn: Specifies the maximum number of concurrent connections that this server accepts, if the number of connections destined to this server is higher than the value specified here.

# # # # #其将被放置于请求队列 to wait for other connections to be released

Note: The values of node and description are adjusted accordingly when deploying multiple nodes.

2, according to the above haproxy configuration file requirements to do the following configuration

(1) Adding Haproxy user groups and users

# Groupadd Haproxy

# useradd-g Haproxy Haproxy

(2) Create a path to the chroot run

# Mkdir/usr/share/haproxy

(3) Open 3306 ports and 48800 ports in the firewall

# Vi/etc/sysconfig/iptables

-A input-m state--state new-m tcp-p TCP--dport 3306-j ACCEPT

-A input-m state--state new-m tcp-p TCP--dport 48800-j ACCEPT

Restarting the firewall

# Service Iptables Restart

3, open Rsyslog haproxy logging function

By default haproxy is not logging, and if logging is required, the syslog of the system needs to be configured, and the Rsyslog service is in the Linux system. The syslog server can be used as a log monitoring center in a network, and Rsyslog is an open source tool that is widely used in Linux systems to forward or receive log messages through the TCP/UDP protocol. To install the Configuration Rsyslog service:

# yum Install Rsyslog # # do not install

# vi/etc/rsyslog.conf

Remove the $ModLoad imudp and the # before the $UDPServerRun 514

$ModLoad IMUDP # # is the module name, supports UDP protocol

$UDPServerRun 514

# #允许514端口接收使用UDP和TCP协议转发过来的日志,

# #而rsyslog在默认情况下, it is on the 514 port listening UDP

# cd/etc/rsyslog.d/# # Rsyslog service will come to this directory to load the configuration

# Touch Haproxy.conf # # Create a haproxy log configuration file

# vi/etc/rsyslog.d/haproxy.conf

Add the following content:

local0.*/var/log/haproxy.log

&~

# #如果不加上面的的 "&~" configuration is written to the/var/log/message file in addition to writing the log in/var/log/haproxy.log

To restart the Rsyslog service after the configuration is saved

# Service Rsyslog Restart

Shutting down system logger: [OK]

Starting system logger: [OK]

(After the Haproxy service starts, you can see the log in the/var/log/haproxy.log)

4, configure the system core IP packet forwarding function

# vi/etc/sysctl.conf

Net.ipv4.ip_forward = 1

Make configuration effective

# sysctl-p

5. Start Haproxy

# service Haproxy Start

# Ps-ef | grep haproxy

Haproxy 23921 1 0 23:27? 00:00:00/usr/sbin/haproxy-d-f/etc/haproxy/haproxy.cfg-p/var/run/haproxy.pid

Root 23924 23179 0 23:27 pts/1 00:00:00 grep haproxy

6. Connect via haproxy using MySQL client mycat

e:mysql-5.6.17-winx64in>mysql-uuser2-proncoo.2-h192.168.1.191-p3306

mysql> show databases;

mysql> use RC_SCHEMA2;

Mysql> Show tables;

Mysql> select * from Edu_user;

Write Data test

mysql> INSERT INTO Edu_user (UserName, PWD) VALUES (' Wu Shui ', ' roncoo.com ');

Then look at database insertions and data synchronization in MySQL.

8, Login Haproxy status information statistics page

192.168.1.191:48800/admin-status

User name and password are admin, corresponding HAPROXY.CFG configuration fragment

"mysql optimization topic" High-availability, load-balanced MySQL cluster solution (12)