Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
None
-
None
Description
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 free version 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 <domain-name>. 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.
1. 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.
2. 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)
|
Maria