I am trying to migrate between cloud providers, using MySql Replication to copy data across live so we can switch with no downtime.
Current setup:
2x Windows servers with old provider, with MySql 8 master-master replicating fine.
2x Linux servers with new provider, with MySql 8 master-master replicating fine.
What I am trying to do it set up replication between one server on each side so all four servers are synchronised. I have setup one of the Linux servers to replicate from one of the Windows machines, and after some access denied errors it now works fine. I'm now trying to set up the same Windows machine to replicate from the Linux machine, and all I'm getting from the replication IO thread is 'Access Denied'. Using MySql Workbench on the server I can log in with the same user just fine - it only fails for the replication thread.
From the terminal on the old server (some details changed for security)
> mysql -h <linuxip> -u <username> -p<password>
Server version: 8.0.25-0ubuntu0.20.04.1 (Ubuntu)
[snip]
mysql> show grants;
+---------------------------------------------------------------+
| Grants for <username>@<windowsip> |
+---------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO `<username>`@`<windowsip>` |
+---------------------------------------------------------------+
1 row in set (0.01 sec)
So the user connects just fine, and seems to have the correct grant. For me this rules out any firewall, or user/grant issues.
But when I run the CHANGE MASTER TO and start the thread, I just get:
error connecting to master 'username@:3306'
- retry-time: 60 retries: 19 message: Access denied for user 'username'@'' (using password: YES)
I have obscured the username and passwords here, but I've copy and pasted multiple times to ensure I'm not messing up the details in the CHANGE MASTER TO query. I have set up a new user with a different password and still the same error.
I've tried searching for this issue online, and can see the same thing multiple times, but none of what was suggested has helped. I have tried:
MASTER_HOST with both hostname and IP address. Setting up user and GRANTS with both username@% and [email protected]. Setting a shorter password, as I've seen messages (from a long time ago) saying a long password will work directly, but not for replication (even though the same password has worked fine for long before just between the old servers). Restarting the service. Nothing has helped and I am now utterly baffled with what to do next.
This seems to be one of those very obscure problems, but I'm hoping someone might have an idea of what to try next.
Thank you.
