MySQL之Atlas（读写分离）
一、什么是读写分离
读写分离,基本的原理是让主数据库处理事务性增、删、改操作( INSERT、DELETE、 UPDATE) , 而从数据库处理
SELECT查询操作。数据库复制被用来把书屋性操作导致的变更同步到集群中的从数据库。
二、为什么读写分离
因为数据库的“写" (写10000条数据到oracle可能要3分钟)操作是比较耗时的。但是数据库的"读”(从oracle读
10000条数据可能只要5秒钟)。所以读写分离,解决的是,数据库的写入,影响了查询的效率。
三、什么时候要读写分离
数据库不一定要读写分离,如果程序使用数据库较多时,而更新少,查询多的情况下会考虑使用,利用数据库主从
同步。可以减少数据库压力,提高性能。当然,数据库也有其它优化方案。memcache 或是表折分,或是搜索引擎。都
是解决方法。
四、Atlas的功用与应用场景
Atlas的功能有:
读写分离、从库负载均衡、自动分表、IP过滤、SQL语句黑白名单、DBA可平滑上下线DB、自动摘除宕机的DB。
Atlas的使用场景:
Atlas是一个位于 前端应用与后端MySQL数据库之间的中间件,它使得应用程序员无再关心读写分离、分表等与
MySQL相关的细节,可以专注于编写业务逻辑,同时使得DBA的运维工作对前端应用透明,上下线DB前端应用无感知。
五、安装使用
环境准备：
四台CentOS机器
c31 192.168.37.31 master主 mysql5.6，mha4mysql-node
c32 192.168.37.32 slave 01(备用主) mysql5.6，mha4mysql-node
c33 192.168.37.33 slave 02 mysql5.6，mha4mysql-node
c34 192.168.37.34 mha+atlas mysql5.6-client，mha4mysql-manager,mha4-mysql-node,atlas
192.168.37.100 vip 绑定到mysql+master
每台机器先安装好mysql5.6
按照mha操作博客（1）（2）做好mysql主从
1、安装Altas
[root@localhost ~]# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm 准备中... ################################# [100%] 正在升级/安装... 1:Atlas-2.2.1-1 ################################# [100%]
2、修改配置文件
用户名与其对应的加密过的MySQL密码，密码使用PREFIX/bin目录下的加密程序encrypt加密，将其替换为你的MySQL的用户名和加密密码！
[root@localhost ~]# /usr/local/mysql-proxy/bin/encrypt 123456 /iZxz+0GRoA= [root@localhost ~]# /usr/local/mysql-proxy/bin/encrypt mha O2jBXONX098= cp /usr/local/mysql-proxy/conf/test.cnf{,.bak} vim /usr/local/mysql-proxy/conf/test.cnf [mysql-proxy] admin-username = user admin-password = pwd proxy-backend-addresses = 192.168.37.100:3306 proxy-read-only-backend-addresses = 192.168.37.32:3306,192.168.37.33:3306 pwds = rep:/iZxz+0GRoA=,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:1234 admin-address = 0.0.0.0:2345 charset=utf8
启动Atlas
/usr/local/mysql-proxy/bin/mysql-proxyd test start
[root@localhost ~]# /usr/local/mysql-proxy/bin/mysql-proxyd test start OK: MySQL-Proxy of test is started [root@localhost ~]# masterha_check_status --conf=/etc/mha/app1.cnf app1 is stopped(2:NOT_RUNNING). [root@localhost ~]# masterha_stop --conf=/etc/mha/app1.cnf MHA Manager is not running on app1(2:NOT_RUNNING). [root@localhost ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 & [1] 11437 [root@localhost ~]# masterha_check_status --conf=/etc/mha/app1.cnf app1 (pid:11437) is running(0:PING_OK), master:192.168.37.31 [root@localhost ~]# masterha_check_status --conf=/etc/mha/app1.cnf app1 (pid:11437) is running(0:PING_OK), master:192.168.37.31 [root@localhost ~]# /usr/local/mysql-proxy/bin/mysql-proxyd test start error: MySQL-Proxy of test is running now [root@localhost ~]# /usr/local/mysql-proxy/bin/mysql-proxyd test restart OK: MySQL-Proxy of test is stopped OK: MySQL-Proxy of test is started
3、Atlas读写分离测试
读测试：
[root@localhost ~]# mysql -umha -pmha -P1234 -h192.168.37.34 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.81-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> select @@server_id; +-------------+ | @@server_id | +-------------+ | 3 | +-------------+ 1 row in set (0.00 sec) MySQL [(none)]> select @@server_id; +-------------+ | @@server_id | +-------------+ | 2 | +-------------+ 1 row in set (0.01 sec)
注:发现上面'server_ id每次的结果都不一样,分别是2台从库的server. id ,并且每执行- -次命令, server, id就会变换一
次，这是因为默认读操作的权重都是1 , 两台从DB默认就是负载均衡。
在配置文件可以设置权重，负载均衡的权重，在端口后面添加权重比例
proxy-read-only-backend-addresses = 192.168.37.32@1:3306,192.168.37.33:3306@2
写测试：登录管理接口
[root@localhost ~]# mysql -umha -pmha -P1234 -h192.168.37.34 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.0.81-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> begin;select @@server_id;commit; Query OK, 0 rows affected (0.01 sec) +-------------+ | @@server_id | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> create database www; Query OK, 1 row affected (0.01 sec) MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | www | +--------------------+ 4 rows in set (0.01 sec)
查看帮助信息，注意端口
[root@localhost ~]# mysql -uuser -ppwd -P2345 -h192.168.37.34 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.99-agent-admin Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> 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)
查看后端的代理库
MySQL [(none)]> select * from backends; +-------------+--------------------+-------+------+ | backend_ndx | address | state | type | +-------------+--------------------+-------+------+ | 1 | 192.168.37.31:3306 | up | rw | | 2 | 192.168.37.32:3306 | up | ro | | 3 | 192.168.37.33:3306 | up | ro | +-------------+--------------------+-------+------+ 3 rows in set (0.00 sec)
下线后端节点
MySQL [(none)]> MySQL [(none)]> set offline 3; +-------------+--------------------+---------+------+ | backend_ndx | address | state | type | +-------------+--------------------+---------+------+ | 3 | 192.168.37.33:3306 | offline | ro | +-------------+--------------------+---------+------+ 1 row in set (0.00 sec) MySQL [(none)]> select * from backends; +-------------+--------------------+---------+------+ | backend_ndx | address | state | type | +-------------+--------------------+---------+------+ | 1 | 192.168.37.31:3306 | up | rw | | 2 | 192.168.37.32:3306 | up | ro | | 3 | 192.168.37.33:3306 | offline | ro | +-------------+--------------------+---------+------+ 3 rows in set (0.00 sec)
上线后端节点
MySQL [(none)]> set online 3; +-------------+--------------------+---------+------+ | backend_ndx | address | state | type | +-------------+--------------------+---------+------+ | 3 | 192.168.37.33:3306 | unknown | ro | +-------------+--------------------+---------+------+ 1 row in set (0.00 sec) MySQL [(none)]> select * from backends; +-------------+--------------------+---------+------+ | backend_ndx | address | state | type | +-------------+--------------------+---------+------+ | 1 | 192.168.37.31:3306 | up | rw | | 2 | 192.168.37.32:3306 | up | ro | | 3 | 192.168.37.33:3306 | unknown | ro | +-------------+--------------------+---------+------+ 3 rows in set (0.00 sec)
