一、准备环境以及服务版本信息(一主两从)
注意：此处省略了 mysql 安装的步骤
二、配置 mysql 主从复制(两台从库上操作)
Mysql 版本：mysql-5.7
mysql -uroot -p
stop slave
;
reset slave
;
change master to master_host
='192.168.100.141',master_user
='slave',master_password
='C%GPYtU7+VuJwxt',master_log_file
='mysql-bin.000001',master_log_pos
=2296
;
start slave
;
show slave status \G
;
三、Atlas 安装配置(主库操作)
Atlas 版本：Atlas-2.2.1
rpm -ivh https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm
vim /usr/local/mysql-proxy/conf/test.cnf
[mysql-proxy
]
admin-username
= atlas
admin-password
= 24CWapFrC%GPYtU7+VuJwxtk
proxy-backend-addresses
= 192.168.100.141:3306
proxy-read-only-backend-addresses
= 192.168.100.142:3306@1,192.168.100.68:3306@1
pwds
= slave:sn4FN1UDChN7Siu,mao:gw++RkAeV40
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/stop/restart
四、测试读写分离功能
mysql -umao -p -h192.168.100.141 -P33060
mysql
> select @@server_id
;
+-------------+
| @@server_id
|
+-------------+
| 50
|
+-------------+
1 row
in set (0.00 sec
)
mysql
> select @@server_id
;
+-------------+
| @@server_id
|
+-------------+
| 3
|
+-------------+
1 row
in set (0.00 sec
)
mysql
> begin
;select @@server_id
;commit
;
Query OK, 0 rows affected
(0.00 sec
)
+-------------+
| @@server_id
|
+-------------+
| 1
|
+-------------+
1 row
in set (0.00 sec
)
Query OK, 0 rows affected
(0.00 sec
)
注：可以看到我们的读操作分别在slave1和slave2上进行了操作
五、查看节点的连接情况，Atlas的管理
1. 连接查看管理帮助
[root@db01 ~
]
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.01 sec
)
2. 查看数据库节点状态
(主库为读写类型，从库只读类型
)
mysql
> select * from backends
;
+-------------+----------------------+-------+------+
| backend_ndx
| address
| state
| type |
+-------------+----------------------+-------+------+
| 1
| 192.168.100.141:3306
| up
| rw
|
| 2
| 192.168.100.142:3306
| up
| ro
|
| 3
| 192.168.100.68:3306
| up
| ro
|
+-------------+----------------------+-------+------+
3 rows
in set (0.00 sec
)
配置解析：
[mysql-proxy
]
admin-username
= atlas
admin-password
= Lhf
}AUGbTZU%.Gqjt%forc8k5o.
}4uc@diR?24CWapFrC%GPYtU7+VuJwxtk
proxy-backend-addresses
= 192.168.100.141:3306
proxy-read-only-backend-addresses
= 192.168.100.142:3306@1,192.168.100.68:3306@1
pwds
= slave:sn4FN1UDChN7Siu8D+d8HRqTQbkEvVqV0WozNp3O2hk
=,maomi:gw++RkAeV40zlJ3NZUvu4xryz+hZmUy9
daemon
= true
keepalive
= true
event-threads
= 8
log-level
= message
log-path
= /usr/local/mysql-proxy/log
proxy-address
= 0.0.0.0:33060
admin-address
= 0.0.0.0:2345
charset
= utf8
