Mysql privileges and replication

MySQLreplication

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!

Best Answer

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

Related Question