- 3.3.4 Making a Partial Backup
- 3.3.4.1 Backing Up Data from Different Storage Engines
- 3.3.4.2 Backing Up Selected Databases
By default, all the files in the data directory are included in the backup, so the backup includes data from all MySQL storage engines, any third-party storage engines, and even any non-database files in that directory. This section explains options you can use to selectively back up or exclude data from particular storage engines.
The --only-known-file-types
option of the mysqlbackup command limits
the backup to only those files that represent known data
files from MySQL or its built-in storage engines, such as
.frm, .ibd,
.myd, and so on. (See the
full list of
extensions.) By default, the
mysqlbackup command backs up all file
extensions within the data directory, which could include
files produced by many different storage engines. Use this
option to omit the additional data files from other storage
engines from the backup, for performance or space reasons.
The --only-innodb option backs
up InnoDB tables only, skipping those of other storage
engines. You might use this option for some backup
operations based on the following considerations:
The InnoDB tables are backed up using the hot backup technique, which does not interfere with database processing.
The
--compressand--incrementaloptions offer benefits only for InnoDB data.In a busy production environment, InnoDB tables might represent the bulk of your important data because of the importance of high concurrency and crash recovery.
In MySQL 5.5 and higher, InnoDB is the default storage engine for new tables.
Example 3.1. Making an Uncompressed Backup of InnoDB Tables
In this example, the options file
/home/pekka/.my.cnf defines the MySQL
installation to back up. Running
mysqlbackup performs the first phase of
the process:
$ mysqlbackup --defaults-file=/home/pekka/.my.cnf --only-innodb backup
...many lines of output...
mysqlbackup: Scanned log up to lsn 32164666892.
mysqlbackup: Was able to parse the log up to lsn 32164666892.
mysqlbackup: Maximum page number for a log record 0
101208 15:33:11 mysqlbackup: Full backup completed!
The backup directory now contains a backup log file and copies of InnoDB data files.
Next Steps:
Make a note of the LSN value in the message at the end of both full and incremental backups, for example,
mysqlbackup: Was able to parse the log up to lsn. You specify this value when performing incremental backups of changes that occur after this full backup.LSN_numberApply the log to the uncompressed backup files, so that the full backup is ready to be restored at any time. You can move the backup data to a different server first, to avoid the CPU and I/O overhead of performing this operation on the database server.
After applying the log, periodically take incremental backups, which are much faster and smaller than a full backup like this.
Example 3.2. Making an Uncompressed Partial Backup of InnoDB Tables
In this example, we have configured MySQL so that some
InnoDB tables have their own tablespaces. We make a
partial backup including only those InnoDB tables in
test database whose name starts with
ib. The contents of the database
directory for test database are shown
below. The directory contains a MySQL description file
(.frm file) for each of the tables
(alex1, alex2,
alex3, blobt3,
ibstest0, ibstest09,
ibtest11a, ibtest11b,
ibtest11c, and
ibtest11d) in the database. Of these 10
tables six (alex1,
alex2, alex3,
blobt3, ibstest0,
ibstest09) are stored in per-table
datafiles (.ibd files).
$ ls /sqldata/mts/test
alex1.frm alex2.ibd blobt3.frm ibstest0.ibd ibtest11a.frm ibtest11d.frm
alex1.ibd alex3.frm blobt3.ibd ibtest09.frm ibtest11b.frm
alex2.frm alex3.ibd ibstest0.frm ibtest09.ibd ibtest11c.frm
We run the mysqlbackup with the
--include option:
$ mysqlbackup --defaults-file=/home/pekka/.my.cnf --include='test\.ib.*' --only-innodb backup
...many lines of output...
mysqlbackup: Scanned log up to lsn 2666737471.
mysqlbackup: Was able to parse the log up to lsn 2666737471.
mysqlbackup: Maximum page number for a log record 0
101208 17:17:45 mysqlbackup: Full backup completed!
The backup directory contains only backups of
ibstest and ibtest09
tables. Other InnoDB tables did not match the include
pattern test\.ib.*. Notice, however,
that the tables ibtest11a,
ibtest11b,
ibtest11c, ibtest11d
are in the backup even though they are not visible in the
directory shown below, because they are stored in the
system tablespace (ibdata1 file) which
is always included in the backup.
$ ls /sqldata-backup/test
ibstest0.ibd ibtest09.ibd
Example 3.3. Making a Compressed Partial Backup
We have configured MySQL so that every InnoDB table has
its own tablespace. We make a partial backup including
only those InnoDB tables whose name starts with
alex or blob. The
contents of the database directory for
test database is shown below.
$ ls /sqldata/mts/test
alex1.frm alex2.ibd blobt3.frm ibstest0.ibd ibtest11a.frm ibtest11d.frm
alex1.ibd alex3.frm blobt3.ibd ibtest09.frm ibtest11b.frm
alex2.frm alex3.ibd ibstest0.frm ibtest09.ibd ibtest11c.frm
We run mysqlbackup with the
--compress and
--include options:
$ mysqlbackup --defaults-file=/home/pekka/.my.cnf --compress \
--include='.*\.(alex|blob).*' --only-innodb backup
...many lines of output...
mysqlbackup: Scanned log up to lsn 2666737471.
mysqlbackup: Was able to parse the log up to lsn 2666737471.
mysqlbackup: Maximum page number for a log record 0
mysqlbackup: Compressed 147 MB of data files to 15 MB (compression 89%).
101208 17:18:04 mysqlbackup: Full backup completed!
The backup directory for the database
test is shown below. The
.ibz files are compressed per-table
datafiles.
$ ls /sqldata-backup/test
alex1.ibz alex2.ibz alex3.ibz blobt3.ibz
User Comments
Add your own comment.