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