#想法:让haproxy进行mysql的用户登录检查,python检查主从状态,正常就不做操作,主从不正常就删除用户,从而让haproxy踢出不正常节点。
1.安装haproxy
tar -xzvf haproxy-1.4.21.tar.gz
cd haproxy-1.4.21
make TARGET=linux26 CPU=x86_64
make install
2.haproxy配置文件
# HAProxy configuration file
global
daemon
stats socket /var/run/haproxy.stat mode 600
log 127.0.0.1 local4
maxconn 40
ulimit-n 80013
pidfile /var/run/haproxy.pid
defaults
log global
mode http
contimeout 4000
clitimeout 42000
srvtimeout 43000
balance roundrobin
listen admin_stats *:80
mode http
stats uri /status
stats realm Global\ statistics
stats auth admin:admin
listen VIP_mysql *:8888
mode tcp
option mysql-check user haproxy#登陆mysql的用户
server server1 192.168.1.102:3306 weight 1 check inter 1s rise 2 fall 2
server server2 192.168.1.103:3306 weight 1 check inter 1s rise 2 fall 2
3.mysql数据库主从搭建
master mysql:
mysql>GRANT REPLICATION SLAVE ON *.* to 'eddy'@'%' identified by '123456';
编辑mysql配置文件
[mysqld]
log-bin=mysql-bin #[必须]启用二进制日志
server-id=1 #[必须]服务器唯一ID
重启mysql服务器
/etc/init.d/mysql restart
mysql> show master status;
+--------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------+----------+--------------+------------------+
| mysql.000007 | 287 | | |
+--------------+----------+--------------+------------------+
slave mysql:
编辑mysql配置文件
[mysqld]
log-bin=mysql-bin #[不是必须]启用二进制日志
server-id=2 #[必须]服务器唯一ID
重启mysql服务器
/etc/init.d/mysql restart
CHANGE MASTER TO
MASTER_HOST='master.mysql.com',#主ip地址
MASTER_USER='eddy',#主授权的用户
MASTER_PASSWORD='123456',#密码
MASTER_PORT=3306,#端口
MASTER_LOG_FILE='mysql.000007',#二进制日志文件
MASTER_LOG_POS=287,#记录点
MASTER_CONNECT_RETRY=10;#重试次数
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.102
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql.000007
Read_Master_Log_Pos: 106
Relay_Log_File: client1-relay-bin.000002
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
看到以下状态表示完成主从配置
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
4.主从检测脚本
#! /usr/bin/env python
# -*- coding: utf-8 -*
import commands
import time
i = 0
while i < 60:
(status1, result1) = commands.getstatusoutput("mysql -ueddy -poracle -e 'show slave status\G'|grep Slave_IO_Running:|awk '{print $2}'")
(status2, result2) = commands.getstatusoutput("mysql -ueddy -poracle -e 'show slave status\G'|grep Slave_SQL_Running:|awk '{print $2}'")
slave_io = result1
slave_sql = result2
if slave_io == 'Yes' and slave_sql == 'Yes':
pass
else:
if result3 == 'haproxy':
commands.getstatusoutput("mysql -ueddy -poracle -e 'drop user haproxy'")
i += 1
time.sleep(1)
crpntab -e
* * * * * /usr/local/haproxy/sbin/check_mysql_status.py
转载于:https://my.oschina.net/eddylinux/blog/535656