mysql 负载均衡 haproxy_haproxy实战之haproxy实现mysql负载均衡

haproxy实战之haproxy实现mysql负载均衡

实验目的

haproxy + mysql实现负载均衡

1.准备机器,做好时间同步,域名主机名解析

192.168.42.151 [node2 haproxy]

192.168.42.152 [node3 mariadb]

192.168.42.153 [node4 mariadb]

2.node3,node4上安装mariadb

(1).安装mariadb

yum install mariadb-server -y

(2).更改基本配置

vim /etc/my.cnf.d/server.cnf

[mysqld]

skip_name_resolve=1

log-bin=mysql-bin

innodb_file_per_table = 1

(3).启动mariadb

systemctl start mariadb

(4).更改密码

mysqladmin -uroot -p password "root"

(5).登录mysql

mysql -u root -p

(6).添加外部访问账号

MariaDB [(none)]> grant all privileges on *.* to 'magedu'@'192.168.42.%' identified by '123456';

MariaDB [(none)]> flush privileges;

(7)登录测试一下

mysql -u magedu -h 192.168.42.155 -p

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 7

Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

3.安装haproxy

node1: (1).haproxy的安装

yum install haproxy -y

(2).配置haproxy 配置文件如下:

#---------------------------------------------------------------------

# Example configuration for a possible web application. See the

# full configuration options online.

#

# http://haproxy.1wt.eu/download/1.4/doc/configuration.txt

#

#---------------------------------------------------------------------

#---------------------------------------------------------------------

# Global settings

#---------------------------------------------------------------------

global

# to have these messages end up in /var/log/haproxy.log you will

# need to:

#

# 1) configure syslog to accept network log events. This is done

# by adding the '-r' option to the SYSLOGD_OPTIONS in

# /etc/sysconfig/syslog

#

# 2) configure local2 events to go to the /var/log/haproxy.log

# file. A line like the following can be added to

# /etc/sysconfig/syslog

#

# local2.* /var/log/haproxy.log

#

log 127.0.0.1 local2

chroot /var/lib/haproxy

pidfile /var/run/haproxy.pid

maxconn 4000

user haproxy

group haproxy

daemon

# turn on stats unix socket

stats socket /var/lib/haproxy/stats

#---------------------------------------------------------------------

# common defaults that all the 'listen' and 'backend' sections will

# use if not designated in their block

#---------------------------------------------------------------------

defaults

mode tcp

log global

option httplog

option dontlognull

option http-server-close

option forwardfor except 127.0.0.0/8

option redispatch

retries 3

timeout http-request 10s

timeout queue 1m

timeout connect 10s

timeout client 1m

timeout server 1m

timeout http-keep-alive 10s

timeout check 10s

maxconn 3000

#---------------------------------------------------------------------

# main frontend which proxys to the backends

#---------------------------------------------------------------------

frontend mysql

bind *:3306

log global

mode tcp

default_backend mysqlsrvs

backend mysqlsrvs

balance roundrobin

server mysql1 192.168.42.152:3306 check

server mysql2 192.168.42.153:3306 check

(3)启动haproxy

systemctl start haproxy

[root@node2 haproxy]# ss -tnl

State Recv-Q Send-Q Local Address:Port Peer Address:Port

LISTEN 0 128 *:22 *:*

LISTEN 0 100 127.0.0.1:25 *:*

LISTEN 0 3000 *:3306 *:*

LISTEN 0 128 :::22 :::*

LISTEN 0 100 ::1:25 :::*

(4)安装mariadb 客户端工具

yum install mariadb -y

(5).测试

mysql -u maged -h 192.168.42.151 -p

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

+--------------------+

此时是只有4个库,我们创建一个库,并退出

MariaDB [(none)]> create database leip;

Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| leip |

| mysql |

| performance_schema |

| test |

+--------------------+

5 rows in set (0.00 sec)

MariaDB [(none)]> exit;

Bye

(6).再一次连接测试

[root@node2 haproxy]# mysql -u magedu -h 192.168.42.151 -p

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 6

Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

+--------------------+

4 rows in set (0.00 sec)

MariaDB [(none)]> exit

Bye

从上面可以看到,此时我们的数据库是只有4个库了,因此我们可以判断,我们连接的是两个不同主机上的mariadb,也就是说负载均衡mysql成功了

原创文章，作者：srayban，如若转载，请注明出处：http://www.178linux.com/78829