【MySQL】maxscale 读写分离测试

1.主库节点测试

--4006 端口对应写入节点。每次都会转发到主节点。
--4008 端口对应读取节点。
[root@mysql1 trace]# mysql -umaxscale -pMaxscale#123 -h192.168.1.60 -P4006 -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 trace]# mysql -umaxscale -pMaxscale#123 -h192.168.1.60 -P4006 -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 trace]# mysql -umaxscale -pMaxscale#123 -h192.168.1.60 -P4006 -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 trace]# mysql -umaxscale -pMaxscale#123 -h192.168.1.60 -P4006 -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 trace]# mysql -umaxscale -pMaxscale#123 -h192.168.1.60 -P4006 -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     |
+------------+

2.只读节点测试 

--4008 端口对应读取节点。
--由此可见每次都转发到 mysql2/mysql3/mysql4,且每次都是轮训读取。

[root@mysql1 trace]# mysql -umaxscale -pMaxscale#123 -h192.168.1.60 -P4008 -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 trace]# mysql -umaxscale -pMaxscale#123 -h192.168.1.60 -P4008 -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 trace]# mysql -umaxscale -pMaxscale#123 -h192.168.1.60 -P4008 -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 trace]# mysql -umaxscale -pMaxscale#123 -h192.168.1.60 -P4008 -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 trace]# mysql -umaxscale -pMaxscale#123 -h192.168.1.60 -P4008 -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 trace]# mysql -umaxscale -pMaxscale#123 -h192.168.1.60 -P4008 -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 trace]# mysql -umaxscale -pMaxscale#123 -h192.168.1.60 -P4008 -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     |
+------------+

3.总结 

mysqlrouter,maxscale,dble 都是非常好的读写分离工具。对于读写分离的场景，非常方便。

MHA 完美的高可用方案+mysqlrouter/maxscale/dble+haproxy+keepalived 实现完美的读写分离加高可用解决方案。