读写分离架构

image.png

安装配置

下载地址
https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm
 rpm -ivh Atlas-2.2.1.el6.x86_64.rpm
cd /usr/local/mysql-proxy/conf
mv test.cnf test.cnf.bak
 vi test.cnf  # 具体配置看默认文件就行，都是中文注释
[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
启动atlas
/usr/local/mysql-proxy/bin/mysql-proxyd test start   # test为配置文件的名字，Atlas也和MHA一样，一个程序可以管理多个实例。
ps -ef |grep proxy

测试

[root@mysql bin]# mysql -umha -pmha -h 10.0.0.11 -P 1234  #1234为Atlas工作端口，其用户必须是Atlas配置文件中授权的后端数据库的用户

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           5 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           6 |
+-------------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           6 |
+-------------+
1 row in set (0.00 sec)

mysql> begin;   #因为修改，一定是事务，用来欺骗Atlas
Query OK, 0 rows affected (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
1 row in set (0.00 sec)

Atlas的管理操作

mysql -uuser -ppwd -h 127.0.0.1 -P 2345  #2345此端口是Atlas的管理端口，其用户和密码是配置文件中配置的。后端数据库中不用有
mysql> select * from help;   #查看帮助
+----------------------------+---------------------------------------------------------+
| command                    | description                                             |
+----------------------------+---------------------------------------------------------+
| SELECT * FROM help         | shows this help                                         |
| SELECT * FROM backends     | lists the backends and their state                      |
| SET OFFLINE $backend_id    | offline backend server, $backend_id is backend_ndx's id |
| SET ONLINE $backend_id     | online backend server, ...                              |
| ADD MASTER $backend        | example: "add master 127.0.0.1:3306", ...               |
| ADD SLAVE $backend         | example: "add slave 127.0.0.1:3306", ...                |
| REMOVE BACKEND $backend_id | example: "remove backend 1", ...                        |
| SELECT * FROM clients      | lists the clients                                       |
| ADD CLIENT $client         | example: "add client 192.168.1.2", ...                  |
| REMOVE CLIENT $client      | example: "remove client 192.168.1.2", ...               |
| SELECT * FROM pwds         | lists the pwds                                          |
| ADD PWD $pwd               | example: "add pwd user:raw_password", ...               |
| ADD ENPWD $pwd             | example: "add enpwd user:encrypted_password", ...       |
| REMOVE PWD $pwd            | example: "remove pwd user", ...                         |
| SAVE CONFIG                | save the backends to config file                        |
| SELECT VERSION             | display the version of Atlas                            |
+----------------------------+---------------------------------------------------------+
16 rows in set (0.00 sec)

# 常用命令
select * from backends; 查看后端节点
mysql> select * from backends;
+-------------+----------------+-------+------+
| backend_ndx | address        | state | type |
+-------------+----------------+-------+------+
|           1 | 10.0.0.12:3306 | up    | rw   |
|           2 | 10.0.0.11:3306 | up    | ro   |
|           3 | 10.0.0.13:3306 | up    | ro   |
+-------------+----------------+-------+------+
3 rows in set (0.00 sec)

backend_ndx 为后端的backend_id
set offline $backend_id;   暂时下线节点
set online $backend_id；暂时上线节点
add master $backend;  暂时添加主节点
add slave $backend；暂时添加从节点
remove backend $backed_id； 删除节点
select * from pwds; 查看所有后端用户
add pwd $pwd； 添加用户
add pwd oldguo:123456; 添加用户
save config; 把以上的操作进行保存