以下是一个示例说明:
假设你的架构是一主一从,主库监听在 6666 端口,从库监听在 6667 端口。
Admin> SELECT hostgroup_id, hostname, port, status, weight FROM mysql_servers;
+--------------+-----------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+-----------------+------+--------+--------+
| 0 | 192.168.198.239 | 6666 | ONLINE | 1 |
| 1 | 192.168.198.239 | 6667 | ONLINE | 1 |
+--------------+-----------------+------+--------+--------+
2 rows in set (0.00 sec)
现在,如果你关闭从库 6667 端口,你会发现 select 查询请求挂起,业务中断,并且请求没有切换到主库:
Admin> SELECT hostgroup_id, hostname, port, status, weight FROM runtime_mysql_servers;
+--------------+-----------------+------+---------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+-----------------+------+---------+--------+
| 0 | 192.168.198.239 | 6666 | ONLINE | 1 |
| 1 | 192.168.198.239 | 6667 | SHUNNED | 1 |
+--------------+-----------------+------+---------+--------+
2 rows in set (0.01 sec)
在 /var/lib/proxysql/proxysql.log
日志中,你可能会看到如下错误信息:
2024-08-23 09:28:21 MyHGC.cpp:228:get_random_MySrvC(): [ERROR] Hostgroup 1 has no servers available! Checking servers shunned for more than 1 second
2024-08-23 09:28:21 mysql_connection.cpp:1203:handler(): [ERROR] Failed to mysql_real_connect() on 1:192.168.198.239:6667 , FD (Conn:0 , MyDS:34) , 2002: Can't connect to server on '192.168.198.239' (111).
2024-08-23 09:28:21 MySQL_Monitor.cpp:3158:monitor_ping(): [ERROR] Server 192.168.198.239:6667 missed 3 heartbeats, shunning it and killing all the connections. Disabling other checks until the node comes back online.
为了解决这个问题,你可以调整 mysql_servers 表的数据,将主库也加入到从库的 reader_hostgroup 组中。
以下是具体的操作步骤:
Admin> SELECT * FROM mysql_replication_hostgroups;
+------------------+------------------+------------+------------------------------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+------------------------------+
| 0 | 1 | read_only | Read Write Split Host Groups |
+------------------+------------------+------------+------------------------------+
1 row in set (0.00 sec)
Admin> update mysql_servers set weight=10 where hostgroup_id=1 and port=6667;
Admin> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '192.168.198.239', 6666);
Admin> LOAD MYSQL SERVERS TO RUNTIME;
Admin> SAVE MYSQL SERVERS TO DISK;
Admin> select hostgroup_id,hostname,port,status from mysql_servers;
+--------------+-----------------+------+--------+
| hostgroup_id | hostname | port | status |
+--------------+-----------------+------+--------+
| 0 | 192.168.198.239 | 6666 | ONLINE |
| 1 | 192.168.198.239 | 6667 | ONLINE |
| 1 | 192.168.198.239 | 6666 | ONLINE |
+--------------+-----------------+------+--------+
3 rows in set (0.00 sec)
经过上述配置,当从库出现故障时,查询请求将自动切换到主库,从而避免业务中断。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。