ProxySQL setup hostgroup concern

Question

It's regarding this instruction from AWS.

https://aws.amazon.com/blogs/database/how-to-use-proxysql-with-open-source-platforms-to-split-sql-reads-and-writes-on-amazon-aurora-clusters/

I am not sure if there is an issue with hostgroup_id. When I run “SELECT hostgroup_id, hostname, status FROM mysql_servers;” , it show 10 for the master, and 20 for the read replica, however, after I run the “LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;”, the hostgroup_id both hosts are all change to 20. Is it normal? Please advise. Thanks

ProxySQLAdmin> INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('uatrds1.ap-southeast-2.rds.amazonaws.com 



',10,3306,1000,10000);
Query OK, 1 row affected (0.00 sec)

ProxySQLAdmin> INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES ('uatreadreplica.ap-southeast-2.rds.amazonaws.com 



',20,3306,1000,10000);
Query OK, 1 row affected (0.00 sec)

ProxySQLAdmin> SELECT hostgroup_id, hostname, status FROM mysql_servers;
+--------------+--------------------------------------------------------------+--------+
| hostgroup_id | hostname                                                     | status |
+--------------+--------------------------------------------------------------+--------+
| 10           | uatrds1.ap-southeast-2.rds.amazonaws.com 



        | ONLINE |
| 20           | uatreadreplica.ap-southeast-2.rds.amazonaws.com 



 | ONLINE |
+--------------+--------------------------------------------------------------+--------+

ProxySQLAdmin> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

ProxySQLAdmin> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.02 sec)


ProxySQLAdmin> SELECT hostgroup_id, hostname, status FROM mysql_servers;
+--------------+--------------------------------------------------------------+--------+
| hostgroup_id | hostname                                                     | status |
+--------------+--------------------------------------------------------------+--------+
| 20           | uatrds1.ap-southeast-2.rds.amazonaws.com 



        | ONLINE |
| 20           | uatreadreplica.ap-southeast-2.rds.amazonaws.com 



 | ONLINE |
+--------------+--------------------------------------------------------------+--------+
2 rows in set (0.00 sec)

Answer 1

First of all, check runtime_mysql_servers for the runtime.

Second, when using auror and not the read only endpoint for the reader, you should also setup the mysql_replication_hostgroups table with check_type = 'innodb_read_only'. Otherwise your setup went broken on a failover (on a failover, all aurora nodes will reboot).

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 |
+--------------+---------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0            | some-aurora-node    | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | an other-aurora-node| 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+---------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)

MySQL [(none)]> select * from runtime_mysql_replication_hostgroups;
+------------------+------------------+------------------+---------+
| writer_hostgroup | reader_hostgroup | check_type       | comment |
+------------------+------------------+------------------+---------+
| 0                | 1                | innodb_read_only |         |
+------------------+------------------+------------------+---------+
1 row in set (0.00 sec)

MySQL [(none)]> select rule_id, active, destination_hostgroup, match_pattern from runtime_mysql_query_rules;
+---------+--------+-----------------------+-------------------------+
| rule_id | active | destination_hostgroup | match_pattern           |
+---------+--------+-----------------------+-------------------------+
| 1       | 1      | 1                     | ^SELECT                 |
| 2       | 1      | 0                     | ^SELECT.*FOR UPDATE$    |
+---------+--------+-----------------------+-------------------------+
2 rows in set (0.00 sec)

furthermore, don't forget to add monitor credentials to the aurora. Than proxysql is able to determine itself the reader and the writer endpoint - on the fly (after failovers).

CREATE USER 'proxysql'@'%' IDENTIFIED BY 'monitor';
GRANT USAGE ON *.* TO 'proxysql'@'%';

monitor is the default password of proxysql's monitor user.

I highly recommend to read the wiki of proxysql https://github.com/sysown/proxysql/wiki It's very well documentated and there are a lot of examples.
The second source of trust when it comes to proxysql is the percona blog: https://www.percona.com/doc/percona-xtradb-cluster/5.7/howtos/proxysql.html
Don't rely on marketing blogs :)