Defining a backup strategy
|
• |
Do I want to use the MySQL Standard/Community or MySQL Enterprise Backup option? Even if you have both versions implemented in your environment, you can only use one strategy with the plug-in. You must use either the MEB-based method or the mysqldump-based method; you cannot use a mixture of the two. |
If you use the MEB-based option, the mysqlbackup command is executed once for all the database objects that you select for backup, and a
mysqlbackup output log is included in the job log. Backing up data involves two stages. In the first stage, all InnoDB tables are copied. In the second stage, all other types of tables are copied. In addition to providing support for hot backup of InnoDB tables, the MEB-based option supports improved backup performance.
Reviewing the backup types for MySQL Standard/Community
If you use the MySQL Standard/Community option, the plug-in uses
mysqldump to provide the following types of backup:
In a Full Backup for the MySQL Standard/Community option, the plug-in uses the
mysqldump utility to back up
every database included in the instance. Full Backups are the foundation of any backup strategy because they provide the starting point for almost every restore scenario. Full Backups generated with the plug-in can be used to restore an entire instance, individual or multiple databases, and individual or multiple tables.
The Purge Binary Logs after Full or Incremental Backup option, which is enabled by default when the
Enable MySQL Replication is disabled and
Enable Point-In-Time Recovery is enabled, ensures that Binary Logs are purged after a Full or Incremental Backup.
If you do not select the Purge Binary Logs… option, the plug-in keeps track of the
Last Log Backed Up in its configuration file, and you can manually purge Binary Logs at your discretion. For example, if you are using a MySQL replication environment where you do not want to purge Binary Logs from the Master Instance until they have been replicated to the Slave Instance, you are responsible for manually purging the Binary Logs.
MySQL Incremental Backups require the MySQL Instance to be started with the “‑log-bin” option, which enables Binary Logging. This procedure is outlined in
Enabling the Binary Log on the MySQL Server (Standard/Community option only). For more information, refer to the Binary Log section in the
MySQL Reference Guide.
As described earlier, the Purge Binary Logs after Full or Incremental Backup option, ensures that Binary Logs are purged after a Full or Incremental Backup. If you do not use this option, the plug-in keeps track of the
Last Log Backed Up in its configuration file, and you can manually purge Binary Logs at your discretion.
A Differential Backup backs up the transaction logs that were generated since the last Full or Incremental Backup. However, the Binary Logs are not purged at the completion of the backup. Therefore, subsequent Differential Backups increase in size and in duration because each backup of this type includes the Binary Logs that were also included in the previous Differential Backup
as well as the Binary Logs that have been generated since the previous Differential Backup. For example, if a Full Backup was taken on Sunday with Differential Backups scheduled Monday through Saturday, Monday’s Differential will include the Binary Logs generated since the Full Backup on Sunday, while Tuesday’s Differential will include the Binary Logs generated on Monday as well as those generated on Tuesday. Wednesday’s Differential will include the Binary Logs for Monday, Tuesday, and Wednesday, and so on.
Differential Backups require the MySQL Instance to be started with the “‑log-bin” option, which enables Binary Logging. This procedure is outlined in
Enabling the Binary Log on the MySQL Server (Standard/Community option only). For more information, refer to the Binary Log section in the
MySQL Reference Guide.
Reviewing the backup types for MySQL Enterprise Backup
For the MySQL Enterprise Backup option, the plug-in executes the
mysqlbackup command once for all selected database objects to achieve the following types of backup: Full and Incremental.
In a Full Backup for the MySQL Enterprise Backup option, the plug-in uses the
mysqlbackup utility to back up
every selected database object included in the instance. Full Backups are the foundation of any backup strategy because they provide the starting point for almost every restore scenario. Full Backups generated with the plug-in can be used to restore an entire instance, individual or multiple databases, and individual or multiple tables.
Examples of backup sequences for MySQL Standard/Community
When business requirements guarantee data protection up to the previous day, but read-only access to the target MySQL Instance is only permissible at intermittent times (for example, after regular business, on only a weekly or bi-weekly basis), and backup time should be as fast as possible, Full Backups coupled with Incremental Backups is the best combination. For example, Full Backups are performed every Sunday night at 11:00 P.M., while Incremental Backups are performed Monday through Saturday at 11:00 P.M. Each Incremental Backup will include the Binary Logs that were generated since the previous night’s backup, whether it be the Sunday evening Full Backup or one of the Incremental Backups.
When business requirements guarantee data protection up to the previous day, but read-only access to the target MySQL Instance is only permissible at intermittent times (for example, after regular business hours, on only a weekly or bi-weekly basis), and restore time should be as fast as possible, Full Backups coupled with Differential Backups is the best combination. For example, Full Backups are performed every Sunday night at 11:00 P.M., while Differential Backups are performed Monday through Saturday at 11:00 P.M. Each Differential Backup will include the Binary Logs that were generated since the last Full Backup. As noted earlier, this requires more overall backup time.
