随着公司的发展，数据显得尤为重要，上次写了服务器的安全操作知之外，今天给大家写一篇针对mysql的双主高可用配置。
本文讲述配置Keepalived实现Mysql双主高可用配置，其中所需知识包含iptables，mysql，sendmail，Keepalived，shell或者其它脚本语言均可。差不多就这么多吧。自己也百度了许多文章，很多都没有讲全，今天我就把整个过程给大家讲述清楚，由于本次内容众多，部分内容不贴图，不全部解释配置是何含义，希望大家理解。如遇不懂的可以加博主私人QQ1156178046，欢迎技术交流！
首先介绍环境
DB 1 centerOS 7 192.168.10.11 +iptables+keepalived master+ntp+sendmail+shell
DB 2 centerOS 7 192.168.10.12 +iptables+keepalived backup+sendmail+shell
VIP地址 192.168.10.20
1.安装mysql部分
yum -y install mysql
如果遇到yum没有可用mysql-server安装源，可见如下配置
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm rpm -ivh mysql-community-release-el7-5.noarch.rpm yum install mysql-server
2.修改DB 1 mysql配置文件，在[mysqld] 段下添加如下内容
vi /etc/my.cnf
server-id = 1 log-bin=mysql-bin relay-log = mysql-relay-bin replicate-wild-ignore-table=mysql.% replicate-wild-ignore-table=test.% replicate-wild-ignore-table=information_schem.% max_allowed_packet = 500M
3.修改DB 2 mysql配置文件，在[mysqld] 段下添加如下内容
vi /etc/my.cnf
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_schem.%
4.手动同步数据库
使用数据库工具mysqldump导入导出即可，命令不赘述。
5.DB 1创建用户并且授权
create USER 'sshs'@'%' IDENTIFIED BY '123456';
grant replication slave on *.* to 'sshs'@'192.168.10.12' identified by '123456';
FLUSH privileges;
6.DB 2创建用户并且授权
create USER 'sshs'@'%' IDENTIFIED BY '123456';
grant replication slave on *.* to 'sshs'@'192.168.10.11' identified by '123456';
FLUSH privileges;
7.在DB1 上设置DB2为自己的主服务器,其中master_log_file和master_log_pos对应的是DB2上show master status;命令结果中的File 和Postion
change master to master_host='192.168.10.12',master_user='sshs',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=106;
start slave;
show slave status\G;
其中重点关注slave_IO-running 和Slave_SQL_Running 值均为YEs即可。
8.在DB2 上设置DB1为自己的主服务器,其中master_log_file和master_log_pos对应的是DB1上show master status;命令结果中的File 和Postion
change master to master_host='192.168.10.11',master_user='sshs',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=106;
start slave;
show slave status\G;
其中重点关注slave_IO-running 和Slave_SQL_Running 值均为YEs即可。
9.不使用最新firewalld ，使用iptables 并开启防火墙。
systemctl stop firewalld systemctl mask firewalld yum install iptables-services systemctl enable iptables service iptables start
防火墙通过命令可借鉴如下
iptables -L -n
iptables -I INPUT -s 0.0.0.0/0 -j ACCEPT
iptables -I OUTPUT -s 0.0.0.0/0 -j ACCEPT
service iptables save
service iptables restart
10.安装sendmail
可查看这篇文章 http://blog.csdn.net/jeccisnd/article/details/72885682 如何使用sendmail实现向外网发送邮件。（也是我写的。。。）
11.DB 1 Keepalived安装部分。DB 2一样
wget http://www.keepalived.org/software/keepalived-1.3.5.tar.gz
tar zxvf keepalived-1.3.5.tar.gz
cd keepalived-1.3.5.tar.gz
./config --sysconf=/etc --with-kernel-dir=/usr/src/kernels/
make
make install
ln -s /usr/local/sbin/keepalived /sbin
chkconfig keepalived on
12.DB 1 配置部分如下,DB 2,将state master 更改为state backup ,priority 100更改为priority 80即可
! Configuration File for keepalived global_defs { router_id LVS_DEVEL vrrp_skip_check_adv_addr vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0 } vrrp_script check_mysqld { script "killall -0 mysqld" ! 检查mysql是否存在，可引用脚本检测mysql slave状态,这里只做简单演示
interval 2 weight 21 } vrrp_instance G1 { state MASTER interface ens33 virtual_router_id 20 priority 100 advert_int 2 nopreempt authentication { auth_type PASS auth_pass qwaszx } notify_master "/etc/keepalived/master.sh" notify_backup "/etc/keepalived/backup.sh" notify_fault "/etc/keepalived/fault.sh" track_script { check_mysqld } virtual_ipaddress { 192.168.10.20/24 dev ens33 } }
其中master.sh文件如下
#!/bin/bash HOSTIP=`ifconfig eth0 | grep inet | egrep -v inet6 | awk -F 'net' '{print $2}'`
输出本机IP LOGFILE=/var/log/keepalived-state.log #状态改变过程，可查看此文件 echo "[Master]" >> $LOGFILE date >> $LOGFILE echo "$HOSTNAME ,$HOSTIP ==> MASTER " | mail -s "This is Test " youremail@mail.com
#发送通知邮件
其中backup.sh文件如下
#!/bin/bash HOSTIP=`ifconfig eth0 | grep inet | egrep -v inet6 | awk -F 'net' '{print $2}'`
输出本机IP LOGFILE=/var/log/keepalived-state.log #状态改变过程，可查看此文件 echo "[Master]" >> $LOGFILE date >> $LOGFILE echo "$HOSTNAME ,$HOSTIP ==> BACKUP " | mail -s "This is Test " youremail@mail.com
#发送通知邮件
其中fault.sh文件如下
#!/bin/bash HOSTIP=`ifconfig eth0 | grep inet | egrep -v inet6 | awk -F 'net' '{print $2}'`
输出本机IP LOGFILE=/var/log/keepalived-state.log #状态改变过程，可查看此文件 echo "[Master]" >> $LOGFILE date >> $LOGFILE echo "$HOSTNAME ,$HOSTIP ==> fault " | mail -s "This is Test " youremail@mail.com
#发送通知邮件
13.启动及关闭命令
service iptables start | stop | restart
service sendmail start | stop | restart
service mysqld start | stop | restart
service keepalived start | stop | restart 或者killall keepalived
通过tail -f /var/log/messages查看程序日志。
14.看到最后的小伙伴不容易，这里奉上检测mysql slave 状态脚本
vi /etc/keepalived/mysqlcheck_slave.sh
相关Keepalived配置文件写法为,其它不变
script "/etc/keepalived/mysqlcheck_slave.sh"
#!/bin/bash MYSQL=`which mysql` MYSQL_HOST=localhost MYSQL_USER=root MYSQL_PASSWORD="xxxxxxxxx" MYSQL_PORT=3306 IA=`$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -P$MYSQL_PORT -e "show slave status\G;" |grep Slave_IO_Running | awk -F ':' '{print $2}'` SA=`$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -P$MYSQL_PORT -e "show slave status\G;" |grep Slave_SQL_Running |egrep -v Slave_SQL_Running_State | awk -F '': '{print $2}'` MYSQLA=`killall -0 mysqld` MYSQLB=`echo $?` if [ $MYSQLB == "0" -a $IA == "Yes" -a $SA = "Yes" ];then MYSQL_STATUS=0 else MYSQL_STATUS=1 fi exit $MYSQL_STATUS
注明：脚本已经修改，测试Keepalived切换成功，原方法可能由于执行环境的变化，导致Keepalived执行免密查询sql命令失败，导致判断不了读写状态，特此改变！
20170612
亲测可用！！！
本文原创内容，转载请注明出处!
(完)
