2

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?

Improve this question

1 Answer 1

Reset to default
2

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

Improve this answer
11
  • 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, 2012 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, 2012 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, 2012 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, 2012 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, 2012 at 18:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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