Documentation Home
MySQL Enterprise Backup 4.0 User's Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.2Mb
PDF (A4) - 1.2Mb
HTML Download (TGZ) - 181.1Kb
HTML Download (Zip) - 207.0Kb

4.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 other relevant files and copied or processed with the MySQL instance locked. The redo logs, undo logs, and the system tablespace 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 15.10, “Performance / Scalability / Capacity Options”. If, as expected, the list of inactive tables identified by the optimistic options do not change during the backup (or, even if it changes by a small percentage), most users will find that the overall backup time is reduced significantly compared to an ordinary backup, as the size of the redo log data to be backed up will be far smaller. Additionally, restore time for the backup will also be reduced, as the apply-log operation will be much faster because of the smaller redo log. However, if it turns out that the list of inactive tables identified changed by a significant portion 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.


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 4.25 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-to-image

Example 4.26 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-to-image

Example 4.27 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 4.28 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
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  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