Tag: replication

Notes on mysql replication in Ubuntu hardy

Posted by – September 8, 2008

I'm moving some sites currently on shared hosting to Slicehost: and setting up MySQL replication so I can run backups from the slave and push them to Amazon S3 storage on a regular basis using s3sync.

This ONLamp article gives a handy step-by-step guide. One thing that got me though, was that I had set up iptables to firewall my servers, and I needed to get my master and slave able to talk to each other without opening up MySQL to the whole world. My solution was to email Slicehost and request private IP addresses for both servers.

I then changed this line in /etc/mysql/my.cnf from

bq. bind-address          = 127.0.0.1

to

bq. bind-address          = my private IP address

on each server. Then I added this to my iptables rules on both:

-A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT

I reloaded the iptables rules with iptables-restore, and then checked with telnet from another server outside of those 2 to make sure I couldn't reach port 3306.

The next problem I encountered was an error like this when I restarted the slave after copying over the data in the master to get started:

error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'

A search of the Ubuntu forums gave me this thread which explained that debian uses a file called /etc/mysql/debian.cnf for that user password, and when I moved things over from the master DB, I changed the password for that user. The solution is to change to password in that file on the slave to the same password found on the master.