2

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?

  • Don't know if you have figured this out yet but it seems to me you forgot to run 'FLUSH PRIVILEGES;' in MySQL after creating the user. – Don Davis Jul 22 at 17:56
1

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.

  • I did a rm -rfv with no root option, or whatever it was, yesterday. Therapeutic, if nothing else. I'll reinstall the slave this weekend. – user27880 Dec 20 '13 at 9:33
0

On your mysqldump did you dump your "mysql" database ? (it contains users tables)

If you can connect to your master check your users:

To see declared MySQL accounts use:

SELECT user, host from mysql.user;

To check if there are declared passwords for root users try:

SHOW GRANTS FOR root;
SHOW GRANTS FOR root@'locahost';

EDIT: For your original problem, i had some issues when using DNS for replication try to use the IP address instead in your "CHANGE MASTER TO" command.

Best regards.

Max.

  • No, still the same error. – user27880 Dec 19 '13 at 14:15
  • And on the master you can connect with mysql -u root -pXXXXXXXX ? – Maxime Fouilleul Dec 19 '13 at 14:19
  • @Max, on the master: repli %.example.com I can't run that command on the slave as I can't get access – user27880 Dec 19 '13 at 14:49
  • curiously, I can run mysql -u repli -pXXXX -h server1.example.com and connect to the server's MySQL instance with no issues. – user27880 Dec 19 '13 at 14:50
  • Ok, so now with this user check if root@'localhost' have a password with "show grants for root@'locahost'" (if yes, you should see something like "IDENTIFIED BY PASSWORD '*32C3BD4B5C86DE68F3F351357DCD7043D3C2188B'") and do the same on your master to see if there are differences – Maxime Fouilleul Dec 19 '13 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.