4

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:

  1. I powered down slave server.
  2. I've created new database with one simple table on the master server
  3. 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?

HTF
  • 3,148
  • 14
  • 52
  • 82

1 Answers1

5

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 STOP command, 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 the sync command to flush files to disk, before finally restarting the server.

Lots of work but it's safer that way. Maybe the SLAVE STOP command 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.

coredump
  • 12,713
  • 2
  • 36
  • 56
  • Hi, Thanks for your reply, really useful. Could you please provide some more information (link to manual) about this sync command. – HTF May 14 '12 at 18:38
  • I've created another slave with Percona Xtrabackup successfully. Everything was running fine until I restarted MySQL daemon. Logs show that the server finished and started on the same position: 120514 22:59:56 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000001', position 222253947 ..... 120514 23:04:09 [Note] Slave I/O thread: connected to master 'repl_user@87.124.126.201:3306',replication started in log 'mysql-bin.000001' at position 222253947. The "SHOW SLAVE STATUS\G;" indicates that everything is fine but there is missing data on slave? – HTF May 14 '12 at 22:17
  • Percona has a tool that makes a check between master and slave to check the replication 'pt-table-checksum`, maybe you can take a look at it. `sync` has a man page. – coredump May 15 '12 at 14:59