MySQL master replication +keepalived to create a highly available MySQL cluster

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

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/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# disabling Symbolic-links is recommended To prevent assorted security Riskssymbolic-links=0log-bin=binlog #开启binlog功能log-bin-index=binlog.indexsync_binlog= 0server_id = 1 #两台机器不能重复, a 11 2 is good [mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

2. Configure the sync account on two machines respectively

10.1.1.20 on the Machine: [[Email protected] ~]# mysqlwelcome to  the MySQL monitor. Commands end with; or \g.Your MySQL  connection id is 2server version: 5.0.77-log sourcedistribution type  ' Help; '  or  ' \h '  for help. Type  ' \c '  toclear the buffer. mysql>  grant replication slave on *.* to ' ab ' @ '% '  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 machine: [[email protected] ~]# Mysqlwelcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 2Server 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

10.1.1.20 on 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)  10.1.1.21 on 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    #执行这命令后   Pay attention to the following two parameters, it must be all right slave_io_running: yesslave_sql_ Running: yes

Do the same in turn

10.1.1.21 on 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)  10.1.1.20 on 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    #执行这命令后   Pay attention to the following two parameters, it must be all right slave_io_running: yesslave_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 can check whether the synced account is created normal!

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 keepalivedglobal_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       # Both configurations here are backup    interface eth0   virtual_router_id 51    priority 100        #优先级, the other one to 90      advert_int 1   nopreempt           #不抢占, only set on high priority machines, 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 TCPreal_server 10.1.1.20 3306 {        weight 3       notify_down / tmp/nimei.sh     #检测到mysql服务挂了就执行这个脚本 (scripts to write on their own)        tcp _check {       connect_timeout 10          #连接超时时间        nb_get_retry 3              #重连次数          delay_before_retry 3        #重连间隔时间        connect_port 3306           #健康检查端口            }       }}

10.1.1.21 Configuration file

! configuration file for keepalivedglobal_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 TCPreal_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-p123welcome to the MySQL monitor. Commands End With;  or \g.your MySQL connection ID is 736Server version:5.1.66-log Source distributioncopyright (c), Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names trademarks of their respectiveowners. Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.mysql>

This success is connected, then you can shut down a machine, or a machine of the MySQL service, see if you can connect!!

Thank you!!

MySQL master replication +keepalived to create a highly available MySQL cluster