Good day everybody, As a newbie, I apologize if I'm doing this wrong.
About the situation: We have two physical Ubuntu servers. These are in the same network and act as master/slave servers. The replication of the databases and thus the approx. 300 GB works successfully. A login to the master is also possible. However, if the user who can log in to the master now wants to log in to the slave, he always receives a login denied.
If I now try to log on to the slave via a MySQL client, I get the following messages:
2022-05-03 9:58:01 1161 [Warning] Aborted connection 1161 to db: 'unconnected' user: 'unauthenticated' host: 'XX' (This connection closed normally without authentication)
2022-05-03 9:58:11 1170 [Warning] Aborted connection 1170 to db: 'unconnected' user: 'unauthenticated' host: 'XX' (This connection closed normally without authentication)
2022-05-03 9:58:21 1179 [Warning] Aborted connection 1179 to db: 'unconnected' user: 'unauthenticated' host: 'XX' (This connection closed normally without authentication)
2022-05-03 9:58:31 1187 [Warning] Aborted connection 1187 to db: 'unconnected' user: 'unauthenticated' host: 'XX' (This connection closed normally without authentication)
2022-05-03 9:58:41 1196 [Warning] Aborted connection 1196 to db: 'unconnected' user: 'unauthenticated' host: 'XX' (This connection closed normally without authentication)
However, the user is available and can also be executed remotely. If I try to log in locally directly on the CLI, I get the same error pattern.
It should be noted that we are working with different database versions here, since the master is currently still running on an ancient MariaDB version.
MySQL Master:
NAME="Ubuntu"
VERSION="16.04.7 LTS (Xenial Xerus)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 16.04.7 LTS"
VERSION_ID="16.04"
VERSION_CODENAME=xenial
UBUNTU_CODENAME=xenial
mariadb-server-10.1/now 10.1.44+maria-1~xenial amd64
mariadb-server-core-10.1/now 10.1.44+maria-1~xenial amd64
MySQL Slave:
NAME="Ubuntu"
VERSION="20.04.4 LTS (Focal Fossa)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 20.04.4 LTS"
VERSION_ID="20.04"
VERSION_CODENAME=focal
UBUNTU_CODENAME=focal
mariadb-server-10.5/unknown,now 1:10.5.15+maria~focal amd64
mariadb-server-core-10.5/unknown,now 1:10.5.15+maria~focal amd64
A netstat -tulpn |grep 3306 shows me that the database is also online.
If I now query the slave status, everything looks fine here too:
MariaDB [(none)]> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: XX.XX.XX.XX
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.024024
Read_Master_Log_Pos: 798994582
Relay_Log_File: mysql-relay-bin.000008
Relay_Log_Pos: 798994879
Relay_Master_Log_File: mariadb-bin.024024
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: database_name
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 798994582
Relay_Log_Space: 798995229
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: optimistic
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_DDL_Groups: 56
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 97448
1 row in set (0.000 sec)
ERROR: No query specified
Is anyone aware of this problem or has an approximate idea of how to implement a login on the slave for read rights?
