I am setting up a Master-slave MySQL replication setup using 3 MySQL 8 nodes(Fresh installations). I have setup a master and configured another nodes to replicate from the master which is working fine.
I have setup MaxScale as a load balancer and I am intending to use it as a load balancer with read write splitting. Following is my setup(domain names are anonymized for privacy reasons).
node1.com - master
node2.com - slave1
I can succesfully connect both nodes with `mysql -uroot -proot -h . Following is my MaxScale configuration.
[maxscale]
threads=auto
admin_secure_gui=false
admin_host=0.0.0.0
[node1]
type=server
address=node1.com
port=3306
protocol=MySQLBackend
[node2]
type=server
address=node2.com
port=3306
protocol=MySQLBackend
[node3]
type=server
address=node3.com
port=3306
protocol=MySQLBackend
[MySQL-Monitor]
type=monitor
module=mariadbmon
servers=node1,node2,node3
user=maxscale
password=password
monitor_interval=2s
[Read-Write-Service]
type=service
router=readwritesplit
servers=node1,node2
user=maxscale
password=password
enable_root_user=true
[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=3307
When I try mysql -uroot -proot -h <maxscale-domain> Maxscale can succesfully connect to the server, but the connection hangs as follows.
➜ ~ mysql -uroot -proot -h <maxscale-domain> -P 3307
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
How can i resolve this?
I have tried the followings to try to debug this.
- Using a new read only service with following configuration
[Read-Service]
type=service
router=readconnroute
servers=node1,node2
router_options=master
user=maxscale
password=password
enable_root_user=true
[Read-Listener]
type=listener
service=Read-Service
protocol=MariaDBClient
port=3306
This has led me somewhere with mysql -uroot -proot as it allows me to connect to both servers succesfully. When router_options=master I can successfully connect to master and query the database. However when router_options=slave I can connect to the slave but cannot query it.
➜ ~ mysql -uroot -proot -h <maxscale-host> -P 3306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.32-0ubuntu0.22.04.2
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@hostname;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id: 8
Current database: *** NONE ***
ERROR 1927 (HY000): Lost connection to backend server: network error (node2, Connection refused)
This led me to believe the issue could be with the node2, but I cannot figure out the issue as it works fine if i connect the mysql client directly to node.
- To ensure that this is not a issue related to any slave, i removed node1 from the network and added a new node3. Then node2 is setup as the master of node3 with replication enabled. Results for the Read only service is as follows.
With router_options=master :
➜ ~ mysql -uroot -proot -h <maxscale-host> -P 3306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 8.0.32-0ubuntu0.22.04.2
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@hostname;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id: 2
Current database: *** NONE ***
ERROR 1927 (HY000): Lost connection to backend server: network error (node2, Connection refused)
It provides the same issue for node2 and 3 both now.
With router_options=slave :
➜ ~ mysql -uroot -proot -h <maxscale-host> -P 3306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 8.0.32-0ubuntu0.22.04.2
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@hostname;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id: 2
Current database: *** NONE ***
ERROR 1927 (HY000): Lost connection to backend server: network error (node3, Connection refused)
Note that all 3 are plain MySQL installations with few empty databases added + binlog enabled. Replication works fine and MaxScale GUI reports as such. It looks like the issue comes from MySQL, but actually it should be related to the MaxScale.
Any thoughts on the matter are highly appreciated.
Edit : Attaching maxscale.log
MariaDB MaxScale /var/log/maxscale/maxscale.log Fri May 5 13:27:43 2023
----------------------------------------------------------------------------
2023-05-05 13:27:43 notice : Module 'mariadbmon' loaded from '/usr/lib/x86_64-linux-gnu/maxscale/libmariadbmon.so'.
2023-05-05 13:27:43 notice : Module 'readconnroute' loaded from '/usr/lib/x86_64-linux-gnu/maxscale/libreadconnroute.so'.
2023-05-05 13:27:43 notice : Module 'readwritesplit' loaded from '/usr/lib/x86_64-linux-gnu/maxscale/libreadwritesplit.so'.
2023-05-05 13:27:43 notice : Using up to 75.53GiB of memory for query classifier cache
2023-05-05 13:27:43 notice : syslog logging is disabled.
2023-05-05 13:27:43 notice : maxlog logging is enabled.
2023-05-05 13:27:43 notice : Host: '<maxscale-host>' OS: Linux@5.4.0-135-generic, #152-Ubuntu SMP Wed Nov 23 20:19:22 UTC 2022, x86_64 with 64 processor cores (64.00 available).
2023-05-05 13:27:43 notice : Total main memory: 503.56GiB (503.56GiB usable).
2023-05-05 13:27:43 notice : MariaDB MaxScale 23.02.1 started (Commit: 5de96ecc39da80a992fc8f362d22a030a844bef5)
2023-05-05 13:27:43 notice : MaxScale is running in process 3097068
2023-05-05 13:27:43 notice : Configuration file: /etc/maxscale.cnf
2023-05-05 13:27:43 notice : Log directory: /var/log/maxscale
2023-05-05 13:27:43 notice : Data directory: /var/lib/maxscale
2023-05-05 13:27:43 notice : Module directory: /usr/lib/x86_64-linux-gnu/maxscale
2023-05-05 13:27:43 notice : Service cache: /var/cache/maxscale
2023-05-05 13:27:43 notice : Working directory: /var/log/maxscale
2023-05-05 13:27:43 notice : Module 'qc_sqlite' loaded from '/usr/lib/x86_64-linux-gnu/maxscale/libqc_sqlite.so'.
2023-05-05 13:27:43 notice : Query classification results are cached and reused. Memory used per thread: 1.18GiB
2023-05-05 13:27:43 notice : Password encryption key file '/var/lib/maxscale/.secrets' not found, using configured passwords as plaintext.
2023-05-05 13:27:43 notice : The systemd watchdog is Enabled. Internal timeout = 30s
2023-05-05 13:27:43 notice : Using HS256 for JWT signatures
2023-05-05 13:27:43 notice : Started REST API on [0.0.0.0]:8989
Edit 2:
Attaching mysql general log for reference. Nothing specific to logging or execution of the query gets logged in the mysql general log either. However when i restart the maxscale server it succesfull y connects it seems which is proven by the statement 2023-05-05T11:45:44.693829Z 19 Connect maxscale@<maxscale-host> on using TCP/IP
2023-05-05T11:45:44.226596Z 18 Query SHOW STATUS LIKE 'Uptime'
2023-05-05T11:45:44.228199Z 18 Query SELECT @@global.server_id, @@read_only
2023-05-05T11:45:44.228584Z 18 Query SHOW SLAVE STATUS
2023-05-05T11:45:44.532309Z 18 Quit
2023-05-05T11:45:44.693829Z 19 Connect maxscale@<maxscale-host> on using TCP/IP
2023-05-05T11:45:44.694243Z 19 Quit
2023-05-05T11:45:44.695671Z 20 Connect maxscale@<maxscale-host> on using TCP/IP
2023-05-05T11:45:44.696020Z 20 Query SET SQL_MODE=''
2023-05-05T11:45:44.696292Z 20 Query SET @@session.autocommit=1
2023-05-05T11:45:44.696512Z 20 Query SET NAMES latin1
2023-05-05T11:45:44.696768Z 20 Query SELECT id, @@global.collation_server FROM information_schema.collations WHERE collation_name=@@global.collation_server
Edit 3: Attaching MaxScale info log as suggested by https://stackoverflow.com/users/7633996/markusjm
2023-05-08 11:05:34 info : Routed [COM_QUERY] to 'node-a' select @@version_comment limit 1
2023-05-08 11:05:34 info : Storing COM_QUERY while in state 'Handshaking': select @@version_comment limit 1
2023-05-08 11:05:34 info : Server 'node-a' failed
2023-05-08 11:05:34 info : Stopped Read-Service client session [1]
2023-05-08 11:05:38 info : Accept authentication from 'admin', using password. Request: /auth
2023-05-08 11:05:44 info : Found matching user 'maxscale'@'%' for client 'maxscale'@'2001:1458:202:56::111:1b' with sufficient privileges.
2023-05-08 11:05:44 info : New session for server node-a. Connections : 1
2023-05-08 11:05:44 info : Started Read-Service client session [2] for 'maxscale' from 2001:1458:202:56::100:1b
2023-05-08 11:05:44 info : Server 'node-a' failed
2023-05-08 11:05:44 info : Stopped Read-Service client session [2]
2023-05-08 11:07:03 info : Found matching user 'maxscale'@'%' for client 'maxscale'@'2001:1458:202:56::111:1b' with sufficient privileges.
2023-05-08 11:07:03 info : New session for server node-a. Connections : 1
2023-05-08 11:07:03 info : Started Read-Service client session [3] for 'maxscale' from 2001:1458:202:56::111:1b
2023-05-08 11:07:03 info : Server 'node-a' failed
2023-05-08 11:07:03 info : Stopped Read-Service client session [3]
2023-05-08 11:10:39 info : Found matching user 'maxscale'@'%' for client 'maxscale'@'2001:1458:202:56::111:1b' with sufficient privileges.
This is after setting log_info=true for maxscale and recreating maxscale MySQL user with mysql_native_password. Following is the user info.
mysql> select * from user where user='maxscale'\G;
*************************** 1. row ***************************
Host: %
User: maxscale
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: Y
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: N
Repl_client_priv: Y
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
Event_priv: N
Trigger_priv: N
Create_tablespace_priv: N
ssl_type:
ssl_cipher: 0x
x509_issuer: 0x
x509_subject: 0x
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *2470C0C06DEE42FD1618BB99005ADCA2629D1E19
password_expired: N
password_last_changed: 2023-05-08 09:02:05
password_lifetime: NULL
account_locked: N
Create_role_priv: N
Drop_role_priv: N
Password_reuse_history: NULL
Password_reuse_time: NULL
Password_require_current: NULL
User_attributes: NULL
1 row in set (0.00 sec)
mysql> show grants for 'maxscale'@'%';
+-------------------------------------------------------------------+
| Grants for maxscale@% |
+-------------------------------------------------------------------+
| GRANT SHOW DATABASES, REPLICATION CLIENT ON *.* TO `maxscale`@`%` |
| GRANT SELECT ON `mysql`.`columns_priv` TO `maxscale`@`%` |
| GRANT SELECT ON `mysql`.`db` TO `maxscale`@`%` |
| GRANT SELECT ON `mysql`.`procs_priv` TO `maxscale`@`%` |
| GRANT SELECT ON `mysql`.`proxies_priv` TO `maxscale`@`%` |
| GRANT SELECT ON `mysql`.`tables_priv` TO `maxscale`@`%` |
| GRANT SELECT ON `mysql`.`user` TO `maxscale`@`%` |
+-------------------------------------------------------------------+
7 rows in set (0.00 sec)
