MYSQL Replication: Changing a Slave Database Server to be a Master

Senario:
Say the webservers you manage use a database backend which is also replicated to two other servers. In the even that there is a failure then one of the others can take over. oldserver1.cswtech.com was not doing well and was also the master in a mysql replication setup with two “slave” servers. One older (olderserver2.cswtech.com) and one basically new (newserver.cswtech.com). The need was to make it so “oldserver1’s” master status was passed onto “newserver” and “oldserver2” would look to “newserver” for slave updates.

Preparation:
To make this easier on myself first in preparation I made sure that every php/mysql web app that I have running is using a dns name for its mysql server setting (drupal, moodle, gallery, xoops, etc). I choose master.cswtech.com. To test this name I made sure that the command: dig master.cswtech.com would respond correctly. I also checked the hosts file for old static entries and made sure that the servers involved were using an internal DNS server that resolves this name. One host file did have this setting, because it was using an external service provider’s DNS instead of our internal.

Making it happen:
I opened 3 separate terminals to each mysql server and logged in as root via SSH. Then I logged onto mysql as root with this command…
mysql -u root -pMyMYSQL-PasswordHERE (Note: no space between -p and the actual password)

DNS:
I moved back to my dns configuration and punched in the new ip address for the mysqlmaster dns entry and forced DNS to update. I updated the HOST file on the one that needed it.

MYSQL:
On the master first ran…
FLUSH LOGS;

On the new master I ran…
STOP SLAVE;
RESET MASTER;
CHANGE MASTER TO MASTER_HOST=’newserversIPaddress’;

On the slave I ran…
STOP SLAVE;
RESET MASTER;
CHANGE MASTER TO MASTER_HOST=’newserversIPaddress; (current IP of newserver)
START SLAVE;

Back to the old master I ran…
STOP SLAVE;
RESET MASTER;
CHANGE MASTER TO MASTER_HOST=’newserversIPaddress’;
START SLAVE;

See the MYSQL official replication FAQ for more information:

http://dev.mysql.com/doc/refman/5.0/en/replication-faq.html

Advertisements

About Craig Szymanski

Systems Administrator Photography Enthusiast
This entry was posted in Linux, Network Administration and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s