18 May 2010

How to Safetly Restart Your MySQL Slave Server

  • Make sure that nothing can access it
    (take it off the load balancer).
  • in mysql client do: STOP SLAVE;
  • in mysql client do: FLUSH TABLES;
    (if it gets stuck here, then you might need to fix it. You can try UNLOCK TABLES.)
  • in command line do: /etc/init.d/mysql stop

...do your what you need to do here...

  • in command line do: /etc/init.d/mysql start
    (check for errors, sometimes in my.cnf)
  • in mysql client do: START SLAVE;
    (normally this would be done automatically when you restart mysql)
  • in mysql client do: SHOW SLAVE STATUS\G
    (check that replication is working properly)
  • After you are satisfied, set up the slave server back on the load balancer

9 comments:

  1. Hi,

    There's also the Slave_open_temp_tables status variable; verify that it is zero before shutting down, or start/stop slave again until it is.

    ReplyDelete
  2. FLUSH LOGS could take a long time depending on your environment, but it will eventually finish.

    My $.02
    G

    ReplyDelete
  3. @Gerry

    I don't know if you want to flush your logs as you might want them for restoration or other slaves.
    FLUSH TABLES, as I have learnt the hard way, will make sure all data and index changes be completed. So if you have delay_key_write on, it will save the changes to disk and won't corrupt your tables.

    ReplyDelete
  4. Oops! I meant FLUSH TABLES :)

    However FLUSH LOGS doesn't cause any harm. Both will be flushed as part of the orderly shutdown.

    ReplyDelete
  5. Thanks Jonathan, keep posting

    ReplyDelete
  6. on a large innodb system, you may want to progressively decrease the value of innodb_max_dirty_pages_pct until 1, then shutdown.

    ReplyDelete
  7. incidentally if a clean shutdown without flush tables is corrupting myisam tables when delay_key_write=0 then this is a bug. please file a bug at bugs.mysql.com...

    ReplyDelete
  8. i forgot my password please tell me HOW TO RETAIN IT

    ReplyDelete
  9. https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

    ReplyDelete