一 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 '1234567'; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> flush privileges; 从库:192.168.132.122 mysql> grant all privileges on *.* to 'atlas'@'192.168.132.122' identified by '1234567'; #atlas连接数据库的用户名是atls,密码是1234567 Query OK, 0 rows affected, 1 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:3306 #从库地址，@后面的数字代表权重，用来负载均衡，默认权重为1。可设置多项，用逗号分隔 proxy-read-only-backend-addresses = 192.168.132.122:3306@5,192.168.132.121:3306@1 #用户名和密码，密码需要使用/usr/local/mysql-proxy/bin/encrypt加密 pwds = atlas:tj+W8xntBW8= daemon = true #守护进程 keepalive = true #线程数 event-threads = 1 #日志级别，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 = 1000 #Atlas监听端口 proxy-address = 0.0.0.0:3316 #如果是一台独立的服务器,这个端口可以和MySQL端口一样,设为3306 #管理监听端口 admin-address = 0.0.0.0:2345 admin-username = atlas admin-password = 1234567 #客户端连接默认字符集，不设置的话一般还需要使用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 | 0 | | Com_replace_select | 0 | | Com_select | 109 | | Connection_errors_select | 0 | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 25 | +--------------------------+-------+ 192.168.132.122: mysql> show global status like '%select%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Com_insert_select | 0 | | Com_replace_select | 0 | | Com_select | 8 | | Connection_errors_select | 0 | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 25 | +--------------------------+-------+
执行读操作
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 | 0 | | Com_replace_select | 0 | | Com_select | 109 | | Connection_errors_select | 0 | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 27 | +--------------------------+-------+ 192.168.122: 执行读操作 mysql> show global status like '%select%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Com_insert_select | 0 | | Com_replace_select | 0 | | Com_select | 10 | | Connection_errors_select | 0 | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 17 | +--------------------------+-------+
多执行几次
[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 | 0 | | Com_replace_select | 0 | | Com_select | 110 | | Connection_errors_select | 0 | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 30 | +--------------------------+-------+ 192.168.132.122 #增加7次 mysql> show global status like '%select%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | Com_insert_select | 0 | | Com_replace_select | 0 | | Com_select | 17 | | Connection_errors_select | 0 | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 0 | | Select_range_check | 0 | | Select_scan | 22 | +--------------------------+-------+
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端.
基本读写分离的验证完成