Backup - MySQL

Topics | How To | Related Topics

Overview

Supported Backup Types

MySQL Backup Jobs

Backup Considerations

Advanced Backup Options

Overview

Plan your backup jobs for this agent by reviewing the following information:

For an overview of backup jobs, see Backup Data.
For a list of supported data types for this agent, see Supported Data Types.
For information on subclients, see Subclients. For information on configuring subclients for this agent, see Subclients - MySQL.

Supported Backup Types

This agent supports the following backup types:

Full Backups
Transaction Log Backups
Do Not Truncate Log

This agent has the following unique functionality. The MySQL iDataAgent backs up very specific sets of data depending upon what type of operation you choose. Review the following information to understand exactly what will be backed up when you perform a backup job at the indicated level.

Transaction Log Backups

A transaction log backup is a backup of the transaction log of the database. This backup captures the transaction log which contains a record of all transactions whether the transaction was committed or not. Transaction log backups are consistent to the start time of the backup.

The use of transaction log backups make point in time recovery possible. You can restore to any point in time within the transaction log. If you use only full and differential backups, you will be able to restore to the time of the backup, but not to a point in time between backups.

A transaction log backup is similar to a traditional incremental backup you might perform on a file system because the transaction log backup contains only the new changes since the full or another transaction log 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 a transaction log backup. If there are dirty pages, any completed transactions will need to be rolled forward if a transaction log restore is performed. Any transactions that are not completed at the time a transaction log backup is performed are rolled back during a restore involving a transaction log backup.

Backing up the Transaction Log of a Damaged Database (Do Not Truncate Log)

In the scenario of a database failure where it is unacceptable to lose any data, you will want to restore to the point of failure. To do so means you will need to capture all transaction log events that occurred after the last backup job was run.

Mysql Database Backup

The mysql database is a system database that gets created when the MySQL Server application is installed. It contains all the meta data information, such as database access permissions, user information etc., that is used to manage the MySQL Server. Hence it is important that you take frequent backups of this database.

You can backup the MySQL database using the appropriate File System iDataAgent. It is mandatory that you backup the MySQL database everytime a new user is created or whenever this database is modified. These backups can later be used during disaster recovery situations when you need to restore the MySQL database.

Backup Considerations

Before performing any backup procedures for this agent, review the following information:

An Archive Log Index File path is created by default when MySQL Server Log_bin is ON. You can create a path or use the default path while configuring an Instance for MySQL iDataAgent. When the Log_bin is OFF, then it is mandatory to provide at least a dummy Archive Log Index File path when you configure an Instance for MySQL iDataAgent.
When you add new databases or add new content to the user defined subclients that are already created for MySQL iDataAgent and run a log backup, the system will detect the newly added databases or content, updates the Index, and performs a data backup after the log backup.
For default Subclient, you must ensure that the Enable Auto Discovery option in the Instance General Properties dialog box is selected. This option is required to update the default subclient with the newly added database(s).
Make sure to perform a full backup before running an incremental/differential backup or restore.
If you perform a backup of the default subclient without changing its default properties, the backup would include all the databases for the MySQL server instance, including any new objects that may have been created. However, if you have created subclients for database, then the default subclient will exclude those databases in other subclients during the backup operation.
If you have created subclients for a database, you can create a complete backup of the database by backing up these subclients. If you have created a group of selected subclients for the database, you must back up all of these subclients to create a complete backup. If you have created separate subclients for the log files, and if these subclients are not included with the subclients defined for databases or subsets, then the subclients for these files must also be backed up to create a complete backup.

Multiple streams are supported for data protection and recovery operations with this agent; however, before utilizing multiple streams, you should understand how their use affects operations. For more information, see Streams.
Backup and restore operations do not support non-English database names.
When you backup two subclients with different data storage policy but same log storage policy, the data backup phase will run simultaneously for both the subclinets. However, during the log backup phase, you can run only one log backup at a time. The log backup for the second subclient will wait till the first log backup operation is completed.
Prior to running a log backup of MySQL databases, ensure that the logs are not pruned by the MySQL Server automatically. To do this, set the expire_log_days parameter to 0 on the MySQL server.