Shulou(Shulou.com)06/01 Report--

This article mainly introduces the handout of haproxy+mycat+mysql architecture, hoping to supplement and update some knowledge for you. If you have any other questions you need to know, you can continue to follow my updated article in the industry information.

Description:

Architectural solution:

Mysql (master/slave)-- active / standby in gtid mode (pos is also available)

Mycat (master/slave)-- xinted plug-in time active / standby mode

Haproxy (master/slave)-keepalived automatic switching

-- keepalived and haproxy must be installed on the same machine. Keepalived is responsible for preempting vip (virtual ip) for the CVM. After preempting the vip, access to the host can be accessed through the original ip (10.118.242.214) or directly through vip (10.118.242. 212).

The keepalived on 10.118.242.214 will also preempt vip, and there is a priority when preempting vip, which is decided by the configuration of keepalived.conf (the higher the value of priority 150 # is, the higher the priority is, the different configuration of this value on 10.118.242.216 to 120Timmaster is different from that on slave). But generally speaking, the keepalived service on which host starts first will preempt vip, even if it is slave, as long as it is started first.

-- haproxy is responsible for distributing requests for vip to mycat. It acts as a load balancer. At the same time, haproxy can also detect whether the mycat is alive. Haproxy will only forward the request to the surviving mycat.

-if one CVM (keepalived+haproxy server) goes down, the keepalived on the other will immediately preempt the vip and take over the service.

If a mycat server goes down, haporxy forwarding will not be forwarded to the down mycat, so mycat is still available.

After MyCAT, there is a connection to the MYSQL database, which here is: Amax B replication mode.

Haproxy + keepalived is installed on the same host.

Mycat+xinted is installed on the same host.

Version: OS: redhat6.4 x86 64bit

Mysql: 5.7.15

Mycat: server-1.5.1-RELEASE

Haproxy: haproxy-1.6.2.tar.gz

Keepalived: keepalived-1.2.16.tar.gz

Before installation, it is best to turn off iptables and selinux

One: configure the local yum source and install the corresponding dependency package (here I directly apply the install oracle dependency package to load, generally oracle uses the package, other software will include. )

Two: install MYSQL data

(before version 5.7, you can use the system root to enable it. After version 5.7, it is restricted and its security is taken into account.)

Note: 1when mysql_install_db MySQL starts to install, it will be transferred from scripts before 5.6to the bin directory.

2. You need to enter the initial password to enter the mysql library for the first time, and the path is under / home/mysql/:

[mysql@martinli02 mysql-5.7.10] $cat / home/mysql/.mysql_secret

# Password set for user 'root@localhost' at 2015-12-27 23:17:57

.hyMlk?! b6M!

3. After logging in, you must change the password mysql > set password=password ('xxxxx').

Mysql > flush privileges

Three: MYSQL master/slave configuration

-reference MYSQL synchronous replication

-set parameters and automatically submit autocommit = 1 (otherwise, no data is displayed when mycat is inserted)

Four: Mycat installation configuration

-- here, I mainly use mycat to achieve read-write separation, without setting up sub-database and sub-table, so

Only involves the modification of schema.xml and server.xml files.

-- the Mysql primary node uses the innodb engine and the standby node uses the myisam engine.

# useradd-m-u 1201-g mysql mycat

# tar-xvf Mycat-server-1.5.1-RELEASE-20160816173057-linux.tar.gz-C / usr/local/

# cd / usr/local/mycat

[root@mysql01 mycat] # ls

Bin catlet conf lib logs version.txt-after unzipping it, just the first five directories

[root@mysql01 mycat] # cd conf/

[root@mysql01 conf] # ls

Autopartition-long.txtlog4j.xml rule.xmlserver.xml

Cacheservice.properties myid.properties schema.xml wrapper.conf

Dnindex.properties partition-hash-int.txt sequence_conf.properties zk-create.yaml

Ehcache.xmlpartition-range-mod.txt sequence_db_conf.properties

Index_to_charset.propertiesrouter.xml sequence_time_conf.properties

-- configure schema.xml parameters--

[root@mysql01 conf] # cat schema.xml

Select user ()

-- server.xml configuration parameters-

[root@mysql01 conf] # cat server.xml

Druidparser

8066

9066

four

sixty-four

8192

one

one

four

163840

1200

Utf8

nine hundred

Mysql123

Mongodb,redis,test,scout

Query123

True

Mongodb,redis,test,scout

Here, the latest version can support ciphertext without plaintext, here for convenience.

Fifth, configure xinetd services and parameters:

You need to add a script to detect port 48700 on mycat 1 Mycat 2, which requires xinetd. Xinetd is the basic service of the linux system, which is mainly used to monitor the survival of MYCAT.

Yum install xinted*

View the configuration file under: / etc/xinted.d/ and / usr/local/bin/xinted:

-- the first installation method:

I install it here in the second way:

Tar-xvf xinetd-2.3.15.tar.gz-C / usr/local/

[root@mysql01 ~] # cd / usr/local/xinetd-2.3.15/

[root@mysql01 xinetd-2.3.15] # ls

Aclocal.m4 config.guess config.log configure COPYRIGHT libs README xinetd.spec AUDIT config.h config.status configure.in INSTALL Makefile TODO xinetd.spec.in CHANGELOG config.h.in config.sub contrib install-sh Makefile.in xinetd

[root@mysql01 xinetd-2.3.15] # cd contrib/

[root@mysql01 contrib] # ls

Empty.conf mycat_monitor.sh xinetd xinetd.conf xinetd.d

[root@mysql01 contrib] # tail-12 xinetd.conf

Groups = yes

Umask = 002

# Generally, banners are not used. This sets up their global defaults

#

# banner =

# banner_fail =

# banner_success =

}

Includedir / usr/local/xinetd-2.3.15/contrib/xinetd.d-- mainly modified here

-/ usr/local/xinetd-2.3.15/contrib/xinetd.d/mycat_status-- Service parameter path

Mycat_status this configuration is mainly used to monitor whether the mycat is alive.

[root@mysql01 contrib] # cat xinetd.d/mycat_status

Service mycat_status

{

Flags = REUSE

Socket_type = stream

Port = 48700

Wait = no

User = root

Server = / usr/local/xinetd-2.3.15/contrib/mycat_monitor.sh

Log_on_failure + = USERID

Disable = no

}

[root@mycat01 xinetd.d] # pwd

/ usr/local/xinetd-2.3.15/contrib/xinetd.d

[root@mycat01 xinetd.d] # cd.. /

[root@mycat01 contrib] # ls

Empty.conf mycat_monitor.sh xinetd xinetd.conf xinetd.d

-- mycat_monitor.sh this configuration is mainly a script used to listen for the survival of mycat.

[root@mysql01 contrib] # cat mycat_monitor.sh

#! / 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

4. / add mycat_status service to etc/services-the startup is not valid until it is added.

Join the mycat_status service

Cd / etc

Vi services

Add the following at the end:

Mycat_status 48700/tcp # mycat_status

Save

Restart the xinetd service

Service xinetd restart

#

[root@mycat01 xinetd] # pwd

/ usr/local/xinetd-2.3.15/xinetd

-- xinetd starts and stops scripts

-- start the script:

#! / bin/bash

/ usr/local/xinetd-2.3.15/xinetd/xinetd-f / usr/local/xinetd-2.3.15/contrib/xinetd.d/mycat_status &

-- stop the script:

#! / bin/bash

Ps-ef | grep xinetd | grep-v grep | awk'{print $2}'| xargs kill-s 9

-

Start mysql, start xinted, start mycat

-

Sixth, install keepalived

-- openssl installation:

Openssl must be installed before keepalive, otherwise there may be an error when installing keepalive, and keepalived depends on openssl.

Yum install gcc* cc*

Install openssl-devel

Yum install openssl-devel-y # if you cannot download and install yum, please modify the yum configuration file

-- installed through the rpm package:

Mkdir-pv / usr/local/openssl/

Tar-xvf xxxxx.gz-C / usr/local/

Cd / usr/local/openssl-1.0.1l/

. / config-- prefix=/usr/local/openssl/

Make

Make test

Make install

Ln-s / usr/local/openssl / usr/local/ssl

-openssl configuration-

Vi / etc/ld.so.conf

At the end of the / etc/ld.so.conf file, add the following:

/ usr/local/openssl/lib

Vi / etc/profile

The contents are as follows:

Export OPENSSL=/usr/local/openssl/bin

Export PATH=$PATH:$OPENSSL

Execute the following statement for the environment variable to take effect:

Source / etc/profile

Test:

Cd / usr/local/openssl/

[root@host-10-118,220,136 openssl] # ls

Bin include lib ssl

[root@host-10-118,220,136 openssl] # cd bin/

[root@host-10-118,220,136 bin] # ls

C_rehash openssl

[root@host-10-118,220,136 bin] # ldd openssl

Linux-vdso.so.1 = > (0x00007fff0ba32000)

Libdl.so.2 = > / lib64/libdl.so.2 (0x0000003883200000)

Libc.so.6 = > / lib64/libc.so.6 (0x0000003882e00000)

/ lib64/ld-linux-x86-64.so.2 (0x0000003882a00000)

[root@host-10-118,220,136 bin] # pwd

/ usr/local/openssl/bin

[root@host-10-118,220,136 bin] # which openssl

/ usr/bin/openssl

[root@host-10-118,220,136 bin] # openssl version

OpenSSL 1.0.1l 15 Jan 2015

[root@host-10-118,220,136 bin] #

-here, as long as the keepalive editor does not make a mistake during the installation, it is fine. Whether openssl is really successful or not.

-- keepalived installation:

Tar-xvf keepalived-1.2.16.tar.gz-C / usr/local/

Cd / usr/local/keepalived-1.2.16/

. / configure

Make & & make install

If the keepalived installation reports an error:

Error installing keepalive: configure: error: No SO_MARK

Add a parameter below to disable fwmark

[root@gw keepalived-1.2.16] #. / configure-disable-fwmark

[root@host-10-118,220,136 etc] # cp / usr/local/etc/rc.d/init.d/keepalived / etc/rc.d/init.d/

[root@host-10-118,220,136 etc] # cp / usr/local/etc/sysconfig/keepalived / etc/sysconfig/

[root@host-10-118,220,136 etc] # cp / usr/local/keepalived-1.2.16/keepalived/etc/keepalived/keepalived.conf / etc/keepalived/

[root@host-10-118,220,136 etc] # cp / usr/local/sbin/keepalived / usr/sbin/

-- keepalived configuration parameters:

[root@mysql03 keepalived] # pwd

/ usr/local/keepalived-1.2.16/keepalived/etc/keepalived

[root@mysql03 keepalived] # cat keepalived.conf-- Master configuration

! Configuration File for keepalived

Vrrp_script chk_http_port

{

Script "/ etc/keepalived/scripts/check_haproxy.sh"

Interval 2

Weight 2

}

Vrrp_instance VI_1

{

State MASTER (main for MASTER and standby for BACKUP)

Interface eth0

Virtual_router_id 51

Priority 150

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1234

}

Track_script {

Chk_http_port

}

Virtual_ipaddress

{

10.118.242.212 dev eth0 scope global-Virtual

10.118.242.211 dev eth0 scope global

}

Notify_master / etc/keepalived/scripts/haproxy_master.sh

Notify_backup / etc/keepalived/scripts/haproxy_backup.sh

Notify_fault / etc/keepalived/scripts/haproxy_fault.sh

Notify_stop / etc/keepalived/scripts/haproxy_stop.sh

}

[root@mysql03 keepalived] # cat keepalived.conf-standby configuration

! Configuration File for keepalived

Vrrp_script chk_http_port

{

Script "/ etc/keepalived/scripts/check_haproxy.sh"

Interval 2

Weight 2

}

Vrrp_instance VI_1

{

State BACKUP (main for MASTER and standby for BACKUP)

Interface eth0-- # Network interface for providing services

Virtual_router_id51-- # VRRP group name. The settings of both nodes must be the same to indicate that each node belongs to the same VRRP group.

Priority 120

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1234

}

Track_script {

Chk_http_port-- # call the script check_haproxy.sh to check whether haproxy is alive

}

Virtual_ipaddress

{

10.118.242.212 dev eth0 scope global-this ip must be consistent with the vip we set in the lvs client

10.118.242.211 dev eth0 scope global

}

Notify_master / etc/keepalived/scripts/haproxy_master.sh

Notify_backup / etc/keepalived/scripts/haproxy_backup.sh

Notify_fault / etc/keepalived/scripts/haproxy_fault.sh

Notify_stop / etc/keepalived/scripts/haproxy_stop.sh

}

-

1. Virtual_router_id 51 represents a cluster group. If there is another cluster in the same network segment, please use a different group number to distinguish it. Such as 52, 53, etc.

2. The eth3 in interface eth3 and 172.17.210.103 dev eth3 scope global refers to the network card. If it is multiple network cards, there may be eth0,eth3,eth4. You can use the ifconfig command to check to make sure that eth0 is the address of the network card that exists on the machine. If some servers have only one network card, but are artificially changed from eth0 to eth3, you will not be able to find it if you write eth0.

-

[root@mysql03 scripts] # cat check_haproxy.sh

#! / bin/bash

STARTHAPROXY= "/ usr/local/haproxy/sbin/haproxy-f / usr/local/haproxy/haproxy.cfg"

STOPKEEPALIVED= "/ etc/init.d/keepalived stop"

LOGFILE= "/ usr/local/keepalived/var/log/keepalived-haproxy-state.log"

Echo "[check_haproxy status]" > > $LOGFILE

A = `ps-C haproxy-- no-header | wc- l`

Echo "[check_haproxy status]" > > $LOGFILE

Date > > $LOGFILE

If [$A-eq 0]; then

Echo $STARTHAPROXY > > $LOGFILE

$STARTHAPROXY > > $LOGFILE 2 > & 1

Sleep5

Fi

If [`ps-C haproxy-- no-header | wc- l`-eq0]; then

Exit 0

Else

Exit 1

Fi

Bold check_haproxy.sh

Vi / etc/keepalived/scripts/check_haproxy.sh

Meaning of the script: if no haproxy process exists, start haproxy and stop keepalived

Check_haproxy.sh

[root@mysql03 scripts] # cat haproxy_master.sh (same as master / slave)

#! / bin/bash

STARTHAPROXY= `/ usr/local/haproxy/sbin/haproxy-f/usr/local/haproxy/ haproxy.cfg`

STOPHAPROXY= `ps-ef | grep sbin/haproxy | grep-vgrep | awk' {print $2}'| xargskill-s 9`

LOGFILE= "/ usr/local/keepalived/var/log/keepalived-haproxy-state.log"

Echo "[master]" > > $LOGFILE

Date > > $LOGFILE

Echo "Being master...." > > $LOGFILE 2 > & 1

Echo "stop haproxy...." > > $LOGFILE 2 > & 1

$STOPHAPROXY > > $LOGFILE 2 > & 1

Echo "start haproxy...." > > $LOGFILE 2 > & 1

$STARTHAPROXY > > $LOGFILE 2 > & 1

Echo "haproxy stared..." > > $LOGFILE

[root@mysql03 scripts] # cat haproxy_fault.sh (same as master / slave)

#! / bin/bash

LOGFILE=/usr/local/keepalived/var/log/keepalived-haproxy-state.log

Echo "[fault]" > > $LOGFILE

Date > > $LOGFILE

[root@mysql03 scripts] # cat haproxy_stop.sh (same as master / slave)

#! / bin/bash

LOGFILE=/usr/local/keepalived/var/log/keepalived-haproxy-state.log

Echo "[stop]" > > $LOGFILE

Date > > $LOGFILE

-- start keepalived:

[root@mysql03 keepalived-1.2.16] # / etc/init.d/keepalived restart

Stopping keepalived: [FAILED]

Starting keepalived: [OK]

-- Test verification

[root@mysql03 scripts] # ip a

1: lo: mtu 16436 qdisc noqueue state UNKNOWN

Link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

Inet 127.0.0.1/8 scope host lo

Inet6:: 1/128 scope host

Valid_lft forever preferred_lft forever

2: eth0: mtu 1500 qdisc mq state UNKNOWN qlen 1000

Link/ether 00:50:56:aa:54:86 brd ff:ff:ff:ff:ff:ff

Inet 10.118.242.214/24 brd 10.118.242.255 scope global eth0

Inet 10.118.242.212/32 scope global eth0

Inet6 fe80::250:56ff:feaa:5486/64 scope link

Valid_lft forever preferred_lft forever

Seven: haproxy installation configuration

# tar xf haproxy-1.4.25.tar.gz-C / usr/local/

# cd haproxy-1.4.25

#-check the version number (os-kener)

# make TARGET=linux26 PREFIX=/usr/local/haproxy install

Note: fill in the TARGET according to the kernel version of the local operating system.

Create configuration file directory, log directory, and write configuration files according to requirements

# mkdir / usr/local/haproxy/ {conf,logs}

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

Configuration parameters are the same as master and slave:

[root@mysql03 conf] # cat haproxy.cfg

Global

Log 127.0.0.1 local0

Maxconn 4096

Chroot / usr/local/haproxy

User mysql

Group mysql

Daemon

Quiet

Nbproc 1

Pidfile / usr/local/haproxy/logs/haproxy.pid

Defaults

Log global

Option dontlognull

Retries 3

Option httplog

Option redispatch

Maxconn 3000

Contimeout 50000

Clitimeout 50000

Srvtimeout 50000

Listen admin_stats

Bind 10.118.242.212:48800

Stats uri / admin-status

Stats auth admin:admin

Mode http

Option httplog

Listen allmycat_service

Bind 10.118.242.212:8096

Mode tcp

Option tcplog

Option httpchk OPTION * HTTP/1.1\ r\ nHost:\ www

Balance roundrobin

Server mycat_209 10.118.242.209:8066 check port 48700 inter 5s rise 2 fall 3

Server mycat_210 10.118.242.210:8066 check port 48700 inter 5s rise 2 fall 3

Srvtimeout 20000

Listen allmycat_admin

Bind 10.118.242.212:8097

Mode tcp

Option tcplog

Option httpchk OPTIONS * HTTP/1.1\ r\ nHost:\ www

Balance roundrobin

Server mycat_209 10.118.242.209:9066 check port 48700 inter 5s rise 2 fall 3

Server mycat_210 10.118.242.210:9066 check port 48700 inter 5s rise 2 fall 3

Srvtimeout 20000

-

Bind 10.118.242.212 8096 corresponds to port mycat 8066

Bind 10.118.242.212 8097 corresponds to port mycat 9066. (need to be consistent with mycat)

Server is mycat with two hosts

-

Configure haproxy logging method:

Yum install rsyslog-y

-- by default, haproxy does not log. In order to record logs, you need to configure the syslog module and rsyslogd service under Linux.

-- install rsyslog first

Cd / etc/rsyslog.d/-if you don't have this directory, you need to create a new one.

[root@mysql03 conf] # cd / etc/rsyslog.d/

[root@mysql03 rsyslog.d] # ls

Haproxy.conf

[root@mysql03 rsyslog.d] # cat haproxy.conf-add content

$ModLoad imudp

$UDPServerRun 514

Local0.* / var/log/haproxy.log

[root@mysql03 rsyslog.d] # pwd

/ etc/rsyslog.d-- add

Add the following to the line # RULES # above:

# Include all config files in / etc/rsyslog.d/

$IncludeConfig / etc/rsyslog.d/*.conf

# RULES #

Add the following under local7.* / var/log/boot.log (the effect of the addition is as follows):

Local0.* / var/log/haproxy.log

Start the rsyslog service

Have a harporxy.log in / var/log/ right away.

[root@mysql03 rsyslog.d] # cd / var/log/

[root@mysql03 log] # ll haproxy.log

-rw-. 1 root root 5160 Nov 15 16:49 haproxy.log

Start haproxy:

# / usr/local/haproxy/sbin/haproxy-c / usr/local/haproxy/conf/haproxy.cfg

-

[root@mysql03 log] # netstat-nltp

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name

Tcp 0 0 0.0.0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 01. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 15. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0

Tcp 0 0 0.0.0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0

Tcp 0 0 127.0.0.1 631 0.0.0.0 * LISTEN 1545/cupsd

Tcp 0 0 127.0.0.1 25 0.0.0. 0 LISTEN 1752/master

Tcp 0 0 127.0.0.1 6010 0.0.0.0 * LISTEN 6278/sshd

Tcp 0 0 127.0.0.1 6011 0.0.0.0 * LISTEN 6387/sshd

Tcp 0 0 127.0.0.1 6012 0.0.0.0 * LISTEN 24216/sshd

Tcp 0 0 10.118.242.212 8096 0.0.0.0 * LISTEN 16997/haproxy

Tcp 00 10.118.242.212 48800 0.0.0.0 * LISTEN 16997/haproxy

Tcp 0 0 10.118.242.212 8097 0.0.0.0 * LISTEN 16997/haproxy

Tcp 0 0 0.0.0.0 56457 0.0.0.0 LISTEN 1399/rpc.statd

Debug through the local MYSQL tool:

MYSQL IP + port can be connected

The MYCAT IP + port can also be connected

The HAPORXY (VIP) + port can also be connected

Through the url address, you can see the haproxy scheduling interface: http://10.118.242.212:48800

Account password admin/admin

-THE END-

-error handling:

[root@mysql01 sbin] # / usr/local/haproxy-1.6.2/sbin/haproxy-f / usr/local/haproxy-1.6.2/conf/haproxy.cfg &

[1] 6783

[root@mysql01 sbin] # [ALERT] 293 parsing 121421 (6783): parsing [/ usr/local/haproxy-1.6.2/conf/haproxy.cfg:21]: 'listen' cannot handle unexpected argument' 10.118.242.211 virtual 48800.

[ALERT] 293 Compact 121421 (6783): parsing [/ usr/local/haproxy-1.6.2/conf/haproxy.cfg:21]: please use the 'bind' keyword for listening addresses.

[ALERT] 293 Compact 121421 (6783): parsing [/ usr/local/haproxy-1.6.2/conf/haproxy.cfg:26]: 'listen' cannot handle unexpected argument' 10.118.242.211 usr/local/haproxy-1.6.2/conf/haproxy.cfg:26 8098.

[ALERT] 293 Compact 121421 (6783): parsing [/ usr/local/haproxy-1.6.2/conf/haproxy.cfg:26]: please use the 'bind' keyword for listening addresses.

[ALERT] 293 Compact 121421 (6783): Error (s) found in configuration file: / usr/local/haproxy-1.6.2/conf/haproxy.cfg

[ALERT] 293 Compact 121421 (6783): Fatal errors found in configuration.

-- here: [/ usr/local/haproxy-1.6.2/conf/haproxy.cfg:21]: 'listen' cannot handle unexpected argument' 10.118.242.211.

It is because of the misspelling:

-

[root@mysql02 sbin] # / usr/local/haproxy-1.6.2/sbin/haproxy-f / usr/local/haproxy-1.6.2/conf/haproxy.cfg

[ALERT] 293 ALERT 151834 (20139): Starting proxy admin_stats: cannot bind socket [10.118.242.211 purl 48800]

[ALERT] 293gamble 151834 (20139): Starting proxy allmycat_service: cannot bind socket [10.118.242.211 purl 8098]

-- the / etc/sysctl.conf parameter needs to be modified when the error is reported here.

[ALERT] 164Compact 110030 (11606): Starting proxy linuxyw.com: cannot bind socket

In fact, this problem is because your haproxy does not get VIP, and your configuration file is bound to the VIP address, so it will prompt the above error. Of course, you should also make sure that your haproxy server has done hearbeat or keepalived, bind VIP, or it will not be highly available.

Solution:

Modify kernel parameters: / etc/sysctl.conf

Net.ipv4.ip_nonlocal_bind=1

Save the results to make them effective

Sysctl-p

Or use echo to enter, provided that there is no such parameter in the sysctl.conf file:

Echo 'net.ipv4.ip_nonlocal_bind = 1' > > / etc/sysctl.conf

Net.ipv4.ip_nonlocal_bind=1 means that when starting haproxy, you are allowed to ignore the existence of VIP

To each machine.

For i in IP1 IP2 IP3 IP4

Do scp / etc/hosts $i:/etc

Done

-MYSQL MY.CNF configuration parameters-

[client]

User=martin

Password=88888888

[mysqld]

# basic settings#

Server-id= 11

Port = 3308

User = mysql

Bind_address = 10.118.242.209

Autocommit = 1

Character_set_server=utf8mb4

Skip_name_resolve = 1

Max_connections = 800,

Max_connect_errors = 1000

Datadir = / data/mysql_data

Transaction_isolation = READ-COMMITTED

Explicit_defaults_for_timestamp = 1

Join_buffer_size = 134217728

Tmp_table_size = 67108864

Tmpdir = / tmp

Max_allowed_packet = 16777216

Sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"

Interactive_timeout = 1800

Wait_timeout = 1800

Read_buffer_size = 16777216

Read_rnd_buffer_size = 33554432

Sort_buffer_size = 33554432

# log settings#

Log_error = error.log

Slow_query_log = 1

Slow_query_log_file = slow.log

Log_queries_not_using_indexes = 1

Log_slow_admin_statements = 1

Log_slow_slave_statements = 1

Log_throttle_queries_not_using_indexes = 10

Expire_logs_days = 90

Long_query_time = 2

Min_examined_row_limit = 100

# replication settings#

Master_info_repository = TABLE

Relay_log_info_repository = TABLE

Log_bin = bin.log

Sync_binlog = 1

Gtid_mode = on

Enforce_gtid_consistency = 1

Log_slave_updates

Binlog_format = row

Relay_log = relay.log

Relay_log_recovery = 1

Binlog_gtid_simple_recovery = 1

Slave_skip_errors = ddl_exist_errors

# innodb settings#

Innodb_page_size = 8192

Innodb_buffer_pool_size = 6G

Innodb_buffer_pool_instances = 8

Innodb_buffer_pool_load_at_startup = 1

Innodb_buffer_pool_dump_at_shutdown = 1

Innodb_lru_scan_depth = 2000

Innodb_lock_wait_timeout = 5

Innodb_io_capacity = 4000

Innodb_io_capacity_max = 8000

Innodb_flush_method = O_DIRECT

Innodb_file_format = Barracuda

Innodb_file_format_max = Barracuda

Innodb_log_group_home_dir = / redolog/

Innodb_undo_directory = / undolog/

Innodb_undo_logs = 128,

Innodb_undo_tablespaces = 3

Innodb_flush_neighbors = 1

Innodb_log_file_size = 4G

Innodb_log_buffer_size = 16777216

Innodb_purge_threads = 4

Innodb_large_prefix = 1

Innodb_thread_concurrency = 64

Innodb_print_all_deadlocks = 1

Innodb_strict_mode = 1

Innodb_sort_buffer_size = 67108864

# semi sync replication settings#

Plugin_dir=/usr/local/mysql/lib/plugin

Plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

Loose_rpl_semi_sync_master_enabled = 1

Loose_rpl_semi_sync_slave_enabled = 1

Loose_rpl_semi_sync_master_timeout = 5000

[mysqld-5.7]

Innodb_buffer_pool_dump_pct = 40

Innodb_page_cleaners = 4

Innodb_undo_log_truncate = 1

Innodb_max_undo_log_size = 2G

Innodb_purge_rseg_truncate_frequency = 128,

Log_timestamps=system

Transaction_write_set_extraction=MURMUR32

Show_compatibility_56=on

After reading the above handouts on haproxy+mycat+mysql architecture, I hope it can bring some help to you in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.