3

My main concern is getting a proper backup, even more so than a temporary outage. The data is golden. I've been doing reading through the documentation and a lot of the backup strategies seem to be.

  • Setup master-slave replica set.
  • When you want to back up, stop replication on the slave to basically "freeze" it.
  • Run your mysqldump or w\e you choose.
  • Restart replication and the slave will eventually catch up.

That's all well and good, but is there a theoretical off-chance that the slave could become corrupt (perhaps replication didn't happen right, or the slave disconnected from the replica set)? If so, I will unknowingly be taking corrupted or stale backups. What is the best strategy to avoid this? The only thing I could think of is:

  • Stop the database service completely on master and slave. Pull them out of the replica set.
  • Edit PHP config files to point to slave server.
  • Start the slave (essentially the new master) as to avoid downtime during backup.
  • Start the old master isolated and run your mysqldump
  • Stop both servers and somehow sync them back up to a consistent state (in case any writes happened on the old slave while the old master was backing up)
  • Fix your conf files, start the old master as the master and your old slave as the slave.

This seems very convoluted, is there a better solution? I don't need multi-master. The reads & writes will happen on one server. The slave is just for failover purposes.

2

Replication status and lag are vital monitors you should take care of. Before start a backup you must know if your slave goes well.

A simple show slave status will show you all needed infos:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.30.40.61
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.021934
          Read_Master_Log_Pos: 205924047
               Relay_Log_File: relay-bin.004199
                Relay_Log_Pos: 205924192
        Relay_Master_Log_File: mysql-bin.021934
             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: 205924047
              Relay_Log_Space: 205924384
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
1 row in set (0.00 sec)

The mains "counters" are Slave_IO_Running and Slave_SQL_Running for replication status and Seconds_Behind_Master for lag (ideally at 0 second).

If you have a slave dedicated to backups (that is a good practice), I recommend you to make a binary copy of your datadir instead (or in addition) of your mysqldump. The restore will be much more easier and quick. However mysqldump is good if you want to restore a partial backup (especially InnoDB tables) or restore a clean shrinked dataset.

If your are afraid by corrumption or delta between Master and Slaves you can use the Percona tool pt-table-checksum (available in the Percona Toolkit) that "Verify MySQL replication integrity" easily.

Max.

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.