What's the secure/proper way of restarting the MySQl slave server. I need to reboot the machine in order to update the kernel and apply some configuration changes.
I've tested it on my lab and unfortunately it didn't work properly:
- I powered down slave server.
- I've created new database with one simple table on the master server
- I powered up the slave server (I haven't noticed any errors in the logs) The slave server has the correct master log position but there is no data created on the master while the slave was down. The master log position is updated when i create new data on the master but it's not appear on the slave?
I'm wondering if I should make any extra steps before I reboot the server, like "SLAVE STOP;" etc.
I have also another question regarding Percona Xtradb backup. When this script is running on the slave it's going behind the master (The I/O is not high and I've set --throttle option anyway)
What's the maximum value that the slave server can go behind the master and it still will be able to catch up with master?
I've had slaves like 24 hours or more behind the master. Giving that the master doesn't delete its binary log files (there's a setting to do that automtically) you are good.
The correct way to stop a slave is issuing the
SLAVE STOPcommand, then stopping the MySQL server using the init script and giving it time to save all the files and correctly close everything. I also use thesynccommand to flush files to disk, before finally restarting the server.Lots of work but it's safer that way. Maybe the
SLAVE STOPcommand isn't really needed, but one never can be too safe with databases. Just remember to start it again after restarting the server.If this doesn't work, you have something wrong with your replication setup. I also use percona DB and that's how I did it many times.