Atlas+keepalived实现mysql读写分离

devil20

本文主要介绍atlas安装、mysql读写分离的配置、mysql主从配置及利用keepalived解决atlas单点故障。

Atlas介绍

Atlas是基于mysql-proxy做的二次开发。Atlas是一个位于应用程序与MySQL之间中间件。在后端DB看来，Atlas相当于连接它的客户端，在前端应用看来，Atlas相当于一个DB。Atlas作为服务端与应用程序通讯，它实现了MySQL的客户端和服务端协议，同时作为客户端与MySQL通讯。它对应用程序屏蔽了DB的细节，同时为了降低MySQL负担，它还维护了连接池。
keepalived工作原理及mysql工作原理参考以下文章
http://hnr520.blog.运维网.com/4484939/1694028
http://hnr520.blog.运维网.com/4484939/1660222

安装配置
系统环境
Centos6.6_x86_64
实验拓扑图


一、mysql主从配置
1.mysql安装
mysql版本：mysql-5.5.43-linux2.6-x86_64.tar.gz
创建用户及相关目录
# groupadd -r -g 306mysql
# useradd -r -u 306 -gmysql mysql
# mkdir -pv /data/mydata
# cd /data/mydata/
# mkdir data binlogs
# chown -R mysql.mysql *
# cd /root/soft/
# tar xfmysql-5.5.43-linux2.6-x86_64.tar.gz -C /usr/local/
# cd /usr/local/
# ln -svmysql-5.5.43-linux2.6-x86_64 mysql
2.初始化mysql
#chown -R root.mysql  mysql/*
# cd mysql/
#./scripts/mysql_install_db --user=mysql --datadir=/data/mydata/data/
# cpsupport-files/my-large.cnf /etc/my.cnf
# cpsupport-files/mysql.server /etc/init.d/mysqld
3.修改配置文件
# vim /etc/my.cnf
datadir =/data/mydata/data
innodb_file_per_table =ON
log-bin=/data/mydata/binlogs/master-bin
binlog_format=row
4.master服务器配置
编辑配置文件
# vim /etc/my.cnf
log-bin=/data/mydata/binlogs/master-bin
server-id=1
启动服务，连接mysql修改密码
mysql> update user setPassword=PASSWORD('123456') where user='root';
mysql> flushprivileges;
创建复制账号
mysql> GRANTREPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.203' IDENTIFIED BY'123456';
mysql> show masterstatus;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB |Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001|     3621 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
5.准备测试数据
mysql> CREATE DATABASEorders;
CREATE TABLE `my_orders`(
  `id` int(10) unsigned NOT NULL AUTO_INCREMENTCOMMENT '表主键',
  `pid` int(10) unsigned NOT NULL COMMENT'产品ID',
  `price` decimal(15,2) NOT NULL COMMENT '单价',
  `num` int(11) NOT NULL COMMENT '购买数量',
  `person` varchar(255) NOT NULL COMMENT'客户姓名',
  `atime` int(10) unsigned NOT NULL COMMENT'下单时间',
  `utime` int(10) unsigned NOT NULL COMMENT'修改时间',
  `isdel` tinyint(4) NOT NULL DEFAULT '0'COMMENT '软删除标识',
  `year` smallint(5) unsigned NOT NULL COMMENT'年',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULTCHARSET=utf8;

INSERT INTO`my_orders`(`pid`,`price`,`num`,`person`,`atime`,`utime`,`year`) VALUES ('1','123.54', '1', '诸葛亮', '1426335606', '1426335606','2015');
INSERT INTO`my_orders`(`pid`,`price`,`num`,`person`,`atime`,`utime`,`year`) VALUES ('3','13.67', '4', '刘备', '1426338765', '1426338765', '2016');
INSERT INTO`my_orders`(`pid`,`price`,`num`,`person`,`atime`,`utime`,`year`) VALUES ('4','44.44', '4', '关羽', '1426754324', '1426754324','2017');
6.slave服务器配置
编辑配置文件，注释掉二进制日志
# vim /etc/my.cnf
relay_log=/data/mydata/binlogs/relay-bin
server-id=2
启动服务，开启复制
mysql> CHANGE MASTERTOMASTER_HOST='192.168.1.204',MASTER_USER='replication',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='master-bin.000001',MASTER_LOG_POS=3621;
mysql> SLAVE START;
mysql> SHOW SLAVESTATUS\G

IO线程和SQL线程均已YES
授权root账号可在192.168.0.0网段登入
mysql>GRANT ALL ON *.* TO 'root'@'192.168.%.%' IDENTIFIED BY '123456';

二、Atlas安装
1.软件安装
# rpm -ivhAtlas-2.2.1.el6.x86_64.rpm
2.生成加密密码
#/usr/local/mysql-proxy/bin/encrypt 123456
/iZxz+0GRoA=
3.编辑配置文件
#vim /usr/local/mysql-proxy/conf/test.cnf
[mysql-proxy]
admin-username = admin
admin-password = admin
proxy-backend-addresses =192.168.1.204:3306
proxy-read-only-backend-addresses= 192.168.1.203:3306
pwds = root:/iZxz+0GRoA=
daemon = true
keepalive = true
event-threads = 8
log-level = message
log-path =/usr/local/mysql-proxy/log
proxy-address =0.0.0.0:1234
admin-address =0.0.0.0:2345
4.启动服务
#/usr/local/mysql-proxy/bin/mysql-proxyd test start
# netstat -ntpl | grepmysql-proxy
tcp        0     0 0.0.0.0:1234               0.0.0.0:*                   LISTEN      4389/mysql-proxy   
tcp        0     0 0.0.0.0:2345               0.0.0.0:*                   LISTEN      4389/mysql-proxy
5.测试atlas连接mysql
# mysql -h 192.168.1.201-P 1234 -uroot -p123456


# mysql -h 192.168.1.201-P 2345 -uadmin -padmin


三、安装keepalived
Centos6.6的keepalived已经是1.2.13版本，故这里直接采用yum方式安装
1.软件安装
# yum install keepalived
2.配置主节点201的keepalived配置文件
# vim/etc/keepalived/keepalived.conf
! Configuration File forkeepalived

global_defs {
   router_id LVS_DEVEL
}

vrrp_script Monitor_Atlas{
script"/usr/local/mysql-proxy/monitor_atlas.sh"
interval 2
weight 2
}

vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
        mcast_src_ip 192.168.1.201
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
    Monitor_Atlas
    }
    virtual_ipaddress {
        192.168.1.200
    }
}
3.提供atlas监控脚本
# vim/usr/local/mysql-proxy/monitor_atlas.sh
#!/bin/bash
if  [ $(ps -ef |grep 'mysql-proxy' |grep -v'grep' |wc -l) -eq "0" ];then
    /usr/local/mysql-proxy/bin/mysql-proxydtest start
    sleep 5
    if [ $(ps -ef |grep 'mysql-proxy' |grep -v'grep' |wc -l) -eq "0"  ];then
        service keepalived stop
        service network restart
    fi
fi
#chmod +x  /usr/local/mysql-proxy/monitor_atlas.sh
4.配置从节点202的keepalived配置文件
复制以上两个文件到从节点，并修改keepalived配置文件
state BACKUP
mcast_src_ip192.168.1.202
priority 95
5.启动服务及测试
在主节点201上启动服务
# service keepalivedstart


在从节点202上启动服务
# service keepalivedstart

6.连接atlas测试
# mysql -h 192.168.1.200-P 1234 -uroot -p123456


# mysql -h 192.168.1.200-P 2345 -uadmin -padmin


四、mysql读写分离及atlas高可用测试
1.读写分离测试
使用navicat连接mysql


查询测试

读写分离测试，为了达到效果在从库上手动插入一条语句
mysql>INSERT INTO `my_orders`(`pid`,`price`,`num`,`person`,`atime`,`utime`,`year`)VALUES ('1', '123.54', '1', 'test_slave', '1426335606', '1426335606','2015');

出现两条结果说明连接的是从库
测试强制从主库查询数据

2.atlas高可用测试
停止主节点keepalived服务
# service keepalived stop
此时vip漂移到从节点上

测试mysq连接一切正常