2. mysql读写分离

1. 整体架构

2. 原理和构建思路

 - 原理
	多台MySQL服务器通过主从复制保持数据一致
	由maxscale代理服务器面向客户端
	收到SQL写请求时，交给主服务器处理
	收到SQL读请求时，交给从服务器处理
- 构建思路
	构建主从同步
	部署maxscale代理服务器
	客户端访问代理服务器测试

3. 主从服务器配置监控和验证用户（主库配置，从库会自动同步）

mysql> grant replication slave,replication client on *.* to myuser@"%" identified by "123456";
mysql> grant select on mysql.* to maxuser@"%" identified by "123456";
mysql> grant all on *.* to test@"%" identified by "123456";

分别是授权监控用户、授权验证用户、授权访问用户。

4. 部署maxscale代理服务器

主从同步参考Mysql主从同步配置这里直接部署代理服务器。

maxscale由MariaDB公司开发

下载地址

1. 安装

rpm  -ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm
#缺少依赖可以用yum或apt-get安装
yum install -y maxscale-2.1.2-1.rhel.7.x86_64.rpm
#离线安装可以用yum下载所有依赖到可以连接互联网的服务，然后scp或上传到离线服务器安装
yum install maxscale-2.1.2-1.rhel.7.x86_64.rpm --downloadonly --downloaddir=/root/

2. 修改配置文件 /etc/maxscale.cnf

# MaxScale documentation:
# https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-23/

# Global parameters
#
# Complete list of configuration options:
# https://mariadb.com/kb/en/mariadb-maxscale-23-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=server
address=192.168.0.101     #主服务器IP地址
port=3306
protocol=MySQLBackend

[server2]             #定义数据库服务器名
type=server
address=192.168.0.102      #从服务器IP地址
port=3306
protocol=MySQLBackend

# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MariaDB Monitor documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-23-mariadb-monitor/

[MariaDB-Monitor]          #定义要监控的数据库节点
type=monitor
module=mysqlmon            #
servers=server1,server2    #定义的数据库服务器名
user=myuser                #定义监控服务用户名
password=Admin@123!!       #定义监控服务密码
monitor_interval=10000

# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
# ReadConnRoute documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-23-readconnroute/

[Read-Only-Service]
type=service
router=readconnroute
servers=server1
user=myuser
password=mypwd
router_options=slave

# ReadWriteSplit documentation:
# https://mariadb.com/kb/en/mariadb-maxscale-23-readwritesplit/

[Read-Write-Service]              #定义读写分离的数据库节点
type=service
router=readwritesplit
servers=server1,server2           #定义的数据库服务器名
user=maxuser                      #定义连接数据库服务器的验证用户
password=Admin@123!!			#密码

# This service enables the use of the MaxAdmin interface
# MaxScale administration guide:
# https://mariadb.com/kb/en/mariadb-maxscale-23-maxadmin-admin-interface/

[MaxAdmin-Service]
type=service
router=cli

# Listener definitions for the services
#
# These listeners represent the ports the
# services will listen on.
#

#[Read-Only-Listener]
#type=listener
#service=Read-Only-Service
#protocol=MariaDBClient
#port=4008

[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=4006

[MaxAdmin-Listener]
type=listener
service=MaxAdmin-Service
protocol=maxscaled
#socket=default
port=4016

上述配置中验证用户、监控用户的用户名和密码需要和第三步数据库中配置的完全一样。

3. 启动maxscale
   [root@localhost~]# maxscale -f /etc/maxscale.cnf

注：maxscale最新版本不能这样启动，提示root用户不允许启动该服务。最好写一个启动文件，在启动文件中指定用户启动改程序

vi /etc/systemd/system/maxscale.server
[Unit]
Description=MaxScale Database Proxy
After=network.target

[Service]
User=maxscale
Group=maxscale
ExecStart=/usr/bin/maxscale -e /var/log/maxscale/maxscale.log -c /etc/maxscale.cnf
Restart=on-failure

[Install]
WantedBy=multi-user.target

4. 查看服务连接管理端口

[root@localhost ~]# ss -nptul | grep maxscale
tcp    LISTEN     0      128    127.0.0.1:8989                  *:*                   users:(("maxscale",pid=45225,fd=35))
tcp    LISTEN     0      128    [::]:4006               [::]:*                   users:(("maxscale",pid=45225,fd=31))
tcp    LISTEN     0      128    [::]:4016               [::]:*                   users:(("maxscale",pid=45225,fd=34))

5. 客户端连接代理服务器验证

[root@clent ~]# mysql -h192.168.0.103 -utest -p123456 -P4006

写入数据、在主库查看数据、在从库验证读取