Master Slave configuration [closed]

I need to set-up a master slave setup for mysql.

1 Master and 1 Slave ( replication ).

Now the main question here is : Is it possible to promote a slave as master when master fails. Then when the old master is up it will be the slave now.

The answer to your question is "yes" and there are instructions that must be followed here. Essentially, assuming Andrew is the current slave and Abe the current master. To swap the two:

Check Andrew to make sure that the sql-thread has finished processing as much as it can. At the mysql prompt, run SHOW PROCESSLIST. (You can also do this by using MySQL Administrator to view the current connections.) Both the IO thread and the sql thread should be listed, as system_users. The IO thread should have a state “Reconnecting after a failed master event read”. If the sql thread is completely caught up, it’s state should be “Has read all relay log; waiting for the slave I/O thread to update it”. If this says something else (like processing relay log), then the slave is still catching up, and you need to let it run until it’s caught up.

An alternate way to do this check would be to run the command SHOW SLAVE STATUS \G, and look at the relay log and position. You can then verify that this is indeed the latest relay log, and use the mysqlbinlog utility to verify that the position is the latest one. Note that you cannot use the SECONDS_BEHIND_MASTERvariable in the slave status output - it will be null if the slave cannot connect to the master.

Once Andrew is caught up, reset it. At the MySQL prompt, run RESET SLAVE;. This will delete the master.info file, and free Andrew from the chains of bondage.

Next, move all of the binary logs to a temporary directory. The binary logs will look like andrew-bin.000001, where andrew is the host name. This step isn’t strictly necessary, but I have an aversion to deleting log files.

Now reset the binary logs for Andrew. This will remove all binary logs, which is why we backed them up in the previous step. It will also make it easy for Abe to connect as a slave to Andrew when we bring Abe back. To do this, run RESET MASTER; on Andrew.

Edit my.cnf on Andrew, to comment out the read_only system variable (if you have it set).

Restart mysql on Andrew: /etc/init.d/mysql restart

Change the masterdb.janitors.com cname to point to Andrew, instead of Abe. This step requires that your application is pointing to the previously setupmasterdb.janitors.com, instead of something like andrew.janitors.com.

The web application should now be succesfully connecting to Andrew as the master database.

Once Abe has recovered, we can bring him up as a slave to Andrew. First step is to edit the my.cnf on Abe, and uncomment the read_only system variable.

Start mysql on Abe (same command as above). Note that this means mysql should not be set to start automatically on machine boot. This is an extra safety measure. I don’t know if mysql will attempt any type of error recovery, such as finishing transactions that it was unable to complete because of the crash - but we don’t want it to do anything that will make it any more different than Andrew (it may already be slightly different, but nothing we can do about that). So, by setting it to read only before we start it up, we make sure that the only updates to the db are those that come from the new master.

Make Abe a slave to Andrew. On Abe, run

CHANGE MASTER TO MASTER_USER='replicator', MASTER_PASSWORD='password', MASTER_HOST='andrew.janitors.com', MASTER_LOG_FILE='andrew-bin.0000001', MASTER_LOG_POS=4;

We know the exact position in the log file because we reset it back in step 4. Also, we are setting master_host to andrew.janitors.com instead ofmasterdb.janitors.com because we don’t necessarily want any failover to a separate master (because different masters will have different binary logs).