mysql启用keepalive_实操：如何使用Keepalived构建MySQL高可用架构

今天，咱们就实操一下使用keepalived如何构建MySQL高可用性架构

一、 系统信息

二、 安装部署mysql

1、安装两台服务器

现在linux安装时，好多安装了mysql，下面以从节点安装为例进行说明

[root@mytest2 mysqldb]# rpm -qa | grep mysql

对显示出来的安装包，采用如下方式进行卸载

[root@mytest2 u02]# rpm -qa | grep mysql

mysql-libs-5.1.66-2.el6_3.x86_64

mysql-5.1.66-2.el6_3.x86_64

mysql-server-5.1.66-2.el6_3.x86_64

qt-mysql-4.6.2-25.el6.x86_64

You have new mail in /var/spool/mail/root

[root@mytest2 u02]# rpm -ev --nodeps mysql-libs-5.1.66-2.el6_3.x86_64[root@mytest2 u02]# rpm -ev --nodeps mysql-5.1.66-2.el6_3.x86_64[root@mytest2 u02]# rpm -ev --nodeps mysql-server-5.1.66-2.el6_3.x86_64[root@mytest2 u02]# rpm -ev --nodeps qt-mysql-4.6.2-25.el6.x86_64

2、准备mysql安装包

下载网址：http://dev.mysql.com，然后选择cluster，然后在网页中出现的选择平台中，

选择linux generic！在这里选择所需要tar包，我这里用的是mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

3、安装部署mysql

1) 安装相关依赖

yum -y groupinstall "Development tools" //是开发工具的意思。如果你源码安装软件就需要安装这个。

同时也要安装Development Libraries ，这个是开发库！yum -y install “Development Libraries”

# yum -y install ncurses ncurses-devel openssl-devel bison gcc gcc-c++ make

2)解压mysql的tar包

# tar -xvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

3) 复制相关目录

# cp -rf mysql-5.7.26-linux-glibc2.12-x86_64 /mysqlsoft/mysql

4)创建mysql用户

# useradd mysql

5)创建mysql的数据目录和日志目录

# mkdir -p /mysqldata/mysql3307

# mkdir -p /mysqldata/logs

6)修改相关目录权限

chown mysql:mysql -R /mysqldata/mysql3307

chown mysql:mysql -R /mysqldata/logs

chown mysql:mysql -R /mysqlsoft/mysql

7)编辑配置文件(省略)

# vi /etc/my3307.cnf

8)初始化mysql

# /mysqlsoft/mysql/bin/mysqld --defaults-file=/etc/my3307.cnf --initialize-insecure --explicit_defaults_for_timestamp --basedir=/mysqlsoft/mysql --datadir=/mysqldata/mysql3307/ --user=mysql

9)启动mysql

#/mysqlsoft/mysql/bin/mysqld_safe --defaults-file=/etc/my3307.cnf --datadir=/mysqldata/mysql3307/ --user=mysql &

10)进入mysql

/mysqlsoft/mysql/bin/mysql -uroot -p -S /mysqldata/tmp/mysql.sock

11)开启GTID

set global gtid_mode=1;

set global gtid_mode=ON_PERMISSIVE;

set global ENFORCE_GTID_CONSISTENCY=on;

set global gtid_mode=ON;

show variables like '%gtid%';

三、 部署双主架构

1、关闭或者配置防火墙

[root@mytest1 ~]# service iptables status

iptables: Firewall is not running.

如果开启的，使用如下操作

[root@mytest1 ~]# service iptables stop

[root@mytest1 ~]# chkconfig iptables off

2、修改MySQL配置文件

Mytest1的配置：

[root@mytest1 ~]# cat /etc/my3307.cnf | grep -v "#"

[mysqld]

log-bin = mysql-bin

server-id = 1113306 (根据实际修改ip后两位+端口号)

datadir = /u01/mysql/data/mysqldb

auto_increment_increment = 2

auto_increment_offset = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

其它的参数，本文不在这里列出来，主要是把主要的参数加进去

[root@mytest2 ~]# cat /etc/my3307.cnf | grep -v "#"

[mysqld]

log-bin = mysql-bin

server-id = 1123306 (根据实际修改ip后两位+端口号)

datadir = /u01/mysql/data/mysqldb

auto_increment_increment = 2

auto_increment_offset = 2

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

划重点：

在主主同步配置时，需要将两台服务器的auto_increment_increment增长量都配置为2，而要把auto_increment_offset分别配置为1和2.这样才可以避免两台服务器同时做更新时自增长字段的值之间发生冲突。

3、创建授权用户

3.1 配置MySQL双主复制

Ø 创建复制账号

节点192.168.0.112

mysql> create user 'repl'@'%' identified by 'repl';

mysql> grant replication slave on *.* to 'repl'@'%';

mysql> flush privileges;

节点192.168.0.112

mysql> create user 'repl'@'%' identified by 'repl';

mysql> grant replication slave on *.* to 'repl'@'%';

mysql> flush privileges;

Ø 配置主主复制

节点192.168.0.112到节点192.168.0.113主从复制

(节点192.168.0.113执行)

mysql> change master to

master_host='192.168.0.112',

master_user='repl',

master_password='repl',

master_port=3307,

master_auto_position=1;

节点192.168.0.113到节点192.168.0.112主从复制

(节点192.168.0.112执行)

mysql> change master to

master_host='192.168.0.113',

master_user='repl',

master_password='repl',

master_port=3307,

master_auto_position=1;

Ø 配置半同步复制

mysql> install plugin rpl_semi_sync_master soname'semisync_master.so';

mysql> set global rpl_semi_sync_master_enabled=1;

mysql> set global rpl_semi_sync_master_timeout=1000;

(两节点都要执行)

Ø 启动slave

mysql> STOP SLAVE IO_THREAD;

mysql> start slave IO_THREAD;

(两节点都要执行)

这里进入本文重点

四、 部署keepalived

1、 安装缺失的包

# yum install -y popt popt-devel openssl openssl-devel gcc

# yum install -y curl gcc openssl-devel libnl3-devel net-snmp-devel

Loaded plugins: fastestmirror, refresh-packagekit

Loading mirror speeds from cached hostfile

Setting up Install Process

Package popt-1.13-7.el6.x86_64 already installed and latest version

Package popt-devel-1.13-7.el6.x86_64 already installed and latest version

Package openssl-1.0.0-27.el6.x86_64 already installed and latest version

Package openssl-devel-1.0.0-27.el6.x86_64 already installed and latest version

Package gcc-4.4.7-3.el6.x86_64 already installed and latest version

Nothing to do

如果没有安装，就会自动安装上去。

2、 解压重编译keepalived

# tar -zxf keepalived-1.3.2.tar.gz (上传的路径为/tmp)

# cd keepalived-1.3.2

# ./configure --prefix=/usr/local/keepalived# make && make install

3、 拷贝文件，并进行相应的配置

在keepalived 1.3.2中，部分系统所需使用的脚本文件在源码安装包中，即脚本存在位置为：/tmp/keepalived-1.3.2/keepalived/etc。

复制可执行文件： 启动文件

cp /mysqlsoft/keepalived/sbin/keepalived /usr/sbin/

复制系统服务文件：

cp /home/appuser/keepalived-2.0.2/keepalived/etc/init.d/keepalived /etc/rc.d/init.d/

复制参数文件：

cp /mysqlsoft/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

创建配置文件/脚本文件目录:

mkdir -p /etc/keepalived/scripts

日志文件：

mkdir -p /mysqlsoft/keepalived/log

注：编译keepalived的操作同时也在另一台机器上执行，其方式完全相同

目录赋权限

chown -R mysql:mysql /etc/keepalived

chown -R mysql:mysql /mysqlsoft/keepalived

chown -R mysql:mysql /etc/keepalived/scripts

chown mysql:mysql -R /mysqlsoft/keepalived/log

4、 keepalive日志的位置设置

vi /etc/sysconfig/keepalived

KEEPALIVED_OPTIONS=”-D -d -S 0”

vi /etc/rsyslog.conf

echo "local0.* /mysqlsoft/keepalived/log/keepalived.log" >>/etc/rsyslog.conf

systemctl restart rsyslog

keepalive的日志位置为：/mysqlsoft/keepalived/log/keepalived.log

5、 编辑keepalived.conf

vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {

router_id mysql_ha

}

vrrp_script chk_mysql {

script "/etc/keepalived/chk_mysql.sh"

interval 1

weight -20

}

vrrp_instance VI_1 {

state BACKUP

interface ens192

virtual_router_id 41

priority 90

nopreempt

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

track_script {

chk_mysql

}

virtual_ipaddress {

192.168.0.114/24

}

}

附chk_mysql.sh ，改sh只简单判断主库mysqld进程是否存在作为切换从库的条件

[root@mysql-02 keepalived]# cat chk_mysql.sh

#!/bin/bash

A=`ps -C mysqld --no-header |wc -l`if [ $A -eq 0 ];then

systemctl stop keepalived.service

fi

6、 启动keepalived

1)分别在两台机器上启动

[root@mytest2 keepalived]# systemctl start keepalived.service

Starting keepalived: [ OK ]

通常情况下，先启动mysql，再启动keepalived

2)#ip a 检查浮动ip在哪台机器上服务

#ip a

7、 通过连接vip和停主库进行切换演练测试

# mysql -uutest -pusertest -h192.168.0.114

mysql> show variables like 'server%';

查询实际访问的mysql数据库。

【结语】

1. 本文主要演示了使用Keepalived构建MySQL高可用架构，具有实操性；

2. 使用Keepalived构建MySQL高可用架构是较为简单的一种方式；可满足刚开始使用MySQL的用户构建简单的高可用性架构。但Keepalived没有日志自动补齐功能，无法将最新的binlog应用到存活节点，容易产生数据丢失；

3. 鉴于上述Keepalived的缺点，本文推荐使用MHA一主二从，它可以自动补齐日志使得一致性有保证，部署的时候不需要改变原有架构就可以使用，后续将会出推文讲述MHA的部署实施。