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”.
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
[mysqlbackup] section of the MySQL
configuration file that you pass to the
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.
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
--with-timestamp option), either specify a
new unique directory name for each backup job, or specify the
--force option to overwrite existing
--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.
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”.
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.