MySQL Replication – Permission Denied Errors

I spent a long time beating my head against the wall trying to solve this, so I thought I’d share it here in the hopes that it helps someone else.

I picked up a very cheap VPS to serve as a backup for various services, and decided to use it as a MySQL slave for backup purposes, and to provide secondary DNS (with a MySQL backend). Despite having no problem maintaining a kind-of-complicated cluster of MySQL boxes at work, I could not for the life of me get basic replication running. It consistently failed:

100829 15:56:56 [ERROR] Slave I/O thread: error connecting to master 'replication@127.0.0.1:7777': Error: 'Access denied for user 'replication'@'localhost' (using password: YES)'  errno: 1045  retry-time: 60  retries: 86400

(Yes, 127.0.0.1 is the master. Port 7777 is an ssh tunnel.) I tore my hair out, and repeatedly dropped and re-created the replication user. I could connect by hand. I tripled-checked credentials. It just didn’t work. Some people advised that master.info was sometimes saved with incorrect information; I blew that file away and re-issued the CHANGE MASTER command. Still nothing.

Finally, someone mentioned an oddity they discovered: with long passwords, replication just refused to connect. I’d used my favorite method of assigning passwords: “head /dev/urandom | shasum” So I changed my password to ‘password’ (just for testing!), flushed privileges on the master, changed the password with CHANGE MASTER, and started the slave again. Viola.

This wouldn’t be nearly as annoying if I could find it documented anywhere. I would argue that it’s a bug. But there it is — shorten your password. (12 characters is the limit I’ve seen put forth.)

5 thoughts on “MySQL Replication – Permission Denied Errors

Leave a Reply

Your email address will not be published. Required fields are marked *