Most backup strategies start with a complete backup of the MySQL server, from which you can restore all databases and tables. After you do one full backup, you might do incremental backups (which are smaller and faster) for the next several backup jobs. Periodically, you then do another full backup to begin the cycle again.

This section outlines some of the considerations for making this most basic kind of backup. Because a full backup can take longer and produce larger backup files then other kinds of backups, your decisions about speed, capacity, and convenience are especially important for this part of the backup strategy.

For examples showing the commands to make a full backup, see Section 3.2.1, “Backing Up an Entire MySQL Instance”.

Options on Command Line or in Configuration File?

For clarity, the examples in this manual typically show command-line options to demonstrate connection parameters and other information that might be the same for each backup job. For convenience and consistency, you can include these options in the [mysqlbackup] section of the MySQL configuration file that you pass to the mysqlbackup command; mysqlbackup also picks them up from the [mysqld] section if they are present. For example, relying on the port information in the configuration file avoids the need to edit your backup scripts if the database instance switches to a different port.

Output in Single Directory or Timestamped Subdirectories?

For convenience, the --with-timestamp option creates uniquely named subdirectories under the backup directory, to hold the output from each backup job. This option is not the default, only for backward compatibility for users who relied on the behavior of the former ibbackup command, which wrote its output to the top-level backup directory. The timestamped subdirectories make it simpler to establish retention periods, for example by removing or archiving backup data past a certain age. If you do use a single backup directory (that is, if you omit the --with-timestamp option), either specify a new unique directory name for each backup job, or specify the --force option to overwrite existing backup files.

Always Full Backup, or Full Backup plus Incremental Backups?

If your InnoDB data volume is small, or if your database is so busy that a high percentage of data changes between backups, you might run a full backup each time. Typically, you can save time and storage space by running periodic full backups, and in between running several incremental backups, as described in Section 3.3.2, “Making an Incremental Backup”.

Use Compression or Not?

Doing a compressed backup can save considerable space, allowing you to keep more sets of backup data on a single server. The tradeoff is that you need extra storage space (to hold both compressed and uncompressed data) while preparing the backup to be restored, and in an emergency you might find you do not have spare storage space or the time to uncompress a huge backup. For that reason, compression is more practical for data that is not urgently needed, or while the backup is in transit to another server, where it will be uncompressed for the apply-log phase.