试验环境：

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/mysqldatadir=/var/lib/mysqlport=3306socket=/var/lib/mysql/mysql.sockserver_id=1log-bin=mysql-binrelay-log=mysql-relay-binreplicate-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/mysqldatadir=/var/lib/mysqlport=3306socket=/var/lib/mysql/mysql.sockserver_id=2log-bin=mysql-binrelay-log=mysql-relay-binreplicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=test.%replicate-wild-ignore-table=information_schema.%

step3：重启两台主从mysql服务

[root@master~]#servicemysqldrestartShuttingdownMySQL..[OK]StartingMySQL.[OK][root@slave~]#servicemysqldrestartShuttingdownMySQL..[OK]StartingMySQL.[OK]

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

记录File和Position的值

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

[root@slave~]#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

2、slave

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

[root@master ~]# service mysqld restart[root@slave ~]# service mysqld restart

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

1、master上创建test1数据库

[root@master~]#mysql-uroot-ppasswd-e'createdatabasetest1'

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

[root@slave~]#mysql-uroot-ppasswd-e'showdatabases'+--------------------+|Database|+--------------------+|information_schema||mysql||performance_schema||test1|+--------------------+

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

step1：安装keepalived

方法一：使用yum安装keepalived，需要安装epel-release源

[root@master ~]# rpm -ivh mirrors.opencas.cn/epel/6/i386/epel-release-6-8.noarch.rpm[root@slave ~]# rpm -ivh mirrors.opencas.cn/epel/6/i386/epel-release-6-8.noarch.rpm

[root@slave ~]# yum -y install keepalived

查看keepalived相关目录

[root@slave~]#ls/usr/sbin/keepalived/usr/sbin/keepalived[root@slave~]#ls/etc/init.d/keepalived/etc/init.d/keepalived[root@slave~]#ls/etc/keepalived/keepalived.conf/etc/keepalived/keepalived.conf

方法二：从keepalived官方网站keepalived.org下载源代码包编译安装

1、下载keepalived最新版

[root@master ~]# wget keepalived.org/software/keepalived-1.2.19.tar.gz

[root@slave ~]# wget keepalived.org/software/keepalived-1.2.19.tar.gz

2、安装keepalived依赖软件包

[root@master ~]# yum install pcre-devel openssl-devel popt-devel libnl-devel

3、解压并安装keepalived

[root@master~]#tarzxfkeepalived-1.2.19.tar.gz[root@master~]#cdkeepalived-1.2.19[root@masterkeepalived-1.2.19]#./configure--prefix=/usr/local/keepalived--sysconf=/etc--with-kernel-dir=/usr/src/kernels/2.6.32-431.el6.x86_64

[root@masterkeepalived-1.2.19]#make[root@masterkeepalived-1.2.19]#makeinstall

查看keepalived相关的文件

[root@masterkeepalived-1.2.19]#ls/etc/keepalived/keepalived.confsamples[root@masterkeepalived-1.2.19]#ls/etc/init.d/keepalived/etc/init.d/keepalived

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

[root@masterkeepalived-1.2.19]#ln-s/usr/local/keepalived/sbin/keepalived/sbin/

设置keepalived启动级别

[root@masterkeepalived-1.2.19]#chkconfig--addkeepalived[root@masterkeepalived-1.2.19]#chkconfig--level35keepalivedon

step2：配置keepalived

1、Master的keepalived.conf配置文件

!ConfigurationFileforkeepalivedglobal_defs{notification_email{root@huangmingming.cn741616710@qq}notification_email_fromkeepalived@localhostsmtp_server127.0.0.1smtp_connect_timeout30router_idLVS_DEVEL}vrrp_instanceHA_1{stateBACKUP#master和slave都配置为BACKUPinterfaceeth0#指定HA检测的网络接口virtual_router_id80#虚拟路由标识，主备相同priority100#定义优先级，slave设置90advert_int2#设定master和slave之间同步检查的时间间隔nopreempt#不抢占模式。只在优先级高的机器上设置即可authentication{auth_typePASSauth_pass1111}virtual_ipaddress{#设置虚拟IP，可以设置多个，每行一个192.168.1.208/24deveth0#MySQL对外服务的IP，即VIP}}virtual_server192.168.1.2083306{delay_loop2lb_algorrlb_kinfDRpersistence_timeout50protocolTCPreal_server192.168.1.2103306{#监听本机的IPweight3notify_down/usr/local/keepalived/bin/mysql.shTCP_CHECK{connect_timeout8nb_get_retry3delay_before_retry3connect_port3306}}}

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

[root@master~]#vim/usr/local/keepalived/bin/mysql.sh#!/bin/bashpkillkeepalived

2、Slave的keepalived.conf配置文件

!ConfigurationFileforkeepalivedglobal_defs{notification_email{root@huangmingming.cn741616710@qq}notification_email_fromkeepalived@localhostsmtp_server127.0.0.1smtp_connect_timeout30router_idLVS_DEVEL}vrrp_instanceHA_1{stateBACKUP#master和slave都配置为BACKUPinterfaceeth0#指定HA检测的网络接口virtual_router_id80#虚拟路由标识，主备相同priority90#定义优先级，slave设置90advert_int2#设定master和slave之间同步检查的时间间隔authentication{auth_typePASSauth_pass1111}virtual_ipaddress{#设置虚拟IP，可以设置多个，每行一个192.168.1.208/24deveth0#MySQL对外服务的IP，即VIP}}virtual_server192.168.1.2083306{delay_loop2lb_algorrlb_kinfDRpersistence_timeout50protocolTCPreal_server192.168.1.2113306{#监听本机的IPweight3notify_down/usr/local/mysql/bin/mysql.shTCP_CHECK{connect_timeout8nb_get_retry3delay_before_retry3connect_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)

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

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

[root@master~]#servicemysqldstopShuttingdownMySQL....SUCCESS!

mysql>showvariableslike'hostname%';ERROR2013(HY000):LostconnectiontoMySQLserverduringquerymysql>showvariableslike'hostname%';ERROR2006(HY000):MySQLserverhasgoneawayNoconnection.Tryingtoreconnect...Connectionid:1268Currentdatabase:***NONE***+---------------+-------+|Variable_name|Value|+---------------+-------+|hostname|slave|+---------------+-------+1rowinset(0.01sec)

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