基于 MySQL 5.6 keepalived 的双主搭建

ycvodzf

基于MySQL 5.6 keepalived 的双主搭建

一、基于环境介绍、

　　①、环境说明:
　　

mysql [(none)]>select version();
+------------+
| version()  |
+------------+
| 5.6.22-log |
+------------+
1 row in set (0.00 sec)

②、IP说明　　
　　
　　Master1 -> 192.168.80.112
　　Master2 -> 192.168.80.113

Vip 192.168.80.100
　　


　　二、Keepalived的安装说明


　　①、
　　

rpm -ivh http://mirrors.ustc.edu.cn/fedora/epel/6/x86_64/epel-release-6-8.noarch.rpm
yum install keepalived
yum install MySQL-python

　　
　　



②、拷贝Keepalived配置文件Keepailved.conf到/etc/keepalived/keepalived.conf(注意脚本后 -h 127.0.0.1  是本机地址)　　
　　

[iyunv@Master1 ~]# cat /etc/keepalived/keepalived.conf
vrrp_script vs_mysql_113 {
script "/etc/keepalived/checkMySQL.py -h 127.0.0.1 -P 3306"
interval 60
}
vrrp_instance VI_113 {
state BACKUP
nopreempt
interface eth0
virtual_router_id 113     #只能从1-255
priority 100
advert_int 5
authentication {
auth_type PASS
auth_pass 0932313     #密码不要超过8位
}
track_script {
vs_mysql_113
}
virtual_ipaddress {
192.168.80.100
}
}
　　
　　



③、拷贝checkMySQL.py到/etc/keepalived/checkMySQL.py（checkMySQL.py这个脚本是用来确认本地的 MySQL
是否可以连接上去，相当于做了一个Connect ping .）　　


　　

[iyunv@Master1 ~]# cat /etc/keepalived/checkMySQL.py
#!/usr/bin/python
#coding: utf-8
# grant usage on *.* to 'monitor'@'%' identified by <span style=&quot;font-size:10px;&quot;>'m0n1tor';</span>
import sys
import os
import getopt
import MySQLdb
import logging

dbhost='localhost'
dbport=3306
dbuser='monitor'
dbpassword='m0n1tor'

def checkMySQL():
global dbhost
global dbport
global dbuser
global dbpassword
shortargs='h:P:'
opts, args=getopt.getopt(sys.argv[1:],shortargs)
for opt, value in opts:
if opt=='-h':
dbhost=value
elif opt=='-P':
dbport=value
#print &quot;host : %s, port: %d, user: %s, password: %s&quot; % (dbhost, int(dbport), dbuser, dbpassword)
db = instanceMySQL(dbhost, dbport, dbuser, dbpassword)
st = db.ishaveMySQL()
#if ( db.connect() != 0 ):
#       return 1
#db.disconnect()
return st
class instanceMySQL:
conn = None
def __init__(self, host=None,port=None, user=None, passwd=None):
self.dbhost= host
self.dbport = int(port)
self.dbuser = user
self.dbpassword = passwd
def ishaveMySQL(self):
cmd=&quot;ps -ef | egrep -i \&quot;mysqld\&quot; | grep %s | egrep -iv \&quot;mysqld_safe\&quot; | grep -v grep | wc -l&quot; % self.dbport
mysqldNum = os.popen(cmd).read()
cmd =&quot;netstat -tunlp | grep \&quot;:%s\&quot; | wc -l&quot; % self.dbport
mysqlPortNum= os.popen(cmd).read()
#print mysqldNum, mysqlPortNum
if ( int(mysqldNum) <= 0):
print &quot;error&quot;
return 1
if ( int(mysqldNum) > 0 and  mysqlPortNum <= 0):
return 1
return 0
def connect(self):
#       print &quot;in db conn&quot;
#               print &quot;host : %s, port: %d, user: %s, password: %s&quot; % (self.dbhost, self.dbport, self.dbuser, self.dbpassword)
try:
self.conn=MySQLdb.connect(host=&quot;%s&quot;%self.dbhost, port=self.dbport,user=&quot;%s&quot;%dbuser, passwd=&quot;%s&quot;%self.dbpassword)
except Exception, e:
#                       print &quot; Error&quot;
print e
return 1
return 0
def disconnect(self):
if (self.conn):
self.conn.close()
self.conn = None

if __name__== &quot;__main__&quot;:
st=checkMySQL()
sys.exit(st)


　　
　　三、Mysql设置
　　


　　①、首先Master1、Master2互为主主，配置主主复制的方法我在这就不赘述了，并且都将Mysql运行在3306端口上。
　　②、创建监控用户：
　　

mysql [(none)]>GRANT REPLICATION CLIENT ON *.* TO 'monitor'@'%' IDENTIFIED BY 'm0n1tor';
Query OK, 0 rows affected (0.00 sec)
mysql [(none)]>flush privileges;
Query OK, 0 rows affected (0.00 sec)

这里的监控用户是与上述checkMYSQL.py 中的账号是一致的。　　
　　


　　四、Master1、Master2一起启动mysql实例，并且都开启Keepalived服务。
　　

[iyunv@Master1 ~]# service keepalived start
正在启动 keepalived：                                      [确定]

查看vip有没有起来:　　
　　

[iyunv@Master1 ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether b0:83:fe:68:03:50 brd ff:ff:ff:ff:ff:ff
inet 192.168.80.112/24 brd 192.168.80.255 scope global eth0
inet 192.168.80.100/32 scope global eth0
inet6 fe80::b283:feff:fe68:350/64 scope link
valid_lft forever preferred_lft forever


[iyunv@Master2 ~]# ip addr            
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether b0:83:fe:69:6d:e5 brd ff:ff:ff:ff:ff:ff
inet 192.168.80.113/24 brd 192.168.80.255 scope global eth0
inet 192.168.80.100/32 scope global eth0
inet6 fe80::b283:feff:fe69:6de5/64 scope link
valid_lft forever preferred_lft forever


状态全部OK...　　
　　


　　五、常见问题
　　

/etc/keepalived/checkMySQL.py -h 192.168.80.113 -P 3306
Traceback (most recent call last):
File &quot;/etc/keepalived/checkMySQL.py&quot;, line 8, in <module>
import MySQLdb
File &quot;/usr/local/lib/python2.7/site-packages/MySQLdb/__init__.py&quot;, line 19, in <module>
import _mysql
ImportError: libmysqlclient.so.18: cannot open shared object file: No such file or directory


如果出现这种问题的处理办法：　　
　　
　　/etc/ld.so.conf.d/mysql-x86_64.conf 中添加

/usr/local/mysql/lib/

ldconfig   后即可。
　　



六、这是我见过的配置相对比较简单也比较高效的Keepalived&#43;双主结构，有兴趣的不妨试试，有问题欢迎留言。 　　



说明