Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have MySQL master and replication servers both running on different machines. We have noticed some system files showing integrity issues in the master machine. Temporarily we would like to make the slave as a master and reformat the original master and then bring back everything to the original architecture.

What are the crucial steps now for us to maintain data integrity?

We can down the master and let the inserts and update take place in the replication db, but how do we revert back to the original situation later?

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

Globally speaking, I would essentially be performing the following steps in the same situation.

Before starting, backup everything on both machines.

  1. Put the website or application into maintenance, preventing users from accessing the database. Also make sure there are no imports/updates/activity coming in from cron jobs. You can verify this by running SHOW FULL PROCESSLIST; in MySQL.
  2. Make sure the slave server is fully synced with the master. Read notes below.
  3. Stop replication on the slave server STOP SLAVE; and turn off the master server.
  4. Exchange IP addresses between the old and new master server. Or point the application's database connection to the slave server.
  5. Bring the application back online.
  6. Perform your maintenance on the old master server.
  7. Once your old master server is back up and running
  8. Put the website or application back into maintenance.
  9. Backup your new master server's database and restore it to the original and now functional master server.
  10. Exchange IP addresses once again between the two machines. Or point the application's database connection to the master server again.
  11. Reconfigure replication between the newly restored master server and the original slave server. Read notes below.
  12. Bring the application back online.

For step 2, when you are making sure that the master and slave are synced, you would do this. Comparing the current log file and file position for both servers.

On the master server

SHOW MASTER STATUS;

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73       | test         |                  |
+---------------+----------+--------------+------------------+

On the slave server

SHOW SLAVE STATUS;

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: master.server.ip.address
                Master_User: root
                Master_Port: 3306
              Connect_Retry: 3
            Master_Log_File: mysql-bin.003
        Read_Master_Log_Pos: 73

Since you are planning on reverting back to the original infrastructure, there is no need to setup the slave server as a new master. You can leave the slave server's configuration in place, so that once the master server is back online, you can restart replication more quickly.

Configuring replication

Rolando answered a similar question with an easy to read how to on setting up replication.

Clarification about master slave configuration in mysql

share|improve this answer
    
very helpful and in depth. I got few things to confirm here. In fact once the master is up and running so how I should stop the slave and dump the data back into the master right? Thereafter I also now required to re-format the slave server as that one also is infected by some bots too. So once the master is alive I should not start it to replicate rite? I should leave it to run then stop it for a while back up the data into slave and then only run the replication am I right here? Please give some hardening tip for mysql from your experience. –  newbie14 Sep 4 '12 at 7:10
    
I updated my answer. At step 3, you stop replication with the STOP SLAVE command from the MySQL console on the slave server. Once the master is backup and running and you have restored a current copy of the database to it, you should restart replication. Unless I didn't understand what you meant. As for hardening tips go, could you put that into a new question? –  Craig Efrein Sep 4 '12 at 16:30
    
no you did understand my question perfectly right.So in the replication behave as master we run the command stop slave right. Then after that I mysql dump into the original db server. That moment I must run first start master is it or is automatically run master to send to replication server? Is it possible to stop the master to send data to replication? –  newbie14 Sep 5 '12 at 17:13
    
The master server just creates bin-logs, so you would simply comment out the server-id and log-bin variables in my.cnf and then restart mysql. You would want to run the STOP SLAVE command on every slave though before turning off bin-logs on the master server. –  Craig Efrein Sep 6 '12 at 5:58
    
So if I understand carefully is that when I the master is back in place that is the time I comment out server-id and log-bin which is the time the slave is being reformatted? So once the slave is ready I must stop the master for a while copy the data to slave am I right? What is best mechanism to copy the data to the slave at that moment? –  newbie14 Sep 6 '12 at 18:12
show 6 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.