Most backup strategies start with a complete backup of the MySQL server, from which you can restore all databases and tables. After you have created a full backup, you might perform incremental backups (which are smaller and faster) for the next several backup tasks. You then make a full backup periodically to begin the cycle again.
For sample commands for making a full backup, see Section 4.2.2, “Backing Up an Entire MySQL Instance”.
This section outlines some of the things to consider when deciding on a strategy for creating full backups. As we shall see, factors like speed, capacity, and convenience are all relevant for your decisions.
For clarity, the examples in this manual often show some of the
command-line options that are used with the
mysqlbackup commands. For convenience and
consistency, you can include those options that remain unchanged
for most backup jobs into the
section of the MySQL configuration file that you supply to
also picks up the options from the
section if they are present there. Putting the options into a
configuration file can simplify backup administration for you:
for example, putting port information into a configuration file,
you can avoid the need to edit your backup scripts each time the
database instance switches to a different port. See
Chapter 15, Configuration Files and Parameters for details about the
use of configuration files.
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, allowing easy
removal and archiving of backup data that has passed a certain
If you do use a single backup directory (that is, if you omit
--with-timestamp option), either specify a
new unique directory name for each backup job, or specify the
--force option to overwrite existing
For incremental backups that uses the
--incremental-base option to specify the
directory containing the previous backup, in order to make the
directory names predictable, you might prefer to not use the
--with-timestamp option and generate a sequence
of directory names with your backup script instead .
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 want to run a full backup each time. However, you can usually save time and storage space by running periodic full backups and then several incremental backups in between them, as described in Section 4.3.2, “Making a Differential or Incremental Backup”.
Creating a compressed backup can save you considerable storage space and reduce I/O usage 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.