NetVault Plug-in for MySQL 12.0


	IMPORTANT: Quest strongly recommends that you omit special characters from database names. If a database name contains any of the following characters, the plug-in cannot restore it: $ ^ = @ # % + Because backup data is streamed from the MySQL Server directly to the NetVault Backup Media Manager, use of the preceding characters might be interpreted as a sequence command, which affects the integrity of the backup data.

Important notes for MySQL Standard/Community

If you intend to use the MySQL Standard/Community option, review the following guidelines and information:

•

All characters except alphanumeric characters and underscores are considered special characters

•

Using the MIXED Binary Logging Format

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:

•

All table types except MyISAM are backed up, even if the database name contains hyphens. These backups occur because the mysqldump command is always used for these table types.

•

If the database name contains hyphens, MyISAM tables are backed up if the MyISAM Backup Method is set to the Mysqldump option introduced in version 4.2. Performance of backups and restores might be negatively affected.

•

If the MyISAM Backup Method is set to use the default Lock & Copy Table Files option and the database name contains hyphens, MyISAM tables are not backed up. Backups are not generated because the plug-in bypasses MySQL commands and tries to copy the table files directly. The plug-in logs an error message indicating that the table file cannot be located, and then fails the backup job without creating a saveset.

With previous versions, the plug-in attempted to verify the existence of the database directory, logged a warning message when it failed to do so, and continued with backup of the next database. The backup completed with warnings and created a saveset that included all other databases.

If you want to maintain the original behavior and still use the Lock & Copy Table Files option for any reason, such as less-than-optimal performance impact when using the Mysqldump option, you can. To do so, manually set the ValidateDatabaseDirectory parameter to TRUE in the plug-in configuration file, “nvmysql.cfg,” as follows:

[MySql:ValidateDatabaseDirectory]

Value=TRUE

If you then decide to use the new behavior instead, you can change the parameter to FALSE or remove the parameter from the “nvmysql.cfg” file.

•

Restoring an Incremental or Differential Backup that includes SQL statements to create databases or database objects — for example, table, view, and so on — fails if any of the databases or database objects exist. To avoid this issue, run a Full Backup after you create or drop one or more databases or database objects. This step ensures that subsequent Incremental or Differential Backups do not include any CREATE or DROP SQL statements.

Using the MIXED Binary Logging Format

MySQL does not enforce the use of the USE statement when the MIXED Binary Logging Format is used. Therefore, Quest recommends that all database users and programs ensure that tables that are modified are in the database selected by USE, and that no cross-database updates are issued. If this guideline is not suitable for your environment, Quest recommends that you do not use the MIXED Binary Logging Format.


	IMPORTANT: Incremental and Differential Backup jobs complete with a warning if the MIXED Binary Logging Format is used.

If your environment uses the MIXED Binary Logging Format, it might prevent Binary Log entries from being replayed during a PIT Recovery. During recovery, the plug-in uses mysqlbinlog with the “‑‑database” option to replay only the entries related to the databases that you selected for the restore job. If “--database” is not used, all entries are replayed, which affects all databases. When the MIXED Binary Logging Format is used, entries are written in a way that might prevent mysqlbinlog with the “‑‑database” option from replaying some, or all, entries. For more information, see https://dev.mysql.com/doc/refman/5.7/en/mysqlbinlog.html#option_mysqlbinlog_database.

To ensure that the MIXED Binary Logging Format works correctly with the “--database” option, all transactions for specific updates to a database have to be issued under a USE statement that selects the database.

This same situation occurs if the Incremental or Differential Backups are not restored and mysqlbinlog applies the current Binary Log from the MySQL Server. This situation occurs because of how the Binary Log is written, not because of how the Binary Log is stored in the backup.


	IMPORTANT: Ensuring that tables that you modify belong to the database specified in the USE statement applies to transactions that are generated through the MySQL command prompt. It also applies to transactions that are generated by scripts, programs, and other applications that interact with the MySQL Server databases.

The following examples demonstrate different ways in which MIXED affects recovery behavior.

•

Example 1: In this example, a row of data is inserted into my_table of my_database. There is no USE statement, so the database in use is the default database, for example, mysql database. If binlog_format is set to MIXED, the following transaction is not replayed when mysqlbinlog applies the “‑-database my_database” option to the Binary Log.

-bash-$ mysql

mysql> insert into my_database.my_table (C1,C2) values(1,now());

Query OK, 1 row affected (0.01 sec)

•

Example 2: In this example, a row of data is inserted into my_table of my_database. There is a USE statement, but a different database is specified; that is, my_database is not selected in the USE statement. If binlog_format is set to MIXED, the following transaction is not replayed when mysqlbinlog applies the “‑-database my_database” option to the Binary Log.

-bash-$ mysql

mysql> use mysql

Database changed

mysql> insert into my_database.my_table (C1,C2) values(2,now());

Query OK, 1 row affected (0.04 sec)

•

Example 3: In this example, a row of data is inserted into my_table of my_database, and my_database is selected in a USE statement. If binlog_format is set to MIXED, the following transaction is replayed when mysqlbinlog applies the “‑-database my_database” option to the Binary Log.

-bash-$ mysql

mysql> use my_database

Database changed

mysql> insert into my_database.my_table (C1,C2) values(3,now());

Query OK, 1 row affected (0.04 sec)

•

Example 4: In this example, there are two insert queries. The first insert is done for my_database, which is different than the database selected in the USE statement. The second insert is done under the scope of a USE statement that selects my_database. If binlog_format is set to MIXED, the first insert is not replayed because my_database is not specified in the USE statement, but the second insert is replayed because my_database is specified in the USE statement.

-bash-$ mysql

mysql> use mysql

Database changed

mysql> insert into my_database.my_table (C1,C2) values(4,now());

Query OK, 1 row affected (0.01 sec)

mysql> use my_database

Database changed

mysql> insert into my_database.my_table (C1,C2) values(5,now());

Query OK, 1 row affected (0.04 sec)

Important notes for MySQL Enterprise Backup

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

•

MySQL recommends that you use InnoDB tables for critical data because the backup process is faster and the reliability and scalability features are significant. MySQL Enterprise Backup lets you back up various kinds of MySQL tables, and it is optimized for backing up InnoDB tables. This option performs a hot backup of all InnoDB tables. Because hot backups are performed while the database is running, the backup does not stop ongoing database operations. Also, any database changes made during the backup process are included. This behavior is important if your environment requires that the database remains online while also supporting its growth, which impacts the time required to complete a backup.

•

When you use this option, the MyISAM table and other non-InnoDB tables are backed up last, using a warm backup. In a warm backup, the database continues to run, but the tables are set to read-only access while the backup is completed.

•

If you want to ensure that the bulk of your data is backed up during the hot-backup phase, consider making InnoDB the default storage engine for new tables and converting existing tables to use the InnoDB storage engine. In MySQL Server 5.5 and later, InnoDB is the default.

•

An Incremental Backup is primarily intended for InnoDB tables and non-InnoDB tables that are read-only or updated infrequently. For non-InnoDB files, the entire file is included if it changed since the last backup.

•

When using the plug-in, all InnoDB tables in a MySQL instance are backed up if either of the following conditions is met:

•

Only tables are explicitly selected for backup, and none of the tables are of type or storage engine InnoDB.

Example: You have a MySQL Instance with two databases, DB1 and DB2. Each database contains two tables: DB1 has T1_InnoDB and T1_MyISAM and DB2 has T2_InnoDB and T2_MyISAM. If you back up T1_MyISAM and T2_MyISAM, T1_InnoDB and T2_InnoDB are also backed up. If you include one of the InnoDB tables, only that InnoDB table is backed up. If you select one of the databases, only the tables in the database are backed up.

•

Some or all databases are selected for backup and all associated InnoDB tables are excluded from the backup.

Example: You have a MySQL Instance with two databases, DB1 and DB2. Each database contains two tables: DB1 has T1_InnoDB and T1_MyISAM and DB2 has T2_InnoDB and T2_MyISAM. If you back up DB1 and DB2 and exclude T1_InnoDB and T2_InnoDB, T1_InnoDB and T2_InnoDB are also backed up. If you exclude only one of the two InnoDB tables, only the other InnoDB table is backed up.

This description reflects the current behavior of MySQL Enterprise Backup, mysqlbackup utility; this behavior might change in a future release, post-3.12, of MySQL.

•

In MySQL 5.6 and later, the innodb_file_per_table configuration option is enabled by default. Any InnoDB tables that are created with the innodb_file_per_table option disabled are stored in the InnoDB system tablespace; they cannot be omitted from the backup. If you must place an InnoDB table outside the tablespace, create it while the innodb_file_per_table option is enabled in MySQL. Each .ibd file contains the data and indexes of only one table.

Defining a backup strategy

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

•

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. 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 utility or applicable NetVault Backup script is run 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 supporting hot backup of InnoDB tables, the MEB-based option supports improved backup performance.

If you use the mysqldump-based option, the command is run once for each table, trigger, and stored procedure. Hot backups are not supported.

•

Understanding that read-only access across the entire instance is required during Full Backups, how often should full-backups be taken?

•

What is more important: quicker backups or quicker restores?

•

What is the maximum amount of acceptable data loss?

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

•

Reviewing the backup types for MySQL Standard/Community

•

Reviewing the backup types for MySQL Enterprise Backup

•

Examples of backup sequences for MySQL Standard/Community

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:

•

Full Backup

•

Incremental Backup

•

Differential Backup

•

Individual Database/Table Copy Only Backup

•

Entire Databases Copy 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.

Full Backups

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

Incremental Backups

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 the Binary Log. 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.

Differential Backups

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 and 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 and those logs generated on Tuesday. Wednesday’s Differential includes the Binary Logs for Monday, Tuesday, and Wednesday, and so on.

Differential Backups require that you start the MySQL Instance with the “‑log-bin” option, which enables the Binary Log. 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.

Incremental vs. Differential Backups

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

Individual Database/Table Copy Only Backups

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.

Entire Databases Copy Backups

As described for Individual Database/Table Copy Only Backups, the Entire Databases Copy Backup option is used only for special purposes as it creates a copy of the selected MySQL databases, including all the corresponding InnoDB tables of the selected databases. “Copy” 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.


	IMPORTANT: You can use this option only if all the tables for the selected databases are InnoDB tables.

Individual Database/Table Copy Only Backups vs. Entire Databases Copy Backups

For each selected database, even if only one table of the database is selected, the Entire Databases Copy Backup option backs up the entire database. This option lets you select individual databases for backup, but it does not let you select individual tables. In addition, this option only supports the backing up of InnoDB tables.

The Individual Database/Table Copy Only Backup option lets you select individual databases and individual tables, and you can include InnoDB and MyISAM tables in the backup. However, backups are usually completed faster for the Entire Databases Copy Backup option than for the Individual Database/Table Copy Only Backup option.

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, Incremental, and TTS.

Full Backups

In a Full Backup for the MySQL Enterprise Backup option, the plug-in uses the mysqlbackup utility or applicable NetVault Backup script 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.

Incremental Backups

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.

Transportable Tablespace (TTS) Backups

If you perform a TTS Backup, the plug-in issues a Full Backup and adds the “--use-tts” MySQL option.


	IMPORTANT: Quest strongly recommends that you only generate TTS Backups as standalone backups, separate from your backup plan. Because TTS Backups are partial backups, you cannot use them to replace or complement a Full or Incremental Backup strategy, nor can you use them for a disaster recovery operation.

If you intend to generate TTS Backups, be aware of the following limitations:

•

TTS Backups are only supported in MySQL Server 5.6 and later.

•

Only InnoDB tables are included in the backup.

•

Only tables that were created with the innodb_file_per_table option enabled are included in the backup.

•

Backup of a partitioned table fails if the partition was created in a shared tablespace.

•

The backup excludes the binary or relay log.

For more limitations on the use of the “--use-tts” option, see https://dev.mysql.com/doc/mysql-enterprise-backup/4.0/en/backup-partial-options.html.