实现MySQL读写分离---maxscale代理服务器配置(详解)

作者:CSDN阿坤

著作:Maxscale读写分离服务

说明:需要动的地方都有注释,没注释的默认即可

1.前提必须配好两台的主从服务并另配一台maxscale服务器.
2.大致流程就是大家熟知的黯然销魂三部曲:安包---->修改配置文件---->启服务!
************首先进入到maxscale服务器上配置

[root@mysqlmaxscale~]#yum -y install  maxscale-2.1.2-1.rhel.7.x86_64.rpm 
[root@mysqlmaxscale~]#systemctl stop mysqld #确保数据库服务关闭,涉及到端口冲突
[root@mysqlmaxscale~]#vim /etc/maxscale.cnf

[maxscale]
threads=auto #设置为根据cpu性能来设置开启线程数量(建议auto)
#设置所用到的两个服务器(可设置多个,5yy和p即可粘贴格式)
[server1] #名不可重复
type=server
address=192.168.4.51 #你所用到的服务器ip
port=3306
protocol=MySQLBackend

[server2]
type=server
address=192.168.4.52 #同上
port=3306
protocol=MySQLBackend

#监控用户信息配置区(用于检查主从服务状态以及MySQL服务状态)
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2 #写入上面所用到的服务器server1与server2
user=maxscalemon  #用户名和密码可自定义,但用户授权时必须一致
passwd=123456
monitor_interval=10000

#读写权限用户配置区(用于检查用户账户是否存在以及读写分离的分配,也就是告诉系统:主-write,从-read)
[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2 #写入参与的服务器server1与server2
user=maxscalerouter  #用户名和密码可自定义,但用户授权时必须一致
passwd=123456
max_slave_connections=100%
#以上两个用户maxscalemon以及maxscalerouter在MySQL中需要grant授权

#maxadmin管理用户配置区(maxscale自带本地用户无需grant授权)
[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    #端口需要自己加上(任意没有用到的端口即可)

***********其次进入到master主服务器配置(slave从服务上配置是不会被同步到master主服务器的)

[root@mysqlmaster~]#mysql -p123456
#密码设为123456的前提必须保证policy=0(密码认证强度规则关闭),length=6(密码只读6位).
#replication slave授予可检查主从状态权限,replication client授予检查数据库mysql的状态权限.
#maxscalemon为mon=monitor监视用户,maxscalerouter路由用户:用来区分读写分离分别是那台服务器
mysql> grant replication slave,replication client on *.* to maxscalemon@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql> grant select on mysql.* to maxscalerouter@"%" identified by '123456';
Query OK, 0 rows affected, 1 warning (0.12 sec)

**********授权结束后回到maxscale服务器上检查配置文件，确认无误后启动maxscale服务

[root@mysqlmaxscale~]#maxscale -f /etc/maxscale.cnf #maxscale内置启动命令
[root@mysqlmaxscale~]#netstat -ntlup |grep maxscale
tcp6       0      0 :::4006                 :::*                    LISTEN      1215/maxscale       
tcp6       0      0 :::4016                 :::*                    LISTEN      1215/maxscale  
#启动服务后日志文件就会生成: /var/log/maxscale/maxscale.log
[root@mysqlmaxscale~]#cat /var/log/maxscale/maxscale.log
#在日志信息中notice(注意)视为正常运行成功信息,如果为error则表示启动失败
2019-09-06 11:16:46   notice : Working directory: /var/log/maxscale
2019-09-06 11:16:46   notice : MariaDB MaxScale 2.1.2 started
2019-09-06 11:16:46   notice : MaxScale is running in process 1215
2019-09-06 11:16:46   notice : Configuration file: /etc/maxscale.cnf
2019-09-06 11:16:46   notice : Log directory: /var/log/maxscale
........
[root@mysqlmaxscale~]#maxadmin -uadmin -pmariadb -P4016 #maxscale管理用户本地登录
#非本地登录: mysql -hmysql57  -P4016 -uadmin -pmariadb(前提必须开启maxscale服务器上的mysql服务,否则报错)
MaxScale> list servers #配置maxscale服务成功后,输入此命令可监察到主从状态
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status              
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.4.51    |  3306 |           0 | Master, Running
server2            | 192.168.4.52    |  3306 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale> exit #退出命令(也可以用quit)

**********到此处呢maxscale整个服务就配置完成了,接下来我们进行配置测试:

测试分为两个步骤:

一、测试主从服务:

#进入主服务器创库建表授权
[root@mysqlmaster ~]# mysql -p123456
mysql> create database db7;
Query OK, 1 row affected (0.12 sec)
mysql> create  table db7.test(id int);
Query OK, 0 rows affected (0.46 sec)
mysql> grant select,insert on db7.* to wxk@"%" identified by '123456';
Query OK, 0 rows affected, 1 warning (0.12 sec)
＃进入从服务器查询权限
[root@mysqlslave ~]# mysql -p123456 -e "show grants for wxk;" 
+------------------------------------------------------------+
| Grants for wxk@%                                           |
+------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'wxk'@'%' WITH GRANT OPTION |
| GRANT SELECT, INSERT ON `db7`.* TO 'wxk'@'%'               |
+------------------------------------------------------------+
2 rows in set (0.00 sec)
#进入客户端查询权限并使用所拥有权限加以确认
[root@mysqlclient ~]# mysql -hmysql51 -uwxk -p123456
mysql> show grants;
+------------------------------------------------------------+
| Grants for wxk@%                                           |
+------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'wxk'@'%' WITH GRANT OPTION |
| GRANT SELECT, INSERT ON `db7`.* TO 'wxk'@'%'               |
+------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from db7.test;
Empty set (0.01 sec)
mysql> insert into db7.test values(6565);
Query OK, 1 row affected (0.16 sec)
mysql> select * from db7.test;
+------+
| id   |
+------+
| 6565 |
+------+
1 row in set (0.00 sec)
mysql>quit

二、测试读写分离：

#进入从服务器进行数据插入
[root@mysqlslave ~]# mysql -p123456
mysql> insert into db7.test values(7879);
Query OK, 1 row affected (0.13 sec)
mysql> select * from db7.test;
+------+
| id   |
+------+
| 6565 |
| 7879 |
+------+
2 rows in set (0.00 sec)
mysql>exit
#进入主服务器查看(主服务器是无法同步从服务器的)
[root@mysqlmaster ~]# mysql -p123456　-e "select * from db7.test;"
+------+
| id   |
+------+
| 6565 |
+------+
1 row in set (0.00 sec)
mysql>quit
＃进入客户端链接4006端口(读写监听端口服务)
[root@mysqlclient ~]# mysql -hmysql57 -uwxk -p123456 -P4006
mysql> select * from db7.test; #此时查看到的数据7879是slave从服务器上所插入的数据
+------+
| id   |
+------+
| 6565 |
| 7879 |
+------+
2 rows in set (0.00 sec)
mysql>exit

*****************当看到从服务器上插入的7879数据时则表示读写分离配置成功!

喜欢的朋友点击关注不迷路熬！(对您有用的话就给个赞吧，有赞有鼓励，干劲满满的！谁还不是个孩子呢！！)