MySQL - Dual-machine HA based on Keepalived

1. Environment description:

OS: CentOS6.5_X64
MASTER: 192.168.0.202
BACKUP: 192.168.0.203
VIP: 192.168.0.204

2. Configure two Mysql master-master synchronization

Regarding the installation of MySQL, you can also refer to "MySQL - CentOS6.5 Compile and Install MySQL5.6.16". Master-master synchronization is based on master-slave synchronization. Configuring the slave server as the master of the previous master server is equivalent to setting the original slave as the master of the original master based on the original master-slave synchronization. You can also refer to "MySQL - MS master-slave replication (read-write separation)" ) Implementation", after setting A as B's Master and B as A's Slave, then set B as A's Master and A as B's Slave.

[root@masterr ~]# yum install mysql-server mysql -y  
[root@masterr ~]# service mysqld start  
[root@masterr ~]# mysqladmin -u root proot  
[root@masterr ~]# vi /etc/my.cnf  #开启二进制日志，设置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                       #忽略所有复制产生的错误       
[root@masterr ~]# service mysqld restart

#First check the log bin log and pos value location

The master configuration is as follows:

[root@ master ~]# mysql -u root -proot  
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:

[root@backup ~]#  mysql -u root -proot  
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;

# After the master-master synchronization configuration is completed, check the synchronization status Slave_IO and Slave_SQL. If it is YES, it means the master-master synchronization is successful.

In the master insert data test:

Check in the backup whether the synchronization is successful:

You can see that it has been successfully synchronized. The data in the user table is also inserted into the backup and synchronized in the same way. The dual master is successfully completed.

3. Configure keepalived to implement hot backup

[root@backup ~]# yum install -y pcre-devel openssl-devel popt-devel #安装依赖包

[root@masterr ~]# wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz  
[root@masterr ~]# tar zxvf keepalived-1.2.7.tar.gz  
[root@masterr ~]# cd keepalived-1.2.7  
[root@masterr ~]#./configure --prefix=/usr/local/keepalived  
make

#Configure keepalived as a system service

[root@masterr ~]# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/  
[root@masterr ~]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/  
[root@masterr ~]# mkdir /etc/keepalived/  
[root@masterr ~]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/  
[root@masterr ~]# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

[root@masterr ~]# vi /etc/keepalived/keepalived.conf  
! Configuration File forkeepalived  
global_defs {  
notification_email {  
test@sina.com  
 }  
notification_email_from  admin@test.com  
smtp_server 127.0.0.1  
smtp_connect_timeout 30  
router_id MYSQL_HA      #标识，双主相同  
 }  
vrrp_instance VI_1 {  
 state BACKUP           #两台都设置BACKUP  
 interface eth0  
 virtual_router_id 51       #主备相同  
 priority 100           #优先级，backup设置90  
 advert_int 1  
 nopreempt             #不主动抢占资源，只在master这台优先级高的设置，backup不设置  
 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算法，用不到，我们就关闭了  
 #lb_kind DR              #LVS模式，如果不关闭，备用服务器不能通过VIP连接主MySQL  
 persistence_timeout 50  #同一IP的连接60秒内被分配到同一台真实服务器  
 protocol TCP  
 real_server 192.168.0.202 3306 {   #检测本地mysql，backup也要写检测本地mysql  
 weight 3  
 notify_down /usr/local/keepalived/mysql.sh    #当mysq服down时，执行此脚本，杀死keepalived实现切换  
 TCP_CHECK {  
 connect_timeout 3    #连接超时  
 nb_get_retry 3       #重试次数  
 delay_before_retry 3 #重试间隔时间  
  }  
}

[root@masterr ~]# vi /usr/local/keepalived/mysql.sh  
#!/bin/bash  
pkill keepalived  
[root@masterr ~]# chmod +x /usr/local/keepalived/mysql.sh  
[root@masterr ~]# /etc/init.d/keepalived start

#The backup server only modifies the priority to 90 and nopreempt If not set, real_server sets the local IP.

#Authorize two Mysql servers to allow root remote login for login testing on other servers!

mysql> grant all on *.* to'root'@'192.168.0.%' identified by 'root';  
mysql> flush privileges;

4. Test high availability

1) Connect through VIP through Mysql client to see if the connection is successful.
2) Stop the master mysql service and check whether it can switch to it normally. You can use the ip addr command to check which server the VIP is on.

3) You can see the master/backup switching process by checking the /var/log/messges log
4) After the master server recovers from failure, does it actively seize resources? Become an active server.
Note: The order of service startup: start MySQL first, then Keepalived.

The above is the content of MySQL - dual-machine HA based on Keepalived. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!