mysql atlas 文档_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:3306proxy-read-only-backend-addresses = 192.168.37.32:3306,192.168.37.33:3306pwds= rep:/iZxz+0GRoA=,mha:O2jBXONX098=daemon= truekeepalive= true

event-threads = 8log-level =message

log-path = /usr/local/mysql-proxy/log

sql-log=ON

proxy-address = 0.0.0.0:1234admin-address = 0.0.0.0:2345charset=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 isstarted

[root@localhost~]# masterha_check_status --conf=/etc/mha/app1.cnf

app1is stopped(2:NOT_RUNNING).

[root@localhost~]# masterha_stop --conf=/etc/mha/app1.cnf

MHA Manageris 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 isrunning now

[root@localhost~]# /usr/local/mysql-proxy/bin/mysql-proxyd test restart

OK: MySQL-Proxy of test isstopped

OK: MySQL-Proxy of test is started

3、Atlas读写分离测试

读测试：

[root@localhost ~]# mysql -umha -pmha -P1234 -h192.168.37.34Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MySQL connection idis 1Server 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.00sec)

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.34Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MySQL connection idis 2Server 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.01sec)+-------------+

| @@server_id |

+-------------+

| 1 |

+-------------+

1 row in set (0.00sec)

Query OK,0 rows affected (0.00sec)

MySQL [(none)]>create database www;

Query OK,1 row affected (0.01sec)

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.34Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MySQL connection idis 1Server 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 * fromhelp;+----------------------------+---------------------------------------------------------+

| 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 * frombackends;+-------------+--------------------+-------+------+

| 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.00sec)

MySQL [(none)]> select * frombackends;+-------------+--------------------+---------+------+

| 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.00sec)

MySQL [(none)]> select * frombackends;+-------------+--------------------+---------+------+

| 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)