Skip navigation links
7.1 mysqlbackup Command-Line Options »
Section Navigation      [Toggle]

Chapter 7. mysqlbackup Command Reference

Table of Contents     [+/-]

7.1. mysqlbackup Command-Line Options
7.2. Example: Backing Up InnoDB and MyISAM Tables
7.3. Example: Verifying a Backup
7.4. Example: Restoring a Database at its Original Location
7.5. Specifying MySQL Connection Settings for mysqlbackup
7.6. Example: Setting MySQL Privileges for mysqlbackup
7.7. Example: Making an Incremental Backup of InnoDB and MyISAM tables

The mysqlbackup command is an easy-to-use tool for making a more complete backup than by using just the ibbackup command. The mysqlbackup command backs up:

  • All the same InnoDB tables, indexes, and so on as ibbackup. (mysqlbackup runs ibbackup to perform this part of the backup.)

  • MyISAM tables.

  • By default, all files in the data directory are included in the backup. If the --only-known-file-types option is specified, the backup includes additional files with only these file extensions:

    • .ARM: Archive storage engine metadata

    • .ARZ: Archive storage engine data

    • .FRM: table definitions

    • .MRG: Merge storage engine references to other tables

    • .MYD: MyISAM data

    • .MYI: MyISAM indexes

    • .OPT: database configuration information

    • .PAR: partition definitions

    • .TRG: trigger parameters

    • .TRN: trigger namespace information

In addition to creating backups, mysqlbackup can prepare a backup for starting a MySQL server on the backup, and it can copy data, index, and log files from backup directory back to their original locations.

mysqlbackup is a command that you can use to take an online backup of your InnoDB tables, and a snapshot of your MyISAM tables which correspond to the same binlog position as the backup of InnoDB tables. It also backs up the .frm files of the tables.

A sample command line to start mysqlbackup is: 

$ mysqlbackup --user=dba --password=xyz --compress /etc/my.cnf /backups

The --user and the --password you specify are used to connect to the MySQL server. This MySQL user must have enough rights in the MySQL server to execute FLUSH TABLES WITH READ LOCK and to create a dummy marker table ibbackup_binlog_marker in the mysql system database in the server (see Section 7.6, “Example: Setting MySQL Privileges for mysqlbackup” for details on the required MySQL privileges). In a backup run, mysqlbackup places the backup in a subdirectory it creates under the directory /backups you specified above. The name of the backup subdirectory is formed from the date and the clock time of the backup run.

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.

When mysqlbackup is run, it first tests a connection to the MySQL server. Then, it calls ibbackup and takes an online backup of InnoDB tables. (This phase does not disturb normal database processing.) When the ibbackup run has almost completed, mysqlbackup executes the SQL command FLUSH TABLES WITH READ LOCK and then copies the MyISAM tables and .frm files to the backup directory. If you do not run long SELECT or other queries in the database at this time, and your MyISAM tables are small, the locked phase only lasts a couple of seconds. Otherwise, the whole database, including InnoDB type tables, can be locked for quite a while. After this, mysqlbackup lets ibbackup run to completion and UNLOCKs the tables.

IMPORTANT:

  • Although the mysqlbackup command backs up InnoDB tables (ideally, the bulk of your data) without interrupting database use, the final stage that copies non-InnoDB files does temporarily put the database into a read-only state. For best backup performance and minimal impact on database processing:

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

    2. Keep your MyISAM tables relatively small.

    Then the locked phase at the end of an mysqlbackup run is short (maybe a few seconds), and does not disturb the normal processing of mysqld much. If the above two conditions are not met in your database application, use the plain ibbackup binary to take the backups.

  • For a large database, a backup run may 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 mysqlbackup and ibbackup Commands” for restrictions on backups at the same time as DDL operations.

Copyright © 2003, 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices