I am testing the maxscale solution with a simple mariadb architecture, a primary (server2) that receives all requests and a replica (server1). Replication is working properly but at the stop of the primary, I have an error reported by maxscale :
2021-08-23 14:36:08 notice : [mariadbmon] Selecting a server to promote and replace 'server2'. Candidates are: 'server1'.
2021-08-23 14:36:08 notice : [mariadbmon] Selected 'server1'.
2021-08-23 14:36:08 notice : [mariadbmon] Performing automatic failover to replace failed master 'server2'.
2021-08-23 14:36:08 error : [mariadbmon] Failed to stop slave connection on 'server1': Query 'SET STATEMENT max_statement_time=3 FOR STOP SLAVE '';' failed on 'server1': 'Access denied for user 'monitor_user'@'192.168.100.%' (using password: YES)' (1045).
2021-08-23 14:36:08 error : [mariadbmon] Failed to remove slave connection(s) from 'server1'.
2021-08-23 14:36:08 error : [mariadbmon] Failover 'server2' -> 'server1' failed.
According to the maxscale github, grants for monitor_user are:
> show grants for 'monitor_user'@'192.168.100.%'\G;
*************************** 1. row ***************************
Grants for monitor_user@192.168.100.%: GRANT RELOAD, SUPER, REPLICATION CLIENT ON *.* TO `monitor_user`@`192.168.100.%` IDENTIFIED BY PASSWORD '***'
Is there a special grant for SET ? I must have missed something... any ideas ? Thank you.
Edit Mon Aug 23 15:19:20 UTC 2021:
Contrary to what MaxScale indicates, it is necessary to add another grant:
GRANT REPLICATION SLAVE ON *.* TO 'monitor_user'@'192.168.100.%'