Chapter 4 mysqlbackup Command Reference

Table of Contents

4.1 mysqlbackup Command-Line Options
4.1.1 Subcommands
4.1.2 Standard Options
4.1.3 Connection Options
4.1.4 Server Repository Options
4.1.5 Backup Repository Options
4.1.6 Metadata Options
4.1.7 Compression Options
4.1.8 Incremental Backup Options
4.1.9 Partial Backup Options
4.1.10 Single-File Backup Options
4.1.11 Performance / Scalability / Capacity Options
4.1.12 Message Logging Options
4.1.13 Progress Report Options
4.1.14 Options for Special Backup Types
4.2 Configuration Files and Parameters
4.2.1 Source Repository Parameters
4.2.2 Backup Repository Parameters
4.2.3 Other Parameters

The mysqlbackup command is an easy-to-use tool for all backup and restore operations. During backup operations, mysqlbackup backs up:

In addition to creating backups, mysqlbackup can pack and unpack backup data, apply to the backup data any changes to InnoDB tables that occurred during the backup operation, and restore data, index, and log files back to their original locations.

Sample command line arguments to start mysqlbackup are:

# Information about data files can be retrieved through the database connection.
# Specify connection options on the command line.
mysqlbackup --user=dba --password --port=3306 \
  --with-timestamp --backup-dir=/export/backups \
  backup

# Or we can include the above options in the configuration file
# under [mysqlbackup], and just specify the configuration file
# and the 'backup' operation.
mysqlbackup --defaults-file=/usr/local/mysql/my.cnf backup

# Or we can specify the configuration file as above, but
# override some of those options on the command line.
mysqlbackup --defaults-file=/usr/local/mysql/my.cnf \
  --compress --user=backupadmin --password --port=18080 \
  backup

The --user and the --password you specify are used to connect to the MySQL server. This MySQL user must have certain privileges in the MySQL server, as described in Section 3.1.2, “Grant MySQL Privileges to Backup Administrator”.

The --with-timestamp option places the backup in a subdirectory created under the directory you specified above. The name of the backup subdirectory is formed from the date and the clock time of the backup run.

For the meanings of other command-line options, see Section 4.1, “mysqlbackup Command-Line Options”. For information about configuration parameters, see Section 4.2, “Configuration Files and Parameters”.

Make sure that the user or the cron job running mysqlbackup has the rights to copy files from the MySQL database directories to the backup directory.

Make sure that your connection timeouts are long enough so that the command can keep the connection to the server open for the duration of the backup run. mysqlbackup pings the server after copying each database to keep the connection alive.

IMPORTANT:
  • Although the mysqlbackup command backs up InnoDB tables without interrupting database use, the final stage that copies non-InnoDB files (such as MyISAM tables and .frm files) temporarily puts the database into a read-only state, using the statement FLUSH TABLES WITH READ LOCK. For best backup performance and minimal impact on database processing:

    1. Do not run long SELECT queries or other SQL statements at the time of the backup run.

    2. Keep your MyISAM tables relatively small and primarily for read-only or read-mostly work.

    Then the locked phase at the end of a mysqlbackup run is short (maybe a few seconds), and does not disturb the normal processing of mysqld much. If the preceding conditions are not met in your database application, use the --only-innodb or --only-innodb-with-frm option to back up only InnoDB tables, or use the --no-locking option to back up non-InnoDB files. Note that MyISAM, .frm, and other files copied under the --no-locking setting cannot be guaranteed to be consistent, if they are updated during this final phase of the backup.

  • For a large database, a backup run might take a long time. Always check that mysqlbackup has completed successfully, either by verifying that the mysqlbackup command returned exit code 0, or by observing that mysqlbackup has printed the text mysqlbackup completed OK!.

  • The mysqlbackup command is not the same as the former MySQL Backup open source project from the MySQL 6.0 source tree. The MySQL Enterprise Backup product supersedes the MySQL Backup initiative.

  • Schedule backups during periods when no DDL operations involving tables are running. See Section A.1, “Limitations of MySQL Enterprise Backup” for restrictions on backups at the same time as DDL operations.