maxscale读写分离

构建数据库读写分离

**构建思路

1. 部署MySQL一主一从结构
2. 部署MySQL代理服务器
   装包、修改配置文件、启动服务
   3.测试配置
   客户端连接代理服务访问数据**

部署maxscale服务
MaxScale代理软件
由MySQL的兄弟公司MariaDB开发
下载地址
https://downloads.mariadb.com/files/MaxScale
]# rpm -ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm

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

[maxscale]				##定义线程个数
threads=auto

[server1]					##定义数据库服务器
type=server
address=192.168.4.11		##master主机IP地址
port=3306
protocol=MySQLBackend

[server2]					##定义数据库服务
type=server
address=192.168.4.22		##slave主机IP地址
port=3306
protocol=MySQLBackend

[MySQL Monitor]			##定义要监控的数据库节点
type=monitor
module=mysqlmon
servers=server1,server2		##主、从数据库的主机名
user=maxscalemon			##监控用户
passwd=123qqq...Q		##监控用户密码
monitor_interval=10000

[Read-Write Service]		##定义读写分离的数据库节点
type=service
router=readwritesplit
servers=server1,server2		##主、从数据库的主机名
user=maxscaleroute		##路由用户
passwd=123qqq...Q		##密码
max_slave_connections=100%

[MaxAdmin Service]		##定义管理服务
type=service
router=cli

[Read-Write Listener]		##定义读写分离服务端口号
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006

[MaxAdmin Listener]		##定义管理服务端口号
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default
port=4016				##端口

在主/从服务器创建授权用户(在主数据库服务器上做)

mysql>  grant replication slave ,replication client on *.* to maxscalemon@"%"  identified by "123qqq...Q";		##创建监控用户	
mysql> grant select on mysql.* to maxscaleroute@"%" identified by "123qqq...Q";		##创建路由用户

启动服务

主要操作:
启动服务、查看端口、停止服务
]

# maxscale -f /etc/maxscale.cnf			##启动服务
]# netstat -nutpl | grep maxscale			##查看端口
tcp6       0      0 :::4006                 :::*                    LISTEN      1300/maxscale       
tcp6       0      0 :::4016                 :::*                    LISTEN      1300/maxscale    
]# kill  -9 PID号						##停止服务 (一次杀不死,可以多杀几次)

查看状态

]# maxadmin  -uadmin -pmariadb -P4006
]# maxadmin -uadmin -pmariadb -P 4016
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status              
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.4.11    |  3306 |           0 | Master, Running
server2            | 192.168.4.22    |  3306 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale> 

测试配置
在代理服务器本机访问管理
]# maxadmin -uadmin -pmariadb -P端口
客户端连接代理访问数据
]# mysql -h服务器地址 -P端口 -u用户名 -p密码

在主服务器上授权用户

mysql> create database gamedb; //建库
Query OK, 1 row affected (0.03 sec)
mysql> grant select,insert,update,delete on gamedb.* to yaya99@"%" identified by "123qqq...A"; //用户授权
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> create table gamedb.user(name char(15)); //建表
Query OK, 0 rows affected (0.31 sec)
mysql> select  * from  gamedb.user;
Empty set (0.01 sec)
mysql>

在从服务器查看是否同步数据

 [root@mysql22 ~]# mysql -uroot -p123qqq...A
mysql> show grants for yaya99@"%"; //查看授权用户
+--------------------------------------------------------------------+
| Grants for yaya99@%                                                |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'yaya99'@'%'                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `gamedb`.* TO 'yaya99'@'%' |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> desc gamedb.user; //查看库表
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(15) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> select  * from gamedb.user;
Empty set (0.00 sec)
mysql>
mysql> insert into  gamedb.user values("xdd"); //用来验证数据读写分离
Query OK, 1 row affected (0.03 sec)
mysql>
mysql> select  * from gamedb.user;
+------+
| name |
+------+
| xdd  |
+------+
1 row in set (0.00 sec)
mysql>

在网站服务器上，连接数据读写分离服务器77

~]# mysql -h192.168.4.77 -P4006 -uyaya99 -p123qqq...A //连接读写分离服

查询数据

MySQL [(none)]> select  * from gamedb.user; //显示的是从服务器上的数据
+------+
| name |
+------+
| xdd  |
+------+
1 row in set (0.00 sec)
MySQL [(none)]>

存储数据

MySQL [(none)]> insert into gamedb.user values("pmm"); //插入记录
Query OK, 1 row affected (0.05 sec)
MySQL [(none)]> select  * from gamedb.user; //查看记录
+------+
| name |
+------+
| xdd  |
| pmm  |
+------+
2 rows in set (0.00 sec)
MySQL [(none)]>

在主服务器本机登录查看数据

[root@mysql11 ~]# mysql -uroot -p123qqq...A -e "select  * from  gamedb.user"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| name |
+------+
| pmm  |
+------+
[root@mysql11 ~]#

客户端是将数据写入主数据库服务器 ,读的是从数据库服务器上的信息.