MySQL主主互备结合keepalived实现高可用

POOPE

　　试验环境：
　　master：192.168.1.210（CentOS6.5）
　　slave：192.168.1.211（CentOS6.5）
　　VIP：192.168.1.208
　　

　　MySQL主主互备模式配置

　　step1：Master服务的/etc/my.cnf配置

[mysqld]
basedir = /usr/local/mysql
datadir = /var/lib/mysql
port = 3306
socket = /var/lib/mysql/mysql.sock
server_id = 1
log-bin = mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%   #指定不需要复制的库，mysql.%表示mysql库下的所有对象
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

　　step2：Slave服务的/etc/my.cnf配置

[mysqld]
basedir = /usr/local/mysql
datadir = /var/lib/mysql
port = 3306
socket = /var/lib/mysql/mysql.sock
server_id = 2
log-bin = mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

　　step3：重启两台主从mysql服务

[root@master ~]# service mysqld restart
Shutting down MySQL..                    [  OK  ]
Starting MySQL.                      [  OK  ]
[root@slave ~]# service mysqld restart
Shutting down MySQL..                    [  OK  ]
Starting MySQL.                      [  OK  ]

　　

　　step4：查看主从的log-bin日志状态
　　记录File和Position的值

[root@master ~]# mysql -uroot -ppasswd -e 'show master status'
Warning: Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |    414 |        |          |           |
+------------------+----------+--------------+------------------+-------------------+

[root@slave ~]# mysql -uroot -ppasswd -e 'show master status'
Warning: Using a password on the command line interface can be insecure.
+------------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |    414 |        |          |           |
+------------------+----------+--------------+------------------+-------------------+

　　

　　step5：创建主从同步replication用户

　　1、master

mysql> grant replication slave on *.* to 'replication'@'192.168.1.211' identified by 'replication';
mysql> flush privileges;
mysql> change master to
  -> master_host='192.168.1.211',
  -> master_user='replication',
  -> master_password='replication',
  -> master_port=3306,
  -> master_log_file='mysql-bin.000001',
  -> master_log_pos=414;
mysql> start slave;

　　2、slave

mysql> grant replication slave on *.* to 'replication'@'192.168.1.210' identified by 'replication';
mysql> flush privileges;
mysql> change master to
  -> master_host='192.168.1.210',
  -> master_user='replication',
  -> master_password='replication',
  -> master_port=3306,
  -> master_log_file='mysql-bin.000001',
  -> master_log_pos=414;
mysql> start slave;

　　
同步失败可能需要停止或重设slave

　　mysql> stop slave;

　　mysql> reset slave;

　　

　　step6：分别在master和slave上查看slave状态，验证是否成功配置主主复制模式

　　1、master

　　2、slave

　　slave状态同步过程可能需要重启MySQL服务

　　[root@master ~]# service mysqld restart
[root@slave ~]# service mysqld restart

　　

　　step7：验证，在master上创建test1数据库，slave上查看是否同步
　　1、master上创建test1数据库

[root@master ~]# mysql -uroot -ppasswd -e 'create database test1'

　　2、slave上查看是否同步创建test1

[root@slave ~]# mysql -uroot -ppasswd -e 'show databases'
+--------------------+
| Database       |
+--------------------+
| information_schema |
| mysql        |
| performance_schema |
| test1        |
+--------------------+

　　

　　安装和配置keepalived实现MySQL双主高可用
　　step1：安装keepalived
　　方法一：使用yum安装keepalived，需要安装epel-release源

　　[root@master ~]# rpm -ivh http://mirrors.opencas.cn/epel/6/i386/epel-release-6-8.noarch.rpm
[root@slave ~]# rpm -ivh http://mirrors.opencas.cn/epel/6/i386/epel-release-6-8.noarch.rpm

　　[root@slave ~]# yum -y install keepalived

　　查看keepalived相关目录

[root@slave ~]# ls /usr/sbin/keepalived 
/usr/sbin/keepalived
[root@slave ~]# ls /etc/init.d/keepalived 
/etc/init.d/keepalived
[root@slave ~]# ls /etc/keepalived/keepalived.conf 
/etc/keepalived/keepalived.conf

　　

　　方法二：从keepalived官方网站http://www.keepalived.org下载源代码包编译安装

　　1、下载keepalived最新版

　　[root@master ~]# wget http://www.keepalived.org/software/keepalived-1.2.19.tar.gz

　　[root@slave ~]# wget http://www.keepalived.org/software/keepalived-1.2.19.tar.gz
　　2、安装keepalived依赖软件包

　　[root@master ~]# yum install pcre-devel openssl-devel popt-devel libnl-devel
　　3、解压并安装keepalived

[root@master ~]# tar zxf keepalived-1.2.19.tar.gz 
[root@master ~]# cd keepalived-1.2.19
[root@master keepalived-1.2.19]# ./configure --prefix=/usr/local/keepalived 
--sysconf=/etc --with-kernel-dir=/usr/src/kernels/2.6.32-431.el6.x86_64

[root@master keepalived-1.2.19]# make
[root@master keepalived-1.2.19]# make install

　　查看keepalived相关的文件

[root@master keepalived-1.2.19]# ls /etc/keepalived/
keepalived.conf  samples
[root@master keepalived-1.2.19]# ls /etc/init.d/keepalived 
/etc/init.d/keepalived

　　链接/usr/local/keepalived/sbin/keepalived到/sbin/目录

[root@master keepalived-1.2.19]# ln -s /usr/local/keepalived/sbin/keepalived /sbin/

　　设置keepalived启动级别

[root@master keepalived-1.2.19]# chkconfig --add keepalived
[root@master keepalived-1.2.19]# chkconfig --level 35 keepalived on

　　

　　step2：配置keepalived
　　1、Master的keepalived.conf配置文件

! Configuration File for keepalived
global_defs {
   notification_email {
   root@huangmingming.cn
   741616710@qq.com
   }
   notification_email_from keepalived@localhost  
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}
vrrp_instance HA_1 {
  state BACKUP        #master和slave都配置为BACKUP
  interface eth0        #指定HA检测的网络接口
  virtual_router_id 80    #虚拟路由标识，主备相同
  priority 100        #定义优先级，slave设置90
  advert_int 1        #设定master和slave之间同步检查的时间间隔
  nopreempt           #不抢占模式。只在优先级高的机器上设置即可
  authentication {
    auth_type PASS
    auth_pass 1111
  }
  virtual_ipaddress {         #设置虚拟IP，可以设置多个，每行一个
    192.168.1.208/24 dev eth0     #MySQL对外服务的IP，即VIP
  }
}
virtual_server 192.168.1.208 3306 {
  delay_loop 2          #每隔2秒查询real server状态
  lb_algo wrr           #lvs 算法
  lb_kinf DR            #LVS模式（Direct Route）
  persistence_timeout 50
  protocol TCP
  real_server 192.168.1.210 3306 {  #监听本机的IP
    weight 1
    notify_down /usr/local/keepalived/bin/mysql.sh
    TCP_CHECK {
    connect_timeout 10     #10秒无响应超时
    bingto 192.168.1.208
    nb_get_retry 3
    delay_before_retry 3
    connect_port 3306
    }
  }
}

　　keepalived检测脚本，当其中一台MySQL服务出现故障down掉时，实现自动切换到正常的MySQL服务器继续提供服务

[root@master ~]# vim /usr/local/keepalived/bin/mysql.sh
#!/bin/bash
pkill keepalived

　　
　　2、Slave的keepalived.conf配置文件

! Configuration File for keepalived
global_defs {
   notification_email {
   root@huangmingming.cn
   741616710@qq.com
   }
   notification_email_from keepalived@localhost
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}
vrrp_instance HA_1 {
  state BACKUP        #master和slave都配置为BACKUP
  interface eth0        #指定HA检测的网络接口
  virtual_router_id 80    #虚拟路由标识，主备相同
  priority 90        #定义优先级，slave设置90
  advert_int 1        #设定master和slave之间同步检查的时间间隔
  authentication {
    auth_type PASS
    auth_pass 1111
  }
  virtual_ipaddress {         #设置虚拟IP，可以设置多个，每行一个
    192.168.1.208/24 dev eth0     #MySQL对外服务的IP，即VIP
  }
}
virtual_server 192.168.1.208 3306 {
  delay_loop 2
  lb_algo wrr
  lb_kinf DR
  persistence_timeout 50
  protocol TCP
  real_server 192.168.1.211 3306 {  #监听本机的IP
    weight 1
    notify_down /usr/local/mysql/bin/mysql.sh
    TCP_CHECK {
    connect_timeout 10
    bingto 192.168.1.208      
    nb_get_retry 3
    delay_before_retry 3
    connect_port 3306
    }
  }
}

　　

　　step3：授权VIP的root用户权限

　　授权远程主机可以通过VIP登录MySQL，并测试数据复制功能

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

　　step4：测试keepalived高可用功能
　　1、远程主机登录通过VIP192.168.1.208登录MySQL，查看MySQL连接状态

mysql> show variables like 'hostname%';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| hostname    | master |
+---------------+--------+
1 row in set (0.00 sec)

　　从上面查看的结果看样看出在正常情况下连接的是master

　　

　　2、故障测试，停止master的MySQL服务，再次查看是否转移至slave服务器上

[root@master ~]# service mysqld stop
Shutting down MySQL.... SUCCESS!

mysql> show variables like 'hostname%';
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> show variables like 'hostname%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:  1268
Current database: *** NONE ***
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| hostname    | slave |
+---------------+-------+
1 row in set (0.01 sec)

　　由测试结果可以看出，keepalived成功转移MySQL服务

　　

　　

　　

[MySQL] MySQL主主互备结合keepalived实现高可用

相关帖子

Mysql8导入数据到Mysql5.7的实现步骤

MySQL数据库触发器从小白到精通

一篇带给你MySQL索引知识详解

MySQL视图和索引专篇精讲

阿里面试MySQL死锁问题的处理

mysql中drop、truncate与delete的区别详析

javascript怎么实现窗口置顶

JavaScript怎样实现点击出现图片效果

使用原生querySelectorAll和forEach来实现点击切换

免插件、DZ内置JS轻松实现外链访问二次确认对话提示框

POOPE LV4