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.x84.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服务器上查看是否写入：

mysql基于Altas实现读写分离

原文：https://www.cnblogs.com/lyqlyqlyq/p/11687808.html