搭建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 效果是一樣的；