NetVault Plug-in for MySQL 11.1 - User Guide

Backing up data: an overview

Before completing a backup, review the information in the following topics:

All characters except alphanumeric characters and underscores are considered special characters. If your environment uses databases whose names contain special characters, such as hyphens, be aware of the following limitations:

If you intend to use the MySQL Enterprise Backup option, review the following guidelines and information:

Defining a backup strategy

When defining a MySQL backup strategy, answer the following questions:

Answering these questions helps you define the type and frequency of backups that should be implemented.

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:

Understanding how these backups differ is the first step in selecting a suitable backup sequence that matches the data protection requirements for each MySQL Instance.

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 ensures that Binary Logs are purged after a Full or Incremental Backup. This option is enabled by default when the plug-in is used with a standard MySQL Server configuration, Enable MySQL Replication is disabled, and Enable Point-In-Time Recovery is enabled. It is disabled when the plug-in is connected to a cluster; you must manage the purging of the binary logs outside of the plug-in.

IMPORTANT: In a mixed environment, where a NetVault Backup Server manages both clustered and standard MySQL Servers, do not re-use a Backup Options Set — created for a standard MySQL Server — for a MySQL-based cluster.

If you do not select the Purge Binary Logs… option, the plug-in tracks the Last Log Backed Up in its configuration file; 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.

An Incremental Backup backs up the transaction logs that were generated since the last Full or Incremental Backup, followed by purging the Binary Logs. Because the Binary Logs are instanced-based, the transaction logs for every database are backed up and purged as a unit.

Incremental Backups are essential in reducing data loss after a media failure or data corruption. You can use Incremental Backups to restore to a time before and after a data corruption, such as incorrect update or dropped table. Unlike Full Backups, Incremental Backups do not require read-only access during the backup.

MySQL Incremental Backups require that you start the MySQL Instance 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, see 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 tracks 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. The size and duration increase 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 includes the Binary Logs generated since the Full Backup on Sunday, while Tuesday’s Differential includes the Binary Logs generated on Monday as well as those logs generated on Tuesday. Wednesday’s Differential includes the Binary Logs for Monday, Tuesday, and Wednesday, and so on.

Similar to an Incremental Backup, a Differential can also be used to reduce data loss after a media failure or data corruption, with the ability to restore to a time before and after the failure or corruption. Unlike a Full Backup, a Differential does not require read-only access during the backup.

Differential Backups require that you start the MySQL Instance 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, see the Binary Log section in the MySQL Reference Guide.

Because Incremental Backups purge the Binary Logs after they are backed up, subsequent Incrementals are quicker because only the Binary Logs that have been created since the last Incremental Backup are backed up. However, restore sequences that use Incremental Backups require that every Incremental taken between the Full Backup and the point of failure must be restored in succession. This process can lead to longer restores due to the increased DBA intervention needed to initiate the multiple restore jobs.

Because Differential Backups do not purge the Binary Logs after they are backed up, each subsequent Differential Backup takes longer because all the Binary Logs since the last Full Backup are included in the backup. Nevertheless, restore sequences that use Differential Backups require that only one Differential Backup be restored after the Full Backup is restored. This process results in quicker restores because less DBA intervention is required during the restore process.

Sometimes a backup must be taken for a special purpose and should not affect the overall backup and restore procedures for a complete database. For example, backups can be a source for a test environment or as an initial synchronization for a replication slave instance. Individual Database/Table Copy Only Backups are designed for these special purposes, in that they allow you to “copy” a MySQL environment. “Copy Only” backups are independent of an established sequence of backups and do not affect the recoverability of Full, Incremental, or Differential Backups. However, they cannot be used as a replacement for a Full Backup.

Reviewing the backup types for MySQL Enterprise Backup

For the MySQL Enterprise Backup option, the plug-in runs 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.

For an InnoDB table, only data that changed since the last Full or Incremental Backup is backed up. For a non-InnoDB table, the entire table is backed up if anything has changed in the table since the last Full or Incremental Backup.