读写分离
master:192.168.2.77
slave:192.168.2.74
mysqlrouter:192.168.2.105
1,提前在官网上下载好mysqlrouter安装包,再在Linux上安装mysqlrouter
[root@localhost ~]
2.修改mysqlrouter的配置文件
[root@localhost ~]
[routing:read_write]
bind_address = 192.168.2.105
bind_port = 7001
mode = read-write
destinations = 192.168.2.77:3306
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 2
[routing:read_only]
bind_address = 192.168.2.105
bind_port = 7002
mode = read-only
destinations = 192.168.2.74:3306
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 2
3.刷新
[root@localhost ~]
Redirecting to /bin/systemctl restart mysqlrouter.service
[root@localhost ~]
tcp 0 0 192.168.2.105:7001 0.0.0.0:* LISTEN 3332/mysqlrouter
tcp 0 0 192.168.2.105:7002 0.0.0.0:* LISTEN 3332/mysqlrouter
tcp6 0 0 :::3306 :::* LISTEN 2949/mysqld
4.master里新建用户验证
4.1验证[read_only]
root@(none) 21:13 mysql>create user 'yangyang'@'%' identified by '123456';
Query OK, 0 rows affected (0.50 sec)
root@(none) 21:15 mysql>grant select on *.* to 'yangyang'@'%';
Query OK, 0 rows affected (0.06 sec)
[root@localhost ~]
Redirecting to /bin/systemctl stop firewalld.service
#使用yangyang用户连接,执行建表语句(写),报错
4.2验证[write_read]
root@(none) 21:14 mysql>create user 'yeye'@'%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
root@(none) 21:16 mysql>grant all on *.* to 'yeye'@'%';
Query OK, 0 rows affected (0.00 sec)
使用yeye用户连接,执行建表语句(写),成功
验证发现:
7001端口虽然是[write_read],但如果连接该端口的用户没有可写权限,也是不能建表
7002端口是[read_only]只能读,但如果新用户有所有权限,也可以建表
所以,mysqlrouter里面的端口只是负责转发。通过连mysqlrouter机器,连的是哪个端口就转发到哪台机器上去。真正能控制权限的是用户。
在做读写分离的时候要注意用户的权限分配。如果slave上的用户有写的权力,该用户在slave上建表后,master上没有这个表,就会导致数据不一致。(读的用户尽量只连读的端口)
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)