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. The timestamped subdirectories make it simpler to establish retention periods, for example by removing or archiving backup data past a certain age. This option is NOT set on default.

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.

With the --incremental-base option, as part of each incremental backup command, you specify the directory containing the previous backup. To make the directory names predictable, you might prefer to leave out the --with-timestamp option and instead generate a sequence of directory names as part of your backup script.

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?

Creating a compressed backup can save you considerable storage space and reduce I/O significantly. And with the LZ4 compression method (introduced since release 3.10), the overhead for processing compression is quite low. In cases where database backups are moving from a faster disk system where the active database files sit to a possibly slower storage, compression will often significantly lower the overall backup time. It can result in reduced restoration time as well. In general, we recommend LZ4 compression over no compression for most users, as LZ4-based backups often finish in a shorter time period. However, test out MySQL Enterprise Backup within your environment to determine what is the most efficient approach.