atlas读写分离
2021-03-17 本文已影响0人
慕知
一，读写分离优势及原理
优势：提升数据库性能，用于高并发场景;
原理：基于主从复制的结构上，基本的原理是让主数据库处理事务性查询，而从数据库处理select查询
二、Atlas安装
1，环境及安装
环境：
atlas 10.0.0.7
db01 10.0.0.51 mysql主库
db02 10.0.0.52 mysql从库
[root@\ db02/tmp]# ll
-rw-r--r-- 1 root root 5574363 2021-03-16 14:55 Atlas-sharding_1.0.1-el6.x86_64.rpm
#rpm直接安装
[root@\ db02/tmp]# yum install -y Atlas-sharding_1.0.1-el6.x86_64.rpm
或
[root@\ db02/tmp]# rpm -ivh Atlas-sharding_1.0.1-el6.x86_64.rpm
#安装完成后atlas的安装目录为/usr/local/mysql-proxy
[root@\ db02/tmp]# cd /usr/local/mysql-proxy/
[root@\ db02/usr/local/mysql-proxy]# ll
drwxr-xr-x 2 root root 125 2021-03-16 15:10 bin
drwxr-xr-x 2 root root 22 2021-03-16 15:10 conf
drwxr-xr-x 2 root root 4096 2021-03-16 15:10 include
drwxr-xr-x 4 root root 4096 2021-03-16 15:10 lib
drwxr-xr-x 2 root root 6 2015-05-19 18:39 libexec
drwxr-xr-x 2 root root 22 2021-03-16 15:10 log
drwxr-xr-x 3 root root 17 2021-03-16 15:10 share
2，授权Atlas管理账号
mysql> grant all privileges on *.* to atlas@'172.16.1.%' identified by 'atlas';
Query OK, 0 rows affected (0.00 sec)
# 两台数据服务器都需要
3，修改test配置文件
# 密码加密，下面的配置文件需要用到（atlas为数据库主从授权的密码）
[root@\ db/usr/local/mysql-proxy]# ./bin/encrypt atlas
KsWNCR6qyNk=
[root@\ db]# vim /usr/local/mysql-proxy/conf/test.cnf
[mysql-proxy]
#带#号的为非必需的配置项目
#管理接口的用户名
admin-username = admin
#管理接口的密码
admin-password = admin
#Atlas后端连接的MySQL主库的IP和端口，可设置多项，用逗号分隔
proxy-backend-addresses = 172.16.1.51:3306
#Atlas后端连接的MySQL从库的IP和端口，@后面的数字代表权重，用来作负载均衡，若省略则默认为1，可设置多项，用逗号分隔
proxy-read-only-backend-addresses = 172.16.1.52:3306@1
... ...
#用户名与其对应的加密过的MySQL密码，密码使用PREFIX/bin目录下的加密程序encrypt加密，下行的user1和user2为示例，将其替换为你的MySQL的用户名和加密密码！
pwds = atlas:KsWNCR6qyNk=
... ...
#Atlas监听的工作接口IP和端口
proxy-address = 0.0.0.0:1234
#Atlas监听的管理接口IP和端口
admin-address = 0.0.0.0:2345
.. ....
4，启动服务
[root@db ~]# cd /usr/local/mysql-proxy/
[root@\ db/usr/local/mysql-proxy]# ./bin/mysql-proxyd test start
OK: MySQL-Proxy of test is started
#查看进程及端口
[root@\ db02/usr/local/mysql-proxy]# !ps
ps -ef | grep proxy
root 21390 1 0 20:30 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root 21391 21390 0 20:30 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root 21404 17221 0 20:32 pts/0 00:00:00 grep --color=auto proxy
root@\ db/usr/local/mysql-proxy]# netstat -lntp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address 8074/php-fpm: maste
tcp 0 0 0.0.0.0:2345 0.0.0.0:* LISTEN 21391/mysql-proxy
tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN 5889/rpcbind
tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN 9056/nginx: master
tcp 0 0 0.0.0.0:8081 0.0.0.0:* LISTEN 9056/nginx: master
tcp 0 0 0.0.0.0:1234 0.0.0.0:* LISTEN 21391/mysql-proxy
... ...
#可以看到1234 和2345端口都已开启
#重启服务
[root@\ db/usr/local/mysql-proxy]# ./bin/mysql-proxyd test restart
#关闭服务
[root@\ db/usr/local/mysql-proxy]# ./bin/mysql-proxyd test stop
5,atlas管理界面
[root@\ db]# mysql -h 172.16.1.7 -P2345 -uadmin -padmin
#查看可查询的语句
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", ... |
| ADD GMASTER $group_id $backend | example: "add gmaster 1 127.0.0.1:3306", ... |
| ADD GSLAVE $group_id $backend | example: "add gslave 1 127.0.0.1:3306", ... |
| REMOVE BACKEND $backend_id | example: "remove backend 1", ... |
| REMOVE GBACKEND $group_id $backend_id | example: "remove gbackend 1 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 |
+---------------------------------------+---------------------------------------------------------+
19 rows in set (0.00 sec)
#查看主后段服务器的工作状态
mysql> select * from backends;
+----------+------------------+-------+------+-------------+
| group_id | address | state | type | backend_ndx |
+----------+------------------+-------+------+-------------+
| -1 | 172.16.1.51:3306 | up | rw | 1 |
| -1 | 172.16.1.52:3306 | up | ro | 2 |
+----------+------------------+-------+------+-------------+
2 rows in set (0.00 sec)
mysql> set offline 3; #某库故障下线
mysql> set online 3; #某库新增上线（后面是主机号）
6,atlas工作界面，读写测试
1，从库设置延迟同步(主从复制同步较快，方便查看检测结果)
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to master_delay=180;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
2，atlas服务端登陆工作账号
#查看查询所指向的server_id，是从库
[root@\ db]# mysql -h 172.16.1.7 -P1234 -uatlas -patlas
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
#执行多次查询
mysql> select @@server_id;
#执行多次写数据
mysql> insert into excel(id) values(34);
3，在主库和从库查看查询及写数据的量变化
mysql> show global status like '%insert%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Com_insert | 1 |
| Com_insert_select | 0 |
| Delayed_insert_threads | 0 |
| Innodb_rows_inserted | 1 |
| Qcache_inserts | 0 |
+------------------------+-------+
5 rows in set (0.00 sec)
总结：
可以看到写数据会到主库172.16.1.51这台机器
查询数据会到从库172.16.1.52这台机器
