Writing in the front
In Glacier, can you tell me how Mycat implements read/write separation in MySQL? In this paper, we implement the use of Mycat to achieve MySQL read-write separation. However, at this point, Mycat has only one node. If Mycat node goes down, the entire MySQL cluster will be unavailable. Therefore, it is necessary to ensure that Mycat node is highly available. So how do you make Mycat nodes highly available? Today, let’s talk about this topic. Build a high availability cluster for Mycat together.
At present, I also develop and maintain Mycat source code in my spare time, friends in learning and using Mycat, you can also add my wechat [SUN_shine_LYz] to communicate!
Note: The article has been included:
GitHub:github.com/sunshinelyz…
Gitee:gitee.com/binghe001/t…
Software version
- Operating system: centos-6.8-x86_64
- JDK version: JDk1.8
- HAProxy version: haproxy-1.5.19.tar.gz
- Mycat version: mycat-server-1.6
- MySQL version: mysql-5.7
The deployment plan
Mycat cluster architecture diagram
Illustration:
HAProxy is responsible for distributing requests to Mycat for load balancing. Meanwhile, HAProxy can detect whether Mycat is alive, and HAProxy will only forward requests to the surviving Mycat. If a Mycat server goes down, HAPorxy does not forward requests to the down Mycat, so Mycat is still available.
Deployment of Mycat node 2
Mycat host 2 (liuyazhuang134, 192.168.209.134) please refer to “Ice, can you tell me how to implement Mycat MySQL read-write separation?”
Note: Both liuYazhuang133 (192.168.209.133) and LiuYazhuang134 (192.168.209.134) need to add (or update) the hostname mapping configuration.
# vi /etc/hostsLiuyazhuang131 192.168.209.132 LIUyazhuang132 192.168.209.133 Liuyazhuang133 192.168.209.134 Liuyazhuang134 192.168.209.135 liuyazhuang135Copy the codeConfigure the Mycat status check service
Note: This needs to be configured on the Mycat node host.
Mycat service hosts (Liuyazhuang133 and Liuyazhuang134) need to add status detection scripts of Mycat service and open corresponding detection ports to provide HAProxy to detect and judge the service status of Mycat. This can be done using xinetd, through which HAProxy can use HTTPCHK to detect the survival status of Mycat. Xinetd refers to extended Internet Daemons. Xinetd is a new generation of network daemon services, also known as super Internet servers. Often used to manage a variety of lightweight Internet services. Xinetd provides functionality similar to inetd+tcp_wrapper, but more powerful and secure. Xinetd is a basic service for Linux.
(1) Install xinetd
If xinetd has not been installed, run the following command to install it:
yum install xinetd
Copy the code(2) Add includedir /etc/xinetd.d
Includedir /etc/xinetd.conf includedir /etc/xinetd.conf includedir /etc/xinetd.
vim /etc/xinetd.conf
Copy the code
(3) Create the /etc/xinetd.d directory
Check whether the /etc/xinetd.d directory exists. If no, create the xinetd.d directory
mkdir /etc/xinetd.d/
Copy the code(4) Add the configuration of Mycat survival detection service
touch /etc/xinetd.d/mycat_status
vi /etc/xinetd.d/mycat_status
Copy the codeAdd the following:
service mycat_status
{
flags = REUSE
If socket_type is stream, set wait to no
socket_type = stream Stream is a TCP packet
port = 48700 ## Service listening port
wait = no ## indicates that there is no waiting, that is, the service will run in a multithreaded manner
user = root ## The user executing the service process
server =/usr/local/bin/Mycat_status ## Service script that needs to be started
log_on_failure += USERID ## Record the contents of login failure
disable = no To start the service, set this parameter to no
}
Copy the codeAdd the /usr/local/bin/mycat_status service script
touch /usr/local/bin/mycat_status
vim /usr/local/bin/mycat_status
Copy the codeAdd the following:
#! /bin/bash
#/usr/local/bin/mycat_status.sh
# This script checks if a Mycat server is healthy running on localhost.
# It will return:
# "HTTP/1.x 200 OK\r" (if Mycat is running smoothly)
# "HTTP/1.x 503 Internal Server Error\r" (else)
Mycat=`/usr/local/Mycat/bin/Mycat status | grep 'not running' | wc -l`
if [ "$Mycat" = "0" ]; then
/bin/echo -e "HTTP / 1.1 200 OK \ r \ n"
else
/bin/echo -e "HTTP / 1.1 503 Service Unavailable \ r \ n"
fi
Copy the code(6) Grant the executable permission to the new script
chmod a+x /usr/local/bin/mycat_status
Copy the codeAdd mycat_status to /etc/services
vi /etc/services
Copy the codeAdd at the end:
mycat_status 48700/tcp # mycat_status
Copy the code
Save the Settings and restart the xinetd service
service xinetd restart
Copy the code(8) Verify whether the mycat_status service is started successfully
netstat -antup|grep 48700
Copy the code
If information similar to the preceding figure is displayed, the service is successfully configured.
(9) Open port 48700 on the firewall of Mycat service host
vi /etc/sysconfig/iptables
Copy the codeAdd:
-A INPUT -m state --state NEW -m tcp -p tcp --dport 48700 -j ACCEPT
Copy the codeSave the Settings and restart the firewall
service iptables restart
Copy the codeScript tests:
/usr/local/bin/mycat_status
Copy the codeHAProxy is introduced
HAProxy official website: www.haproxy.org/ Official documents of HAProxy versions: cbonte.github. IO /haproxy-dco…
HAProxy is a free, fast and reliable solution that provides high availability, load balancing and proxy software based on TCP (Layer 4) and HTTP (Layer 7) applications. It supports virtual hosting.
HAProxy has three versions: 1.4, 1.5, 1.6, and 1.7. CentOS6.6 has an RPM package of 1.5.
HAProxy1.5 supports SSL, DDoS protection and other functions. the most featureful version, supports SSL, IPv6, keep-alive, DDoS protection, etc… Mycat officially recommends using HAProxy as the high availability load balancing agent for Mycat.
The installation of HAProxy
Install the software on the server 192.168.209.135.
(1) Download or upload haproxy-1.5.19.tar.gz to /usr/local/src and decompress it
cd /usr/local/ SRC/wget http://www.haproxy.org/download/1.5/src/haproxy-1.5.19.tar.gz tar - ZXVF haproxy - 1.5.19. Tar. GzcdHaproxy - 1.5.19Copy the code(2) If you need to know the installation precautions, you can check the software description of HAProxy
less README
Copy the code(3) Install the dependency packages required for compilation
yum install gcc gcc-c++ pcre pcre-devel zlib zlib-devel openssl openssl-devel
Copy the code(4) Compilation
make TARGET=linux2628 ARCH=x86_64 USE_PCRE=1 USE_OPENSSL=1 USE_ZLIB=1 PREFIX=/usr/local/haproxy
Copy the codeIf the kernel version is higher than 2.6.28, set it to linux2628. Run the # uname -r command to check the Linux OS kernel version. ARCH specifies the system architecture
(5) Create installation directory /usr/local/haproxy
mkdir /usr/local/haproxy
Copy the code(6) Perform installation
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
Copy the code(7) Create a configuration file directory
mkdir -p /usr/local/haproxy/conf
mkdir -p /etc/haproxy/
Copy the code(8) Copy the configuration file from the configuration file template and add the configuration file soft connection
cp /usr/local/ SRC/haproxy 1.5.19 / examples/haproxy CFG/usr /local/haproxy/conf/
ln -s /usr/local/haproxy/conf/haproxy.cfg /etc/haproxy/haproxy.cfg
Copy the code(9) Copy the error page and add the directory soft connection (HTTP mode optional)
cp -r /usr/local/ SRC/haproxy - 1.5.19 / examples/errorfiles/usr /local/haproxy/
ln -s /usr/local/haproxy/errorfiles /etc/haproxy/errorfiles
Copy the code(10) Copy the startup file and grant the executable permission
cp /usr/local/ SRC/haproxy 1.5.19 / examples/haproxy init/etc/rc. D/init. D/haproxy chmod + x/etc/rc. D/init. D/haproxyCopy the code(11) Add haProxy command script soft connection
ln -s /usr/local/haproxy/sbin/haproxy /usr/sbin
Copy the code(12) Set HAProxy startup
chkconfig --add haproxy
chkconfig haproxy on
Copy the codeBuild Mycat load balancing cluster
Here, we use HAProxy to build Mycat load balancing cluster. HAProxy supports TCP (Layer 4) and HTTP (Layer 7) proxy applications. In this lesson, we will use HAProxy to make MyCat load balancing proxy using TCP mode. In Layer 4 mode, HAProxy only forwards bidirectional traffic between the client and server. HAProxy is simple to configure and has a very good server health check function. When the back-end server of its proxy fails, HAProxy automatically removes the server and adds the server after the fault recovers.
(1) Modify the haproxy. CFG configuration file
Specific parameters that can refer to the official configuration document/usr/local/haproxy/doc/haproxy/configuration. TXT or making connections: cbonte. Making. IO/haproxy – dco…
vi /usr/local/haproxy/conf/haproxy.cfg
Copy the codeThe content of the edited file is as follows:
Parameters in the ## global configuration are process-level parameters, usually dependent on the operating system on which they are running
global
log 127.0.0.1 local0 info A maximum of two syslog servers can be defined
### local0 is the logging device, corresponding to the configuration in /etc/rsyslog.conf, which is reclaimed by default at the info log level
# log 127.0.0.1 local1 info
chroot /usr/share/haproxy Change the working directory of HAProxy to the specified directory and perform this before giving up permissions
### chroot() can improve the security level of haProxy
group haproxy The gid is the same as the user group name
user haproxy ## is the same as the uid, but the username is used here
daemon Set haProxy to run as a daemon
nbproc 1 Number of haProxy processes started
Haproxy can only be used in daemon mode; Start 1 process by default,
The multi-process mode is generally only used in fields where a single process can open only a few file descriptors
maxconn 4096 Set the maximum number of concurrent connections accepted by each haproxy process.
### this is equivalent to the command line option "-n", "ulimit-n" automatically calculates the result of the formal reference from the parameter setting
Pid file (default path: /var/run/haproxy.pid)
node liuyazhuang135 ## Define the name of the current node, which is used when multiple HAproxy processes share the same IP address in HA scenarios
description liuyazhuang135 ## Description of the current instance
## Defaults: Used to provide default parameters for all other configuration segments, which can be reset by the next "defaults"
defaults
log global Inherit the definition of log from global
mode http (TCP: layer 4, HTTP: layer 7, health: status check, will only return OK)
TCP: The instance runs in pure TCP mode. A full-duplex connection will be established between the client and server.
#### And does not perform any type of check on Layer 7 packets, which is the default mode
### HTTP: Instances run in HTTP mode, client requests are deeply parsed before being forwarded to back-end servers,
#### All requests that are not compatible with RFC mode will be rejected
### health: The instance runs in health mode and only responds to inbound requests with an "OK" message and closes the connection,
#### and no log information is recorded. This mode is used for monitoring status detection requests of corresponding external components
option httplog
retries 3
option redispatch If the server corresponding to the serverId fails, force redirect to another healthy server
maxconn 2000 Maximum number of concurrent connections for the front-end (default: 2000)
### This cannot be used for the Backend section. For large sites, you can increase this value as much as possible to allow HAProxy to manage the connection queue.
### to avoid being unable to answer user requests. Of course, this maximum cannot exceed the definition in the "global" section.
Also, keep in mind that HaProxy maintains two buffers per connection, each 8KB in size,
Plus other data, each connection will take up approximately 17KB of RAM space, which means that after proper optimization,
Maintain 40,000-50,000 concurrent connections with 1GB of available RAM space.
### If you specify a large value, extreme scenarios may end up taking up more space than the current host memory available,
### This may have unexpected results, so set it to an acceptable value of wise absolute, which defaults to 2000
timeout connect 5000ms Connection timeout (default: us,ms,s,m,h,d)
timeout client 50000ms The client timed out
timeout server 50000ms The server timed out
## HAProxy status information statistics page
listen admin_stats
bind: 48800## Bind port
stats uri /admin-status ## Statistics page
stats auth admin:admin ## Set the user and password for statistics page authentication. If you want to set more than one user, write another line
mode http
option httplog ## Enable logging HTTP requests
## LISTEN: Used to define a complete proxy by associating the "front end" with the "back end", usually only useful for TCP traffic
listen mycat_servers
bind: 3307## Bind port
mode tcp
option tcplog Log TCP requests
option tcpka Whether to allow sending Keepalive messages to the server and clientOption HTTPCHK OPTIONS * HTTP/1.1\r\nHost:\ WWWCheck backend service status
Port 48700 on the back-end server (port value configured via xinetd on the back-end server) sends OPTIONS requests
HAProxy will determine whether the backend service is available based on the returned content.
### 2XX and 3XX response codes indicate health status, other response codes or no response indicate server failure.
balance roundrobin Load balancing algorithms can be used in "defaults", "Listen", and "backend". The default is pollingServer mycat_01 192.168.209.133:8066 Check Port 48700 Inter 2000ms Rise 2 fall 3 weight 10 Server mycat_02 192.168.209.134:8066 Check Port 48700 Inter 2000ms Rise 2 Fall 3 weight 10Server
[: port]] [param*]
### Serser Declares a server on the backend, which can only be used in the Listen and Backend sections.
###
Specifies the internal name for this server, which will appear in logs and warning messages
### IPv4 address of this server. Resolvable hostname is also supported, but the hostname needs to be resolved to the responding IPv4 address at startup
### [:[port]] Specifies, optionally, the destination port for sending client connection requests to this server
### [param*] ### [param*] ### [param*] ### [param*]
#### weight: indicates the weight. The default value is 1. The maximum value is 256
#### backup: set as the standby server. This server cannot be enabled on other servers only in load balancing scenarios
#### check: Starts monitoring the status of this server, which can be set more finely with additional parameters
#### inter: Set the interval for monitoring status check. The unit is ms. The default value is 2000.
##### can also use Fastinter and downinter to optimize this event delay based on server-side themes
#### rise: Set the number of times that a server needs to be checked when switching from the offline state to the normal state. If this parameter is not set, the default value is 2.
#### fall: Sets the number of times that a server needs to be checked when switching from the normal state to the offline state. If this parameter is not set, the default value is 3.
#### cookie: Sets the cookie value for the specified server. The value specified here will be checked when the request is inbound.
##### The server first selected for this value will be selected by subsequent requests for persistent connection functionality
#### maxconn: Specifies the maximum number of concurrent connections accepted by this server. If the number of connections sent to this server is higher than the value specified here,
##### it will be placed on the request queue waiting for other connections to be released
Copy the codeNote: In multi-node deployment, the values of node and description must be adjusted accordingly.
(2) Perform the following configuration according to the HAProxy configuration file
- Add haProxy user groups and users
groupadd haproxy
useradd -g haproxy haproxy
Copy the code- Create a path for chroot to run
mkdir /usr/share/haproxy
Copy the code- Ports 3306 and 48800 are enabled on 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
Copy the codeRestarting the Firewall
service iptables restart
Copy the code(3) Enable the HAProxy log recording function of Rsyslog
By default, haProxy does not record logs. To record logs, you need to configure syslog, which is the Rsyslog service in Linux. The syslog server can be used as a log monitoring center on a network. Rsyslog is an open source tool that is widely used in Linux systems to forward or receive log messages over TCP/UDP. Installing and configuring the Rsyslog service:
yum install rsyslog ## Install without install
vi /etc/rsyslog.conf
Copy the codeModLoadimudp: ModLoadimudp: ModLoadimudp: ModLoad IMudp: ModLoadimudp: ModLoad IMudp: UDPServerRun: 514 Support UDP UDPServerRun 514 ## Allows port 514 to receive logs using UDP and TCP, while rsyslog by default, $IncludeConfig /etc/rsyslog.d/*.conf Is not included. The result is as follows:
cd /etc/rsyslog.d/ ## The rsyslog service loads the configuration from this directory
touch haproxy.conf Create haProxy log profile
vi /etc/rsyslog.d/haproxy.conf
Copy the codeAdd the following:
local0.* /var/log/haproxy.log
&~
Copy the codeIf you do not add the “&~” configuration, in addition to /var/log/haproxy.log, it will be written to the /var/log/message file to save configuration and restart the rsyslog service
service rsyslog restart
Shutting down system logger: [ OK ]
Starting system logger: [ OK ]
Copy the codeAfter the HAProxy service is started, you can see the log in /var/log/haproxy.log
(4) Configure the IP packet forwarding function of the system kernel
vi /etc/sysctl.conf
net.ipv4.ip_forward = 1
Copy the codeMake the configuration take effect
sysctl -p
Copy the code(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
Copy the code(6) Use MySQL client to connect to Mycat through HAProxy
Mysql > show databases; mysql> lyz_schema1; mysql> show tables; mysql> select * from lyz_user;Copy the code
Write data test
insert into lyz_user (userName, pwd) values ('lyz6'666666');
Copy the code
The query result is as follows:
(7) Log in to the HAProxy status statistics page
http://192.168.209.135:48800/admin-status
The user name and password are admin, corresponding to the haproxy. CFG configuration segment
## HAProxy status information statistics page
listen admin_stats
bind: 48800## Bind port
stats uri /admin-status ## Statistics page
stats auth admin:admin ## Set the user and password for statistics page authentication. If you want to set more than one user, write another line
mode http
option httplog ## Enable logging HTTP requests
Copy the codeThe statistics page is as follows:
Ok, that’s enough for today. I’m Glacier. See you next time
Glacier Original PDF
Follow Glacier Technology wechat official account:
Reply to “Concurrent Programming” to receive the PDF of In-depth Understanding of High Concurrent Programming (1st edition).
Reply “concurrent source code” to get the “Concurrent programming core Knowledge (source code Analysis first edition)” PDF document.
Reply to “Limit Traffic” to get the PDF document “Distributed Solution under 100 million Traffic”.
Reply to “design patterns” to get the PDF of “simple Java23 design patterns”.
Reply “new Java8 features” obtain the Java8 new features tutorial PDF document.
Reply to “Distributed Storage” to receive the PDF of “Learn Distributed Storage Techniques from Glacier”.
Reply to “Nginx” to receive the PDF of Learn Nginx Technology from Glacier.
Reply to “Internet Engineering” to get the PDF of “Learn Internet Engineering techniques from Glacier”.
Big welfare
WeChat search the ice technology WeChat 】 the public, focus on the depth of programmers, daily reading of hard dry nuclear technology, the public, reply within [PDF] have I prepared a line companies interview data and my original super hardcore PDF technology document, and I prepared for you more than your resume template (update), I hope everyone can find the right job, Learning is a way of unhappy, sometimes laugh, come on. If you’ve worked your way into the company of your choice, don’t slack off. Career growth is like learning new technology. If lucky, we meet again in the river’s lake!
In addition, I open source each PDF, I will continue to update and maintain, thank you for your long-term support to glacier!!
Write in the last
If you think glacier wrote good, please search and pay attention to “glacier Technology” wechat public number, learn with glacier high concurrency, distributed, micro services, big data, Internet and cloud native technology, “glacier technology” wechat public number updated a large number of technical topics, each technical article is full of dry goods! Many readers have read the articles on the wechat public account of “Glacier Technology” and succeeded in job-hopping to big factories. There are also many readers to achieve a technological leap, become the company’s technical backbone! If you also want to like them to improve their ability to achieve a leap in technical ability, into the big factory, promotion and salary, then pay attention to the “Glacier Technology” wechat public account, update the super core technology every day dry goods, so that you no longer confused about how to improve technical ability!