Topics | How To | Related Topics
Plan your backup jobs for this agent by reviewing the following information:
This agent supports the following backup types:
The state of the databases must be online to ensure successful data protection operations.
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.
MySQL full backups are database backups and does not include the traditional logs. For detailed information on full backups, see Full 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. Binary log must be enabled to perform transaction log backup.
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 previous transaction log backup.
Add the following under [mysqld] section in MySQL Configuration file (my.cnf for UNIX and my.ini for WINDOWS) to enable the Binary Logs:
[mysqld]
log-bin=mysql-bin.log
Restart the MySQL server for the changes to take effect. After restarting the MySQL Server, you can verify binary logging using the following query:
mysql > show variables like “%log_bin%”.
If it is enabled then the value of ‘log_bin’ will be ON otherwise the value will be OFF.
The binary/transaction logs are important for MySQL iDataAgent. However, they are not mandatory. The use of transaction log backups makes point in time recovery possible. You can restore to any point in time within the transaction log. If you use only full and backups, you will be able to restore to the time of the backup, but not to a point in time between backups. It is your choice to enable (ON) or disable (OFF) the transaction logs.
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 previous 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.
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.
It is possible to log performance counters for successful backups about time taken and bytes read/write for each backup and the time taken for buffer allocation [MySQLBackup.log (Windows) and MySQLBackupChild.log (Unix)]. The counters can be enabled by setting the following Registry Key:
The Performance Counters appear as follows:
3900 4b0 03/22 11:53:42 48 ::MySqlBackupDb::DoBackupMySql() - Performance counter enabled from Registry
3900 4b0 03/22 12:02:05 48 ::MySqlBackupDb::DoBackupMySql() - Performace for Database auto_4 is Bytes= 9406567384, Time= 190.661203 Sec(s), Average Throughput= 165.413699 GB/h
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.
It is mandatory that you backup the MySQL database every time 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.
MySQL dump uses the default value of max_allowed_packet variable while creating dump from MySQL server.
Before performing any backup procedures for this agent, review the following information:
When you backup a default subclient, if some of the auto discovered databases are removed/missing from the MySQL server, the backup job for the default subclient still gets completed successfully. However, if manually discovered databases of the default or user-defined subclients are removed/missing from the MySQL server, then the specific backup job will complete with errors.
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.