作者: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/f[maxscale]threads=auto #设置为根据cpu性能来设置开启线程数量(建议auto)#设置所用到的两个服务器(可设置多个,5yy和p即可粘贴格式)[server1] #名不可重复type=serveraddress=192.168.4.51 #你所用到的服务器ipport=3306protocol=MySQLBackend[server2]type=serveraddress=192.168.4.52 #同上port=3306protocol=MySQLBackend#监控用户信息配置区(用于检查主从服务状态以及MySQL服务状态)[MySQL Monitor]type=monitormodule=mysqlmonservers=server1,server2 #写入上面所用到的服务器server1与server2user=maxscalemon #用户名和密码可自定义,但用户授权时必须一致passwd=123456monitor_interval=10000#读写权限用户配置区(用于检查用户账户是否存在以及读写分离的分配,也就是告诉系统:主-write,从-read)[Read-Write Service]type=servicerouter=readwritesplitservers=server1,server2 #写入参与的服务器server1与server2user=maxscalerouter #用户名和密码可自定义,但用户授权时必须一致passwd=123456max_slave_connections=100%#以上两个用户maxscalemon以及maxscalerouter在MySQL中需要grant授权#maxadmin管理用户配置区(maxscale自带本地用户无需grant授权)[MaxAdmin Service]type=servicerouter=cli[Read-Write Listener]type=listenerservice=Read-Write Serviceprotocol=MySQLClientport=4006[MaxAdmin Listener]type=listenerservice=MaxAdmin Serviceprotocol=maxscaledsocket=defaultport=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/f #maxscale内置启动命令[root@mysqlmaxscale~]#netstat -ntlup |grep maxscaletcp6 00 :::4006 :::*LISTEN1215/maxscale tcp6 00 :::4016 :::*LISTEN1215/maxscale #启动服务后日志文件就会生成: /var/log/maxscale/maxscale.log[root@mysqlmaxscale~]#cat /var/log/maxscale/maxscale.log#在日志信息中notice(注意)视为正常运行成功信息,如果为error则表示启动失败-09-06 11:16:46 notice : Working directory: /var/log/maxscale-09-06 11:16:46 notice : MariaDB MaxScale 2.1.2 started-09-06 11:16:46 notice : MaxScale is running in process 1215-09-06 11:16:46 notice : Configuration file: /etc/f-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, Runningserver2 | 192.168.4.52 | 3306 | 0 | Slave, Running-------------------+-----------------+-------+-------------+--------------------MaxScale> exit #退出命令(也可以用quit)

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

测试分为两个步骤:

一、测试主从服务:

#进入主服务器创库建表授权[root@mysqlmaster ~]# mysql -p123456mysql> 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 -p123456mysql> 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 -p123456mysql> 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 -P4006mysql> select * from db7.test; #此时查看到的数据7879是slave从服务器上所插入的数据+------+| id |+------+| 6565 || 7879 |+------+2 rows in set (0.00 sec)mysql>exit

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

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