Replica's IP changed, can't connect to source, Replica_IO_State: Connecting to source

MySQL & MariaDB
,
1

Hello,
I’ve setup replica on my local machine, my ISP changed IP address and backup is broken. I’ve added permission on source with new IP and I can also connect from replica to source mysql.

mysql --host=s.s.s.s --user=repl --password=‘password’;
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 255030
Server version: 8.0.39 MySQL Community Server - GPL

Copyright (c) 2000, 2024, 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> SHOW GRANTS;
±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for repl@x.x.x.x |
±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON . TO repl@x.x.x.x |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON . TO repl@x.x.x.x |
±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.10 sec)

For some reason replica is not able to connect to source

Replica

mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
Replica_IO_State: Connecting to source

Last_IO_Errno: 1045
Last_IO_Error: Error connecting to source ‘repl@x.x.x.x:1234’. This was attempt 29/86400, with a delay of 60 seconds between attempts. Message: Access denied for user ‘repl’@‘cpcxyz6.4-2.cable.virginm.net’ (using password: YES)
Last_SQL_Errno: 0
Last_SQL_Error:

It is weird that it shows ‘repl’@‘cpcxyz6.4-2.cable.virginm.net’ instead of my local system’s IP address. I’m not sure how it is being picked up from.

How do I fix it?

2

Either create a new user ‘repl’@‘cpcxyz6.4-2.cable.virginm.net’ with permissions, or change your config to add skip-name-resolve in the [mysqld] section and restart mysql.

4

Adding skip-name-resolve on source was causing issues with my websites so I had to rule this out.

I created a new user ‘repl’@‘cpcxyz6.....’ with permissions on Source.

mysql> SELECT User, Host FROM mysql.user;
±-------------------------±------------------------------------------------+
| User | Host |
±-------------------------±------------------------------------------------+

| repl | cpcxyz6.4-2… |

Restarted both source and replica mysql.

Still the same error.

Is there any other changes to make anywhere?

5

Not sure what else to tell you. MySQL is telling you, quite literally, that you have a user/password issue. What permissions did you GRANT? Are you 100% certain the password is correct?

FYI, completely unnecessary. You can use ‘STOP REPLICA; START REPLICA;’ to bounce replication without restarting MySQL.

On the replica, use command line, mysql -h <source> -u repl -p and enter the replication password. If that fails, then 100% you have username/password issues.

6

Forgot to tell earlier, this command does work and I can access source’s mysql from replica. This was the reason I asked if there is anything else to be done in Xtrabackup’s setting somewhere?

Any other command to try or any other settings to check?

mysql --host=x.x.x.x --user=repl --password=‘sdf34jdfer’;
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 34956
Server version: 8.0.39 MySQL Community Server - GPL

Copyright (c) 2000, 2024, 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>

I can also run

show databases;

and

use dbname;

when connected via command line, so it seems that manual connect works but not the replication setup.

I know that there is an issue with permission but I’m not able to find out where and how to fix it.

7

You have too many grants for replication user. You need to REVOKE ALL to remove of all those unnecessary grants. Then the ONLY grants you need for replication are REPLICATION_CLIENT and REPLICATION_SLAVE

GRANT REPLICATION_CLIENT, REPLICATION_SLAVE ON *.* TO 'repl'@'cpcxyz6.4-2.cable.virginm.net’ IDENTIFIED BY 'mypassword';

8

Here is the grant for replica user

mysql> SHOW GRANTS FOR ‘repl’@‘cpc9.virginm.net’;
±-------------------------------------------------------------------------------------------+
| Grants for repl@ cpc9.cable.virginm.net |
±-------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON . TO repl@cpc9.cable.virginm.net |
±-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

I think the issue is with hostname being resolved on source instead of IP address.

9

That user is wrong. Look at the error message from above. MySQL tells you exactly how to create the user:

Access denied for user ‘repl’@‘cpcxyz6.4-2.cable.virginm.net’ (using password: YES)

You created the account repl@cpc9.cable.virginm.net and those are not the same.

10

That hostnames are just redacted.

Anyways I figured out the issue. It was incorrect password used during the repl user creation. I used correct password and it worked. Thanks for help.