MySQL 读写分离

MySQL Router是什么？

MySQL Router是MySQL官方提供的一个轻量级MySQL中间件

MySQL Router实现了MySQL的读写分离，对MySQL请求进行了负载均衡。

注意：前提是后端实现了MySQL的主从复制

配置MySQL Router

角色名	主机ip	MySQL版本
MySQL Router	192.168.102.138	MySQL Router 8.0.33
master	192.168.102.139	MySQL 5.7.41
slave	192.168.102.136	MySQL 5.7.41

---

1.下载MySQL Router源码包

 2.上传到Linux里并且安装

[root@MySQL Router ~]# ls
anaconda-ks.cfg  mysql-router-community-8.0.33-1.el7.x86_64.rpm

[root@MySQL Router ~]# rpm -ivh mysql-router-community-8.0.33-1.el7.x86_64.rpm 
警告：mysql-router-community-8.0.33-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-router-community-8.0.33-1.e################################# [100%]

[root@MySQL Router ~]# rpm -qpl mysql-router-community-8.0.33-1.el7.x86_64.rpm 
警告：mysql-router-community-8.0.33-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
/etc/logrotate.d/mysqlrouter
/etc/mysqlrouter
/etc/mysqlrouter/mysqlrouter.conf
/usr/bin/mysqlrouter
/usr/bin/mysqlrouter_keyring
/usr/bin/mysqlrouter_passwd
/usr/bin/mysqlrouter_plugin_info
/usr/lib/systemd/system/mysqlrouter.service
/usr/lib/tmpfiles.d/mysqlrouter.conf
/usr/lib64/mysqlrouter
/usr/lib64/mysqlrouter/connection_pool.so
/usr/lib64/mysqlrouter/destination_status.so
/usr/lib64/mysqlrouter/http_auth_backend.so
/usr/lib64/mysqlrouter/http_auth_realm.so
/usr/lib64/mysqlrouter/http_server.so
/usr/lib64/mysqlrouter/io.so
/usr/lib64/mysqlrouter/keepalive.so
/usr/lib64/mysqlrouter/metadata_cache.so
/usr/lib64/mysqlrouter/private
/usr/lib64/mysqlrouter/private/libmysqlharness.so.1
/usr/lib64/mysqlrouter/private/libmysqlharness_stdx.so.1
/usr/lib64/mysqlrouter/private/libmysqlharness_tls.so.1
/usr/lib64/mysqlrouter/private/libmysqlrouter.so.1
/usr/lib64/mysqlrouter/private/libmysqlrouter_connection_pool.so.1
/usr/lib64/mysqlrouter/private/libmysqlrouter_destination_status.so.1
/usr/lib64/mysqlrouter/private/libmysqlrouter_http.so.1
/usr/lib64/mysqlrouter/private/libmysqlrouter_http_auth_backend.so.1
/usr/lib64/mysqlrouter/private/libmysqlrouter_http_auth_realm.so.1
/usr/lib64/mysqlrouter/private/libmysqlrouter_io_component.so.1
/usr/lib64/mysqlrouter/private/libmysqlrouter_metadata_cache.so.1
/usr/lib64/mysqlrouter/private/libmysqlrouter_mysqlxmessages.so.1
/usr/lib64/mysqlrouter/private/libmysqlrouter_routing.so.1
/usr/lib64/mysqlrouter/private/libprotobuf-lite.so.3.19.4
/usr/lib64/mysqlrouter/rest_api.so
/usr/lib64/mysqlrouter/rest_connection_pool.so
/usr/lib64/mysqlrouter/rest_metadata_cache.so
/usr/lib64/mysqlrouter/rest_router.so
/usr/lib64/mysqlrouter/rest_routing.so
/usr/lib64/mysqlrouter/router_openssl.so
/usr/lib64/mysqlrouter/router_protobuf.so
/usr/lib64/mysqlrouter/routing.so
/usr/share/doc/mysql-router-community-8.0.33
/usr/share/doc/mysql-router-community-8.0.33/INFO_BIN
/usr/share/doc/mysql-router-community-8.0.33/INFO_SRC
/usr/share/doc/mysql-router-community-8.0.33/LICENSE.router
/usr/share/doc/mysql-router-community-8.0.33/README.router
/usr/share/man/man1/mysqlrouter.1.gz
/usr/share/man/man1/mysqlrouter_passwd.1.gz
/usr/share/man/man1/mysqlrouter_plugin_info.1.gz
/var/log/mysqlrouter
/var/run/mysqlrouter

3.修改配置文件

[root@MySQL Router ~]# cd /etc/mysqlrouter/
[root@MySQL Router mysqlrouter]# ls
mysqlrouter.conf
[root@MySQL Router mysqlrouter]# vim mysqlrouter.conf 
[routing:read_write]
bind_address = 192.168.102.138
bind_port = 7001
mode = read-write
destinations = 192.168.102.139:3306
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 2

[routing:read_only]
bind_address = 192.168.102.138
bind_port = 7002
mode = read-only
destinations = 192.168.102.136:3306
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 2

4.启动MySQL Router

[root@MySQL Router mysqlrouter]# service mysqlrouter start
Redirecting to /bin/systemctl start mysqlrouter.service
[root@MySQL Router mysqlrouter]# netstat -anplut|grep mysqlrouter
tcp        0      0 192.168.102.138:7001    0.0.0.0:*               LISTEN      8894/mysqlrouter    
tcp        0      0 192.168.102.138:7002    0.0.0.0:*               LISTEN      8894/mysqlrouter

---

在master上创建2个用户，一个有读写权限，一个读权限。

root@(none) 12:46  mysql>grant all on *.* to "wu"@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.05 sec)

root@(none) 12:47  mysql>grant select on *.* to 'xie'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

---

另外用一台机器来测试

[root@Localhost ~]# mysql -uwu -p123456 -P7001 -h 192.168.102.138
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.102.138' (113)

 连不上的原因是防火墙没有关闭

[root@MySQL Router mysqlrouter]# service firewalld stop
Redirecting to /bin/systemctl stop firewalld.service

---

使用wu用户登录，测试读写权限。

[root@Localhost ~]# mysql -uwu -p'123456' -P7002 -h 192.168.102.138
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

wu@(none) 13:51  mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hunan              |
| mysql              |
| performance_schema |
| sys                |
| tennis             |
| test               |
| wei                |
+--------------------+
8 rows in set (0.01 sec)

wu@(none) 13:51  mysql>show processlist;
+----+-------------+-----------------------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host                  | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------------------+------+---------+------+--------------------------------------------------------+------------------+
|  1 | system user |                       | NULL | Connect | 5419 | Waiting for master to send event                       | NULL             |
|  2 | system user |                       | NULL | Connect | 3761 | Slave has read all relay log; waiting for more updates | NULL             |
|  4 | root        | localhost             | NULL | Sleep   |  941 |                                                        | NULL             |
|  8 | wu          | 192.168.102.138:52774 | NULL | Query   |    0 | starting                                               | show processlist |
+----+-------------+-----------------------+------+---------+------+--------------------------------------------------------+------------------+
4 rows in set (0.03 sec)

wu@wei 13:53  mysql>select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  7 | cheng |
+----+-------+
1 rows in set (0.01 sec)

wu@wei 13:53  mysql>insert into t1 values(8,'wu');
Query OK, 1 row affected (0.02 sec)

wu@wei 13:53  mysql>select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  7 | cheng |
|  8 | wu    |
+----+-------+
2 rows in set (0.01 sec)

---

使用xie用户登录，测试读权限。

[root@Localhost ~]# mysql -uxie -p'123456' -P7002 -h 192.168.102.138
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

xie@(none) 13:59  mysql>use wei;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
xie@wei 13:59  mysql>select * from t1;
+----+-------+
| id | name  |
+----+-------+
|  7 | cheng |
|  8 | wu    |
+----+-------+
2 rows in set (0.01 sec)

xie@wei 13:59  mysql>insert into t1 values(10,'xie');
ERROR 1142 (42000): INSERT command denied to user 'xie'@'192.168.102.138' for table 't1'

因为没有写权限，所以xie用户使用insert会报错。