一、MySQL读写分离架构

  1. 安装配置
--- 安装Atlas
yum install -y Atlas*
cd /usr/local/mysql-proxy/conf
--- 修改配置文件
mv test.cnf test.cnf.bak
cat > test.cnf <<EOF

[mysql-proxy]

admin-username = user
admin-password = pwd
proxy-backend-addresses = 10.0.0.55:3306
proxy-read-only-backend-addresses = 10.0.0.51:3306,10.0.0.53:3306
pwds = repl:3yb5jEku5h4=,mha:O2jBXONX098=
daemon = true
keepalive = true
event-threads = 8
log-level = message
log-path = /usr/local/mysql-proxy/log
sql-log=ON
proxy-address = 0.0.0.0:33060
admin-address = 0.0.0.0:2345
charset=utf8
EOF

--- 启动atlas
/usr/local/mysql-proxy/bin/mysql-proxyd test start
--- 查看Atlas是否启动
ps -ef |grep proxy
  1. 测试
    1）读
[root@db03 ~]# mysql -umha -pmha -h10.0.0.53 -P33060
db03 [(none)]>select @@server_id;

2）写

db03 [(none)]>begin;
db03 [(none)]>select @@server_id;
db03 [(none)]>commit;
  1. 添加用户
    1）主节点添加用户
db02 [(none)]>grant all on *.* to root@'10.0.0.%' identified by '123';

2）密码加密（db03）

[root@db03 /usr/local/mysql-proxy/bin]# /usr/local/mysql-proxy/bin/encrypt 123

3）改配置文件重启

pwds = repl:3yb5jEku5h4=,mha:O2jBXONX098=,root:3yb5jEku5h4=

/usr/local/mysql-proxy/bin/mysql-proxyd test restart
  1. 在线管理
    1）连接到管理端口2345
[root@db03 ~]# mysql -uuser -ppwd -h 127.0.0.1 -P2345

2）查看帮助

db03 [(none)]>select * from help;

3）查看所有后端节点

SELECT * FROM backends;

4）上线、下线节点

SET OFFLINE 3;
SET ONLINE 3;

5）在线添加、删除从节点

add slave 10.0.0.53:3306;
REMOVE BACKEND 3;

6）用户操作

db03 [(none)]>SELECT * FROM pwds
db03 [(none)]>add pwd root:123;
db03 [(none)]>REMOVE PWD root;

7）保存配置文件

db03 [(none)]>save config;