本文共 3729 字，大约阅读时间需要 12 分钟。

前言

最近做一个项目，项目考虑了一些风险，其中就有mysql宕机的风险，mysql是申请了两台服务器。于是打算搞个主主复制，用keepalived进行漂移实现高可用。

方案可行性

首先主主复制可以保证两台机器数据同步，keeplalived通过虚拟ip，保证我们的mysql可以快速切换。方案可行。

搭建步骤

首先我们准备好两台已经搭建好的mysql服务器。

ip分别为15.1.1.46，15.1.1.50

修改配置文件

在第一台mysql服务器上的my.cf文件中，我们增加如下内容。

server-id=1log-bin=mysql-binbinlog-ignore-db=mysql                （可以不加）binlog-ignore-db=information_schema  （可以不加）binlog-do-db=mydatabase            （可以不加）binlog_format=STATEMENTauto-increment-increment = 2auto-increment-offset = 1

增加后的整体情况如下：

[mysqld]server-id=1log-bin=mysql-binbinlog_format=STATEMENTauto-increment-increment = 2auto-increment-offset = 1sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESbasedir = /home/mysql-5.7.25datadir = /home/mysql-5.7.25/dataport = 3306socket = /tmp/mysql.sockcharacter-set-server=utf8log-error = /home/mysql-5.7.25/data/mysqld.logpid-file = /home/mysql-5.7.25/data/mysqld.pid

第一台机器配置完毕。

第二台机器进行如下配置：

server-id=2log-bin=mysql-binbinlog_format=STATEMENTauto-increment-increment = 2auto-increment-offset = 2

和第一台基本一样，不过是increment不同，防止自增冲突。

整体配置文件如下：

[mysqld]server-id=2log-bin=mysql-binbinlog_format=STATEMENTauto-increment-increment = 2auto-increment-offset = 2sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLESbasedir = /home/mysql-5.7.25datadir = /home/mysql-5.7.25/dataport = 3306socket = /tmp/mysql.sockcharacter-set-server=utf8log-error = /home/mysql-5.7.25/data/mysqld.logpid-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;

记住上方的file 和postion切换到第二台机器执行如下

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;

两个都是yes证明复制成功。

第一台机器复制第二台，第二台作为主机

第二台机器执行如下：

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';

展示master的位置

show master status;

切换到第一台机器

执行如下命令

change master to master_host='15.1.1.50',master_user='root',master_password='123456',master_log_file='mysql-bin.000007',master_log_pos=154;

然后开启复制

start slave;展示从机的复制情况show slave status\G;如果下图中两个都是yes则证明复制成功。

使用keepalived进行漂移保证高可用

（1）安装yum包

yum install -y gcc openssl-devel popt-devel（2）上传keepalived并解压tar -zxvf keepalived-1.2.15.tar.gz（3）解压keepalived并进行编译cd keepalived-1.2.15./configure --prefix=/usr/local/keepalivedmake && make install（4）进行配置文件的初始化复制工作cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.dcp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfigmkdir /etc/keepalivedcp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/cp /usr/local/keepalived/sbin/keepalived /usr/sbin/（5）开机自启chkconfig --add keepalivedchkconfig keepalived on以上步骤第二台机器也如此执行（6）进入主机 /etc/keepalived。修改里面的 keepalived.conf文件

! Configuration File for keepalivedglobal_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）编写监测的自杀脚本

当mysql挂掉之后，keepalived监控到mysql挂掉，keepalived才能进行漂移。

#!/bin/bashps -ef | grep mysqld| grep -v grep &> /dev/nullif [ $? -eq 0 ]then	echo "mysql服务正常运行！！！"else	service keepalived stop	echo "mysql服务已经停止！请及时解决！！！"fi

（8）编写从机的keepalived的脚本

上面的步骤里面的keepalived的conf按照下面的改下。其他不用动。state MASTER 改为BACKUPpriority 100 改为90（9）将keepalived的开启

service keepalived start

总结

至此mysql高可用已经搭建完毕。