MySQL High Availability Keepalived+mysql (dual Master Hot Spares)

Environment Description:
Os:centos6.5_x64
master:192.168.0.202
backup:192.168.0.203
vip:192.168.0.204

1. Configure two MySQL master sync
[email protected] ~]# Yum install mysql-server mysql-y
[[Email protected] ~]# service mysqld start
[Email protected] ~]# mysqladmin-u root password 123.com
[[email protected] ~]# vi/etc/my.cnf #开启二进制日志, set ID
[Mysqld]
Server-id = 1 #backup这台设置2
Log-bin = Mysql-bin
Binlog-ignore-db = Mysql,information_schema #忽略写入binlog日志的库
Auto-increment-increment = 2 #字段变化增量值
Auto-increment-offset = 1 #初始字段ID为1
Slave-skip-errors = All #忽略所有复制产生的错误
[Email protected] ~]# service mysqld restart

#先查看下log bin Logs and POS value locations

The master configuration is as follows:

[Email protected] Master ~]# mysql-u root-p123.com
Mysql> GRANT REPLICATION SLAVE on * * to ' REPLICATION ' @ ' 192.168.0.% ' identified by ' REPLICATION ';
mysql> flush Privileges;
Mysql> Change Master to
Master_host= ' 192.168.0.203 ',
Master_user= ' Replication ',
master_password= ' Replication ',
Master_log_file= ' mysql-bin.000002 ',
master_log_pos=106; #对端状态显示的值
mysql> start slave; #启动同步

The backup configuration is as follows:

[Email protected] ~]# mysql-u root-p123.com
Mysql> GRANT REPLICATION SLAVE on * * to ' REPLICATION ' @ ' 192.168.0.% ' identified by ' REPLICATION ';
mysql> flush Privileges;
Mysql> Change Master to
Master_host= ' 192.168.0.202 ',
Master_user= ' Replication ',
master_password= ' Replication ',
Master_log_file= ' mysql-bin.000002 ',
master_log_pos=106;
mysql> start slave;

#主主同步配置完毕, view the synchronization status Slave_io and slave_sql Yes to indicate that the primary master synchronization was successful.

Under the Master Insert data test:

In Backup to see if the synchronization was successful:

You can see that the past has been successfully synchronized, the same as the backup inserted into the user table data, the same synchronization in the past, the two owners have succeeded.

2, configuration keepalived to achieve hot standby
[email protected] ~]# Yum install-y pcre-devel openssl-devel popt-devel #安装依赖包

[Email protected] ~]# wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz
[Email protected] ~]# tar zxvf keepalived-1.2.7.tar.gz
[Email protected] ~]# CD keepalived-1.2.7
[Email protected] ~]#./configure--prefix=/usr/local/keepalived
Make && make install

#将keepalived配置成系统服务

[Email protected] ~]# cp/usr/local/keepalived/etc/rc.d/init.d/keepalived/etc/init.d/
[Email protected] ~]# cp/usr/local/keepalived/etc/sysconfig/keepalived/etc/sysconfig/
[Email protected] ~]# mkdir/etc/keepalived/
[Email protected] ~]# cp/usr/local/keepalived/etc/keepalived/keepalived.conf/etc/keepalived/
[Email protected] ~]# cp/usr/local/keepalived/sbin/keepalived/usr/sbin/

[Email protected] ~]# vi/etc/keepalived/keepalived.conf
! Configuration File forkeepalived
Global_defs {
Notification_email {
[Email protected]
}
Notification_email_from [email protected]
Smtp_server 127.0.0.1
Smtp_connect_timeout 30
router_id Mysql_ha #标识, double master Same
}
Vrrp_instance Vi_1 {
State BACKUP #两台都设置BACKUP
Interface eth0
virtual_router_id Wuyi #主备相同
Priority #优先级, backup settings 90
Advert_int 1
Nopreempt #不主动抢占资源, only in master this high priority setting, backup does not set
Authentication {
Auth_type PASS
Auth_pass 1111
}
virtual_ipaddress {
192.168.0.204
}
}
Virtual_server 192.168.0.204 3306 {
Delay_loop 2
#lb_algo RR #LVS算法, we're off.
#lb_kind DR #LVS模式, if not shut down, the standby server cannot connect to master MySQL via VIP
Persistence_timeout #同一IP的连接60秒内被分配到同一台真实服务器
Protocol TCP
Real_server 192.168.0.202 3306 {#检测本地mysql, Backup also write to detect local MySQL
Weight 3
Notify_down/usr/local/keepalived/mysql.sh #当mysq服down时, execute this script, kill keepalived to implement the switch
Tcp_check {
Connect_timeout 3 #连接超时
Nb_get_retry 3 #重试次数
Delay_before_retry 3 #重试间隔时间
}
}

[Email protected] ~]# vi/usr/local/keepalived/mysql.sh
#!/bin/bash
Pkill keepalived
[Email protected] ~]# chmod +x/usr/local/keepalived/mysql.sh
[[email protected] ~]#/etc/init.d/keepalived start

#backup服务器只修改priority为90, Nopreempt not set, real_server set local IP.

#授权两台Mysql服务器允许root远程登录 for landing tests on other servers!
Mysql> Grant All on * * to ' root ' @ ' 192.168.0.% ' identified by ' 123.com ';
mysql> flush Privileges;
3. Test High Availability
1, through the MySQL client through the VIP connection, see if the connection is successful.
2, stop the master this MySQL service, whether it can switch to the past, you can use the IP addr command to see the VIP on which server.

3, can see the/var/log/messges log, see the main standby switching process
4, master server failure recovery, whether proactively seize resources, become Active server.

MySQL High Availability Keepalived+mysql (dual Master Hot Spares)