mysql + keepalived 双主高可用

Mysql + keepalived双主高可用集群

 

环境：centos6.4 两台

      服务器1：192.168.92.128

      服务器2：192.168.92.130

Mysql5.5

关闭防火墙

service iptables stop

Chkconfig iptables off

Setenforce 0

修改/etc/selinux/config

将SELINUX=enforcing改为SELINUX=disabled

 两台同时创建mysql用户

groupadd mysql

groupadd mysqluseradd -s /sbin/nologin -g mysql -M mysql

 

 在这里采用的是mysql二进制源码包

直接解压就行，不用编译安装

 

替换/etc/my.cnf文件

建立mysql数据文件目录

chown -R mysql.mysql /usr/local/mysql

/bin/cp support-files/my-small.cnf /etc/my.cnf

mkdir -p /usr/local/mysql/data

 

 安装mysql依赖包libaio-devel

yum -y install libaio-devel

 

初始化

mysql/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql

 

 拷贝mysql启动脚本

cp support-files/mysql.server /etc/init.d/mysqld

chmod +x /etc/init.d/mysqld

 

 设置mysql开机启动

把/etc/init.d/mysqld start 命令放到/etc/rc.local文件里

设置mysql环境变量

vim /etc/profile

添加export PATH=$PATH:/usr/local/mysql/bin

运行source /etc/profile

 

 

设置mysql数据库root的登录密码

mysqladmin -u root password 'admin'

 

 

修改两台的mysql配置文件/etc/my.cnf

两台的mysql均要开启binlog日志功能

把其中一台配置文件里的server-id  = 1改为server-id = 2

Master1配置

log-bin = mysql-bin

binlog_format = mixed

server-id = 1

relay-log = relay-bin

relay-log-index = slave-relay-bin.index

auto-increment-increment = 2

auto-increment-offset = 1

 

重启mysql

Service mysqld restart

Master2配置

log-bin = mysql-bin

binlog_format = mixed

server-id = 2

relay-log = relay-bin

relay-log-index = slave-relay-bin.index

auto-increment-increment = 2

auto-increment-offset = 2

 

重启mysql

Service mysqld restart

 

 在192.168.92.128服务器新建一个用户赋予“REPLICATION SLAVE”的权限

GRANT  REPLICATION SLAVE ON *.* TO 'replication'@'192.168.92.130' IDENTIFIED  BY 'replication'; 

刷新权限

flush  privileges; 

查看下log bin日志和pos值位置

Show master status;

 

 mysql> change  master to

    -> master_host='192.168.92.130',

    -> master_user='replication',

    -> master_password='replication',

    -> master_log_file='mysql-bin.000003',

-> master_log_pos=107;对端状态显示值

 

如果这里执行失败可以退出mysql

在/usr/local/mysql/data/下有两个文件可能被占用

relay-log.info

master.info

把这两个文件删除

重新进入mysql执行

 

执行完后启动

start slave;

show slave status\G

查看IO和SQL

出现两个YES成功

 

 做下测试

在master1创建表用户

 

 

在master2查看数据同步过来没有

 

 

然后两台安装keepalived

Yum -y install keeplived

添加系统服务

chkconfig keepalived --add

chkconfig keepalived on

 

然后编辑两台keepalived配置

vim /etc/keepalived/keepalived.conf

修改IP smtp_server   127.0.0.1

标识，双主相同  mysql_ha

两台都是BACKUP

两个服务器id相同

id主的100，从的低于100就行

VIP两台相同

检测本地mysql，backup也要写检测

当mysq服down时，执行此脚本，杀死keepalived

 

 

 

 vim /etc/keepalived/mysql.sh

编辑

#!/bin/bash 
pkill keepalived

 

提升权限

chmod +x /etc/keepalived/mysql.sh

做下测试

停掉主服务192.168.92.128这台mysql服务

Service mysqld stop

看VIP还在这台服务器上没

 

然后查看下192.168.92.130这台服务的IP