Keepalived+mysql for High Availability

In response to the company's needs, to create a more secure MySQL cluster, to achieve MySQL failover after the switch, the study of a few days finally have the results, share.
First, introduce the function of this cluster scheme.
1, MySQL server after the failure of automatic transfer, repaired automatically cut back
2, MySQL service failure automatic transfer, after repair automatically cut back
3, can be achieved within a few seconds to transfer
The following are experimental environments, please modify the response parameters according to the actual situation
Production environment MySQL Master primary sync primary key conflict handling? http://www.linuxidc.com/Linux/2013-07/86890.htm
MySQL + keepalived + LVS single-point write Master Sync High-availability architecture experiment? http://www.linuxidc.com/Linux/2013-05/84002.htm
MySQL primary master sync configuration? http://www.linuxidc.com/Linux/2013-05/83815.htm
centos?6.3 mysql master copy note? http://www.linuxidc.com/Linux/2013-06/85983.htm
MySQL primary master replication under Linux? http://www.linuxidc.com/Linux/2013-10/91683.htm
Lab Environment:
MYSQL1 ip:10.1.1.20
Mysql2? ip:10.1.1.21
MySQL vip:10.1.1.25
Three machines are installed CentOS 6.5 32-bit (virtual machine environment)
Experiment started!!!
First, install MySQL, and build Master sync.
I believe that the master-slave synchronization we will do, the same truth, the main master synchronization is two machines mutual primary relationship, on any one machine write will be synchronized.
The process of installing MySQL does not explain, Yum is fine.
Configure Primary Master Synchronization
1. Configure/ETC/MY.CNF
[Mysqld]
Datadir=/var/lib/mysql
Socket=/var/lib/mysql/mysql.sock
User=mysql

Disabling symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0
Log-bin=binlog? #开启binlog功能
Log-bin-index=binlog.index
sync_binlog=0
server_id = 1?? #两台机器不能重复, a 11 a 2 is good
?
[Mysqld_safe]
Log-error=/var/log/mysqld.log
Pid-file=/var/run/mysqld/mysqld.pid

2. Configure the sync account on both machines
10.1.1.20 on the machine:
[[email protected] ~]# MySQL
Welcome to the MySQL monitor. Commands End With; or \g.
Your MySQL Connection ID is 2
Server version:5.0.77-log sourcedistribution
?
Type ' help ', ' or ' \h ' for help. Type ' \c ' toclear the buffer.
?
mysql> GRANT replication Slave on . To ' AB ' @ ' percent ' identified by ' 123 ';
Query OK, 0 rows Affected (0.00 sec)
?
mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)
10.1.1.21 on the machine:
[[email protected] ~]# MySQL
Welcome to the MySQL Mon Itor. Commands End With; or \g.
Your MySQL Connection ID is 2
Server version:5.0.77-log sourcedistribution
?
Type ' help ', ' or ' \h ' for help. Type ' \c ' toclear the buffer.
?
mysql> GRANT replication Slave on . To ' AB ' @ ' percent ' identified by ' 123 ';
Query OK, 0 rows Affected (0.00 sec)
?
mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)

Note: Because this article is written in the experimental environment, so did not consider any security issues, synchronization account is the highest authority, please set the response permissions according to the actual situation!!
3. Set up sync
On the 10.1.1.20 machine:
Mysql> flush tables with read lock;
Mysql> Show master status;
+---------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+---------------+----------+--------------+------------------+
| binlog.000003 | 365 | | |
+---------------+----------+--------------+------------------+
1 row in Set (0.03 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.03 sec)
On the 10.1.1.21 machine:
mysql> Change Master tomaster_host= ' 10.1.1.20 ', master_port=3306, master_user= ' ab ', master_password= ' 123 ', master_ Log_file= ' binlog.000003 ', master_log_pos=365;
Query OK, 0 rows affected (0.06 sec)
mysql> start slave;
Query OK, 0 rows Affected (0.00 sec)
Mysql> show slave status \g? #执行这命令后 be careful to observe the following two parameters, you must be all yes.
Slave_io_running:yes
Slave_sql_running:yes
Do the same thing in turn
On the 10.1.1.21 machine:
Mysql> flush tables with read lock;
Mysql> Show master status;
+---------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+---------------+----------+--------------+------------------+
| binlog.000004 | 207 | | |
+---------------+----------+--------------+------------------+
1 row in Set (0.03 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.03 sec)
On the 10.1.1.20 machine:
?
mysql> Change Master tomaster_host= ' 10.1.1.21 ', master_port=3306, master_user= ' ab ', master_password= ' 123 ', master_ Log_file= ' binlog.000004 ', master_log_pos=207;
Query OK, 0 rows affected (0.06 sec)
mysql> start slave;
Query OK, 0 rows Affected (0.00 sec)
Mysql> show slave status \g? #执行这命令后 be careful to observe the following two parameters, you must be all yes.
Slave_io_running:yes
Slave_sql_running:yes
In this case, the master synchronization is completed, you can simply test, to write data on two machines to see if it will be synchronized to another machine
PS: If error? Slave_io_running:no? You can check if the synced account is created properly!
Second, install the keepalived and set up monitoring
The keepalived is installed on two MySQL servers.
First install the keepalived process does not explain the normal decompression installation is good
Post-installation configuration vim/etc/keepalived/keepalived.conf content as follows
Configuration file for 10.1.1.20
! Configuration File for Keepalived
Global_defs {
? Notification_email {
? ? [Email protected]
? ? [Email protected]
? ? [Email protected]
? }
? Notification_email_from [email protected]
? Smtp_server 127.0.0.1
? Smtp_connect_timeout 30
? router_id Lvs_devel
}
?
Vrrp_instance Vi_1 {
? State backup??? #两台配置此处均是BACKUP
? Interface eth0
? VIRTUAL_ROUTER_ID 51
? Priority 100??? #优先级, change to 90 for the other one?
? Advert_int 1
? Nopreempt????? #不抢占, set only on high priority machines, with low priority machines not set
? Authentication {
? ? ? Auth_type PASS
? ? ? Auth_pass 1111
? }
? virtual_ipaddress {
? ? ? 10.1.1.25
? }
}
?
Virtual_server 10.1.1.25 3306 {
? Delay_loop 6
? Lb_algo WRR
? Lb_kind DR
? Persistence_timeout 50???? #会话保持时间?
? Protocol TCP
?
Real_server 10.1.1.20 3306 {
? ? ? Weight 3
? ? ? Notify_down/tmp/nimei.sh?? #检测到mysql服务挂了就执行这个脚本 (script to write on its own ha)
? ? ? Tcp_check {
? ? ? Connect_timeout 10???? #连接超时时间
? ? ? Nb_get_retry 3?????? #重连次数?
? ? ? Delay_before_retry 3??? #重连间隔时间
? ? ? Connect_port 3306???? #健康检查端口?
? ? ? ? }
? ? ? }
}

Configuration file for 10.1.1.21
! Configuration File for Keepalived
Global_defs {
? Notification_email {
? ? [Email protected]
? ? [Email protected]
? ? [Email protected]
? }
? Notification_email_from [email protected]
? Smtp_server 127.0.0.1
? Smtp_connect_timeout 30
? router_id Lvs_devel
}
?
Vrrp_instance Vi_1 {
? State backup
? Interface eth0
? VIRTUAL_ROUTER_ID 51
? Priority 90
? Advert_int 1
? Authentication {
? ? ? Auth_type PASS
? ? ? Auth_pass 1111
? }
? virtual_ipaddress {
? ? ? 10.1.1.25
? }
}
?
Virtual_server 10.1.1.25 3306 {
? Delay_loop 6
? Lb_algo WRR
? Lb_kind DR
? Persistence_timeout 50
? Protocol TCP
?
Real_server 10.1.1.21 3306 {
? ? ? Weight 3
? ? ? notify_down/tmp/nimei.sh
? ? ? Tcp_check {
? ? ? Connect_timeout 10
? ? ? Nb_get_retry 3
? ? ? Delay_before_retry 3
? ? ? Connect_port 3306
? ? ? ? ? }
? ? ? }
}?
Write a script that monitors whether the MySQL service is dead or not, and script it according to the location of the configuration file above.
vim/tmp/nimei.sh
#!/bin/sh?
Pkill keepalived
The script is simple, and the goal is to trigger this script when Keepalived detects that the MySQL service is dead, kill the keepalived process, and let another machine take over
Good modification after starting keeplived service
This entire cluster is built to complete
Third, testing
Find a machine connect MySQL with virtual IP
[Email protected] html]# mysql-uab? -H 10.1.1.25-p123
Welcome to the MySQL monitor.? Commands End With; or \g.
Your MySQL Connection ID is 736
Server Version:5.1.66-log Source Distribution
?
Copyright (c) 2000, 2012,? oracle?and/or its affiliates. All rights reserved.
?
Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.
Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.
Mysql>

Keepalived+mysql for High Availability