搭建MySQL负载均衡及高可用环境

mysql 开启二进制日志，做到数据库之间的双向复制，保持数据一致性；

#提醒一下，实际生产环境没人会这样搭建的，仅通过这个实验来理解这些概念，希望阅者能有所获；

server1_ip=192.168.5.11
server2_ip=192.168.5.12
server_vip=192.168.5.111
================================================
#此处关闭了防火墙，开启则另配置相应规则
service iptables stop
chkconfig iptables off

ls /opt/soft/ #提取准备软件到此处
haproxy-1.4.20.tar.gz  keepalived-1.2.7.tar.gz

mkdir /opt/keepalived
mkdir /opt/scripts/ #此实验用到脚本目录
mkdir /opt/log/ #此实验日志文件夹
=================================================

! Configuration File for keepalived    #简单的头部，这里主要可以做邮件通知报警等的设置，此处就暂不配置了；  global_defs {          notificationd LVS_DEVEL  }    #预先定义一个脚本，方便后面调用，也可以定义多个，方便选择；  vrrp_script mysql_chk {          script "/opt/scripts/mysql_chke.sh"          interval 2  #脚本循环运行间隔          weight 2  #脚本的结果导致优先级变更，成功+2  }  #VRRP虚拟路由冗余协议配置  vrrp_instance VI_1 {   #VI_1 是自定义的名称；          state MASTER   #表明这是一台主设备，备用设备为 BACKUP          interface eth0          #指定VIP需要绑定的物理网卡          virtual_router_id 11  #VRID虚拟路由标识，也叫做分组名称，该组内的设备需要相同          priority 150   #定义这台设备的优先级 1-254；          advert_int 1   #生存检测时的组播信息发送间隔，组内一致            authentication {  #设置验证信息，组内一致                  auth_type PASS         #有PASS 和 AH 两种，常用 PASS                  auth_pass 111         #密码          }            virtual_ipaddress {  #指定VIP地址，组内一致，可以设置多个IP                  192.168.5.111/24          }            track_script {  #使用在这个域中使用预先定义的脚本                  mysql_chk          }     #此部分所载入的脚本为外部脚本，不需要预先定义；   #也可不添加,此实验在后半部分安装haproxy后，才有添加；          notify_master /opt/scripts/start_haproxy.sh #表示当切换到master状态时,要执行的脚本          notify_fault /opt/scripts/stop_keepalived.sh #故障时执行的脚本          notify_stop /opt/scripts/stop_haproxy.sh        #keepalived停止运行前运行的脚本  }

#!/bin/bash  #mysql_chke.sh  #  a=`ps -C mysqld --no-header | wc -l`  if [ $a -eq 0 ];then          sleep 3                  /sbin/service keepalived stop                  echo "`date +%c` stop keepalived" >> /opt/log/stop_keepalived.log          fi  fi

global   #全局系统配置          log 127.0.0.1 local0 info #定义日志级别[err warning info debug]       #local0 是日志设备，必须为24种标准syslog设备之一；          maxconn 4096  #最大链接数          uid 0   #运行该程序的用户,此处没有其它用户了，就用的 root          gid 0          daemon   #以后台形式运行          nbproc 1  #进程数量    defaults   #默认配置          mode tcp  #所处理的类别 http | tcp | health          option redispatch #serverId对应的服务器挂掉后,强制定向到其他健康的服务器          retries 3  #三次连接失败则服务器不用          timeout connect 5000 #连接超时          timeout client 50000 #客户端超时          timeout server 50000 #服务器超时          timeout check 2000 #心跳检测超时    listen proxy          bind 192.168.5.111:3306  #监听地址          mode tcp          balance roundrobin  #定义负载方式，此处为轮询            log 127.0.0.1 local0 info #定义日志类型   #rise 3三次正确表示服务器可用，fall 3表示3次失败表示服务器不可用          server db1 192.168.5.11:3306 check inter 1200 rise 2 fall 3 weight 1          server db2 192.168.5.12:3306 check inter 1200 rise 2 fall 3 weight 1    #服务器状态监控配置，可以通过定义的地址查看集群状态；  listen haproxy_stats          log 127.0.0.1 local0 info          mode http          bind 192.168.5.111:8888          option httplog          stats uri /status          stats realm Haproxy Manager          stats auth admin:admin  #设置监控地址的帐号与密码

#!bin/bash  #start_haproxy.sh    sleep 5  get=`ip addr  |grep 192.168.5.111 |wc -l`  echo $get >> /opt/log/start_haproxy.log    if [ $get -eq 1 ]  then          echo "`date +%c` success to get vip" >> /opt/log/start_haproxy.log          /opt/haproxy/sbin/haproxy -f /opt/haproxy/conf/haproxy.cfg  else          echo "`date +%c` can not get vip" >> /opt/log/start_haproxy.log  fi

#!bin/bash  #stop_haproxy.sh    pid=`pidof haproxy`  echo "`date +%c` stop haproxy" >> /opt/log/stop_haproxy.log  kill -9 $pid

#!bin/bash  #stop_keepalived.sh    pid=`pidof keepalived`  if [ $pid == "" ]  then          echo "`date +%c` no keepalived process id"  >> /opt/log/stop_keepalived.log  else          echo "`date +%c` will stop keepalived "  >> /opt/log/stop_keepalived.log          /etc/init.d/keepalived stop  fi

=======================================================

[mysql Manager Slave]

service mysqld restart

mysql> show master status; #查看mysql 的当前二进制日志文件
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mysqlbinlog.000001 |       98 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.28 sec)


#分别连接对方 mysql 日志，开始备份；记得替换 Master_Host 和日志名及MASTER_LOG_POS；
>CHANGE MASTER TO MASTER_HOST='192.168.5.12',master_port=3306,MASTER_USER='mywait',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysqlbinlog.000001',MASTER_LOG_POS=98;
>START SLAVE; #开始同步


mysql> SHOW SLAVE STATUS\G #查看mysql同步状态
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.5.11
                Master_User: mywait
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysqlbinlog.000001
        Read_Master_Log_Pos: 98
             Relay_Log_File: relay-log.000002
              Relay_Log_Pos: 237
      Relay_Master_Log_File: mysqlbinlog.000001
           Slave_IO_Running: Yes #表明获取对方日志文件的连接成功；
          Slave_SQL_Running: Yes #将获取到的日志转成sql语句回写本地数据库成功；
            Replicate_Do_DB: db1
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 98
            Relay_Log_Space: 237
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)


#主要就是看 Slave_IO_Running，Slave_SQL_Running
#这里错误的话，多数情况是 Slave_IO_Running 的问题，防火墙，用户权限，日志是否有启用等都需要判断；
=====================================================================
=====================================================================
#至此 mysql 双主互备搭建完成，我们的全部实验规划也全部完成；
#进入全面测试阶段；


#在 server1 的DB1里新建一张表，并赋值
>use db1;
>create table mywait(name char(9),phone char(14));
>insert into mywait(name,phone) values('wait',15888888888);

#新建一个具有 db1 权限的用户
grant all on db1.* to diaosi2@'%' identified by '123456';
>flush privileges;

#切换到 test 库,在mywait 表中插入一条数据；
use test;
insert into mywait(name,phone) values ('diaosi',15002839961);


#在 server2做验证；
>use db1;
>show tables;
>select * from mywait;

#此时数据与 server1 的会数据一致，表示mysql同步成功；

select user,host,password from mysql.user;
#在 server1 创建的用户也会被 server2 所同步；

#select * from test.mywait
#可以看到 server1上的 test 库并没有被同步；


#客户机上测试
mysql -udiaosi2 -p123456 -h 192.168.5.111 -e "select * from db1.mywait;"


#完毕；
=======================================================
#存在的问题；
在做mysql_chke 脚本时，本打算使用检查进程的形式判断服务是否启动；
`ps -C mysqld --no-header | wc -l`
当检查mysql 进程不存在的时候，先试着启动一次mysqld ,然后再检测，如果还是启动不了服务，再结束 keepalived ;
但是在使用 /etc/rc.d/init.d/mysqld start 启动后，出现一些问题；
比如mysql 配置文件错误或是注销用户等，mysql服务已然起不来了，虽然手动起不来服务；
但使用 ps -C mysqld 还是可以检查出一条mysqld 的进程来，这是什么情况没弄明白，希望能得到指点；


可优化项，haproxy 的功能很多，可以做成基于权重的分配方式，或是根据访问地址的，甚至可以使用 cookie 做判断；
大家都可以多测试一下；
有些地方是需要在两台服务器上同时配置的，大家看的时候多留意一下，测试过程中多看日志是最好的排错方式；


#ip a | ip addr | ip address 效果是一样的；