mysql主从之基于atlas读写分离
一 mysql读写分离的概念
写在主库,主库一般只有一个,读可以分配在多个从库上,如果写压力不大的话,也能把读分配到主库上.
实现是基于atlas实现的,atlas是数据库的中间件,程序只需要连接atlas入口,无需读写单独指定,atlas决定在那个数据库进行读或者写操作,可以实现mysql的读写分离
atlas是一台独立的机器,目前实验把atlas配置在192.168.132.122上
主库+从库 给予atlas读写的权限
主库:192.168.132.121
mysql> grant all privileges on *.* to 'atlas'@'192.168.132.122' identified by '';
Query OK, rows affected, warning (0.01 sec)
mysql> flush privileges;
从库:192.168.132.122
mysql> grant all privileges on *.* to 'atlas'@'192.168.132.122' identified by ''; #atlas连接数据库的用户名是atls,密码是1234567
Query OK, rows affected, warning (0.00 sec)
mysql> flush privileges;
atlas对主端和从端都有了读写权限
二 安装atlas
Atlas下载地址
https://github.com/Qihoo360/Atlas/releases
[root@slave ~]# cd /usr/local/src/
[root@slave src]# yum install epel-release -y
[root@slave src]# yum install openssl-devel autoconf gcc glib2 glib2-devel libevent-devel flex-devel flex jemalloc jemalloc-devel lua-devel -y
[root@slave src]# wget https://github.com/Qihoo360/Atlas/releases/download/sharding-1.0.1/Atlas-sharding_1.0.1-el6.x86_64.rpm
[root@slave src]# rpm -ivh Atlas-sharding_1.0.1-el6.x86_64.rpm
[root@slave src]# cd /usr/local/mysql-proxy/
[root@slave mysql-proxy]# ll
[root@slave mysql-proxy]# ./bin/mysql-proxy --version
三 配置读写分离
密码加密
[root@slave mysql-proxy]# /usr/local/mysql-proxy/bin/encrypt '1234567'
[root@slave mysql-proxy]# vim /usr/local/mysql-proxy/atlas.cnf
[mysql-proxy]
#主库地址
proxy-backend-addresses = 192.168.132.121:
#从库地址,@后面的数字代表权重,用来负载均衡,默认权重为1。可设置多项,用逗号分隔
proxy-read-only-backend-addresses = 192.168.132.122:@,192.168.132.121:@
#用户名和密码,密码需要使用/usr/local/mysql-proxy/bin/encrypt加密
pwds = atlas:tj+W8xntBW8=
daemon = true
#守护进程
keepalive = true
#线程数
event-threads =
#日志级别,message、warning、critical、error、debug
log-level = warning
#日志存放的路径
log-path = /usr/local/mysql-proxy/log
#SQL日志的开关,OFF代表不记录SQL日志,ON代表记录SQL日志,REALTIME代表记录SQL日志且实时写入磁盘
sql-log = OFF
#慢日志输出设置。单位:ms
sql-log-slow =
#Atlas监听端口
proxy-address = 0.0.0.0:3316 #如果是一台独立的服务器,这个端口可以和MySQL端口一样,设为3306
#管理监听端口
admin-address = 0.0.0.0:
admin-username = atlas
admin-password =
#客户端连接默认字符集,不设置的话一般还需要使用set names utf8
charset = utf8
#ip白名单
#client-ips = 127.0.0.1, 192.168.3.1
最终是这样
程序连接 atlas192.168.237.128:3316 -> 主库+从库
四 启动atlas
[root@slave mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxyd --help
[root@slave mysql-proxy]# /usr/local/mysql-proxy/bin/mysql-proxyd atlas start
[root@slave mysql-proxy]# netstat -ntlp|grep mysql-proxy
五 验证
5.1 读操作验证
主端查看读操作的次数
192.168.132.121:
mysql> show global status like '%select%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Com_insert_select | |
| Com_replace_select | |
| Com_select | |
| Connection_errors_select | |
| Select_full_join | |
| Select_full_range_join | |
| Select_range | |
| Select_range_check | |
| Select_scan | |
+--------------------------+-------+
192.168.132.122:
mysql> show global status like '%select%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Com_insert_select | |
| Com_replace_select | |
| Com_select | |
| Connection_errors_select | |
| Select_full_join | |
| Select_full_range_join | |
| Select_range | |
| Select_range_check | |
| Select_scan | |
+--------------------------+-------+
执行读操作
192.168.132.121执行
[root@master ~]# mysql -uatlas -p1234567 -h 192.168.132.122 -P3316 -e 'select * from darren.test;'
192.168.132.121: 未执行读操作
mysql> show global status like '%select%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Com_insert_select | |
| Com_replace_select | |
| Com_select | |
| Connection_errors_select | |
| Select_full_join | |
| Select_full_range_join | |
| Select_range | |
| Select_range_check | |
| Select_scan | |
+--------------------------+-------+
192.168.: 执行读操作
mysql> show global status like '%select%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Com_insert_select | |
| Com_replace_select | |
| Com_select | |
| Connection_errors_select | |
| Select_full_join | |
| Select_full_range_join | |
| Select_range | |
| Select_range_check | |
| Select_scan | |
+--------------------------+-------+
多执行几次
[root@master ~]# mysql -uatlas -p1234567 -h 192.168.132.122 -P3316 -e 'select * from darren.test;'
[root@master ~]# mysql -uatlas -p1234567 -h 192.168.132.122 -P3316 -e 'select * from darren.test;'
[root@master ~]# mysql -uatlas -p1234567 -h 192.168.132.122 -P3316 -e 'select * from darren.test;'
查看结果
因为配置文件配置读取的操作权重是5:1
192.168.132.121:执行一次
mysql> show global status like '%select%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Com_insert_select | |
| Com_replace_select | |
| Com_select | |
| Connection_errors_select | |
| Select_full_join | |
| Select_full_range_join | |
| Select_range | |
| Select_range_check | |
| Select_scan | |
+--------------------------+-------+
192.168.132.122 #增加7次
mysql> show global status like '%select%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Com_insert_select | |
| Com_replace_select | |
| Com_select | |
| Connection_errors_select | |
| Select_full_join | |
| Select_full_range_join | |
| Select_range | |
| Select_range_check | |
| Select_scan | |
+--------------------------+-------+
5.2 写操作验证
192.168.132.121使用atlat进入数据库
[root@master mysql]# mysql -uatlas -p1234567 -h 192.168.132.122 -P3316
mysql> insert into test values (5);
192.168.132.121查看
mysql> select * from test;
192.168.132.122查看:
mysql> select * from darren.test;
多执行几次
mysql> insert into test values (6);
mysql> insert into test values (7);
mysql> insert into test values (8);
mysql> insert into test values (9);
mysql> insert into test values (10);
192.168.132.121查看:
mysql> select * from test;
192.168.132.122查看
数据一直,说明写操作一直在主端192.168.132.121端.
基本读写分离的验证完成
最新文章
- 荒芜的周六-PHP之面向对象(三)
- UVA11136Hoax or what( multiset的应用)
- Html笔记(九)头标签
- 简单的背包问题(入门)HDU2602 HDU2546 HDU1864
- #define 和 typedef场合
- iOS应用的几个阶段
- 在Debian9(linux)上使用 的 python 3 IDLE(已经安装了python 2.7 的情况下)
- 【linux之shell脚本】
- Web应用安全测试
- Spring Boot:简介
- DB2数据库常用的函数
- mongodb与java整合
- Philosopher’s Walk(递归)
- 基于MATLAB System Generator 搭建Display Enhancement模型
- Linux基础命令---显示树形进程pstree
- Delphi : 制作程序启动欢迎界面
- ELASTIC 5.2部署并收集nginx日志
- JAVA RSA私钥 加密(签名) 对应 C# RSA私钥 加密(签名)
- BZOJ 3173 最长上升子序列(树状数组+二分+线段树)
- CC1101是一种低成本真正单片的UHF收发器
热门文章
- 《C语言深度解剖》学习笔记之函数
- vs code python保存时pylint提示";Unable to import 'flask'";
- 2019-8-31-dotnet-通过-WMI-获取系统信息
- @雅礼集训01/13 - T1@ union
- 2006年NOIP普及组复赛题解
- IntStack(存放int型值,带迭代器) 附模板化实现 p406
- Python--day41--事件和信号量之模拟连接数据库并在连接三次后抛出连接超时异常
- servicemix-4.5.3 启动日志
- 常用mime.types
- Moq基础 判断方法被执行