8.6.3. Creating an External Backup

There are several considerations to take into account when you are using a tool other than Tungsten Replicator to take a backup. We have taken great care to build all of these into our tools. If the options provided do not meet your needs, take these factors into account when taking your own backup.

  • How big is your data set?

    The mysqldump tool is easy to use but will be very slow once your data gets too large. We find this happens around 1GB. The xtrabackup tool works on large data sets but requires more expertise. Choose a backup mechanism that is right for your data set.

  • Is all of your data in transaction-safe tables?

    If all of your data is transaction-safe then you will not need to do anything special. If not then you need to take care to lock tables as part of the backup. Both mysqldump and xtrabackup take care of this. If you are using other mechanisms you will need to look at stopping the replicator, stopping the database. If you are taking a backup of the master then you may need to stop all access to the database.

  • Are you taking a backup of the master?

    The Tungsten Replicator stores information in a schema to indicate the restart position for replication. On the master there can be a slight lag between this position and the actual position of the master. This is because the database must write the logs to disk before Tungsten Replicator can read them and update the current position in the schema.

    When taking a backup from the master, you must track the actual binary log position of the master and start replication from that point after restoring it. See Section 8.7.2, “Restoring an External Backup” for more details on how to do that. When using mysqldump use the --master-data=2 option. The xtrabackup tool will print the binary log position in the command output.

Using mysqldump can be a very simple way to take consistent backup. Be aware that it can cause locking on MyISAM tables so running it against your master will cause application delays. The example below shows the bare minimum for arguments you should provide:

shell> mysqldump --opt --single-transaction --all-databases --add-drop-database --master-data=2