mysql基于Altas读写分离并实现高可用
实验环境准备：
master:192.168.200.111
slave1:192.168.200.112
slave2:192.168.200.113
Altas:192.168.200.114
client:192.168.200.115
第一步：关闭防火墙（master,slave1,slave2,Altas）
[root@localhost ~]# systemctl stop firewalld && setenforce 0 && iptables -F
第二步：在master,slave1,slave2上实现主从复制
步骤省略，详情请参考以下网址：
https://www.cnblogs.com/lyqlyqlyq/p/11677034.html
master:
mysql> grant all on *.* to 'admin'@'192.168.200.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
slave1:
mysql> grant all on *.* to 'admin'@'192.168.200.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
slave2:
mysql> grant all on *.* to 'admin'@'192.168.200.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
第三步：配置Altas服务器
[root@localhost ~]# wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm
[root@localhost ~]# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm
[root@localhost ~]# cd /usr/local/mysql-proxy/bin/
[root@localhost bin]# ./encrypt 123456
/iZxz+0GRoA=
[root@localhost bin]# cp /usr/local/mysql-proxy/conf/test.cnf /usr/local/mysql-proxy/conf/test.cnf.bak
[root@localhost bin]# vim /usr/local/mysql-proxy/conf/test.conf
[mysql-proxy]
admin-username = user
admin-password = pwd
proxy-backend-addresses = 192.168.200.111:3306
proxy-read-only-backend-addresses = 192.168.200.112:3306,192.168.200.113:3306
pwds = admin:/iZxz+0GRoA=
daemon = true
keepalive = true
event-threads = 8
log-level = message
log-path = /usr/local/mysql-proxy/log
sql-log = REALTIME
proxy-address = 0.0.0.0:1234
admin-address = 0.0.0.0:2345
[root@localhost bin]# /usr/local/mysql-proxy/bin/mysql-proxyd test start
OK: MySQL-Proxy of test is started //出现即成功
[root@localhost ~]# netstat -lnpt | grep mysql-proxy
tcp 0 0 0.0.0.0:2345 0.0.0.0:* LISTEN 28754/mysql-proxy
tcp 0 0 0.0.0.0:1234 0.0.0.0:* LISTEN 28754/mysql-proxy
第四步：测试
此时的三台MySQL服务器是主从备份
在mysql-master 创建db_test 库及该库下的test表
mysql> create database AB_ab;
mysql> create table ab(id char(1));
可以在112和113上查看该库及表
mysql> show databases;
在112和113 服务器上停止主从备份
mysql> stop slave;
在111上添加表内容
mysql> insert into AB_ab.ab values('9');
此前在服务器上同步了表，所以在从服务器上可以直接手动插入其他内容
在slave1 上：
mysql> insert into AB_ab.ab values('1');
在slave2上：
mysql> insert into AB_ab.ab values('2'');
在115上测试
[root@localhost ~]# yum -y install mariadb 或者mysql
mysql -uadmin -p123456 -h 192.168.200.114 -P1234
(1) 读测试
2）写测试：
mysql> insert into AB_ab.ab values('5'');
在master服务器上查看是否写入：
高可用：
192.168.200.113 Atlas备
[root@localhost ~]# wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm
[root@localhost ~]# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm
[root@localhost ~]# cd /usr/local/mysql-proxy/bin/
[root@localhost bin]# ./encrypt 123456
/iZxz+0GRoA=
[root@localhost bin]# cp /usr/local/mysql-proxy/conf/test.cnf /usr/local/mysql-proxy/conf/test.cnf.bak
[root@localhost bin]# vim /usr/local/mysql-proxy/conf/test.conf
[mysql-proxy]
admin-username = user
admin-password = pwd
proxy-backend-addresses = 192.168.200.111:3306
proxy-read-only-backend-addresses = 192.168.200.112:3306,192.168.200.113:3306
pwds = admin:/iZxz+0GRoA=
daemon = true
keepalive = true
event-threads = 8
log-level = message
log-path = /usr/local/mysql-proxy/log
sql-log = REALTIME
proxy-address = 0.0.0.0:1234
admin-address = 0.0.0.0:2345
[root@localhost bin]# /usr/local/mysql-proxy/bin/mysql-proxyd test start
OK: MySQL-Proxy of test is started //出现即成功
[root@localhost ~]# netstat -lnpt | grep mysql-proxy
tcp 0 0 0.0.0.0:2345 0.0.0.0:* LISTEN 28754/mysql-proxy
tcp 0 0 0.0.0.0:1234 0.0.0.0:* LISTEN 28754/mysql-proxy
[root@localhost ~]# yum -y install keepalived
[root@localhost ~]# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
[root@localhost ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id LVS_ATLAS
}
vrrp_script chk_atlas_port {
script "/opt/check_atlas.sh"
weight 5
}
vrrp_instance VI_1 {
state BACKUP
interface ens32
virtual_router_id 51
priority 99
nopreempt
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
chk_atlas_port
}
virtual_ipaddress {
192.168.200.254
}
}
[root@localhost ~]# vim cat /opt/chk_atlas.sh
#!/bin/bash
counter=$(ps -C mysql-proxy --no-heading | wc -l)
if [ "${counter}" = "0" ];then
/usr/local/mysql-proxy/bin/mysql-proxyd test start
sleep 3
counter=$(ps -C mysql-proxy --no-heading | wc -l)
if [ "{counter}" = "0" ];then
systemctl stop keepalived
fi
fi
[root@localhost ~]# chmod +x /opt/chk_atlas.sh
[root@localhost ~]# systemctl start keepalived
192.168.200.114 Atlas主
[root@localhost ~]# yum -y install keepalived
[root@localhost ~]# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.bak
[root@localhost ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id LVS_ATLAS
}
vrrp_script chk_atlas_port {
script "/opt/check_atlas.sh"
weight 5
}
vrrp_instance VI_1 {
state MASTER
interface ens32
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
chk_atlas_port
}
virtual_ipaddress {
192.168.200.254
}
}
[root@localhost ~]# vim cat /opt/chk_atlas.sh
#!/bin/bash
counter=$(ps -C mysql-proxy --no-heading | wc -l)
if [ "${counter}" = "0" ];then
/usr/local/mysql-proxy/bin/mysql-proxyd test start
sleep 3
counter=$(ps -C mysql-proxy --no-heading | wc -l)
if [ "{counter}" = "0" ];then
systemctl stop keepalived
fi
fi
[root@localhost ~]# chmod +x /opt/chk_atlas.sh
[root@localhost ~]# systemctl start keepalived
高可用搭建完成！
