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:
... 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:
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?