replication-manager
replication-manager copied to clipboard
Published
20 hours ago •
signal18
signal18
After triggering HA, the web operation maintenance, but the proxysql status shows abnormality.
hi As title,
Replication Manager : v2.3.14 ProxySQL: v2.5.5
The following are my basic parameter settings for repman and proxysql:
== mysql architecture ==
172.24.47.131 <-> 172.24.47.132 (master master)
== repman main config ==
replication-multi-master = true
failover-readonly-state = true
db-servers-hosts = "172.24.47.131:3306,172.24.47.132:3306"
db-servers-prefered-master = "172.24.47.131:3306"
proxysql=true
proxysql-servers="172.24.47.134"
proxysql-port=6033
proxysql-admin-port=6032
proxysql-writer-hostgroup=10
proxysql-reader-hostgroup=11
proxysql-user="XXX"
proxysql-password="XXX"
proxysql-bootstrap=false
proxysql-bootstrap-users = false
proxysql-bootstrap-hostgroups = false
proxysql-bootstrap-variables = false
== proxysql status=
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_repl
ication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 30
| 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 30
| 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
MySQL [(none)]> select * from mysql_replication_hostgroups;
+------------------+------------------+------------+-----------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+-----------+
| 10 | 11 | read_only | W:10,R:11 |
+------------------+------------------+------------+-----------+
1 row in set (0.000 sec)
MySQL [(none)]> select * from runtime_mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 172.24.47.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 30 | 0 | 0 | |
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 30 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 30 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.001 sec)
When repman starts initially, the web operation will set 172.24.47.132:3306 -> maintenance
The proxysql status is as follows:
MySQL [(none)]> select * from runtime_mysql_servers;
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 172.24.47.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 30 | 0 | 0 | |
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 30 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | OFFLINE_SOFT | 100 | 0 | 1000 | 30 | 0 | 0 | |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.001 sec)
After I trigger HA, the master is transferred to 172.24.47.132:3306, which triggers the script to adjust the proxysql status as follows:
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_repl
ication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 30
| 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 30
| 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
MySQL [(none)]> select * from runtime_mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 30 | 0 | 0 | |
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 30 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 30 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.001 sec)
At this time, when I perform a web operation to set 172.24.47.131:3306 -> maintenance, the row with hostgroup_id=11 and hostname=172.24.47.132 disappears from the proxysql status
The proxysql status is as follows:
MySQL [(none)]> select * from runtime_mysql_servers;
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 30 | 0 | 0 | |
| 11 | 172.24.47.131 | 3306 | 0 | OFFLINE_SOFT | 100 | 0 | 1000 | 30 | 0 | 0 | |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.001 sec)
Any thoughts on why this is happening, or how to fix it? I'm happy to provide more information if needed.
Yes by default the master is not in the reader group can you try
--proxy-servers-read-on-master Should RO route via proxies point to master
--proxy-servers-read-on-master-no-slave Should RO route via proxies point to master when no more replicats (default true)
Hi svaroqui
Thank you for your quick response,
proxy-servers-read-on-master=true helped me, but I still have the following issues that need assistance:
Q1: When I initially start repman, I can perform maintenance on the master, but when I trigger HA and perform the same operation, I get an error, and the weight and max_replication_lag for hostgroup_id = 10 change.
The proxysql status is as follows:
## before HA
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 30 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 30 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.000 sec)
MySQL [(none)]> select * from runtime_mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 172.24.47.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 30 | 0 | 0 | |
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 30 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 30 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.001 sec)
## before HA, master maintenance
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | OFFLINE_SOFT | 1 | 0 | 1000 | 30 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 30 | 0 | 0 | |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.001 sec)
MySQL [(none)]> select * from runtime_mysql_servers;
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | OFFLINE_SOFT | 1 | 0 | 1000 | 30 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 30 | 0 | 0 | |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.001 sec)
## after HA
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 30 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 30 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.000 sec)
MySQL [(none)]> select * from runtime_mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 30 | 0 | 0 | |
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 30 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 30 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.001 sec)
## after HA, master maintenance
I get an error,and the weight and max_replication_lag for hostgroup_id = 10 change
error: ProxySQL could not set 172.24.47.132:3306 as offline_soft (Error 1045: ProxySQL Admin Error: UNIQUE constraint failed: mysql_servers.hostgroup_id, mysql_servers.hostname, mysql_servers.port)
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 30 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 30 | 0 | 0 | |
| 10 | 172.24.47.132 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.000 sec)
MySQL [(none)]> select * from runtime_mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 172.24.47.132 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | |
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 30 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 30 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.001 sec)
Moreover, regardless of whether I perform maintenance on the master or slave, the weight and max_replication_lag for hostgroup_id = 10 are changed. I tried adding proxysql-janitor-weights and found that it can control the change in weights, but I still can't find a parameter to control max_replication_lag. Additionally, I'm not clear why performing maintenance on the slave affects the master,and I get an error on master performing maintenance, proxysql status will still change.
Any thoughts on why this is happening, or how to fix it? I'm happy to provide more information if needed.
Hi @peterpan104,
max_replication_lag will not affect anything when server is a writer hostgroup. it will only affect the reader hostgroup as it need to check the replication lag before fetching read_only data
I will make an enhancement for the proxysql max_replication_delay and weight options in proxysql.
hi ahfa92 Thank you for your reply. I think the max_replication_lag and weight settings for the writer hostgroup are not really important, as there will only be one machine. However, if these can be controlled, that would be great!
I have a few more questions that I hope can be answered:
Q1: The results I get when performing maintenance on the master during the initial repman startup and after triggering HA are different. What are your thoughts on this?
Q2: When performing maintenance on the slave, some settings for the writer hostgroup were also modified, which I found strange. Shouldn't repman only make adjustments to the slave host:port being operated on?
hi @peterpan104, before I can answer correctly, can you explain the process, like: online -> maintenance -> online -> HA -> online -> maintenance -> online?
I never wanted to used weight for proxysql only for the janitor proxysql that was added to open access to multiple clusters inside an infrastructure like janitor proxysql ( map user to specific cluster )-> multiple proxysql -> master -> slave May be i mixup the functions from the proxysql class that could result in such bad situation
hi @ahfa92
Thank you for your patient response!
The following shows my operation process and the status of proxysql.
Q1:The results I get when performing maintenance on the master during the initial repman startup and after triggering HA are different. What are your thoughts on this?
The proxysql status is as follows:
## before HA,master online
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.000 sec)
MySQL [(none)]> select * from runtime_mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 172.24.47.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.001 sec)
The above is the status of proxysql when I initially started repman. My default mysql_servers only existed in hostgroup_id = 11, but proxysql will add the server with read_only = 0 to hostgroup_id = 10.
Now when I operate master online -> maintenance in repman,
The proxysql status is as follows:
## before HA,master maintenance
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | OFFLINE_SOFT | 1 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.000 sec)
MySQL [(none)]> select * from runtime_mysql_servers;
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | OFFLINE_SOFT | 1 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.001 sec)
we can see that the row with hostgroup_id = 10 has disappeared, and the status of the master (172.24.47.131) has changed to OFFLINE_SOFT, which is what I expected.
Now, when I change the master from maintenance -> online,
The proxysql status is as follows:
## before HA,master online
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.000 sec)
MySQL [(none)]> select * from runtime_mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 172.24.47.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.001 sec)
we can see that the row with hostgroup_id = 10 has reappeared, which is also what I expected.
Now I manually trigger HA.
The proxysql status is as follows:
## after HA,master online
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.000 sec)
MySQL [(none)]> select * from runtime_mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.002 sec)
We can see that the weights of the two servers in mysql_servers have been swapped, which is what I modified through the repman HA trigger script, and everything is as I expected.
Now, when I change the master online -> maintenance,
The proxysql status is as follows:
## after HA,master maintenance
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
| 10 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.000 sec)
MySQL [(none)]> select * from runtime_mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.001 sec)
I see that a new row has been added to mysql_servers, and the max_replication_lag for hostgroup_id = 10 has changed from 500 -> 0. In reality, the weight will also be changed, but I have set the repman parameter proxysql-janitor-weights=1, so the change is not visible. Additionally, the servers are still in the online state. These changes are not what I expected in runtime_mysql_servers.
Now, when I change the master from maintenance -> online,
The proxysql status is as follows:
## after HA,master online
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
| 10 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.000 sec)
MySQL [(none)]> select * from runtime_mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.001 sec)
the status in mysql_servers remains unchanged. Although both my read and write groups exist, mysql_servers and runtime_mysql_servers have deviated from my expectations, and the fact that the same operation yields different results has left me puzzled.
I suspect that this operation causes repman to write to the mysql_servers table in proxysql, but I can't find a way to verify this.
The above is the operation process for the first question.
Now, let me proceed to demonstrate the operation process for the second question.
Q2:When performing maintenance on the slave, some settings for the writer hostgroup were also modified, which I found strange. Shouldn't repman only make adjustments to the slave host:port being operated on?
Now, let me restore the proxysql status and restart repman, and then follow the same operation steps up until after triggering HA.
The proxysql status is as follows:
## after HA,slave online
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.000 sec)
MySQL [(none)]> select * from runtime_mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.002 sec)
We can see that everything is as I expected.
Now, when I change the slave online -> maintenance,
The proxysql status is as follows:
## after HA,slave maintenance
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | OFFLINE_SOFT | 100 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
| 10 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.000 sec)
MySQL [(none)]> select * from runtime_mysql_servers;
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 11 | 172.24.47.131 | 3306 | 0 | OFFLINE_SOFT | 100 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.001 sec)
The result is the same as when performing maintenance on the master. A new row has been added to mysql_servers, and the max_replication_lag for hostgroup_id = 10 has changed from 500 -> 0. The proxysql status has deviated from my configuration, and the fact that operating on the slave (172.24.47.131) also modified the settings for the master (172.24.47.132) is puzzling and not what I expected.
Do you have any thoughts on these two issues?
hi @svaroqui
I want to implement read-write separation through the weight parameter in mysql_servers, so that most SELECT queries will go to the slave, and only when the slave is down, the query will go to the master. Maybe there is a better way to set up repman to achieve the effect, but I haven't found it yet. :(((
Hi @peterpan104, thank you for the reply. I already found the root cause. It is because of the function only create a row with minimum requirement to proxysql. The impact is the max_replication_lag is not set, and it will take default value of insert which is 0
Hi @ahfa92
Thank you for the reply.
I think when repman operates maintenance, it should only update the status of mysql_servers without modifying other places. In this way, whether repman needs to modify the parameters of proxysql through parameters is not so important, right?
There may be other situations that need to be set up that I have not considered, but I think in most cases proxysql should not be interfered with too much.
Replication manager designed to handle more than one slave and sometimes it use multiple masters. So the process will delete the master first and re-pick from readers.
To prevent master as reader you can use
proxy-servers-read-on-master=false
As you can see in router/proxysql.go it will only change the variables using update query if you are not using read-on-master.
Maybe we can develop more specific configurator in GUI for proxysql in later versions.
hi @ahfa92
func (psql *ProxySQL) SetOfflineSoft(host string, port string) error {
sql := fmt.Sprintf("UPDATE mysql_servers SET status='OFFLINE_SOFT', hostgroup_id='%s' WHERE hostname='%s' AND port='%s' AND hostgroup_id in ('%s','%s')", psql.ReaderHG, host, port, psql.ReaderHG, psql.WriterHG)
_, err := psql.Connection.Exec(sql)
return err
}
Maybe the hostgroup_id doesn't need to be updated?
I think this might be causing the problem.
The following shows my operation process and the status of proxysql.
repman parameter settings
proxy-servers-read-on-master = false
proxy-servers-read-on-master-no-slave = true
Now,I restore proxysql status and restart repman.
This is the initial state of mysql_servers
## before HA ,online
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.000 sec)
I set the msater maintain,it will only update the status column of mysql_servers
## before HA, set the master maintain
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | OFFLINE_SOFT | 1 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.000 sec)
I set the master online and the slave settings maintenance, it will only update the status column of mysql_servers.
## before HA, set the master online
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.000 sec)
## before HA, set the slave maintenance
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | OFFLINE_SOFT | 100 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.000 sec)
Now I trigger HA, the master will be transferred to server 172.24.47.132,And mysql_servers will be as follows.
## after HA,online
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.001 sec)
I set the master maintenance, it will only update the status column of mysql_servers.
## after HA,set the master maintain
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | OFFLINE_SOFT | 1 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.000 sec)
I set the master online,hostgroup_id is updated to 10,it should not be updated.
## after HA,set the master online
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 500 | 0 | 0 | |
| 10 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.000 sec)
Now,I restore the proxysql state to when HA is complete.
## after HA,online
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 500 | 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.001 sec)
I set the slave (172.24.47.131) maintenance, In addition to changing the slave's status to offline_soft, also change the msater's hostgroup_id to 10,I think this is a wrong update.
## after HA,set the slave maintenance
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | OFFLINE_SOFT | 100 | 0 | 1000 | 500 | 0 | 0 | |
| 10 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.000 sec)
Finally,I set the slave (172.24.47.131) online,At this time mysql_servers has deviated from my settings.
## after HA,set the slave online
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | ONLINE | 100 | 0 | 1000 | 500 | 0 | 0 | |
| 10 | 172.24.47.132 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.000 sec)
The above shows why hostgroup_id shouldn't be updated , and the strange phenomenon of changing the status of the master when operating the slave.
Can you show me the dashboard with log level info? It should have some kind of clue what process is happening when the hostgroup is changing.
Hi @ahfa92 The following is a more detailed proxysql log,I'm happy to provide more information if needed.
after HA,set the master maintenance
2024-04-29 13:36:35 [INFO] Received LOAD MYSQL SERVERS TO RUNTIME command
2024-04-29 13:36:35 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 11 , address: 172.24.47.131 , port: 3306 , gtid_port: 0 , weight: 100 , status: ONLINE , max_connections: 1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 11 , address: 172.24.47.132 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 10 , address: 172.24.47.132 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
2024-04-29 13:36:35 [INFO] Dumping mysql_servers: ALL
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 0 | 0 | 1000 | 500 | 0 | 0 | | 140446554849760 |
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500 | 0 | 0 | | 140446517403648 |
| 11 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140447047260736 |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
2024-04-29 13:36:35 [INFO] Dumping mysql_servers_incoming
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_repl
ication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500
| 0 | 0 | |
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 0 | 0 | 1000 | 500
| 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
2024-04-29 13:36:35 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-----------------+--------------+---------------+------+
| mem_pointer | hostgroup_id | hostname | port |
+-----------------+--------------+---------------+------+
| 140447047260736 | 11 | 172.24.47.132 | 3306 |
+-----------------+--------------+---------------+------+
2024-04-29 13:36:35 MySQL_HostGroups_Manager.cpp:1668:commit(): [WARNING] Removed server at address 1404470472
60736, hostgroup 11, address 172.24.47.132 port 3306. Setting status OFFLINE HARD and immediately dropping all
free connections. Used connections will be dropped when trying to use them
2024-04-29 13:36:35 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+--------------
-------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-------
----------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replicati
on_lag | use_ssl | max_latency_ms | comment | mem_pointer | gtid_port | weight | status | compression | max_co
nnections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+--------------
-------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-------
----------+---------------------+---------+----------------+---------+
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+--------------
-------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-------
----------+---------------------+---------+----------------+---------+
2024-04-29 13:36:35 [INFO] New mysql_replication_hostgroups table
writer_hostgroup: 10 , reader_hostgroup: 11, check_type read_only, comment: W:10,R:11
2024-04-29 13:36:35 [INFO] New mysql_group_replication_hostgroups table
2024-04-29 13:36:35 [INFO] New mysql_galera_hostgroups table
2024-04-29 13:36:35 [INFO] New mysql_aws_aurora_hostgroups table
2024-04-29 13:36:35 [INFO] New mysql_hostgroup_attributes table
2024-04-29 13:36:35 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 11 , address: 172.24.47.131 , port: 3306 , gtid_port: 0 , weight: 100 , status: ONLINE , max_connections:
1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 11 , address: 172.24.47.132 , port: 3306 , gtid_port: 0 , weight: 1 , status: OFFLINE_HARD , max_connecti
ons: 1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 10 , address: 172.24.47.132 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1
000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
2024-04-29 13:36:35 [INFO] Dumping mysql_servers: ALL
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment |
mem_pointer |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140446554849760 |
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140446517403648 |
| 11 | 172.24.47.132 | 3306 | 0 | 1 | 3 | 0 | 1000 | 500 | 0 | 0 | |
140447047260736 |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
2024-04-29 13:36:35 [INFO] Checksum for table mysql_servers is 0x857A656FD265FD5E
2024-04-29 13:36:35 [INFO] Checksum for table mysql_replication_hostgroups is 0xD4D945D04C29A5FF
2024-04-29 13:36:35 [INFO] Rebuilding 'Hostgroup_Manager_Mapping' due to checksums change - mysql_servers { ol
d: 0xA9E120869FA55F17, new: 0x857A656FD265FD5E }, mysql_replication_hostgroups { old:0xD4D945D04C29A5FF, new:0
xD4D945D04C29A5FF }
2024-04-29 13:36:35 [INFO] MySQL_HostGroups_Manager::commit() locked for 2ms
2024-04-29 13:36:35 [INFO] Received LOAD MYSQL SERVERS TO RUNTIME command
2024-04-29 13:36:35 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 11 , address: 172.24.47.131 , port: 3306 , gtid_port: 0 , weight: 100 , status: ONLINE , max_connections:
1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 10 , address: 172.24.47.132 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1
000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
2024-04-29 13:36:35 [INFO] Dumping mysql_servers: ALL
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment |
mem_pointer |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140446554849760 |
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140446517403648 |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
2024-04-29 13:36:35 [INFO] Dumping mysql_servers_incoming
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_repl
ication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 0 | 0 | 1000 | 500
| 0 | 0 | |
| 11 | 172.24.47.132 | 3306 | 0 | 1 | 2 | 0 | 1000 | 500
| 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
2024-04-29 13:36:35 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-----------------+--------------+---------------+------+
| mem_pointer | hostgroup_id | hostname | port |
+-----------------+--------------+---------------+------+
| 140446517403648 | 10 | 172.24.47.132 | 3306 |
+-----------------+--------------+---------------+------+
2024-04-29 13:36:35 MySQL_HostGroups_Manager.cpp:1668:commit(): [WARNING] Removed server at address 1404465174
03648, hostgroup 10, address 172.24.47.132 port 3306. Setting status OFFLINE HARD and immediately dropping all
free connections. Used connections will be dropped when trying to use them
2024-04-29 13:36:35 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+--
---------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_repl
ication_lag | use_ssl | max_latency_ms | comment | mem_pointer | gtid_port | weight | status | compression | m
ax_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+--
---------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.132 | 3306 | 0 | 1 | 2 | 0 | 1000 | 500
| 0 | 0 | | 0 | 0 | 1 | 2 | 0 | 1
000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+--
---------------+---------------------+---------+----------------+---------+
2024-04-29 13:36:35 [INFO] Creating new server in HG 11 : 172.24.47.132:3306 , gtid_port=0, weight=1, status=2
2024-04-29 13:36:35 [INFO] New mysql_replication_hostgroups table
writer_hostgroup: 10 , reader_hostgroup: 11, check_type read_only, comment: W:10,R:11
2024-04-29 13:36:35 [INFO] New mysql_group_replication_hostgroups table
2024-04-29 13:36:35 [INFO] New mysql_galera_hostgroups table
2024-04-29 13:36:35 [INFO] New mysql_aws_aurora_hostgroups table
2024-04-29 13:36:35 [INFO] New mysql_hostgroup_attributes table
2024-04-29 13:36:35 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 11 , address: 172.24.47.131 , port: 3306 , gtid_port: 0 , weight: 100 , status: ONLINE , max_connections:
1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 11 , address: 172.24.47.132 , port: 3306 , gtid_port: 0 , weight: 1 , status: OFFLINE_SOFT , max_connecti
ons: 1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 10 , address: 172.24.47.132 , port: 3306 , gtid_port: 0 , weight: 1 , status: OFFLINE_HARD , max_connecti
ons: 1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
2024-04-29 13:36:35 [INFO] Dumping mysql_servers: ALL
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment |
mem_pointer |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140446554849760 |
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 3 | 0 | 1000 | 500 | 0 | 0 | |
140446517403648 |
| 11 | 172.24.47.132 | 3306 | 0 | 1 | 2 | 0 | 1000 | 500 | 0 | 0 | |
140447047260736 |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
2024-04-29 13:36:35 [INFO] Checksum for table mysql_servers is 0x19E1F46003B0A2CA
2024-04-29 13:36:35 [INFO] Checksum for table mysql_replication_hostgroups is 0xD4D945D04C29A5FF
2024-04-29 13:36:35 [INFO] Rebuilding 'Hostgroup_Manager_Mapping' due to checksums change - mysql_servers { ol
d: 0x857A656FD265FD5E, new: 0x19E1F46003B0A2CA }, mysql_replication_hostgroups { old:0xD4D945D04C29A5FF, new:0
xD4D945D04C29A5FF }
2024-04-29 13:36:35 [INFO] MySQL_HostGroups_Manager::commit() locked for 1ms
after HA,set the master online
2024-04-29 13:41:53 [INFO] Received LOAD MYSQL SERVERS TO RUNTIME command
2024-04-29 13:41:53 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 11 , address: 172.24.47.131 , port: 3306 , gtid_port: 0 , weight: 100 , status: ONLINE , max_connections: 1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 11 , address: 172.24.47.132 , port: 3306 , gtid_port: 0 , weight: 1 , status: OFFLINE_SOFT , max_connections: 1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
2024-04-29 13:41:53 [INFO] Dumping mysql_servers: ALL
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 0 | 0 | 1000 | 500 | 0 | 0 | | 140446554849760 |
| 11 | 172.24.47.132 | 3306 | 0 | 1 | 2 | 0 | 1000 | 500 | 0 | 0 | | 140447047260736 |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
2024-04-29 13:41:53 [INFO] Dumping mysql_servers_incoming
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500
| 0 | 0 | |
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 0 | 0 | 1000 | 500
| 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
2024-04-29 13:41:53 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-----------------+--------------+---------------+------+
| mem_pointer | hostgroup_id | hostname | port |
+-----------------+--------------+---------------+------+
| 140447047260736 | 11 | 172.24.47.132 | 3306 |
+-----------------+--------------+---------------+------+
2024-04-29 13:41:53 MySQL_HostGroups_Manager.cpp:1668:commit(): [WARNING] Removed server at address 1404470472
60736, hostgroup 11, address 172.24.47.132 port 3306. Setting status OFFLINE HARD and immediately dropping all
free connections. Used connections will be dropped when trying to use them
2024-04-29 13:41:53 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+--
---------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_repl
ication_lag | use_ssl | max_latency_ms | comment | mem_pointer | gtid_port | weight | status | compression | m
ax_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+--
---------------+---------------------+---------+----------------+---------+
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500
| 0 | 0 | | 0 | 0 | 1 | 0 | 0 | 1
000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+--
---------------+---------------------+---------+----------------+---------+
2024-04-29 13:41:53 [INFO] Creating new server in HG 10 : 172.24.47.132:3306 , gtid_port=0, weight=1, status=0
2024-04-29 13:41:53 [INFO] New mysql_replication_hostgroups table
writer_hostgroup: 10 , reader_hostgroup: 11, check_type read_only, comment: W:10,R:11
2024-04-29 13:41:53 [INFO] New mysql_group_replication_hostgroups table
2024-04-29 13:41:53 [INFO] New mysql_galera_hostgroups table
2024-04-29 13:41:53 [INFO] New mysql_aws_aurora_hostgroups table
2024-04-29 13:41:53 [INFO] New mysql_hostgroup_attributes table
2024-04-29 13:41:53 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 11 , address: 172.24.47.131 , port: 3306 , gtid_port: 0 , weight: 100 , status: ONLINE , max_connections: 1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 11 , address: 172.24.47.132 , port: 3306 , gtid_port: 0 , weight: 1 , status: OFFLINE_HARD , max_connections: 1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 10 , address: 172.24.47.132 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
2024-04-29 13:41:53 [INFO] Dumping mysql_servers: ALL
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment |
mem_pointer |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140446554849760 |
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140446517403648 |
| 11 | 172.24.47.132 | 3306 | 0 | 1 | 3 | 0 | 1000 | 500 | 0 | 0 | |
140447047260736 |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
2024-04-29 13:41:53 [INFO] Checksum for table mysql_servers is 0x857A656FD265FD5E
2024-04-29 13:41:53 [INFO] Checksum for table mysql_replication_hostgroups is 0xD4D945D04C29A5FF
2024-04-29 13:41:53 [INFO] Rebuilding 'Hostgroup_Manager_Mapping' due to checksums change - mysql_servers { ol
d: 0x19E1F46003B0A2CA, new: 0x857A656FD265FD5E }, mysql_replication_hostgroups { old:0xD4D945D04C29A5FF, new:0
xD4D945D04C29A5FF }
2024-04-29 13:41:53 [INFO] MySQL_HostGroups_Manager::commit() locked for 2ms
2024-04-29 13:41:53 [INFO] Received LOAD MYSQL SERVERS TO RUNTIME command
2024-04-29 13:41:53 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 11 , address: 172.24.47.131 , port: 3306 , gtid_port: 0 , weight: 100 , status: ONLINE , max_connections:
1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 10 , address: 172.24.47.132 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1
000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
2024-04-29 13:41:53 [INFO] Dumping mysql_servers: ALL
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment |
mem_pointer |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140446554849760 |
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140446517403648 |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
2024-04-29 13:41:53 [INFO] Dumping mysql_servers_incoming
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_repl
ication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500
| 0 | 0 | |
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 0 | 0 | 1000 | 500
| 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
2024-04-29 13:41:53 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-------------+--------------+----------+------+
| mem_pointer | hostgroup_id | hostname | port |
+-------------+--------------+----------+------+
+-------------+--------------+----------+------+
2024-04-29 13:41:53 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+--------------
-------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-------
----------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replicati
on_lag | use_ssl | max_latency_ms | comment | mem_pointer | gtid_port | weight | status | compression | max_co
nnections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+--------------
-------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-------
----------+---------------------+---------+----------------+---------+
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+--------------
-------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-------
----------+---------------------+---------+----------------+---------+
2024-04-29 13:41:53 [INFO] New mysql_replication_hostgroups table
writer_hostgroup: 10 , reader_hostgroup: 11, check_type read_only, comment: W:10,R:11
2024-04-29 13:41:53 [INFO] New mysql_group_replication_hostgroups table
2024-04-29 13:41:53 [INFO] New mysql_galera_hostgroups table
2024-04-29 13:41:53 [INFO] New mysql_aws_aurora_hostgroups table
2024-04-29 13:41:53 [INFO] New mysql_hostgroup_attributes table
2024-04-29 13:41:53 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 11 , address: 172.24.47.131 , port: 3306 , gtid_port: 0 , weight: 100 , status: ONLINE , max_connections:
1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 10 , address: 172.24.47.132 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1
000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
2024-04-29 13:41:53 [INFO] Dumping mysql_servers: ALL
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment |
mem_pointer |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140446554849760 |
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140446517403648 |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
2024-04-29 13:41:53 [INFO] Checksum for table mysql_servers is 0x857A656FD265FD5E
2024-04-29 13:41:53 [INFO] Checksum for table mysql_replication_hostgroups is 0xD4D945D04C29A5FF
2024-04-29 13:41:53 [INFO] MySQL_HostGroups_Manager::commit() locked for 1ms
2024-04-29 13:41:53 [INFO] read_only_action_v2() detected RO=0 on server 172.24.47.132:3306 for the first time
after commit(), but no need to reconfigure
after HA,set the slave maintenance
2024-04-29 13:47:08 [INFO] Received LOAD MYSQL SERVERS TO RUNTIME command
2024-04-29 13:47:08 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 11 , address: 172.24.47.131 , port: 3306 , gtid_port: 0 , weight: 100 , status: ONLINE , max_connections: 1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 11 , address: 172.24.47.132 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 10 , address: 172.24.47.132 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
2024-04-29 13:47:08 [INFO] Dumping mysql_servers: ALL
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 0 | 0 | 1000 | 500 | 0 | 0 | | 140446554849760 |
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500 | 0 | 0 | | 140446517403648 |
| 11 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140447047213536 |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
2024-04-29 13:47:08 [INFO] Dumping mysql_servers_incoming
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_repl
ication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500
| 0 | 0 | |
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 0 | 0 | 1000 | 500
| 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
2024-04-29 13:47:08 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-----------------+--------------+---------------+------+
| mem_pointer | hostgroup_id | hostname | port |
+-----------------+--------------+---------------+------+
| 140447047213536 | 11 | 172.24.47.132 | 3306 |
+-----------------+--------------+---------------+------+
2024-04-29 13:47:08 MySQL_HostGroups_Manager.cpp:1668:commit(): [WARNING] Removed server at address 1404470472
13536, hostgroup 11, address 172.24.47.132 port 3306. Setting status OFFLINE HARD and immediately dropping all
free connections. Used connections will be dropped when trying to use them
2024-04-29 13:47:08 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+--------------
-------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-------
----------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replicati
on_lag | use_ssl | max_latency_ms | comment | mem_pointer | gtid_port | weight | status | compression | max_co
nnections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+--------------
-------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-------
----------+---------------------+---------+----------------+---------+
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+--------------
-------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-------
----------+---------------------+---------+----------------+---------+
2024-04-29 13:47:08 [INFO] New mysql_replication_hostgroups table
writer_hostgroup: 10 , reader_hostgroup: 11, check_type read_only, comment: W:10,R:11
2024-04-29 13:47:08 [INFO] New mysql_group_replication_hostgroups table
2024-04-29 13:47:08 [INFO] New mysql_galera_hostgroups table
2024-04-29 13:47:08 [INFO] New mysql_aws_aurora_hostgroups table
2024-04-29 13:47:08 [INFO] New mysql_hostgroup_attributes table
2024-04-29 13:47:08 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 11 , address: 172.24.47.131 , port: 3306 , gtid_port: 0 , weight: 100 , status: ONLINE , max_connections:
1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 11 , address: 172.24.47.132 , port: 3306 , gtid_port: 0 , weight: 1 , status: OFFLINE_HARD , max_connecti
ons: 1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 10 , address: 172.24.47.132 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1
000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
2024-04-29 13:47:08 [INFO] Dumping mysql_servers: ALL
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment |
mem_pointer |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140446554849760 |
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140446517403648 |
| 11 | 172.24.47.132 | 3306 | 0 | 1 | 3 | 0 | 1000 | 500 | 0 | 0 | |
140447047213536 |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
2024-04-29 13:47:08 [INFO] Checksum for table mysql_servers is 0x857A656FD265FD5E
2024-04-29 13:47:08 [INFO] Checksum for table mysql_replication_hostgroups is 0xD4D945D04C29A5FF
2024-04-29 13:47:08 [INFO] Rebuilding 'Hostgroup_Manager_Mapping' due to checksums change - mysql_servers { ol
d: 0xA9E120869FA55F17, new: 0x857A656FD265FD5E }, mysql_replication_hostgroups { old:0xD4D945D04C29A5FF, new:0
xD4D945D04C29A5FF }
2024-04-29 13:47:08 [INFO] MySQL_HostGroups_Manager::commit() locked for 1ms
2024-04-29 13:47:08 [INFO] Received LOAD MYSQL SERVERS TO RUNTIME command
2024-04-29 13:47:08 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 11 , address: 172.24.47.131 , port: 3306 , gtid_port: 0 , weight: 100 , status: ONLINE , max_connections:
1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 10 , address: 172.24.47.132 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1
000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
2024-04-29 13:47:08 [INFO] Dumping mysql_servers: ALL
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment |
mem_pointer |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140446554849760 |
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140446517403648 |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
2024-04-29 13:47:08 [INFO] Dumping mysql_servers_incoming
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_repl
ication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500
| 0 | 0 | |
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 2 | 0 | 1000 | 500
| 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
2024-04-29 13:47:08 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-------------+--------------+----------+------+
| mem_pointer | hostgroup_id | hostname | port |
+-------------+--------------+----------+------+
+-------------+--------------+----------+------+
2024-04-29 13:47:08 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+-----------------+-----------+--------+--------+------------
-+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_repl
ication_lag | use_ssl | max_latency_ms | comment | mem_pointer | gtid_port | weight | status | compression
| max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+-----------------+-----------+--------+--------+------------
-+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 0 | 0 | 1000 | 500
| 0 | 0 | | 140446554849760 | 0 | 100 | 2 | 0
| 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+-----------------+-----------+--------+--------+------------
-+-----------------+---------------------+---------+----------------+---------+
2024-04-29 13:47:08 [INFO] Changing status for server 11:172.24.47.131:3306 (172.24.47.131:3306) from 0 (0) to
2
2024-04-29 13:47:08 [INFO] New mysql_replication_hostgroups table
writer_hostgroup: 10 , reader_hostgroup: 11, check_type read_only, comment: W:10,R:11
2024-04-29 13:47:08 [INFO] New mysql_group_replication_hostgroups table
2024-04-29 13:47:08 [INFO] New mysql_galera_hostgroups table
2024-04-29 13:47:08 [INFO] New mysql_aws_aurora_hostgroups table
2024-04-29 13:47:08 [INFO] New mysql_hostgroup_attributes table
2024-04-29 13:47:08 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 11 , address: 172.24.47.131 , port: 3306 , gtid_port: 0 , weight: 100 , status: OFFLINE_SOFT , max_connec
tions: 1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 10 , address: 172.24.47.132 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1
000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
2024-04-29 13:47:08 [INFO] Dumping mysql_servers: ALL
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment |
mem_pointer |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 2 | 0 | 1000 | 500 | 0 | 0 | |
140446554849760 |
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140446517403648 |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
2024-04-29 13:47:08 [INFO] Checksum for table mysql_servers is 0xC5BC02CCEFF597CB
2024-04-29 13:47:08 [INFO] Checksum for table mysql_replication_hostgroups is 0xD4D945D04C29A5FF
2024-04-29 13:47:08 [INFO] Rebuilding 'Hostgroup_Manager_Mapping' due to checksums change - mysql_servers { ol
d: 0x857A656FD265FD5E, new: 0xC5BC02CCEFF597CB }, mysql_replication_hostgroups { old:0xD4D945D04C29A5FF, new:0
xD4D945D04C29A5FF }
2024-04-29 13:47:08 [INFO] MySQL_HostGroups_Manager::commit() locked for 2ms
2024-04-29 13:47:08 [INFO] read_only_action_v2() detected RO=0 on server 172.24.47.132:3306 for the first time
after commit(), but no need to reconfigure
after HA,set the slave online
2024-04-29 13:49:41 [INFO] Received LOAD MYSQL SERVERS TO RUNTIME command
2024-04-29 13:49:41 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 11 , address: 172.24.47.131 , port: 3306 , gtid_port: 0 , weight: 100 , status: OFFLINE_SOFT , max_connections: 1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 10 , address: 172.24.47.132 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
2024-04-29 13:49:41 [INFO] Dumping mysql_servers: ALL
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 2 | 0 | 1000 | 500 | 0 | 0 | | 140446554849760 |
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500 | 0 | 0 | | 140446517403648 |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
2024-04-29 13:49:41 [INFO] Dumping mysql_servers_incoming
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_repl
ication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500
| 0 | 0 | |
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 0 | 0 | 1000 | 500
| 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
2024-04-29 13:49:41 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-------------+--------------+----------+------+
| mem_pointer | hostgroup_id | hostname | port |
+-------------+--------------+----------+------+
+-------------+--------------+----------+------+
2024-04-29 13:49:41 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+-----------------+-----------+--------+--------+------------
-+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_repl
ication_lag | use_ssl | max_latency_ms | comment | mem_pointer | gtid_port | weight | status | compression
| max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+-----------------+-----------+--------+--------+------------
-+-----------------+---------------------+---------+----------------+---------+
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 2 | 0 | 1000 | 500
| 0 | 0 | | 140446554849760 | 0 | 100 | 0 | 0
| 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+-----------------+-----------+--------+--------+------------
-+-----------------+---------------------+---------+----------------+---------+
2024-04-29 13:49:41 [INFO] Changing status for server 11:172.24.47.131:3306 (172.24.47.131:3306) from 2 (2) to
0
2024-04-29 13:49:41 [INFO] New mysql_replication_hostgroups table
writer_hostgroup: 10 , reader_hostgroup: 11, check_type read_only, comment: W:10,R:11
2024-04-29 13:49:41 [INFO] New mysql_group_replication_hostgroups table
2024-04-29 13:49:41 [INFO] New mysql_galera_hostgroups table
2024-04-29 13:49:41 [INFO] New mysql_aws_aurora_hostgroups table
2024-04-29 13:49:41 [INFO] New mysql_hostgroup_attributes table
2024-04-29 13:49:41 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 11 , address: 172.24.47.131 , port: 3306 , gtid_port: 0 , weight: 100 , status: ONLINE , max_connections:
1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 10 , address: 172.24.47.132 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1
000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
2024-04-29 13:49:41 [INFO] Dumping mysql_servers: ALL
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment |
mem_pointer |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140446554849760 |
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140446517403648 |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
2024-04-29 13:49:41 [INFO] Checksum for table mysql_servers is 0x857A656FD265FD5E
2024-04-29 13:49:41 [INFO] Checksum for table mysql_replication_hostgroups is 0xD4D945D04C29A5FF
2024-04-29 13:49:41 [INFO] Rebuilding 'Hostgroup_Manager_Mapping' due to checksums change - mysql_servers { ol
d: 0xC5BC02CCEFF597CB, new: 0x857A656FD265FD5E }, mysql_replication_hostgroups { old:0xD4D945D04C29A5FF, new:0
xD4D945D04C29A5FF }
2024-04-29 13:49:41 [INFO] MySQL_HostGroups_Manager::commit() locked for 2ms
2024-04-29 13:49:41 [INFO] Received LOAD MYSQL SERVERS TO RUNTIME command
2024-04-29 13:49:41 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 11 , address: 172.24.47.131 , port: 3306 , gtid_port: 0 , weight: 100 , status: ONLINE , max_connections:
1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 10 , address: 172.24.47.132 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1
000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
2024-04-29 13:49:41 [INFO] Dumping mysql_servers: ALL
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment |
mem_pointer |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140446554849760 |
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140446517403648 |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
2024-04-29 13:49:41 [INFO] Dumping mysql_servers_incoming
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_repl
ication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500
| 0 | 0 | |
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 0 | 0 | 1000 | 500
| 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------
------------+---------+----------------+---------+
2024-04-29 13:49:41 [INFO] Dumping mysql_servers LEFT JOIN mysql_servers_incoming
+-------------+--------------+----------+------+
| mem_pointer | hostgroup_id | hostname | port |
+-------------+--------------+----------+------+
+-------------+--------------+----------+------+
2024-04-29 13:49:41 [INFO] Dumping mysql_servers JOIN mysql_servers_incoming
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+--------------
-------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-------
----------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replicati
on_lag | use_ssl | max_latency_ms | comment | mem_pointer | gtid_port | weight | status | compression | max_co
nnections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+--------------
-------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-------
----------+---------------------+---------+----------------+---------+
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+--------------
-------+---------+----------------+---------+-------------+-----------+--------+--------+-------------+-------
----------+---------------------+---------+----------------+---------+
2024-04-29 13:49:41 [INFO] New mysql_replication_hostgroups table
writer_hostgroup: 10 , reader_hostgroup: 11, check_type read_only, comment: W:10,R:11
2024-04-29 13:49:41 [INFO] New mysql_group_replication_hostgroups table
2024-04-29 13:49:41 [INFO] New mysql_galera_hostgroups table
2024-04-29 13:49:41 [INFO] New mysql_aws_aurora_hostgroups table
2024-04-29 13:49:41 [INFO] New mysql_hostgroup_attributes table
2024-04-29 13:49:41 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 11 , address: 172.24.47.131 , port: 3306 , gtid_port: 0 , weight: 100 , status: ONLINE , max_connections:
1000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 10 , address: 172.24.47.132 , port: 3306 , gtid_port: 0 , weight: 1 , status: ONLINE , max_connections: 1
000 , max_replication_lag: 500 , use_ssl: 0 , max_latency_ms: 0 , comment:
2024-04-29 13:49:41 [INFO] Dumping mysql_servers: ALL
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment |
mem_pointer |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
| 11 | 172.24.47.131 | 3306 | 0 | 100 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140446554849760 |
| 10 | 172.24.47.132 | 3306 | 0 | 1 | 0 | 0 | 1000 | 500 | 0 | 0 | |
140446517403648 |
+-----+---------------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-
----------------+
2024-04-29 13:49:41 [INFO] Checksum for table mysql_servers is 0x857A656FD265FD5E
2024-04-29 13:49:41 [INFO] Checksum for table mysql_replication_hostgroups is 0xD4D945D04C29A5FF
2024-04-29 13:49:41 [INFO] MySQL_HostGroups_Manager::commit() locked for 1ms
2024-04-29 13:49:41 [INFO] read_only_action_v2() detected RO=0 on server 172.24.47.132:3306 for the first time
after commit(), but no need to reconfigure
I never study putting the master in maintenance any reason for doing this it would break all writes for a long time ? Does not mean we should not cover that case as well.
Normally, there aren't many chances to perform maintenance on the master, but I have to consider every possible scenario.
The main issue now is that before triggering HA, no matter how I perform maintenance, all the results meet my expectations. However, after triggering HA, performing maintenance on either the master or slave causes the mysql_servers table in ProxySQL to deviate from my default settings. I think this result is unreasonable, and this is what I'm trying to express.
Thank you for the feedback. I will make a fix ok this one. Hope I can commit the fix this week.
On Mon, Apr 29, 2024, 3:47 PM peterpan104 @.***> wrote:
Normally, there aren't many chances to perform maintenance on the master, but I have to consider every possible scenario.
The main issue now is that before triggering HA, no matter how I perform maintenance, all the results meet my expectations. However, after triggering HA, performing maintenance on either the master or slave causes the mysql_servers table in ProxySQL to deviate from my default settings. I think this result is unreasonable, and this is what I'm trying to express.
— Reply to this email directly, view it on GitHub https://github.com/signal18/replication-manager/issues/560#issuecomment-2082179168, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIM5KTEPZCQMLSQEUWZEXXTY7YCIZAVCNFSM6AAAAABGSJFUNCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDAOBSGE3TSMJWHA . You are receiving this because you were mentioned.Message ID: @.***>
Agree our priority is.to avoid loosing user setting, this is indeed is not acceptable. thanks for raising the issue
I'm glad this issue will be fixed. Thank you again for both of your reply!
Hi @peterpan104 , Can you verify whether the issue already solved in latest release v2.3.25
hi @ahfa92
The issues mentioned above have not been resolved yet, and there will be the following errors after upgrading repman:
ERROR Failed to parse binary log datetime string. . Str: . Host: 172.24.47.186:3306 - mysql-bin.000010
Additionally, I used tcpdump to capture what settings repman made to proxysql, showing as follows:
## maintence master
UPDATE mysql_servers SET status='OFFLINE_SOFT', hostgroup_id='11' WHERE hostname='172.24.47.186' AND port='3306' AND hostgroup_id in ('11','10');
LOAD MYSQL SERVERS TO RUNTIME;
## online master
UPDATE mysql_servers SET status='ONLINE' WHERE hostname='172.24.47.186' AND port='3306' AND hostgroup_id in ('11','10');
LOAD MYSQL SERVERS TO RUNTIME;
## maintence slave
UPDATE mysql_servers SET status='OFFLINE_SOFT', hostgroup_id='11' WHERE hostname='172.24.47.187' AND port='3306' AND hostgroup_id in ('11','10')
LOAD MYSQL SERVERS TO RUNTIME
## online slave
UPDATE mysql_servers SET status='ONLINE' WHERE hostname='172.24.47.187' AND port='3306' AND hostgroup_id in ('11','10')
LOAD MYSQL SERVERS TO RUNTIME
## trigger switchover
UPDATE mysql_servers SET status='ONLINE', hostgroup_id='11' WHERE hostname='172.24.47.186' AND port='3306' AND hostgroup_id in ('11','10')
UPDATE mysql_servers SET status='ONLINE', hostgroup_id='10' WHERE hostname='172.24.47.187' AND port='3306' AND hostgroup_id in ('11','10')
LOAD MYSQL SERVERS TO RUNTIME
## failover_script
load mysql servers to mem
update mysql_servers set weight = abs(101-weight) where hostname in ('172.24.47.186','172.24.47.187') and port = '3306'
save mysql servers to disk
load mysql servers to run
## maintence master
UPDATE mysql_servers SET status='ONLINE', hostgroup_id='11' WHERE hostname='172.24.47.186' AND port='3306' AND hostgroup_id in ('11','10');
UPDATE mysql_servers SET status='ONLINE', hostgroup_id='10' WHERE hostname='172.24.47.187' AND port='3306' AND hostgroup_id in ('11','10');
LOAD MYSQL SERVERS TO RUNTIME;
UPDATE mysql_servers SET status='OFFLINE_SOFT', hostgroup_id='11' WHERE hostname='172.24.47.187' AND port='3306' AND hostgroup_id in ('11','10');
LOAD MYSQL SERVERS TO RUNTIME;
## online master
UPDATE mysql_servers SET status='ONLINE', hostgroup_id='11' WHERE hostname='172.24.47.186' AND port='3306' AND hostgroup_id in ('11','10');
UPDATE mysql_servers SET status='ONLINE', hostgroup_id='10' WHERE hostname='172.24.47.187' AND port='3306' AND hostgroup_id in ('11','10');
LOAD MYSQL SERVERS TO RUNTIME;
UPDATE mysql_servers SET status='ONLINE' WHERE hostname='172.24.47.187' AND port='3306' AND hostgroup_id in ('11','10');
LOAD MYSQL SERVERS TO RUNTIME;
## maintence slave
UPDATE mysql_servers SET status='ONLINE', hostgroup_id='11' WHERE hostname='172.24.47.186' AND port='3306' AND hostgroup_id in ('11','10');
UPDATE mysql_servers SET status='ONLINE', hostgroup_id='10' WHERE hostname='172.24.47.187' AND port='3306' AND hostgroup_id in ('11','10');
LOAD MYSQL SERVERS TO RUNTIME;
UPDATE mysql_servers SET status='OFFLINE_SOFT', hostgroup_id='11' WHERE hostname='172.24.47.186' AND port='3306' AND hostgroup_id in ('11','10');
LOAD MYSQL SERVERS TO RUNTIME;
## online slave
UPDATE mysql_servers SET status='ONLINE', hostgroup_id='11' WHERE hostname='172.24.47.186' AND port='3306' AND hostgroup_id in ('11','10');
UPDATE mysql_servers SET status='ONLINE', hostgroup_id='10' WHERE hostname='172.24.47.187' AND port='3306' AND hostgroup_id in ('11','10');
LOAD MYSQL SERVERS TO RUNTIME;
UPDATE mysql_servers SET status='ONLINE' WHERE hostname='172.24.47.186' AND port='3306' AND hostgroup_id in ('11','10');
LOAD MYSQL SERVERS TO RUNTIME;
I must emphasize again that UPDATE mysql_servers should not modify hostgroup_id, and I think this is the cause of the problem!
During the trigger switchover, it also updates mysql_servers, but since I've set failover_script, the proxysql configuration doesn't deviate from my defaults.
After the trigger switch, repman's operations on proxysql increased significantly, and I am still skeptical about these additional operations. I hope the above process can help you better understand the problem.
Maybe the query in router/proxysql.go should be modified as follows:
## modify
UPDATE mysql_servers SET status='OFFLINE_SOFT' WHERE hostname='%s' AND port='%s'
## router/proxysql.go
func (psql *ProxySQL) SetOfflineSoft(host string, port string) error {
sql := fmt.Sprintf("UPDATE mysql_servers SET status='OFFLINE_SOFT', hostgroup_id='%s' WHERE hostname='%s' AND port='%s' AND hostgroup_id in ('%s','%s')", psql.ReaderHG, host, port, psql.ReaderHG, psql.WriterHG)
_, err := psql.Connection.Exec(sql)
return err
}
Hi @peterpan104
We already committed for the HA, as for maintenance mode we already merged and should be able to see in next release. Previously I was unsure about offline_soft in master, so I tried to ask ProxySQL expert about this. We already added a commit about maintenance mode after consultation with Rene Cannao. Hope this will fix the maintenance mode in our next release
Hi @peterpan104
This thread is for ProxySQL maintenance mode and HA.
For binary timestamp error will be discussed in #579
hi @caffeinated92 Thanks for your help, I'll look forward to the new version.
Hi @caffeinated92
After I installed the latest version of repman, proxysql no longer deviates from my default settings.
But as I mentioned above ,after triggering the switchover, as well as performing maintenance operations on the master or slave, the following functions are used:
func (psql *ProxySQL) SetReader(host string, port string) error {
sql := fmt.Sprintf("UPDATE mysql_servers SET status='ONLINE', hostgroup_id='%s' WHERE hostname='%s' AND port='%s' AND hostgroup_id in ('%s','%s')", psql.ReaderHG, host, port, psql.ReaderHG, psql.WriterHG)
_, err := psql.Connection.Exec(sql)
return err
}
func (psql *ProxySQL) SetWriter(host string, port string) error {
sql := fmt.Sprintf("UPDATE mysql_servers SET status='ONLINE', hostgroup_id='%s' WHERE hostname='%s' AND port='%s' AND hostgroup_id in ('%s','%s')", psql.WriterHG, host, port, psql.ReaderHG, psql.WriterHG)
_, err := psql.Connection.Exec(sql)
return err
}
Although repman did not successfully modify mysql_servers as I got the following error:
Failover ProxySQL could not set server 172.24.47.187:3306 Master (Error 1045: ProxySQL Admin Error: UNIQUE constraint failed: mysql_servers.hostgroup_id, mysql_servers.hostname, mysql_servers.port)
When I start repman for the first time, operating maintenance will not call these functions. Is there any way to avoid using function?
Hi @peterpan104,
can you give me the state of mysql_servers and runtime_mysql_servers in the proxysql DB before and after switchover?
I need to know if your mysql_servers having the correct usage of parameter. As we know that master as reader = true vs false is different in handling switchover. And I also need the value of .toml regarding proxy-servers-read-on-master and no slave.
Thank you.
Well UNIQUE constraint failed. is coming from the update indeed when the leader is also a reader we should have 2 entries for same server one in the reader and one in the writer groups so both become reader or writer because of where hostgroup_id in ('%s','%s') I think it's better to split this query in 2 to always avoid the error , may be passing the boolean for writerisreader
When I start repman for the first time, operating maintenance will not call these functions? Is there any way to avoid using function?
did you try disable proxysql-bootstrap=false ?