Home

Database

Mysql Tutorial

使用HAProxy给MySQL slave群进行负载均衡和状态监控

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 04:42 PM

haproxy mysql use load conduct

一.安装haproxy haproxy机器 http://haproxy.1wt.deu 需翻墙 tar zxvf haproxy-1.4.25.tar.gzcd haproxy-1.4.25make TARGET=linux26make installmkdir -p /usr/local/haproxy/chown nobody:nobody /usr/local/haproxy/mkdir /etc/haproxy/cp examples/haprox

blog_haproxy

一.安装haproxy

haproxy机器

http://haproxy.1wt.deu

需翻墙

tar zxvf haproxy-1.4.25.tar.gz
cd haproxy-1.4.25
make TARGET=linux26
make install
mkdir -p /usr/local/haproxy/
chown nobody:nobody /usr/local/haproxy/
mkdir /etc/haproxy/
cp examples/haproxy.cfg /etc/haproxy/
cp examples/haproxy.init /etc/init.d/haproxy
chown root:root /etc/init.d/haproxy 
chmod 700 /etc/init.d/haproxy

Copy after login

修改haproxy启动脚本

/usr/sbin/$BASENAME
改成
/usr/local/sbin/$BASENAME

1	`sed -i -r 's\|/usr/sbin\|/usr/local/sbin\|' /etc/init.d/haproxy`

Copy after login

编辑配置文件
vi /etc/haproxy/haproxy.cfg

global
    #log 127.0.0.1  local0
    log 127.0.0.1   local3 info
    #log loghost    local0 info
    maxconn 4096
    chroot /usr/local/haproxy
    uid nobody
    gid nobody
    daemon
    debug
    #quiet
defaults
    log global
    mode    tcp
    #option httplog
    option  dontlognull
    retries 3
    option redispatch
    maxconn 2000
    contimeout  5000
    clitimeout  50000
    srvtimeout  50000
frontend mysql
    bind 192.168.0.107:3306
    maxconn 3000
    default_backend mysql_slave
backend mysql_slave  
    #cookie SERVERID rewrite
    mode tcp
    balance roundrobin 
    #balance    source 
    #balance    leastconn 
    contimeout 10s
    timeout check 2s
    option httpchk OPTIONS * HTTP/1.1\r\nHost:\ www
    server  mysql_192_168_0_104_3306 192.168.0.104:3306 weight 1 check port 9300 inter 5s rise 2 fall 3
    server  mysql_192_168_0_104_3307 192.168.0.104:3307 weight 1 check port 9301 inter 5s rise 2 fall 3
    #server mysql_192_168_0_106_3306 192.168.0.106:3306 weight 1 check port 9300 inter 5s rise 2 fall 3
listen  admin_status
    mode  http
    bind 192.168.0.107:8000
    option httplog
    log global
    stats enable
    stats refresh 30s
    stats hide-version
    stats realm Haproxy\ Statistics
    stats uri  /admin-status 
    stats auth  admin:123456 
    stats admin if TRUE

Copy after login

打开监控的iptables

1	`iptables -A INPUT -p tcp -m tcp -s 192.168.0.0/24 --dport 8000 -j ACCEPT`

Copy after login

添加自启动并启动haproxy服务

chkconfig &ndash;add haproxy 
chkconfig haproxy on
service haproxy start

Copy after login

被监控机上

我这里是单机双实例,所以有2个脚本,单机只需一个脚本和一个服务端口就行
编辑mysql检测3306脚本
vi /opt/shell/mysqlchk_status_3306.sh

#!/bin/bash 
# 
# /usr/local/bin/mysqlchk_status.sh 
# 
# This script checks if a mysql server is healthy running on localhost. It will 
# return: 
# 
# "HTTP/1.x 200 OK\r" (if mysql is running smoothly) 
# 
# &ndash; OR &ndash; 
# 
# "HTTP/1.x 503 Internal Server Error\r" (else) 
# 
MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USERNAME="mysqlcheck"
MYSQL_PASSWORD="paSSword"
MYSQL_PATH="/opt/mysql/bin/"
# 
# We perform a simple query that should return a few results 
#${MYSQL_PATH}mysql -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USERNAME} -p${MYSQL_PASSWORD} -e "show slave status\G;" >/tmp/rep${MYSQL_PORT}.txt
${MYSQL_PATH}mysql -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USERNAME} -p${MYSQL_PASSWORD} -e "show full processlist;" >/tmp/processlist${MYSQL_PORT}.txt
${MYSQL_PATH}mysql -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USERNAME} -p${MYSQL_PASSWORD} -e "show slave status\G;" >/tmp/rep${MYSQL_PORT}.txt
iostat=`grep "Slave_IO_Running" /tmp/rep${MYSQL_PORT}.txt  |awk &#39;{print $2}&#39;`            
sqlstat=`grep "Slave_SQL_Running" /tmp/rep${MYSQL_PORT}.txt |awk &#39;{print $2}&#39;`           
result=$(cat /tmp/processlist${MYSQL_PORT}.txt|wc -l)
echo iostat:$iostat and sqlstat:$sqlstat
# if slave_IO_Running and Slave_sql_Running ok,then return 200 code 
if [ "$result" -gt "3" ] && [ "$iostat" = "Yes" ] && [ "$sqlstat" = "Yes" ];
then
        # mysql is fine, return http 200 
        /bin/echo -e "HTTP/1.1 200 OK\r\n"
else
        # mysql is down, return http 503 
        /bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
fi

Copy after login

vi /opt/shell/mysqlchk_status_3307.sh

#!/bin/bash 
# 
# /usr/local/bin/mysqlchk_status.sh 
# 
# This script checks if a mysql server is healthy running on localhost. It will 
# return: 
# 
# "HTTP/1.x 200 OK\r" (if mysql is running smoothly) 
# 
# &ndash; OR &ndash; 
# 
# "HTTP/1.x 503 Internal Server Error\r" (else) 
# 
MYSQL_HOST="localhost"
MYSQL_PORT="3307"
MYSQL_USERNAME="mysqlcheck"
MYSQL_PASSWORD="paSSword"
MYSQL_PATH="/opt/mysql/bin/"
# 
# We perform a simple query that should return a few results 
#${MYSQL_PATH}mysql -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USERNAME} -p${MYSQL_PASSWORD} -e "show slave status\G;" >/tmp/rep${MYSQL_PORT}.txt
${MYSQL_PATH}mysql -S/data/mysql/mysql.sock -u${MYSQL_USERNAME} -p${MYSQL_PASSWORD} -e "show full processlist;" >/tmp/processlist${MYSQL_PORT}.txt
${MYSQL_PATH}mysql -S/data/mysql/mysql.sock -u${MYSQL_USERNAME} -p${MYSQL_PASSWORD} -e "show slave status\G;" >/tmp/rep${MYSQL_PORT}.txt
iostat=`grep "Slave_IO_Running" /tmp/rep${MYSQL_PORT}.txt  |awk &#39;{print $2}&#39;`            
sqlstat=`grep "Slave_SQL_Running" /tmp/rep${MYSQL_PORT}.txt |awk &#39;{print $2}&#39;`           
result=$(cat /tmp/processlist${MYSQL_PORT}.txt|wc -l)
#echo iostat:$iostat and sqlstat:$sqlstat
echo $result
# if slave_IO_Running and Slave_sql_Running ok,then return 200 code 
if [ "$result" -gt "3" ] && [ "$iostat" = "Yes" ] && [ "$sqlstat" = "Yes" ];
then
        # mysql is fine, return http 200 
        /bin/echo -e "HTTP/1.1 200 OK\r\n"
else
        # mysql is down, return http 503 
        /bin/echo -e "HTTP/1.1 503 Service Unavailable\r\n"
fi

Copy after login

chmod 775 /opt/shell/mysqlchk_status_3306.sh
chmod 775 /opt/shell/mysqlchk_status_3307.sh

在mysql slave另行建立一个具有process和slave_client权限的账号。

CREATE USER &#39;mysqlcheck&#39;@&#39;localhost&#39; IDENTIFIED BY &#39;PaSSword&#39;;
GRANT PROCESS , REPLICATION CLIENT ON * . * TO &#39;mysqlcheck&#39;@&#39;localhost&#39; IDENTIFIED BY &#39;PaSSword&#39; WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
flush privileges;

Copy after login

测试脚本
./mysqlchk_status_3306.sh

添加服务
绑定内网ip,运行于930端口,只开放给192.168.0内网
yum install -y xinetd
vim /etc/xinetd.d/mysql_status

service mysqlchk_status3306
{ 
        flags           = REUSE 
        socket_type     = stream 
        bind            = 192.168.0.104
        port            = 9300
        wait            = no 
        user            = nobody 
        server          = /opt/shell/mysqlchk_status_3306.sh
        log_type        = FILE /dev/null
        log_on_failure  += USERID 
        disable         = no 
        only_from       = 192.168.0.0/24 
}
service mysqlchk_status3307
{ 
        flags           = REUSE 
        socket_type     = stream 
        bind            = 192.168.0.104
        port            = 9301
        wait            = no 
        user            = nobody 
        server          = /opt/shell/mysqlchk_status_3307.sh
        log_type        = FILE /dev/null
        log_on_failure  += USERID 
        disable         = no 
        only_from       = 192.168.0.0/24 
}

Copy after login

bind和only_from的ip地址要有haproxy能请求的权限,使用drbd用0.0.0.0
user要用server脚本的执行权限
port端口要在/etc/service 中声明

chattr -i /etc/services
vi /etc/services

1 2	`mysqlchk_status3306 9300/tcp #haproxy mysql check` `mysqlchk_status3307 9301/tcp #haproxy mysql check`

Copy after login

services中的mysqlchk_status3306 要和xinetd.d中service名对应

打开iptables

1 2	`iptables -A INPUT -p tcp -m tcp -s 192.168.0.0/24 --dport 9300 -j ACCEPT` `iptables -A INPUT -p tcp -m tcp -s 192.168.0.0/24 --dport 9301 -j ACCEPT`

Copy after login

/etc/init.d/iptables save

添加自启动及启动服务
chkconfig xinetd –level 345 on
/etc/init.d/xinetd start

查看是否运行
netstat -lntp

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:9300                0.0.0.0:*                   LISTEN      4863/xinetd         
tcp        0      0 0.0.0.0:9301                0.0.0.0:*                   LISTEN      4863/xinetd

Copy after login

如果没有的话注意检测下bind地址及服务端口

在监控机运行测试
telnet 192.168.0.104 9300

Trying 192.168.0.104...
Connected to 192.168.0.104 (192.168.0.104).
Escape character is &#39;^]&#39;.
/opt/shell/mysqlchk_status_3306.sh: line 24: /tmp/processlist3306.txt: Permission denied
/opt/shell/mysqlchk_status_3306.sh: line 25: /tmp/rep3306.txt: Permission denied
HTTP/1.1 200 OK
Connection closed by foreign host.

Copy after login

之前用root运行过所以报错,在被监控机删除临时文件

1 2	`rm -f /tmp/processlist3306.txt /tmp/processlist3307.txt` `rm -f /tmp/rep3306.txt /tmp/rep3307.txt`

Copy after login

没有输出则需检查mysqlchk_status_3306.sh脚本执行权限

启动后/var/log/messages 中会有很多日志

Oct 23 14:37:00 lova xinetd[11057]: START: mysqlchk_status3306 pid=11464 from=192.168.0.22
Oct 23 14:37:00 lova xinetd[11057]: EXIT: mysqlchk_status3306 status=0 pid=11464 duration=0(sec)
Oct 23 14:37:05 lova xinetd[11057]: START: mysqlchk_status3306 pid=11494 from=192.168.0.22
Oct 23 14:37:05 lova xinetd[11057]: EXIT: mysqlchk_status3306 status=0 pid=11494 duration=0(sec)

Copy after login

在haproxy配置中将日志输出到黑洞
log_type = FILE /dev/null

查看监控

直接访问localhost是503

http://localhost/

503 Service Unavailable

No server is available to handle this request.

加上admin-status

http://localhost/admin-status

应用时需在slave mysql上的mysql添加通过haproxy的用户权限

haproxy的命令
/etc/init.d/haproxy
Usage: haproxy {start|stop|restart|reload|condrestart|status|check}

附
优化time_wait,防止端口耗尽
vi /etc/sysctl.conf

net.ipv4.ip_local_port_range = 1025 65000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 15
net.ipv4.tcp_max_tw_buckets = 35000

Copy after login

sysctl -p

使用nginx反向代理haprox后台

#省略
listen  admin_status
    mode  http
    bind 192.168.0.107:8000
    option httplog
    log global
    stats enable
    stats refresh 30s
    stats hide-version
    stats realm Haproxy\ Statistics
    #stats uri  /admin-status
    stats uri  /haproxy/
    #stats auth  admin:123456
    #stats admin if TRUE

Copy after login

nginx.conf

#省略
             location ~* ^/haproxy/
             {
          proxy_pass        http://192.168.0.107:8000;
          proxy_set_header  Host        $host;
          proxy_set_header  X-Real-IP   $remote_addr;
          proxy_set_header  X-Forwarded-For $proxy_add_x_forwarded_for;
          #proxy_set_header X-Forwarded-For $remote_addr;
          proxy_redirect    off;
             }
#省略

Copy after login

参考:

http://linux.die.net/man/5/xinetd.conf

http://adslroot.blogspot.com/2013/12/haproxy-mysql.html

http://sssslide.com/www.slideshare.net/Severalnines/haproxy-mysql-slides

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Assassin's Creed Shadows: Seashell Riddle Solution

4 weeks ago By DDD

What's New in Windows 11 KB5054979 & How to Fix Update Issues

3 weeks ago By DDD

Where to find the Crane Control Keycard in Atomfall

4 weeks ago By DDD

Roblox: Dead Rails - How To Complete Every Challenge

1 months ago By DDD

Atomfall guide: item locations, quest guides, and tips

1 months ago By DDD

Hot Tools

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Where is the login entrance for gmail email?

7708

Java Tutorial

1640

CakePHP Tutorial

1394

Laravel Tutorial

1288

PHP Tutorial

1232

Related knowledge

MySQL: An Introduction to the World's Most Popular Database Apr 12, 2025 am 12:18 AM

MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.

How to open phpmyadmin Apr 10, 2025 pm 10:51 PM

You can open phpMyAdmin through the following steps: 1. Log in to the website control panel; 2. Find and click the phpMyAdmin icon; 3. Enter MySQL credentials; 4. Click "Login".

Why Use MySQL? Benefits and Advantages Apr 12, 2025 am 12:17 AM

MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.

MySQL's Place: Databases and Programming Apr 13, 2025 am 12:18 AM

MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen

How to connect to the database of apache Apr 13, 2025 pm 01:03 PM

Apache connects to a database requires the following steps: Install the database driver. Configure the web.xml file to create a connection pool. Create a JDBC data source and specify the connection settings. Use the JDBC API to access the database from Java code, including getting connections, creating statements, binding parameters, executing queries or updates, and processing results.

How to start mysql by docker Apr 15, 2025 pm 12:09 PM

The process of starting MySQL in Docker consists of the following steps: Pull the MySQL image to create and start the container, set the root user password, and map the port verification connection Create the database and the user grants all permissions to the database

MySQL's Role: Databases in Web Applications Apr 17, 2025 am 12:23 AM

The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

How to install mysql in centos7 Apr 14, 2025 pm 08:30 PM

The key to installing MySQL elegantly is to add the official MySQL repository. The specific steps are as follows: Download the MySQL official GPG key to prevent phishing attacks. Add MySQL repository file: rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm Update yum repository cache: yum update installation MySQL: yum install mysql-server startup MySQL service: systemctl start mysqld set up booting

See all articles