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

(4)
sraybansrayban
上一篇 2017-06-29
下一篇 2017-06-29

相关推荐

  • 第四天作业

    1 、创建 用户gentoo ,附加组为bin 和root ,默认shell为/bin/csh ,注释信息为"Gentoo Distribution" 1 useradd -G bin,root -s /bin/csh -c "Gentoo Distribut…

    Linux干货 2016-08-04
  • 马哥教育网络班21期-第五周课程练习

    第五周作业 1、显示/boot/grub/grub.conf中以至少一个空白字符开头的行; [root@zz ~]# egrep "^[[:space:]]+" /boot/grub/grub.conf 2、显示/etc/rc.d/rc.sysinit文件中以#开头,后面跟至少一个空白字符,而后又有…

    Linux干货 2016-07-29
  • Linux文本处理三剑客之一grep

            终于又到了一周一篇博客的日子 在这学习已经三周了,慢慢养成了写博客的好习惯,也慢慢的懂得了怎么写博客。这周给我印象最深刻的就是正则表达式,原本打算要写前几天所学的内容,但是昨天学到正则表达式让我有点懵,所以我今天会用一天的时间去给大家详细讲解正则表达式,争取让那些和我一样困解的人能够豁然开朗,也正是因为…

    2017-07-29
  • shell脚本之变量类型、算数运算符、条件测试

    一、bash中的变量类型     本地变量:仅对当前shell有效,对其子shell无效             变量赋值:name=value      &n…

    Linux干货 2016-08-15
  • shell脚本的一点补充

    脚本内容补充 数组 变量:存储单个元素的内存空间数组:存储多个元素的连续的内存空间,相当于多个变量的集合。 数组名和索引 索引:编号从0开始,属于数值索引注意:索引可支持使用自定义的格式,而不仅是数值格式,即为关联索引,bash4.0版本之后开始支持。bash的数组支持稀疏格式(索引不连续) 数组的使用 声明数组: declare -a ARRAY_NAME…

    Linux干货 2016-08-24
  • Linux运维学习历程-第二天-虚拟机的配置

    学习Linux我自己的感觉是可以按Linus的哲学思想来学习 比如一切皆文件,那我们首先可以记住一些重要的常见的路径和文件,并知道有什么作用,这样在初期学习时,我们要干什么时,知道在哪里找;   而命令我们可以每天记忆并练习一些,本身Linux的基本命令都是一下短小精悍的而且有些命令名本身就是英文单词,像date命令就是和系统时间有关的命令用来显示…

    Linux干货 2016-08-03