Table of Contents [+/-]
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 to perform this part of the
.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,
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 --user=dba --password=xyz --compress /etc/my.cnf /backups
--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
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
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.
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.)
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
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,
run to completion and
UNLOCKs the tables.
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:
Do not run long
SELECT or other SQL
queries at the time of the backup run.
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
completed successfully, either by verifying that the
mysqlbackup command returned exit code 0,
or by observing that
printed the text “mysqlbackup completed OK!”.
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
ibbackup Commands” for restrictions on backups at
the same time as DDL operations.