LVS+KeepAlived，搭建MySQL高可用负载均衡

[root@master1~]# cat /etc/hosts
192.168.1.121 master1.node.com
192.168.1.114 master2.node.com
192.168.1.122 slave1.node.com
192.168.1.123 slave2.node.com

[root@master1 ~]# cat /mysqldata/auto.cnf
[auto]
server-uuid=fa6cdd4e-337b-11e4-97e9-000c293a63cc

[mysqld]
binlog-format=ROW
log-bin=master-bin
log-slave-updates=true
gtid-mode=on 
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=1 #不得与其他节点相同
report-port=3306
port=3306
datadir=/mydata
socket=/tmp/mysql.sock
auto_increment_increment=2 #该配置只需要在master1、master2上配置 用来确保自增键不冲突 
auto_increment_offset=1 #该配置只需要在master1、master2上配置 用来确保自增键不冲突 
binlog-do-db=mydb #需要同步的数据库，多个数据库添加多行配置项即可,需要重启mysql实例

[mysqld]
binlog-format=ROW
log-slave-updates=true
gtid-mode=on 
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=11 #不得与其他节点相同
report-port=3306
port=3306
log-bin=mysql-bin.log
datadir=/mydata
socket=/tmp/mysql.sock
auto_increment_increment=2 #该配置只需要在master1、master2上配置 用来确保自增键不冲突 
auto_increment_offset=1 #该配置只需要在master1、master2上配置 用来确保自增键不冲突 
binlog-do-db=mydb #需要同步的数据库，多个数据库添加多行配置项即可,需要重启mysql实例

mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'192.168.1.114' IDENTIFIED BY 'passwd';

slave> CHANGE MASTER TO MASTER_HOST='master1.node.com',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='passwd',
-> MASTER_LOG_FILE='master-bin.000003',#需要在对应的master上边执行show master status;查看
-> MASTER_LOG_POS=1174;#需要在对应的master上边执行show master status;查看

mysql> show slave status\G;
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: master2.node.com
 Master_User: repl
 Master_Port: 3306
Connect_Retry: 60
 Master_Log_File: mygateway-bin.000025
 Read_Master_Log_Pos: 231
 Relay_Log_File: initiator-relay-bin.000012
Relay_Log_Pos: 409
Relay_Master_Log_File: mygateway-bin.000025
 Slave_IO_Running: Yes
Slave_SQL_Running: Yes
 Replicate_Do_DB: 
 Replicate_Ignore_DB: 
 Replicate_Do_Table: 
 Replicate_Ignore_Table: 
 Replicate_Wild_Do_Table: 
 Replicate_Wild_Ignore_Table: 
 Last_Errno: 0
 Last_Error: 
 Skip_Counter: 0
 Exec_Master_Log_Pos: 231
 Relay_Log_Space: 586
 Until_Condition: None
 Until_Log_File: 
Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File: 
 Master_SSL_CA_Path: 
 Master_SSL_Cert: 
Master_SSL_Cipher: 
 Master_SSL_Key: 
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error: 
 Last_SQL_Errno: 0
 Last_SQL_Error: 
 Replicate_Ignore_Server_Ids: 
 Master_Server_Id: 11
 Master_UUID: 50aef63f-ed82-11e4-94f3-000c293a63bb
 Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
 SQL_Remaining_Delay: NULL
 Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
 Master_Retry_Count: 86400
 Master_Bind: 
 Last_IO_Error_Timestamp: 
 Last_SQL_Error_Timestamp: 
 Master_SSL_Crl: 
 Master_SSL_Crlpath: 
 Retrieved_Gtid_Set: 50aef63f-ed82-11e4-94f3-000c293a63bb:1-10
Executed_Gtid_Set: 50aef63f-ed82-11e4-94f3-000c293a63bb:1-10,
fa6cdd4e-337b-11e4-97e9-000c293a63cc:1-30
Auto_Position: 1
1 row in set (0.01 sec)

[root@master1~]# yum -y install keeplived

#!/bin/bash 
# description: Config realserver lo and apply noarp
SNS_VIP=192.168.1.120 #修改为对应的VIP即可
. /etc/rc.d/init.d/functions
case "$1" in
start)
 ifconfig lo:0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIP
 /sbin/route add -host $SNS_VIP dev lo:0
 echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
 echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
 echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
 echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce
 sysctl -p >/dev/null 2>&1
 echo "RealServer Start OK" 
 ;;
stop)
 ifconfig lo:0 down
 route del $SNS_VIP >/dev/null 2>&1
 echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore
 echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce
 echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore
 echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce
 echo "RealServer Stoped" 
 ;;
*)
 echo "Usage: $0 {start|stop}" 
 exit 1
esac

[root@localhost~]# chmod u+x /etc/rc.d/init.d/realserver.sh
[root@localhost~]# /etc/rc.d/init.d/realserver.sh start #启动lvs脚本设置参数，绑定VIP

vrrp_instance VI_1 {
 state MASTER #在备机上修改为BACKUP
 interface eth1 #VIP要绑定到eth1上,是具体情况而定，填写具体的主机网卡名称
 virtual_router_id 52
 priority 100 #对应备机的值要小于这个值
 advert_int 1
 authentication {
 auth_type PASS #备机上要与之一致
 auth_pass 1111 #备机上要与之一致
 }

 virtual_ipaddress {
 192.168.1.120/32 dev eth1 label eth1:0 #VIP要绑定到eth1上,是具体情况而定，填写具体的主机网卡名称,修改为对应的VIP
 }
}

virtual_server 192.168.1.120 3306 {#修改为对应的VIP
 delay_loop 6
 lb_algo rr #lvs负载均衡算法
 lb_kind DR #lvs的转发模式
 #nat_mask 255.255.255.0
 #persistence_timeout 50
 protocol TCP

 real_server 192.168.1.121 3306 {#修改为对应的realserever
 weight 2
 TCP_CHECK {
 connect_timeout 3
 nb_get_retry 3
 delay_before_retry 3
 connect_port 3306
 }
 }

 real_server 192.168.1.114 3306 {#修改为对应的realserver
 weight 2
 TCP_CHECK {
 connect_timeout 3
 nb_get_retry 3
 delay_before_retry 3
 connect_port 3306
 }
 }

}

[root@localhost~]#/etc/init.d/keepalived start
[root@localhost~]#ip add #查看IP状态
[root@localhost~]# ipvsadm -Ln # 查看LVS状态，若有没有该工具yum -y install ipvsadm即可

notify_up $PATH/SCRIPT.sh #检测到服务开启后执行的脚本 可以是邮件报警，如某某IP，mysql挂掉。。
notify_down $PATH/SCRIPT.sh #检测到服务停止后执行的脚本.

state BACKUP #都修改成BACKUP
virtual_router_id 60 #默认51 主从都修改为60
priority 100 #优先级（1-254之间），另一台改为90，备用节点必须比主节点优先级低。
nopreempt #不抢占资源，意思就是它活了之后也不会再把主抢回来,备机不需要设置改项