Documentation Home
MySQL Enterprise Backup 3.11 User's Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.0Mb
PDF (A4) - 1.0Mb
HTML Download (TGZ) - 162.0Kb
HTML Download (Zip) - 188.0Kb


3.3.6 Making an Optimistic Backup

Optimistic backup is a feature introduced in MySQL Enterprise Backup 3.11 for improving performance for backing up and restoring huge databases in which only a small number of tables are modified frequently.

During a hot backup of a huge database (say, in the order of terabytes), huge redo log files could be generated on the server when the backup is in progress. As the redo log files grow faster than they can be processed by mysqlbackup, the backup operation can actually fail when mysqlbackup cannot catch up with the redo log cycles and LSNs get overwritten by the server before they are read by mysqlbackup. Moreover, the apply-log step for preparing a backup for restoration can take a very long time as mysqlbackup has huge ibbackup_logfile files (created from the big redo log files) to apply to the backup. The problems are intensified when the I/O resources available for reading and writing the redo logs are scarce during the backup and restoration processes.

Optimistic backup relieves the problems by dividing the backup process into two internal phases, which are transparent to the users:

  1. Optimistic phase: In this first phase, tables that are unlikely to be modified during the backup process (referred to as the inactive tables below, identified by the user with the optimistic-time option or, by exclusion, with the optimistic-busy-tables option) are backed up without locking the MySQL instance. And because those tables are not expected to be changed before the backup is finished, redo logs, undo logs, and system table spaces are not backed up by mysqlbackup in this phase.

  2. Normal phase: In this second phase, tables that are not backed up in the first phase (referred to as the busy tables below) are being backed up in a manner similar to how they are processed in an ordinary backup: the InnoDB files are copied first, and then the other relevant files and copied or processed with the MySQL instance locked. Also, if it turns out that some of the inactive tables have actually been modified after they were backed up in the optimistic phase, they are copied again in the normal phase. The redo logs, undo logs, and the system table space are also backed up in this phase.

An optimistic backup occurs whenever the optimistic-time or optimistic-busy-tables option is used. For how to use the options, see detailed descriptions for them in Section 5.1.11, “Performance / Scalability / Capacity Options”. If, as expected, the inactive tables identified by the options are not changed a lot during the backup, the overall backup time (time for the backup operation plus time for the apply-log operation) can be reduced significantly comparing with an ordinary backup because the apply-log operation will be much faster. However, if it turns out that the inactive tables identified are modified a lot during the backup process, benefits of performing an optimistic back up will become limited and, in the worst case, an optimistic backup might actually take longer to perform and, for a single-file backup, the size of the backup will be larger when comparing with an ordinary backup. Therefore, users should be careful in identifying which tables are inactive and which are busy when trying to perform an optimistic backup.

Note

An optimistic backup cannot be performed for an incremental backup or a backup using transportable tablespaces (TTS).

The following examples illustrate how to make an optimistic backup.

Example 3.19 Optimistic Backup Using the Option optimistic-time=YYMMDDHHMMSS

In this example, tables that have been modified since the noon of May 16, 2011 are treated as busy tables and backed up in the normal phase of an optimistic backup, and all other tables are backed up in the optimistic phase:

mysqlbackup --defaults-file=/etc/my.cnf --optimistic-time=110516120000 backup

Example 3.20 Optimistic Backup Using the Option optimistic-time=now

In this example, all tables are treated as inactive tables and backed up in the optimistic phase of an optimistic backup:

mysqlbackup --defaults-file=/etc/my.cnf --optimistic-time=now backup

Example 3.21 Optimistic Backup Using the optimistic-busy-tables Option

In this example, tables in mydatabase that are prefixed by mytables- in their names are treated as busy tables and backed up in the normal phase of an optimistic backup, and all other tables are backed up in the optimistic phase:

mysqlbackup --defaults-file=/etc/my.cnf --optimistic-busy-tables='^mydatabase\.mytables-.*'  backup

When you use both the optimistic-time and optimistic-busy-tables options and they come into conflict on determining which tables are to be busy tables, optimistic-busy-tables takes precedence over optimistic-time. For example:

Example 3.22 Optimistic and Partial Backup Using both the optimistic-busy-tables and optimistic-time Options

In this example, tables in mydatabase that are prefixed by mytables- in their names are treated as busy tables and backed up in the normal phase, even if they have not been modified since May 16, 2010, the time specified by optimistic-time:

mysqlbackup --defaults-file=/etc/my.cnf --optimistic-busy-tables='^mydatabase\.mytables-.*'  \
--optimistic-time=100516 backup



User Comments
  Posted by Revathi Rangachari on April 16, 2015
A working example of optimistic backup with two tables - in RHEL 6.3, mysql 5.6.18 MEB 3.12:

./mysqlbackup --only-known-file-types --host='localhost' --user=user --password=pass --socket='/var/lib/mysql/mysql.sock' --with-timestamp --slave-info --datadir=/var/lib/mysql/datadir --backup-dir=/meb/bkp --optimistic-busy-tables='mydatabase.mytable1, mytable2' --optimistic-time=now backup-and-apply-log

Thanks Daniel, yes there is a typo there (near --socket=) which I added now. I wanted to try the command without any regex and see if backup completed successfully.
  Posted by Daniel So on March 16, 2017
The above example is wrong. There's a missing quote for --socket=, and the regex is also incorrect. It should look like this:

./mysqlbackup --only-known-file-types --host='localhost' --user=user --password=pass --socket='/var/lib/mysql/mysql.sock' --with-timestamp --slave-info --datadir=/var/lib/mysql/datadir --backup-dir=/meb/bkp --optimistic-busy-tables='mydatabase\.mytable[12]' --optimistic-time=now backup-and-apply-log
Sign Up Login You must be logged in to post a comment.