前言
最近做一个项目，项目考虑了一些风险，其中就有MysqL宕机的风险，MysqL是申请了两台服务器。于是打算搞个主主复制，用keepalived进行漂移实现高可用。
方案可行性
首先主主复制可以保证两台机器数据同步，keeplalived通过虚拟ip，保证我们的MysqL可以快速切换。方案可行。
搭建步骤
首先我们准备好两台已经搭建好的MysqL服务器。@H_502_11@ ip分别为15.1.1.46，15.1.1.50
修改配置文件
在第一台MysqL服务器上的my.cf文件中，我们增加如下内容。
server-id=1
log-bin=MysqL-bin
binlog-ignore-db=MysqL （可以不加）
binlog-ignore-db=information_schema （可以不加）
binlog-do-db=mydatabase （可以不加）
binlog_format=STATEMENT
auto-increment-increment = 2
auto-increment-offset = 1
增加后的整体情况如下：
[MysqLd]
server-id=1
log-bin=MysqL-bin
binlog_format=STATEMENT
auto-increment-increment = 2
auto-increment-offset = 1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
basedir = /home/MysqL-5.7.25
datadir = /home/MysqL-5.7.25/data
port = 3306
socket = /tmp/MysqL.sock
character-set-server=utf8
log-error = /home/MysqL-5.7.25/data/MysqLd.log
pid-file = /home/MysqL-5.7.25/data/MysqLd.pid
第一台机器配置完毕。@H_502_11@ 第二台机器进行如下配置：
server-id=2
log-bin=MysqL-bin
binlog_format=STATEMENT
auto-increment-increment = 2
auto-increment-offset = 2
和第一台基本一样，不过是increment不同，防止自增冲突。@H_502_11@ 整体配置文件如下：
[MysqLd]
server-id=2
log-bin=MysqL-bin
binlog_format=STATEMENT
auto-increment-increment = 2
auto-increment-offset = 2
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
basedir = /home/MysqL-5.7.25
datadir = /home/MysqL-5.7.25/data
port = 3306
socket = /tmp/MysqL.sock
character-set-server=utf8
log-error = /home/MysqL-5.7.25/data/MysqLd.log
pid-file = /home/MysqL-5.7.25/data/MysqLd.pid
两台机器全部重启
service MysqL restart
第二台机器复制第一台，第一台作为主机
第一台机器执行如下：
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
展示当前bin-log位置
show master status;
change master to master_host='15.1.1.46',master_user='root',master_password='123456',master_log_file='MysqL-bin.000001',master_log_pos=154;
开启复制
start slave;
展示从机复制情况
show slave status\G;
第二台机器执行如下：
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
展示master的位置
show master status;
切换到第一台机器@H_502_11@ 执行如下命令
change master to master_host='15.1.1.50',master_log_file='MysqL-bin.000007',master_log_pos=154;
然后开启复制@H_502_11@ start slave;@H_502_11@ 展示从机的复制情况@H_502_11@ show slave status\G;@H_502_11@ 如果下图中两个都是yes则证明复制成功。
使用keepalived进行漂移保证高可用
（1）安装yum包@H_502_11@ yum install -y gcc openssl-devel popt-devel@H_502_11@ （2）上传keepalived并解压@H_502_11@ tar -zxvf keepalived-1.2.15.tar.gz@H_502_11@ （3）解压keepalived并进行编译@H_502_11@ cd keepalived-1.2.15@H_502_11@ ./configure --prefix=/usr/local/keepalived@H_502_11@ make && make install@H_502_11@ （4）进行配置文件的初始化复制工作@H_502_11@ cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d@H_502_11@ cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig@H_502_11@ mkdir /etc/keepalived@H_502_11@ cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/@H_502_11@ cp /usr/local/keepalived/sbin/keepalived /usr/sbin/@H_502_11@ （5）开机自启@H_502_11@ chkconfig --add keepalived@H_502_11@ chkconfig keepalived on@H_502_11@ 以上步骤第二台机器也如此执行@H_502_11@ （6）进入主机 /etc/keepalived。修改里面的 keepalived.conf文件
! Configuration File for keepalived
global_defs {
router_id MysqL-HA
}
vrrp_script check_MysqL {
script "/home/MysqL/MysqL.sh"
interval 1
weight 2
}
vrrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
check_MysqL
}
virtual_ipaddress {
15.1.1.99
}
}
（7）编写监测的自杀脚本@H_502_11@ 当MysqL挂掉之后，keepalived监控到MysqL挂掉，keepalived才能进行漂移。
#!/bin/bash
ps -ef | grep MysqLd| grep -v grep &> /dev/null
if [ $? -eq 0 ]
then
echo "MysqL服务正常运行！！！"
else
service keepalived stop
echo "MysqL服务已经停止！请及时解决！！！"
fi
（8）编写从机的keepalived的脚本@H_502_11@ 上面的步骤里面的keepalived的conf按照下面的改下。其他不用动。@H_502_11@ state MASTER 改为BACKUP@H_502_11@ priority 100 改为90@H_502_11@ （9）将keepalived的开启
service keepalived start
总结
至此MysqL高可用已经搭建完毕。