2

I want to recreate the whole privilege system in my database, and that includes the slave user on the master. It means i have to stop the slave, run a set master command again and than start it. My problem is that im afraid that the slave might be in the middle of reading a binlog and then when i'll start the slave with the new account (with a new binlog and master position ofcourse) there will be a data gap between the slave and the master. Someone knows maybe if i have to worry about this case and how to avoid it? Thank you very much!

6

You are over-thinking this. Stopping the slave doesn't cause you to lose your binlog position, and changing the replication username and password doesn't, either, as long as that is all you change.

On the slave:

mysql> STOP SLAVE;

This won't stop in the middle of a binlog read.

Create the new account in the master, then reconfigure the slave to use them:

mysql> CHANGE MASTER TO MASTER_USER='foo', MASTER_PASSWORD='bar';
mysql> START SLAVE;

Unless you change other parameters with CHANGE MASTER TO, the slave will reconnect with the new credentials and resume where it left off. You do not need to specify host, port, file, position, or anything else, and you should not specify them, unless they are changing too... in which case, yes, you'll need to restart with new coordinates.

https://dev.mysql.com/doc/refman/5.6/en/change-master-to.html

2

On slave:

  • stop slave;
  • Note down Relay_Master_Log_File & Exec_Master_Log_Pos

On Master:

  • Do your changes on master.

On slave:

  • Issue "CHANGE MASTER TO MASTER_HOST=..." on slave using above noted co-ordinates (Relay_Master_Log_File & Exec_Master_Log_Pos).

Your slave should be okay!

  • Isn't specifying the log file and position unnecessary, as explained in @Michael - sqlbot's answer above? – James L Sep 4 '15 at 22:41
  • Yes Michael is right ofcourse... – mysql_user Sep 5 '15 at 4:45
2

Are you trying to run a command on the Master and have it not replicate to the slave?

http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_sql_log_bin

SET sql_log_bin = 0;

turns off replication of your session.

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.