I am configuring a master-slave MySQL replication setup with 3 MySQL 8 nodes (fresh install). I have set up a master node and configured another node to replicate from the working master node.
I have set up MaxScale Free Edition as a load balancer, which I intend to use as a load balancer with read-write partitioning. Below is my setup (domain name is anonymized for privacy).
node1.com - master |
node2.com-slave1 |
I can use `mysql -uroot -root -h
[maximum scale] |
thread = auto |
admin_secure_gui=false |
admin_host=0.0.0.0 |
[node 1] |
type=server |
Address = node1.com |
port=3306 |
protokol = mysql-backend |
[node 2] |
type=server |
Address = node2.com |
port=3306 |
protokol = mysql-backend |
[node 3] |
type=server |
Address = node3.com |
port=3306 |
protokol = mysql-backend |
[MySQL Monitoring] |
type=monitor |
module = mariadbmon |
Server = Node1, Node2, Node3 |
users = max scale |
password = password |
monitor_interval=2s |
[Read and write service] |
type=service |
router = read-write separation |
server = node1, node2 |
users = max scale |
password = password |
enable_root_user=真 |
[read-write listener] |
type = listener |
service= read and write service |
protokol = MariaDBClient |
port=3307 |
When I try `mysql -uroot -root -h
➜~mysql-root-root-h |
mysql: [WARNING] Using passwords on the command line interface may be insecure. |
Welcome to MySQL Monitor. Commands end with ; or \g. |
How can I solve this problem?
I tried the following to try to troubleshoot it.
1. Use a new read-only service with the following configuration
[Reading Service] |
type=service |
router = read connection route |
server = node1, node2 |
router_options=master |
users = max scale |
password = password |
enable_root_user=真 |
[reader] |
type = listener |
service = read service |
protokol = MariaDBClient |
port=3306 |
This put me in the place of `mysql -uroot -root` as it allowed me to connect to both servers. When router_options=master I can connect to master and query the database. But when `router_options=slave`, I can connect to the slave but not query it.
➜ ~mysql-root-root-h |
mysql: [WARNING] Using passwords on the command line interface may be insecure. |
Welcome to MySQL Monitor. Commands end with ; or \g. |
Your MySQL Connection ID is 7 |
Serverversion: 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 family of companies |
branches. Other names may be trademarks of their respective companies |
owner. |
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 makes me think that the problem might be with node2, but I can't figure it out, because if I connect the mysql client directly to the node, it works fine.
2. To make sure this was not a problem related to any slaves, I removed node1 from the network and added a new node3. Then set node2 as the primary node for node3 with replication enabled. The results for read-only services are as follows.
Use `router_options=master`:
See Also
它是有史以来最全面的 MySQL 用户和权限策略吗？你说了算➜ ~mysql-root-root-h |
mysql: [WARNING] Using passwords on the command line interface may be insecure. |
Welcome to MySQL Monitor. Commands end with ; or \g. |
Your MySQL connection ID is 1 |
Serverversion: 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 family of companies |
branches. Other names may be trademarks of their respective companies |
owner. |
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 now gives the same problem for node2 and 3.
Use `router_options=slave`:
➜ ~mysql-root-root-h |
mysql: [WARNING] Using passwords on the command line interface may be insecure. |
Welcome to MySQL Monitor. Commands end with ; or \g. |
Your MySQL connection ID is 1 |
Serverversion: 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 family of companies |
branches. Other names may be trademarks of their respective companies |
owner. |
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 clean MySQL installs with some empty databases added + binlog enabled. Replication works fine, as does the MaxScale GUI report. It seems to be a problem with MySQL, but it should be a problem with MaxScale.
Any thoughts on this matter are greatly appreciated.
EDIT: Attached maxscale.log
MariaDB MaxScale /var/log/maxscale/maxscale.log Fre 5. maj 13:27:43 2023 |
------------------------------------------------------ ---------------------------- |
2023-05-05 13:27:43 NOTE: Module 'mariadbmon' loaded from '/usr/lib/x86_64-linux-gnu/maxscale/libmariadbmon.so'. |
2023-05-05 13:27:43 NOTE: Module 'readconnroute' loaded from '/usr/lib/x86_64-linux-gnu/maxscale/libreadconnroute.so'. |
2023-05-05 13:27:43 NOTE: Module 'readwritesplit' loaded from '/usr/lib/x86_64-linux-gnu/maxscale/libreadwritesplit.so'. |
2023-05-05 13:27:43 NOTE: Using up to 75.53 GiB memory for query classification cache |
2023-05-05 13:27:43 NOTE: System logging disabled. |
2023-05-05 13:27:43 NOTE: Maxlog logging enabled. |
2023-05-05 13:27:43 Notification: Host: ' |
2023-05-05 13:27:43 Notification: Total main memory: 503.56GiB (503.56GiB available). |
2023-05-05 13:27:43 Note: MariaDB MaxScale 23.02.1 release (commit: 5de96ecc39da80a992fc8f362d22a030a844bef5) |
2023-05-05 13:27:43 NOTE: MaxScale running in process 3097068 |
2023-05-05 13:27:43 Note: Configuration file: /etc/maxscale.cnf |
2023-05-05 13:27:43 Note: Log directory: /var/log/maxscale |
2023-05-05 13:27:43 Note: Data folder: /var/lib/maxscale |
2023-05-05 13:27:43 Note: Module library: /usr/lib/x86_64-linux-gnu/maxscale |
2023-05-05 13:27:43 NOTE: Serving cache: /var/cache/maxscale |
2023-05-05 13:27:43 Note: Working directory: /var/log/maxscale |
2023-05-05 13:27:43 NOTE: Module 'qc_sqlite' loaded from '/usr/lib/x86_64-linux-gnu/maxscale/libqc_sqlite.so'. |
2023-05-05 13:27:43 Note: Query classification results are cached and reused. Memory used per thread: 1.18GiB |
2023-05-05 13:27:43 NOTE: Password encryption key file '/var/lib/maxscale/.secrets' not found, using configured password as clear text. |
2023-05-05 13:27:43 NOTE: Systemd Watchdog is enabled. Internal timeout = 30 seconds |
2023-05-05 13:27:43 Note: Use HS256 for JWT signing |
2023-05-05 13:27:43 NOTE: Starting REST API at [0.0.0.0]:8989 |
EDIT 2:
The general mysql log is attached for reference. Also, nothing specific to logging or query execution is logged in the general mysql log. But when I restart the maxscale server it connects successfully which seems to be indicated by the phrase "2023-05-05T11:45:44.693829Z 19 Connectmaxscale@
2023-05-05T11:45:44.226596Z 18 QuerySHOW STATUS AS "Uptime" |
2023-05-05T11:45:44.228199Z 18 QuerySELECT @@global.server_id, @@read_only |
2023-05-05T11:45:44.228584Z 18 QuerySHOW SLAVE STATUS |
2023-05-05T11:45:44.532309Z 18 Exit |
2023-05-05T11:45:44.693829Z 19 Connectmaxscale@ |
2023-05-05T11:45:44.694243Z 19 Exit |
2023-05-05T11:45:44.695671Z 20 Connectmaxscale@ |
2023-05-05T11:45:44.696020Z 20 QuerySET SQL_MODE='' |
2023-05-05T11:45:44.696292Z 20 QuerySET @@session.autocommit=1 |
2023-05-05T11:45:44.696512Z 20 QuerySET NAMES latin1 |
2023-05-05T11:45:44.696768Z 20 QuerySELECT id, @@global.collation_server 来自 information_schema.collations WHERE collation_name=@@global.collation_server |
EDIT 3: Attached the MaxScale infolog as suggestedhttps://stackoverflow.com/users/7633996/markusjm
2023-05-08 11:05:34 INFO: Routing [COM_QUERY] til 'node-a' VÆLG @@version_comment limit 1 |
2023-05-08 11:05:34 INFO: Storing COM_QUERY in "handshake" mode: SELECT @@version_comment limit 1 |
2023-05-08 11:05:34 INFO: Server 'node-a' failed |
2023-05-08 11:05:34 INFO: Stopped reading service client session [1] |
2023-05-08 11:05:38 INFO: Accepting authentication from 'administrator' 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: Starting read service client session for 'maxscale' from 2001:1458:202:56::100:1b [2] |
2023-05-08 11:05:44 INFO: Server 'node-a' failed |
2023-05-08 11:05:44 INFO: Stopped reading 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: Starting read service client session for 'maxscale' from 2001:1458:202:56::111:1b [3] |
2023-05-08 11:07:03 INFO: Server 'node-a' failed |
2023-05-08 11:07:03 INFO: Stopped reading 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 the maxscale MySQL user with mysql_native_password. Below is the user information.
mysql> select * from user where user='maxscale'\G; |
***************************** 1st line ********************** * ****** |
been: % |
User: maxscale |
select_priv:N |
Insert_priv: N |
Update permissions: N |
delete_priv:N |
create_priv:N |
Drop_priv：N |
Reload_priv：N |
Shutdown_priv: N |
Process_priv：N |
File permissions: N |
Grant Privileges: N |
References_priv: N |
Index_priv: N |
Change privacy: N |
Show_db_priv: Ja |
Super privacy: N |
create_tmp_table_priv:N |
Lock_tables_priv：N |
Execute permissions: N |
Repl_slave_priv: N |
repl_client_priv: ha |
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_topic: 0x |
Biggest problem: 0 |
Maximum update: 0 |
Maximum connection: 0 |
Maximum number of user connections: 0 |
Plugin: mysql_native_password |
authentication_string: *2470C0C06DEE42FD1618BB99005ADCA2629D1E19 |
password_expires: N |
password_last_changed: 2023-05-08 09:02:05 |
password_lifetime：NULL |
account_locked: N |
create_role_priv:N |
Drop_rolle_priv：N |
Password_reuse_history: NULL |
Password Reuse Time: NULL |
Password_require_current: NULL |
User Attribute: NULL |
1 row in a group (0.00 seconds) |
mysql> show assignments for "maxscale"@"%"; |
+------------------------------------------------ - ------------------+ |
|Sponsored to maxscale@%| |
+------------------------------------------------ - ------------------+ |
| ASSIGN 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`@`%` | |
+------------------------------------------------ - ------------------+ |
A set of 7 lines (0.00 seconds) |
maria