Mysql – Best backup strategy for MySQL (MariaDB) replication set

mariadbMySQLreplication

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.

Best Answer

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.

Related Question