【MySQL】mysql router 读写分离中间件使用

1.检查MySQL router; 

[root@mysql1 mysqlrouter]# netstat -ntpl|grep mysqlrouter
tcp        0      0 192.168.1.11:7001       0.0.0.0:*               LISTEN      129393/mysqlrouter  
tcp        0      0 192.168.1.11:7002       0.0.0.0:*               LISTEN      129393/mysqlrouter  

2.测试主节点 

[root@mysql1 etc]# mysql -uroot -prootroot -h192.168.1.11 -P7001 -e "select @@hostname;"
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql1     |
+------------+
[root@mysql1 etc]# mysql -uroot -prootroot -h192.168.1.11 -P7001 -e "select @@hostname;"
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql1     |
+------------+
[root@mysql1 etc]# mysql -uroot -prootroot -h192.168.1.11 -P7001 -e "select @@hostname;"
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql1     |
+------------+

--可以看到每次都是连接到mysql1（主节点）; 
--关闭主库.则 7001 无法访问主库。
systemctl start mysqld 
[root@mysql1 etc]# mysql -uroot -prootroot -h192.168.1.11 -P7001 -e "select @@hostname;"
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to remote MySQL server for client connected to '192.168.1.11:7001'
[root@mysql1 etc]# mysql -uroot -prootroot -h192.168.1.11 -P7001 -e "select @@hostname;"
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to remote MySQL server for client connected to '192.168.1.11:7001'
[root@mysql1 etc]# mysql -uroot -prootroot -h192.168.1.11 -P7001 -e "select @@hostname;"
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to remote MySQL server for client connected to '192.168.1.11:7001'
[root@mysql1 etc]# mysql -uroot -prootroot -h192.168.1.11 -P7001 -e "select @@hostname;"
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to remote MySQL server for client connected to '192.168.1.11:7001'

--启动主库  
systemctl start mysqld  
--7001 端口可以重新访问主库。
[root@mysql1 etc]# mysql -uroot -prootroot -h192.168.1.11 -P7001 -e "select @@hostname;"
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql1     |
+------------+
[root@mysql1 etc]# mysql -uroot -prootroot -h192.168.1.11 -P7001 -e "select @@hostname;"
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql1     |
+------------+
[root@mysql1 etc]# mysql -uroot -prootroot -h192.168.1.11 -P7001 -e "select @@hostname;"
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql1     |
+------------+

3.测试读节点负载均衡 

[root@mysql1 etc]# mysql -uroot -prootroot -h192.168.1.11 -P7002 -e "select @@hostname;"
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql1     |
+------------+
[root@mysql1 etc]# mysql -uroot -prootroot -h192.168.1.11 -P7002 -e "select @@hostname;"
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql2     |
+------------+
[root@mysql1 etc]# mysql -uroot -prootroot -h192.168.1.11 -P7002 -e "select @@hostname;"
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql3     |
+------------+
[root@mysql1 etc]# mysql -uroot -prootroot -h192.168.1.11 -P7002 -e "select @@hostname;"
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql4     |
+------------+
[root@mysql1 etc]# mysql -uroot -prootroot -h192.168.1.11 -P7002 -e "select @@hostname;"
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql1     |
+------------+
[root@mysql1 etc]# mysql -uroot -prootroot -h192.168.1.11 -P7002 -e "select @@hostname;"
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql2     |
+------------+
[root@mysql1 etc]# mysql -uroot -prootroot -h192.168.1.11 -P7002 -e "select @@hostname;"
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql3     |
+------------+
[root@mysql1 etc]# mysql -uroot -prootroot -h192.168.1.11 -P7002 -e "select @@hostname;"
Logging to file '/data/mysql/logs/query.log'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql4     |
+------------+
--可以看到，只读节点按照mysql1,mysql2,mysql3,mysql4 的方式轮询。

4.总结 

mysqlrouter 是一款非常便捷的读写分离中间件。