MySQL主主互备结合keepalived实现高可用

页面导航:首页 > 数据库 > Mysql > MySQL主主互备结合keepalived实现高可用

MySQL主主互备结合keepalived实现高可用

来源: 作者: 时间:2016-02-21 09:46 【

试验环境:master:192 168 1 210(CentOS6 5)slave:192 168 1 211(CentOS6 5)VIP:192 168 1 208MySQL主主互备模式配置step1:Master服务的 etc my cnf配置[mysqld]basedir= usr local mysqldatadir= var

试验环境:

master:192.168.1.210(CentOS6.5)

slave:192.168.1.211(CentOS6.5)

VIP:192.168.1.208

 

MySQL主主互备模式配置

step1:Master服务的/etc/my.cnf配置

[mysqld]
basedir=/usr/local/mysql
datadir=/var/lib/mysql
port=3306
socket=/var/lib/mysql/mysql.sock

server_id=1
log-bin=mysql-bin
relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysql.%#指定不需要复制的库,mysql.%表示mysql库下的所有对象
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

step2:Slave服务的/etc/my.cnf配置

[mysqld]
basedir=/usr/local/mysql
datadir=/var/lib/mysql
port=3306
socket=/var/lib/mysql/mysql.sock

server_id=2
log-bin=mysql-bin
relay-log=mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

step3:重启两台主从mysql服务

[[email protected]~]#servicemysqldrestart
ShuttingdownMySQL..[OK]
StartingMySQL.[OK]
[[email protected]~]#servicemysqldrestart
ShuttingdownMySQL..[OK]
StartingMySQL.[OK]

 

step4:查看主从的log-bin日志状态

记录File和Position的值

[[email protected]~]#mysql-uroot-ppasswd-e'showmasterstatus'
Warning:Usingapasswordonthecommandlineinterfacecanbeinsecure.
+------------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+------------------+----------+--------------+------------------+-------------------+
|mysql-bin.000001|414||||
+------------------+----------+--------------+------------------+-------------------+
[[email protected]~]#mysql-uroot-ppasswd-e'showmasterstatus'
Warning:Usingapasswordonthecommandlineinterfacecanbeinsecure.
+------------------+----------+--------------+------------------+-------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
+------------------+----------+--------------+------------------+-------------------+
|mysql-bin.000001|414||||
+------------------+----------+--------------+------------------+-------------------+

 

step5:创建主从同步replication用户

1、master

mysql>grantreplicationslaveon*.*to'replication'@'192.168.1.%'identifiedby'replication';
mysql>flushprivileges;
mysql>changemasterto
->master_host='192.168.1.211',
->master_user='replication',
->master_password='replication',
->master_port=3306,
->master_log_file='mysql-bin.000001',
->master_log_pos=414;
mysql>startslave;

2、slave

mysql>grantreplicationslaveon*.*to'replication'@'192.168.1.%'identifiedby'replication';
mysql>flushprivileges;
mysql>changemasterto
->master_host='192.168.1.210',
->master_user='replication',
->master_password='replication',
->master_port=3306,
->master_log_file='mysql-bin.000001',
->master_log_pos=414;
mysql>startslave;

同步失败可能需要停止或重设slave

mysql> stop slave;

mysql> reset slave;

 

step6:分别在master和slave上查看slave状态,验证是否成功配置主主复制模式

1、master

wKiom1a9p3TCJIhtAACcT4EvyoI330.jpg

2、slave

wKioL1a9qAGhFg9WAACSAFHbWEE538.jpg

slave状态同步过程可能需要重启MySQL服务

[[email protected] ~]# service mysqld restart[[email protected] ~]# service mysqld restart

 

step7:验证,在master上创建test1数据库,slave上查看是否同步

1、master上创建test1数据库

[[email protected]~]#mysql-uroot-ppasswd-e'createdatabasetest1'

2、slave上查看是否同步创建test1

[[email protected]~]#mysql-uroot-ppasswd-e'showdatabases'
+--------------------+
|Database|
+--------------------+
|information_schema|
|mysql|
|performance_schema|
|test1|
+--------------------+

 

安装和配置keepalived实现MySQL双主高可用

step1:安装keepalived

方法一:使用yum安装keepalived,需要安装epel-release源

[[email protected] ~]# rpm -ivh http://mirrors.opencas.cn/epel/6/i386/epel-release-6-8.noarch.rpm[[email protected] ~]# rpm -ivh http://mirrors.opencas.cn/epel/6/i386/epel-release-6-8.noarch.rpm

[[email protected] ~]# yum -y install keepalived

wKiom1a9r5ziXXxnAADRYEWW7dI218.jpg

查看keepalived相关目录

[[email protected]~]#ls/usr/sbin/keepalived
/usr/sbin/keepalived
[[email protected]~]#ls/etc/init.d/keepalived
/etc/init.d/keepalived
[[email protected]~]#ls/etc/keepalived/keepalived.conf
/etc/keepalived/keepalived.conf

 

方法二:从keepalived官方网站http://www.keepalived.org源代码包编译安装

1、下载keepalived最新版

[[email protected] ~]# wget http://www.keepalived.org/software/keepalived-1.2.19.tar.gz

[[email protected] ~]# wget http://www.keepalived.org/software/keepalived-1.2.19.tar.gz

2、安装keepalived依赖软件包

[[email protected] ~]# yum install pcre-devel openssl-devel popt-devel libnl-devel

3、解压并安装keepalived

[[email protected]~]#tarzxfkeepalived-1.2.19.tar.gz
[[email protected]~]#cdkeepalived-1.2.19

[]#./configure--prefix=/usr/local/keepalived
--sysconf=/etc--with-kernel-dir=/usr/src/kernels/2.6.32-431.el6.x86_64

wKiom1a9ypfA0sbqAABMQd1mThE780.jpg

[]#make
[]#makeinstall

查看keepalived相关的文件

[]#ls/etc/keepalived/
keepalived.confsamples
[]#ls/etc/init.d/keepalived
/etc/init.d/keepalived

链接/usr/local/keepalived/sbin/keepalived到/sbin/目录

[]#ln-s/usr/local/keepalived/sbin/keepalived/sbin/

设置keepalived启动级别

[]#chkconfig--addkeepalived
[]#chkconfig--level35keepalivedon

 

step2:配置keepalived

1、Master的keepalived.conf配置文件

!ConfigurationFileforkeepalived

global_defs{
notification_email{


}
[email protected]
smtp_server127.0.0.1
smtp_connect_timeout30
router_idLVS_DEVEL
}

vrrp_instanceHA_1{
stateBACKUP#master和slave都配置为BACKUP
interfaceeth0#指定HA检测的网络接口
virtual_router_id80#虚拟路由标识,主备相同
priority100#定义优先级,slave设置90
advert_int2#设定master和slave之间同步检查的时间间隔
nopreempt#不抢占模式。只在优先级高的机器上设置即可
authentication{
auth_typePASS
auth_pass1111
}

virtual_ipaddress{#设置虚拟IP,可以设置多个,每行一个
192.168.1.208/24deveth0#MySQL对外服务的IP,即VIP
}
}

virtual_server192.168.1.2083306{
delay_loop2
lb_algorr
lb_kinfDR
persistence_timeout50
protocolTCP

real_server192.168.1.2103306{#监听本机的IP
weight3
notify_down/usr/local/keepalived/bin/.sh
TCP_CHECK{
connect_timeout8
nb_get_retry3
delay_before_retry3
connect_port3306
}
}

}

keepalived检测脚本,当其中一台MySQL服务出现故障down掉时,实现自动切换到正常的MySQL服务器继续提供服务

[[email protected]~]#vim/usr/local/keepalived/bin/mysql.sh
#!/bin/bash
pkillkeepalived

 

2、Slave的keepalived.conf配置文件

!ConfigurationFileforkeepalived

global_defs{
notification_email{


}
[email protected]
smtp_server127.0.0.1
smtp_connect_timeout30
router_idLVS_DEVEL
}

vrrp_instanceHA_1{
stateBACKUP#master和slave都配置为BACKUP
interfaceeth0#指定HA检测的网络接口
virtual_router_id80#虚拟路由标识,主备相同
priority90#定义优先级,slave设置90
advert_int2#设定master和slave之间同步检查的时间间隔
authentication{
auth_typePASS
auth_pass1111
}

virtual_ipaddress{#设置虚拟IP,可以设置多个,每行一个
192.168.1.208/24deveth0#MySQL对外服务的IP,即VIP
}
}

virtual_server192.168.1.2083306{
delay_loop2
lb_algorr
lb_kinfDR
persistence_timeout50
protocolTCP

real_server192.168.1.2113306{#监听本机的IP
weight3
notify_down/usr/local/mysql/bin/mysql.sh
TCP_CHECK{
connect_timeout8
nb_get_retry3
delay_before_retry3
connect_port3306
}
}

}

 

step3:授权VIP的root用户权限

授权远程主机可以通过VIP登录MySQL,并测试数据复制功能

mysql>grantallon*.*toroot@'192.168.1.208'identifiedby'741616710';
mysql>flushprivileges;

step4:测试keepalived高可用功能

1、远程主机登录通过VIP192.168.1.208登录MySQL,查看MySQL连接状态

mysql>showvariableslike'hostname%';
+---------------+--------+
|Variable_name|Value|
+---------------+--------+
|hostname|master|
+---------------+--------+
1rowinset(0.00sec)

wKioL1a97YXgwxcBAAAhF3WzdpI609.jpg

从上面查看的结果看样看出在正常情况下连接的是master

 

2、故障测试,停止master的MySQL服务,再次查看是否转移至slave服务器上

[[email protected]~]#servicemysqldstop
ShuttingdownMySQL....SUCCESS!
mysql>showvariableslike'hostname%';
ERROR2013(HY000):LostconnectiontoMySQLserverduringquery
mysql>showvariableslike'hostname%';
ERROR2006(HY000):MySQLserverhasgoneaway
Noconnection.Tryingtoreconnect...
Connectionid:1268
Currentdatabase:***NONE***

+---------------+-------+
|Variable_name|Value|
+---------------+-------+
|hostname|slave|
+---------------+-------+
1rowinset(0.01sec)

wKiom1a97ezz4qMQAABPHTYltu0845.jpg

由测试结果可以看出,keepalived成功转移MySQL服务

Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<