MySQL high availability based on keepalived

• Objective

Because of the recent use of MySQL database, but the company's server and business is limited, so only a single MySQL. So, the problem is obvious, if this MySQL is broken, it will affect the entire company's business, so consider doing a high-availability MySQL program. Currently, MySQL has a lot of high-availability programs, here choose Keepalived+mysql to achieve high availability.

• Environment Introduction

ID	OS	IP	Role
Node1	Centos6.5_x64	192.168.1.159	Master
Node2	Centos6.5_x64	192.168.1.160	Slave

• MySQL Master master configuration

# Execute in master and slave[Root@node1~]# yum install-y mysql-server MySQL #安装Mysql[Root@node1~]# service Mysqld start[Root@node1~]# mysqladmin-u root password 123.com #为Mysql的root用户设置密码[Root@node1~]# vim/etc/my.cnf #编辑Mysql配置文件, add the following:[Mysqld]server-id =1                    #Slave这台设置2Log-bin = Mysql-binbinlog-ignore-db = Mysql,information_schema#忽略写入binlog日志的库Auto-increment-increment =2             #字段变化增量值Auto-increment-offset =1              #初始字段ID为1Slave-skip-errors = All[root@node1~]# service Mysqld Restart

Note: Check the log bin log and POS value location on Master and slave first

[root@node1 ~]# mysql -u root -p123.commysql> show master status;

[root@node2 ~]# mysql -u root -p123.commysql> show master status;

Note: The operation on Master is as follows: [Root@node1~]# mysql-u Root-p123.comMysql> Grant All on*. * To' Root '@'% 'Identified by ' 123.com '  withGrant option;mysql> flush privileges;mysql> Change Master to -master_host=' 192.168.1.160 ', -Master_user=' Root ', -master_password=' 123.com ', -master_log_file=' mysql-bin.000001 ',# According to the actual log bin of the end of the fill -master_log_pos=106;# According to the actual POS value of the end of the completion

Note: operation on slave is as follows: [Root@node2~]# mysql-u Root-p123.comMysql> Grant All on*. * To' Root '@'% 'Identified by ' 123.com '  withGrant option;mysql> flush privileges;mysql> Change Master to -master_host=' 192.168.1.159 ', -Master_user=' Root ', -master_password=' 123.com ', -master_log_file=' mysql-bin.000001 ',# According to the actual log bin of the end of the fill -master_log_pos=335;# According to the actual POS value of the end of the completion

主主同步配置完毕，在Master和Slave上分别查看同步状态，Slave_IO和Slave_SQL是YES说明主主同步成功。mysql> show slave status\G

• Master Master Sync Test

Use the client to connect to any MySQL, create a library, create a table, insert fields, delete tables, deleted the library and other operations, another MySQL can be synchronized.

在这里，进行简单一些的测试，先在Master上创建一个新库，名为main

然后到Slave上查看是否有名为main的库

接下来在slave把名为main的库删了，会发现master上的main也没有了。到此，主主同步完成，

• Keepalived High-availability configuration

Note: inMasterAndSlavePerformkeepalivedMust andMysqlInstalled on the same machine# Install keepalived[Root@node1~]# yum install-y pcre-devel openssl-devel popt-devel[Root@node1~]# wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz[Root@node1~]# tar zxvf keepalived-1.2.7.tar.gz[Root@node1~]# CD keepalived-1.2.7[Root@node1~]#./configure--prefix=/usr/local/keepalived[Root@node1~]# make && make install# Configure keepalived as a system service[Root@node1~]# cp/usr/local/keepalived/etc/rc.d/init.d/keepalived/etc/init.d/[Root@node1~]# cp/usr/local/keepalived/etc/sysconfig/keepalived/etc/sysconfig/[Root@node1~]# mkdir/etc/keepalived/[Root@node1~]# cp/usr/local/keepalived/etc/keepalived/keepalived.conf/etc/keepalived/[Root@node1~]# cp/usr/local/keepalived/sbin/keepalived/usr/sbin/

# Modify the keepalived configuration file on Master[Root@node1~]# vim/etc/keepalived/keepalived.conf! Configuration File forkeepalivedglobal_defs {router_id mysql_ha}vrrp_instance vi_1 { StateBACKUP# Two configurations here are backupInterface ETH0virtual_router_idWuyiPriority -   # Priority, another change toAdvert_int1Nopreempt# no preemption, only set on high priority machines, low priority machines are not setAuthentication {Auth_type PASS Auth_pass1111} virtual_ipaddress {192.168.1.208     # VIP}}virtual_server192.168.1.208 3306{# Specific settings for VIPDelay_loop2   # Check the Real_server status once every 2 seconds    #lb_algo WRR # LVS algorithm    #lb_kind DR # LVS ModePersistence_timeout -   # Session hold TimeProtocol TCP Real_server192.168.1.159 3306{Weight3notify_down/usr/Local/keepalived/mysql.sh#检测到服务down后执行的脚本Tcp_check {connect_timeoutTen    # Connection time-outNb_get_retry3       # Number of re-connectDelay_before_retry3   # re-connect interval timeConnect_port3306   # Health Check Port}     }}

# Modify the keepalived configuration file on slave[Root@node2~]# vim/etc/keepalived/keepalived.conf! Configuration File forkeepalivedglobal_defs {router_id mysql_ha}vrrp_instance vi_1 { StateBACKUP interface ETH0virtual_router_idWuyiPriority -Advert_int1Authentication {Auth_type PASS Auth_pass1111} virtual_ipaddress {192.168.1.208}}virtual_server192.168.1.208 3306{Delay_loop2    #lb_algo WRR    #lb_kind DRPersistence_timeout -Protocol TCP Real_server192.168.1.160 3306{Weight3notify_down/usr/Local/keepalived/mysql.sh Tcp_check {connect_timeoutTenNb_get_retry3Delay_before_retry3Connect_port3306}     }}

# 配置故障切换脚本,Master和Slave上执行[[email protected] ~]# vim /usr/local/keepalived/mysql.sh#!/bin/shpkill keepalived[[email protected] ~]# chmod +x /usr/local/keepalived/mysql.sh   

# 此前两台Mysql已经启动，这里只用启动Keepalived(Master和Slave上执行)[root@node1 ~]# service keepalived start

• High Availability Testing

# 在Master上查看VIP是否存在[root@node1 ~]# ip addr

# 停止Master上的Keepalived，查看VIP是否漂移到了Slave上[root@node1 ~]# service keepalived stop[root@node2 ~]# ip addr

# 开启Master上的Keepalived，查看Master是否抢占了VIP，没有抢占Slave的VIP说明成功[root@node1 ~]# service keepalived start

# Stop MySQL on slave, find slave on keepalived will stop automatically, and VIP has drifted to master[Root@node2~]# service Mysqld Stop[Root@node2~]# ps-ef |grep mysqldRoot4589   2159  0  .:xxpts/2    xx:xx:xx grepMysqld[root@node2~]# ps-ef |grep keepalivedRoot4591   2159  0  .:xxpts/2    xx:xx:xx grepKeepalived[root@node1~]# IP Addr

• Summarize

To this, the MySQL high availability is all done based on the keepalived implementation. This solution can provide MySQL with fault tolerance, the disadvantage is also obvious, only one MySQL service, another can only play a backup function, can not achieve similar to the effect of load balancing. Of course, you can add some middleware to complete load balancing, read and write separation functions, here is unknown explanation ...

MySQL high availability based on keepalived