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”.
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