Mysql – Reformatting master MySQL server and temporary solution

data integrityMySQLreplication

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?

Best Answer

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

Before starting, backup everything on both machines.

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.
Make sure the slave server is fully synced with the master. Read notes below.
Stop replication on the slave server STOP SLAVE; and turn off the master server.
Exchange IP addresses between the old and new master server. Or point the application's database connection to the slave server.
Bring the application back online.
Perform your maintenance on the old master server.
Once your old master server is back up and running
Put the website or application back into maintenance.
Backup your new master server's database and restore it to the original and now functional master server.
Exchange IP addresses once again between the two machines. Or point the application's database connection to the master server again.
Reconfigure replication between the newly restored master server and the original slave server. Read notes below.
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

Related Solutions

Mysql – How to resolve the master server shut down/unavailability in thesql with master – slave replication

Before you perform any mysqldump to fully restore a Slave, you should consult the output of SHOW SLAVE STATUS\G. Let's start with a sample SHOW SLAVE STATUS\G:

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000254
        Read_Master_Log_Pos: 858190247
             Relay_Log_File: relay-bin.066069
              Relay_Log_Pos: 873918
      Relay_Master_Log_File: mysql-bin.000254
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 858190247
            Relay_Log_Space: 873772
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

Please notice that there are two sets of replication coordinates from the Master

(Master_Log_File,Read_Master_Log_Pos)
(Relay_Master_Log_File,Exec_Master_Log_Pos)

There is a major difference between them

(Master_Log_File,Read_Master_Log_Pos) tells you the last binlog statement from the Master's log file and log position that the Slave read from the Master and placed in its Relay Logs.
(Relay_Master_Log_File,Exec_Master_Log_Pos) tells you the last binlog statement from the Master's log file and log position that the Slave read from the Master and placed in its Relay Logs THAT IS NEXT TO BE EXECUTED ON THE SLAVE.

The timestamps from these two coordinates helps you figure out Seconds_Behind_Master.

Knowing these things, here is what you can do:

Step 01) Run SHOW SLAVE STATUS\G
Step 02) Get Relay_Master_Log_File,Exec_Master_Log_Pos from SHOW SLAVE STATUS\G (In the sample, that would be (mysql-bin.000254,858190247)
Step 03) STOP SLAVE;
Step 04) CHANGE MASTER TO master_log_file='mysql-bin.000254',master_log_pos=858190247;
Step 05) START SLAVE;
Step 06) Wait 10 seconds
Step 07) Run SHOW SLAVE STATUS\G and check Seconds_Behind_Master

If the Seconds_Behind_Master is a number and eventaully drops to zero, replication is fully reesatablished.

After doing all this, if replication breaks because of a corrupt binary log from the master, then you do the last resort:

Steo 01) On the Master, RESET MASTER; to erase all binary logs and start with a new one
Step 02) On the Master, run this

This create proper dump for the slave

echo "STOP SLAVE;" > /root/MySQLData.sql
mysqldump --all-databases --routines --triggers --flush-privileges --master-data=1 >> /root/MySQLData.sql
echo "START SLAVE;" >> /root/MySQLData.sql

Step 03) scp /root/MySQLData.sql over to the Slave and Load it in MySQL on the Slave

Give it a Try !!!

Mysql – Mix of Cyclic and Chained MySQL replication – What do you do with “log-slave-updates”..

Well, you are absolutely right to be concerned about log-slave-updates causing an issue with your Master-Master setup, though it won't necessarily be an infinite loop changes. I suspect if you are writing to both masters, that you will constantly be having to set skip_slave_counter and restart the slave thread.... In a word, not ideal.

I would look over this post, specifically the short section on data integrity:

The safest solution is to simply never write data to both masters.

So, if both master's are up, but your clients only ever write to one master at a time (Master 1 by default), then you shouldn't have an issue.

*shouldn't doesn't mean you won't, though.

Best Answer

Related Solutions

Mysql – How to resolve the master server shut down/unavailability in thesql with master – slave replication

Mysql – Mix of Cyclic and Chained MySQL replication – What do you do with “log-slave-updates”..

Related Question