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.

This entry was posted in System Administration on by .

About phil

My name is Phil Lembo. In my day job I’m an enterprise IT architect for a leading distribution and services company. The rest of my time I try to maintain a semi-normal family life in the suburbs of Raleigh, NC. E-mail me at philipATlembobrothersDOTcom. The opinions expressed here are entirely my own and not those of my employers, past, present or future (except where I quote others, who will need to accept responsibility for their own rants).