title: Promoting a mysql slave to master link: https://onemoretech.wordpress.com/2013/09/19/promoting-a-mysql-slave-to-master/ author: phil2nc description: post_id: 6363 created: 2013/09/19 17:26:24 created_gmt: 2013/09/19 21:26:24 comment_status: closed post_name: promoting-a-mysql-slave-to-master status: publish post_type: post

Promoting a mysql slave to master

This is essential. If you're running MySQL server in a master-slave configuration you need to know how to promote the slave to master in the event the worst happens, which, believe me, it will. Usually this happens in the context of a master going down and being unrecoverable for a time. Consider that YMMV under different circumstances. Syntax is different depending upon the MySQL server version involved. If you can access the master I'd recommend you run:

mysql> flush logs;

For v5.0, 5.1 use this syntax on the slave:

mysql> stop slave;
mysql> change master to MASTER_HOST='';
mysql> reset slave;

Make sure that you remove any "master-" statements from my.cnf before restarting the server or all hell will break loose. Best advice is to never put "master-" configs in a slave's my.cnf, ever. For v5.5 use this syntax on the slave:

mysql> stop slave;
mysql> reset slave all;

You can use "show slave status\G" in either case to verify it has been reset. If the master ever comes back you have the option of re-configuring it as a slave, of course, and then reversing the process to promote it back to master and then re-initialize the slave. If you have a maintenance window it is probably safest to do a mysqldump and rebuild both master and slave.

Copyright 2004-2019 Phil Lembo