Mysql – Connection refused when trying to set up replication

MySQL

I set up replication. I've done this on two servers so far, and it's worked. Third time unlucky, as it were.

The production server is CentOS 6.4, and the replication server is CentOS 6.5. The prod box will join its little brother soon as a 6.5 box, on 3 January – date of the change. But anyway, the MySQL server is 5.1.71 on both servers.

On the server, I edit the /etc/my.cnf file:

log-bin=mysql-bin
server-id=1

… I create the user :

CREATE USER 'repli'@'%.example.com' IDENTIFIED BY 'blagblah';

.. and then … 

GRANT REPLICATION SLAVE ON *.* TO 'repli'@'%example.com' IDENTIFIED BY 'blagblah';

A mysqldump is performed every morning, so I grab the dump and rsync it over to the slave. I get the master status, the filename and the position in the log.

On the slave, I edit the /etc/my.cnf file:

server-id=2

I then restart the server, but (is this where I have gone wrong?), I just start it from /etc/init.d/.

I login to the mysql instance and execute:

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='server1.example.com', MASTER_USER='repli', MASTER_PASSWORD='blagblah', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=24112816;
START SLAVE;

When I check slave status, however, it tells me that there is an error connecting to the master.

So I start to wonder whether the fact that I didn't start the server with 'skip-slave-start' might be responsible. So I shut down the server, and execute:

sudo /usr/bin/mysqld_safe --skip-slave-start

… and this is where it starts to get weird.

mysql -u root -pXXXXXXXX

And the server tells me .. 

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Excuse me? This is the slave server, and the box on which five minutes earlier, I had being typing commands.

Where have I gone wrong?

Best Answer

After you import the dump file from the master, your new root password on the slave will stop being valid on the slave not later than, but sometimes as late as, the first time you restart it, because the mysql.user table is going to be overwritten... the new root password after restart on the slave is going to be the same as the root password from the master (assuming the master has a SUPER user called "root").

The workaround is either using the master's root password (assuming it has a root@localhost account) or is to keep your connection open to the slave while restoring the dump file, and then after the restore is done, modify the mysql.user table to have the values you want, before disconnecting and restarting. FLUSH PRIVILEGES; after making the change, then try logging in from an additional connection to verify that it's going to work, then restart the slave.

If you RESET SLAVE; before the restart, you shouldn't need to --skip-slave-start.

Related Question