keepalived+mysql双主复制高可用方案-低调大师

keepalived+mysql双主复制高可用方案

2018-11-29 757

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
import getopt
import MySQLdb
import logging



dbhost='localhost'
dbport=3306
dbuser='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 in opts:
                if opt=='-h':
                        dbhost=value
                elif 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 ):
        #       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="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, mysqlPortNum
                if ( int(mysqldNum) <= 0):
                        print "error"
                        return 1
                if ( int(mysqldNum) > 0 and  mysqlPortNum <= 0):
                        return 1
                return 0

        def 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
                        return 1
                return 0
        def disconnect(self):
                if (self.conn):
                        self.conn.close()
                        self.conn = None


if __name__== "__main__":
        st=checkMySQL()
        sys.exit(st)
复制代码

A B启用keepalived

# /etc/init.d/keepalived start

shell>chkconfig –level 2345 keepalived on

ps：先启动，你内心期望成为对外服务的机器，确认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 ARPs on eth0 for 192.168.79.66
Mar 20 05:36:08 slave1 Keepalived_healthcheckers[48657]: Netlink reflector reports IP 192.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

微信关注我们

原文链接：https://blog.roncoo.com/article/127116

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

常见的web应用漏洞有哪些？？？

总结下web 常见的几个漏洞 1.SQL注入 2.XSS跨站点脚本 3.缓冲区溢出 4.cookies修改 5.上传漏洞 6.命令行注入 1 sql 漏洞 SQL注入攻击是黑客对数据库进行攻击的常用手段之一。随着B/S模式应用开发的发展，使用这种模式编写应用程序的程序员也越来越多。但是由于程序员的水平及经验也参差不齐，相当大一部分程序员在编写代码的时候，没有对用户输入数据的合法性进行判断，使应用程序存在安全隐患。用户可以提交一段数据库查询代码，根据程序返回的结果，获得某些他想得知的数据，这就是所谓的SQL Injection，即SQL注入。 2 XSS 夸站点漏洞 XSS是一种经常出现在web应用中的计算机安全漏洞，它允许恶意web用户将代码植入到提供给其它用户使用的页面中。比如这些代码包括HTML代码和客户端脚本。攻击者利用XSS漏洞旁路掉访问控制——例如同源策略(same origin policy)。这种类型的漏洞由于被黑客用来编写危害性更大的网络钓鱼(Phishing)攻击而变得广为人知。对于跨站脚本攻击，黑客界共识是：跨站脚本攻击是新型的“缓冲区溢出攻击“，而JavaScr...

2018-11-29

812

参考视频内容：http://www.roncoo.com/course/view/fb3050a5b34b42f39ccad83ebebc89c1 一、软件版本操作系统：CentOS-6.5-x86_64 zabbix版本：3.0.3 二、zabbix标准： 1、主机命名规范： BJ-monitor-h-zabbix-01 **命名规范是，我们看一台主机能够知道主机位于哪里：BJ；做什么的：monitor；虚拟化还是实体机：h；跑什么服务:zabbix 第几台:01 2、一台主机直接关联多个模板:不用创建多个分组（可以结合CMDB平台） Memcached ：192.168.10.100 Php Linux:192.168.10.100 多个模板:192.168.10.100 linux,memcached,port,partion 3、自定义key开启：Include=/usr/local/zabbix/etc/zabbix_agentd.conf.d/*.conf 方便识别服务名和增删key。也方便结合saltstack等批量工具。 4、使用模板继承（linux...

2018-11-29

885

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。