作用：实现数据访问读写分离，减轻单台服务器的兵法访问压力
前提：实现主从同步
原理：
MySQL读写分离是指，作为后端mysql主从服务器的代理，它直接接受客户端的请求，对SQL语句进行分析，判断出是读操作还是写操作，然后分发至对应的mysql服务器上。让master处理写操作，让slave处理读操作。
非常适用于读操作量比较大的场景，可减轻master的压力。
mysql-proxy是官方提供的mysql中间件产品可以实现负载平衡，读写分离，failover等，还有一些第三方的中间价也可以实现此功能，Maxscale就是其中一种。
软件：mariadb-maxscale：由mysql兄弟公司Mariadb开发
下载软件：
浏览器搜索mariadb maxscale
/kb/en/maxscale/
主要配置文件：/etc/f
一、规划
代理服务器IP：192.168.21.168读服务IP：192.168.21.168写服务器IP：192.168.21.169mysql版本：5.7.27maxscale版本：2.2.9前提：已实现主从同步
二、安装配置Maxscale
1.安装MaxScale
[root@whx software]# rpm -ivh maxscale-2.2.9-1.centos.7.x86_64.rpm
2.在主从库配置用户及权限（主库创建即可，从库自动同步）
#创建监控用户(root@localhost:mysql.sock)[(none)]>grant replication slave,replication client on *.* to scalemon identified by '123456';Query OK, 0 rows affected, 1 warning (0.06 sec)#以下权限在Maxscale2.2.9版本中是必须的权限，否则无法启动(root@localhost:mysql.sock)[(none)]>grant select on mysql.* to scalemon;#创建路由用户(root@localhost:mysql.sock)[(none)]>grant select on mysql.* to maxscale identified by '123456';Query OK, 0 rows affected, 1 warning (0.10 sec)#创建访问用户(root@localhost:mysql.sock)[(none)]>grant all on *.* to student identified by '123456';Query OK, 0 rows affected, 1 warning (0.01 sec)(root@localhost:mysql.sock)[(none)]>create database gamedb;(root@localhost:mysql.sock)[(none)]>grant all on gamedb.* to student identified by '123456’;
3.修改配置文件
[root@whx software]# cp /etc/f /etc/f.bak[root@whx software]# cat /etc/f#对照修改# MaxScale documentation:# /kb/en/mariadb-enterprise/mariadb-maxscale-22/# Global parameters## Complete list of configuration options:# /kb/en/mariadb-enterprise/mariadb-maxscale-22-mariadb-maxscale-configuration-usage-scenarios/[maxscale]threads=auto# Server definitions## Set the address of the server to the network# address of a MariaDB server.#[server1]type=serveraddress=192.168.21.168port=6001protocol=MariaDBBackend[server2]type=serveraddress=192.168.21.169port=6001protocol=MariaDBBackend# Monitor for the servers## This will keep MaxScale aware of the state of the servers.# MariaDB Monitor documentation:# /kb/en/mariadb-enterprise/mariadb-maxscale-22-mariadb-monitor/[MariaDB-Monitor]type=monitormodule=mariadbmonservers=server1,server2user=scalemonpasswd=123456 #官网推荐是用加密文件monitor_interval=2000# Service definitions## Service Definition for a read-only service and# a read/write splitting service.## ReadConnRoute documentation:# /kb/en/mariadb-enterprise/mariadb-maxscale-22-readconnroute/[Read-Only-Service]type=servicerouter=readconnrouteservers=server1,server2user=scalemonpasswd=123456router_options=slave# ReadWriteSplit documentation:# /kb/en/mariadb-enterprise/mariadb-maxscale-22-readwritesplit/[Read-Write-Service]type=servicerouter=readwritesplitservers=server1,server2user=scalemonpasswd=123456# This service enables the use of the MaxAdmin interface# MaxScale administration guide:# /kb/en/mariadb-enterprise/mariadb-maxscale-22-maxadmin-admin-interface/[MaxAdmin-Service]type=servicerouter=cli# Listener definitions for the services## These listeners represent the ports the# services will listen on.#[Read-Only-Listener]type=listenerservice=Read-Only-Serviceprotocol=MariaDBClientport=4008[Read-Write-Listener]type=listenerservice=Read-Write-Serviceprotocol=MariaDBClientport=4006[MaxAdmin-Listener]type=listenerservice=MaxAdmin-Serviceprotocol=maxscaledsocket=default
删除配置文件中有#号的行
[root@whx software]# sed -i '/#/d' /etc/
启动
Maxscale2.2.9版本不允许直接由root用户使用 maxscale -f /etc/f启动服务，需要指定用户-U
Maxscale日志，数据位置，需要注意修改权限：/var/lib/maxscale、/var/log/maxscale、/var/run/maxscale
maxscale -f /etc/f -U mysqlchown -R mysql.mysql /var/lib/maxscalechown -R mysql.mysql /var/log/maxscalechown -R mysql.mysql /var/run/maxscale
4.端口查看
[root@whx maxscale]# netstat -utnalp |grep maxscaletcp 00 127.0.0.1:89890.0.0.0:*LISTEN18740/maxscaletcp 00 192.168.21.168:37008 192.168.21.169:6001ESTABLISHED 18740/maxscaletcp 00 192.168.21.168:39848 192.168.21.168:6001ESTABLISHED 18740/maxscaletcp6 00 :::4008 :::*LISTEN18740/maxscaletcp6 00 :::4006 :::*LISTEN18740/maxscale
注：4006读写分离使用的端口
4008管理服务使用的端口
5.登陆
[root@whx maxscale]# maxadmin -S /tmp/maxadmin.sockMaxScale> help。#查看帮助MaxScale> list clientsClient Connections-----------------+------------------+----------------------+------------Client| DCB | Service | Session-----------------+------------------+----------------------+------------localhost | 0x1cb1550 | MaxAdmin-Service| 0x1cb2b30-----------------+------------------+----------------------+------------MaxScale> list serversServers.-------------------+-----------------+-------+-------------+--------------------Server | Address | Port | Connections | Status -------------------+-----------------+-------+-------------+--------------------server1 | 192.168.21.168 | 6001 | 0 | Master, Runningserver2 | 192.168.21.169 | 6001 | 0 | Slave, Running-------------------+-----------------+-------+-------------+——————————
使用代理端口，IP登陆数据库，可以进行读写操作
[root@whx maxscale]# mysql -h 192.168.21.168 -P4006 -ustudent -p
6.关闭maxscale服务
Killall -9 maxscale
