python mysql 双主_keepalived+mysql双主复制高可用方案
MySQL雙主復制，即互為Master-Slave(只有一個Master提供寫操作)，可以實現數據庫服務器的熱備，但是一個Master宕機后不能實現動態切換。而Keepalived通過虛擬IP，實現了雙主對外的統一接口以及自動檢查、失敗切換機制。聯合使用，可以實現MySQL數據庫的高可用方案。
實驗環境：
OS：centos 6.x x86_64系統
MySQL版本: ：mysql 5.6.22 ? 64 位
A: master ：192.168.79.3 3306
B: slave ：192.168.79.4 3306
操作系統時間一致更改：
# date -s "20150319 15:51:42"
# hwclock --systohc
AB數據庫安裝及主從配置
新建mysql用戶，用戶組，創建 datadir
# groupadd mysql
# useradd mysql -g mysql -s /sbin/nologin -d /opt/mysql
解壓mysql二進制安裝包，對解壓后的mysql目錄加一個符號連接
# cd /opt/mysql
# tar -xvzf mysql-5.6.22-linux-glibc2.5-x86_64.tar.gz
# cd /usr/local/
# ln -s /opt/mysql/mysql-5.6.22-linux-glibc2.5-x86_64 mysql
# ls
bin etc games include lib lib64 libexec mysql sbin share src
創建mysql的安裝目錄并修改權限
# mkdir -p /data/mysql/mysql_3306/{data,logs,tmp}
chown -R mysql:mysql /data/mysql/
chown -R mysql:mysql /usr/local/mysql/
加環境變量,解決找不到mysql命令的問題
echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
source /etc/profile
創建修改mysql配置文件
修改my.cnf
server_id
log_slave_updates
gtid-mode= off
binlog-ignore-db=mysql
replicate-ignore-db=mysql
auto_increment_offset= 1;
auto_increment_increment= 2;
ps：主從庫的server_id不要一樣。 如果從庫是整個拷貝的uuid也不要一樣，data下的auto.cnf. ?從庫：auto_increment_offset= 2;auto_increment_increment= 2;
初始化系統數據文件 ，在basedir下初始化
# ./scripts/mysql_install_db --user=mysql --defaults-file=/etc/my.cnf --datadir=/data/mysql/mysql_3306/data
安裝mysql后的善后工作
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
/etc/init.d/mysql start
delete from mysql.user where user!='root' or host!='localhost';
truncate mysql.db;
drop database test;
grant all privileges on *.* to 'liyt'@'%' identified by 'liyt';
grant replication slave, replication client on *.* to 'repl'@'%' identified by 'replslave';
flush privileges;
reset master;
啟動 slave mysql服務
A:
>show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mybinlog.000001 | 120 | | | |
+-----------------+----------+--------------+------------------+-------------------+
B:
change master to master_host='192.168.79.3', master_port=3306 ,master_user='repl', master_password='replslave', master_log_file='mybinlog.000001', master_log_pos=120;
B:
>show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mybinlog.000001 | 120 | | | |
+-----------------+----------+--------------+------------------+-------------------+
A:
change master to master_host='192.168.79.4', master_port=3306 ,master_user='repl', master_password='replslave', master_log_file='mybinlog.000001', master_log_pos=120;
測試雙主同步：創建新的數據庫和表看是否能同步
keepalived安裝及配置
GRANT REPLICATION CLIENT ON *.* TO 'monitor'@'%' IDENTIFIED BY 'monitor';
#yum install keepalived
#yum install MySQL-python
配置A:
[root@taotao ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id MYSQL_3
}
vrrp_script chk_mysql {
script "/etc/keepalived/checkMySQL.py -h 192.168.79.3 -P 3306"
interval 60
}
vrrp_instance VI_MYSQL1 {
state BACKUP
nopreempt
interface eth0
virtual_router_id 82
priority 100
advert_int 5
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
chk_mysql
}
virtual_ipaddress {
192.168.79.66
}
}
這里state不配置MASTER，是期望在MASTER宕機后再恢復時，不主動將MASTER狀態搶過來，避免MySQL服務的波動。
由于不存在使用lvs進行負載均衡，不需要配置虛擬服務器virtual server，下同。
配置B:
[root@taotao ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id MYSQL_4
}
vrrp_script chk_mysql {
script "/etc/keepalived/checkMySQL.py -h 192.168.79.4 -P 3306"
interval 60
}
vrrp_instance VI_MYSQL1 {
state BACKUP
nopreempt
interface eth0
virtual_router_id 82
priority 90
advert_int 5
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
chk_mysql
}
virtual_ipaddress {
192.168.79.66
}
}
checkMySQL.pyAB完全一樣代碼如下：
# cat /etc/keepalived/checkMySQL.py
#!/usr/bin/python
#coding: utf-8#
import sys
import os
importgetoptimport MySQLdb
import logging
dbhost='localhost'dbport=3306dbuser='monitor'dbpassword='monitor'def checkMySQL():
global dbhost
global dbport
global dbuser
global dbpassword
shortargs='h:P:'opts, args=getopt.getopt(sys.argv[1:],shortargs)for opt, value inopts:if opt=='-h':
dbhost=valueelif opt=='-P':
dbport=value
#print"host : %s, port: %d, user: %s, password: %s" % (dbhost, int(dbport), dbuser, dbpassword)
db=instanceMySQL(dbhost, dbport, dbuser, dbpassword)
st=db.ishaveMySQL()#if ( db.connect() != 0 ):# return1#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= passwddef ishaveMySQL(self):
cmd="ps -ef | egrep -i \"mysqld\" | grep %s | egrep -iv \"mysqld_safe\" | grep -v grep | wc -l" %self.dbport
mysqldNum=os.popen(cmd).read()
cmd="netstat -tunlp | grep \":%s\" | wc -l" %self.dbport
mysqlPortNum=os.popen(cmd).read()
#print mysqldNum, mysqlPortNumif ( int(mysqldNum) <= 0):
print"error"return1
if ( int(mysqldNum) > 0 and mysqlPortNum <= 0):
return1return0def connect(self):
# print"in db conn"# print"host : %s, port: %d, user: %s, password: %s" %(self.dbhost, self.dbport, self.dbuser, self.dbpassword)
try:
self.conn=MySQLdb.connect(host="%s"%self.dbhost, port=self.dbport,user="%s"%dbuser, passwd="%s"%self.dbpassword)
except Exception, e:
# print"Error"print e
return1return0def disconnect(self):if(self.conn):
self.conn.close()
self.conn=Noneif __name__== "__main__":
st=checkMySQL()
sys.exit(st)
View Code
A B啟用keepalived# /etc/init.d/keepalived startshell>chkconfig –level 2345 keepalived onps：先啟動，你內心期望成為對外服務的機器，確認VIP綁定到那臺機器上，然后在啟動另外一臺的keepalived
觀察配置A的日志：
[root@taotao ~]# /etc/init.d/keepalived start
[root@taotao ~]# tail -f /var/log/messages
Mar 20 05:09:01 taotao Keepalived[56536]:?Starting Keepalived?v1.2.13 (10/15,2014)
Mar 20 05:09:01 taotao Keepalived[56538]:Starting Healthcheck child?process, pid=56539
Mar 20 05:09:01 taotao Keepalived[56538]: Starting VRRP child process, pid=56540
Mar 20 05:09:01 taotao Keepalived_healthcheckers[56539]: Netlink reflector reports IP 192.168.79.3 added
Mar 20 05:09:01 taotao Keepalived_healthcheckers[56539]: Netlink reflector reports IP fe80::20c:29ff:fed8:3944 added
Mar 20 05:09:01 taotao Keepalived_healthcheckers[56539]: Registering Kernel netlink reflector
Mar 20 05:09:01 taotao Keepalived_healthcheckers[56539]: Registering Kernel netlink command channel
Mar 20 05:09:01 taotao Keepalived_vrrp[56540]: Netlink reflector reports IP 192.168.79.3 added
Mar 20 05:09:01 taotao Keepalived_vrrp[56540]: Netlink reflector reports IP fe80::20c:29ff:fed8:3944 added
Mar 20 05:09:01 taotao Keepalived_vrrp[56540]: Registering Kernel netlink reflector
Mar 20 05:09:01 taotao Keepalived_vrrp[56540]: Registering Kernel netlink command channel
Mar 20 05:09:01 taotao Keepalived_vrrp[56540]: Registering gratuitous ARP shared channel
Mar 20 05:09:01 taotao Keepalived_healthcheckers[56539]: Opening file '/etc/keepalived/keepalived.conf'.
Mar 20 05:09:01 taotao Keepalived_healthcheckers[56539]: Configuration is using : 6251 Bytes
Mar 20 05:09:01 taotao Keepalived_vrrp[56540]: Opening file '/etc/keepalived/keepalived.conf'.
Mar 20 05:09:01 taotao Keepalived_vrrp[56540]: Configuration is using : 63953 Bytes
Mar 20 05:09:01 taotao Keepalived_vrrp[56540]: Using LinkWatch kernel netlink reflector...
Mar 20 05:09:01 taotao Keepalived_healthcheckers[56539]: Using LinkWatch kernel netlink reflector...
Mar 20 05:09:01 taotao Keepalived_vrrp[56540]: VRRP_Instance(VI_MYSQL1)Entering BACKUP STATE
Mar 20 05:09:01 taotao Keepalived_vrrp[56540]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]
Mar 20 05:09:01 taotao Keepalived_vrrp[56540]: VRRP_Script(chk_mysql) succeeded
Mar 20 05:09:16 taotao Keepalived_vrrp[56540]: VRRP_Instance(VI_MYSQL1)?Transition to MASTER STATE
Mar 20 05:09:21 taotao Keepalived_vrrp[56540]: VRRP_Instance(VI_MYSQL1) Entering MASTER STATE
Mar 20 05:09:21 taotao Keepalived_vrrp[56540]: VRRP_Instance(VI_MYSQL1) setting protocol VIPs.
Mar 20 05:09:21 taotao Keepalived_vrrp[56540]: VRRP_Instance(VI_MYSQL1) Sending gratuitous ARPs on?eth0 for 192.168.79.66
Mar 20 05:09:21 taotao Keepalived_healthcheckers[56539]: Netlink reflector reports IP 192.168.79.66 added
Mar 20 05:09:26 taotao Keepalived_vrrp[56540]: VRRP_Instance(VI_MYSQL1) Sending gratuitous ARPs on eth0 for 192.168.79.66
ps：包含了三個進程： keepalived healthcheck vrrp協議 三個進程
測試：
1)關閉A服務的mysql服務觀察A和B的日志
A：
[root@taotao ~]# /etc/init.d/mysqld stop
Shutting down MySQL....[ OK ]
[root@taotao ~]# tail -f /var/log/messages
Mar 20 05:36:04 taotao Keepalived_vrrp[56540]:?VRRP_Script(chk_mysql) failed
Mar 20 05:36:07 taotao Keepalived_vrrp[56540]: VRRP_Instance(VI_MYSQL1) Entering FAULT STATE
Mar 20 05:36:07 taotao Keepalived_vrrp[56540]: VRRP_Instance(VI_MYSQL1)?removing protocol VIPs.
Mar 20 05:36:07 taotao Keepalived_vrrp[56540]: VRRP_Instance(VI_MYSQL1) Now in FAULT state
Mar 20 05:36:07 taotao Keepalived_healthcheckers[56539]: Netlink reflector reports?IP 192.168.79.66 removed
B：
[root@taotao ~]# tail -f /var/log/messages
Mar 20 05:36:03 slave1 Keepalived_vrrp[48658]: VRRP_Instance(VI_MYSQL1)Transition to MASTER STATE
Mar 20 05:36:08 slave1 Keepalived_vrrp[48658]: VRRP_Instance(VI_MYSQL1)Entering MASTER STATE
Mar 20 05:36:08 slave1 Keepalived_vrrp[48658]: VRRP_Instance(VI_MYSQL1)?setting protocol VIPs.
Mar 20 05:36:08 slave1 Keepalived_vrrp[48658]: VRRP_Instance(VI_MYSQL1)?Sending gratuitous ARPson eth0 for 192.168.79.66
Mar 20 05:36:08 slave1 Keepalived_healthcheckers[48657]: Netlink reflector reports IP192.168.79.66 added
Mar 20 05:36:13 slave1 Keepalived_vrrp[48658]: VRRP_Instance(VI_MYSQL1) Sending gratuitous ARPs on eth0 for 192.168.79.66
2)開啟A服務的mysql服務觀察A日志
A：
[root@taotao ~]# /etc/init.d/mysqld start
Starting MySQL...........................[ OK ]
[root@taotao ~]# tail -f /var/log/messages
Mar 20 05:42:01 taotao Keepalived_vrrp[56540]: VRRP_Script(chk_mysql) succeeded
Mar 20 05:42:03 taotao Keepalived_vrrp[56540]: VRRP_Instance(VI_MYSQL1)Entering BACKUP STATE
通過vip連接mysql
# mysql -h 192.168.79.66 -P 3306 -uliyt -p
總結
以上是生活随笔為你收集整理的python mysql 双主_keepalived+mysql双主复制高可用方案的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： ImageMagick/Graphics
- 下一篇： T03 手机键盘