Advanced Backup for MySQL

Full Backups

Full backups provide the most comprehensive protection of data.

Backups for any client start with a full backup. The full backup becomes a baseline to which subsequent backup types are applied. For example, a full backup must be performed before a transaction log backup can be initiated.

Procedure

1. From the CommCell Browser, navigate to Client Computers > <Client> > MySQL > <Instance>.

2. Right-click an <Subclient>, and then click Backup.

3. In the Backup Options for Subclient dialog box, select Full as the backup type.

4. In the Job Initiation area, select Immediate to run the backup right now.

5. Click OK.

   You can track the progress of the job from the Job Controller.

   When the backup is complete, the Job Controller displays Completed.

Incremental Backups

An incremental backup captures the transaction log that contains a record of all committed or uncommitted transactions. Incremental backups are consistent with the start time of the backup.

Incremental backups make point-in-time recovery possible. This is useful in the case of a database failure where it is unacceptable to lose any data, and you want to restore to the point of failure. If you use only full backups, you can restore to the time of the backup, but not to a point-in-time between backups.

A MySQL incremental backup is similar to a traditional incremental backup that you might perform on a file system because the incremental backup contains only the new changes since the full or another incremental backup.

Each time a transaction log is backed up, it is truncated to the exact time of the backup. No checkpoint is issued at this time; therefore, dirty pages are not written to disk before or after an incremental backup. If there are dirty pages (pages that are changed and not yet added to the database files), any completed transactions must be rolled forward if a transaction log restore is performed. Any transactions not completed at the time an incremental backup is performed are rolled back during a restore involving an incremental backup.

Before You Begin

• Enable binary logging on the MySQL Server.

• Set the MySQL Server system variable expire_log_days to 0 to make sure that the logs are not pruned by the MySQL Server automatically.

• In case of row-based logging, statement-based logging, and mixed logging, follow specific syntaxes to insert statements in the logs. For more information about the syntaxes, see What are the syntaxes to insert statement in case of row-based logging, statement-based logging, and mixed logging?

  Note

  If the syntax for insert statements were not properly used (as mentioned in the link above), you might face issues while recovering data that was backed up using incremental backup operation. This happens due to a MySQL limitation.

Procedure

1. From the CommCell Browser, navigate to Client Computers | <Client> | MySQL | <Instance>.

2. Right-click a <Subclient>, and then click Backup.

3. In the Backup Options for Subclient dialog box, select Incremental as the backup type.

4. Optional: For a full instance XtraBackup, to back up data along with the logs, select the Data check box.

5. In the Job Initiation area, select Immediate to run the job now.

6. Click OK.

   You can track the progress of the job from the Job Controller. When the backup is complete, the Job Controller displays Completed.

Back Up Transaction Log of a Damaged Database (Do Not Truncate Logs)

By default, a full backup truncates and shrinks transaction logs automatically to save space. This might not be acceptable if you experience a database failure and you want to restore to the point of failure. In this case, you must capture all of the transaction log events that have occurred since the last backup was run.

This operation retains the active transaction log.

1. From the CommCell Browser, navigate to Client Computers | <Client> | MySQL | <Instance>.

2. Right-click a <Subclient>, and then click Backup.

3. In the Backup Options for Subclient dialog box, select Incremental as the backup type.

4. Select Do Not Truncate Log.

   When this option is selected, the transaction logs before the full or log only backup are not purged from the MySQL Server instance.

5. In the Job Initiation area, select Immediate to run the job now.

6. Click OK.

   You can track the progress of the job from the Job Controller. When the backup is complete, the Job Controller displays Completed.