失眠网，内容丰富有趣，生活中的好帮手！
失眠网XML网站地图
全站
首页 失眠 养生 抑郁症 自闭症 抗癌 肿瘤 糖尿病 肾病 乙肝 精神分裂 减肥 育儿
推荐专题：
失眠网 > python mysql 双主_keepalived+mysql双主复制高可用方案

python mysql 双主_keepalived+mysql双主复制高可用方案

时间：2023-11-21 22:11:49

相关推荐

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 "0319 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配置文件

修改f

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下的f. 从库：auto_increment_offset= 2;auto_increment_increment= 2;

初始化系统数据文件 ，在basedir下初始化

# ./scripts/mysql_install_db --user=mysql --defaults-file=/etc/f --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 Keepalivedv1.2.13 (10/15,)

Mar 20 05:09:01 taotao Keepalived[56538]:Starting Healthcheck childprocess, 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 oneth0 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 reportsIP 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双主复制高可用方案》对你有帮助，请点赞、收藏，并留下你的观点哦！

本内容不代表本网观点和政治立场，如有侵犯你的权益请联系我们处理。
网友评论
网友评论仅供其表达个人看法，并不表明网站立场。
相关阅读
keepalived mysql双主架构图_基于MySQL双主的高可用解决方案理论及实践

keepalived mysql双主架构图_基于MySQL双主的高可用解决方案理论及实践

2020-11-28

mysql双主多从高可用配置_双主MySQL+keepalived高可用配置

mysql双主多从高可用配置_双主MySQL+keepalived高可用配置

2023-12-19

keepalived+mysql双主高可用配置

keepalived+mysql双主高可用配置

2022-12-15

mysql 双主 脑裂_MySQL 高可用性keepalived+mysql双主

mysql 双主 脑裂_MySQL 高可用性keepalived+mysql双主

2022-07-04

最近发布
失眠求解：神衰神衰配方曝光 黄连 肉桂 生地黄等草药为你打造安眠利器！

失眠求解：神衰神衰配方曝光 黄连 肉桂 生地黄等草药为你打造安眠利器！

2024-07-23

什么方法可以治失眠

什么方法可以治失眠

2024-07-23

上班族应该怎样克服失眠症状

上班族应该怎样克服失眠症状

2024-07-23

食物和失眠：你吃过可能催发失眠的食物吗？

食物和失眠：你吃过可能催发失眠的食物吗？

2024-07-23

合欢花的神奇功效：缓解失眠问题

合欢花的神奇功效：缓解失眠问题

2024-07-23

失眠之梦：枕头上的无尽流浪

失眠之梦：枕头上的无尽流浪

2024-07-23

失眠易毁五脏六腑！这个失眠秘方 用好了 整夜睡得香！

失眠易毁五脏六腑！这个失眠秘方 用好了 整夜睡得香！

2024-07-23

失眠多梦 肝火旺？这3种食物可能是肝脏灭火器 多留心了！

失眠多梦 肝火旺？这3种食物可能是肝脏灭火器 多留心了！

2024-07-23

患者 女 33岁 每于经期头晕头痛 心悸失眠 月经量少 色淡质稀 舌淡 苔薄白 脉

患者 女 33岁 每于经期头晕头痛 心悸失眠 月经量少 色淡质稀 舌淡 苔薄白 脉

2024-07-23

很有哲理的几句话 让人揪心 失眠时可以看一下

很有哲理的几句话 让人揪心 失眠时可以看一下

2024-07-23

推荐专题
最近总是头胀失眠 辣木籽泡水喝能治疗失眠吗 失眠飞行pianosolo 失眠鱿鱼卷 失眠贴治失眠的原理是什么 失眠头疼怎么按摩 吴彦祖爆失眠 失眠接诊流程 走完夜路失眠 晚上失眠眼袋好大 熏香对失眠 失眠吃猪脚党参当归好吗 银手镯有益失眠 dm治失眠 请问有什么药治失眠效果好
猜你喜欢：
突然便秘和失眠 小屋治失眠 失眠到天亮要不要睡 昨晚失眠很无助用词恰当吗 颈复康能治颈椎病失眠吗 仑伐替尼吃后会失眠吗 30岁的女人总失眠吗 为什么用眼过度会失眠呢 槟榔泡酒治疗失眠效果好吗 药浴泡脚可以治失眠吗 失眠的神明 失眠电台tvb 失眠图片大气 走读申请失眠 阶段性失眠一般有几天 彻夜失眠考试 venusss失眠海洋 允许失眠失眠吧 健身人群普遍失眠 脑神经炎会引起失眠吗 如何解决失眠的问题 为啥老失眠有睡意却失眠睡不着 失眠突发 喝茶为什么会失眠喝茶失眠的原因 小孩做梦失眠 失眠医案案例 气血充足的人会失眠吗 失眠能不能打篮球锻炼 扎针治失眠学位 34岁宝妈失眠健忘
展开

失眠网 免责声明© 2024 All Rights Reserved.

湘ICP备19021678号网站地图XML

© 2024 All Rights Reserved.

失眠网 免责声明