There are times when you may need to promote to MySQL Slave server to a Master server. This task can be done pretty easily, but it is important to note that once this is done, you will need to rebuild your Master MySQL server as it is no longer the point of truth for your databases.
Stop the ‘IO_THREAD’ on the MySQL SLAVE:
mysql> stop slave io_thread;
Check the ‘processlist’ to make sure that the SLAVE has read the entire relay log:
mysql> show processlist;; +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ | 2 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | | 4 | system user | | NULL | Connect | 124 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ 2 rows in set (0.00 sec)
Stop the MySQL Slave process on MySQL 5.0 and MySQL 5.1
mysql> stop slave; mysql> reset slave;
Stop the MySQL Slave process on MySQL 5.5 and MySQL 5.6
mysql> stop slave; mysql> reset slave all;
Confirm that the following line is commented out in your /etc/my.cnf
# read-only = 1
Restart MySQL to re-read your my.cnf:
service mysql restart
Finally, don’t forget to update your web applications or anything else using the databases to point to the MySQL Slave IP address since it is now the new MySQL Master server.