InnoDB Hot Backup, or ibbackup, is a tool which
allows you to backup a running InnoDB database under MySQL without
setting any locks or disturbing normal database processing. You get a
consistent copy of your database, as if the copy were taken at a
precise point in time. InnoDB Hot Backup is also the ideal method of
setting up new slaves if you use the MySQL replication on InnoDB
tables.
The basic command to take a backup is ibbackup
my.cnf my2.cnf. The principle of
ibbackup is that it reads from the my.cnf
file the information where the ibdata and
ib_logfiles are, and makes a backup of them to the locations
specified in my2.cnf file.
The .cnf files are MySQL options files, and they must contain
the following parameter values:
The rest of the contents in the files are ignored. The directory
paths have to be absolute, because ibbackup is not aware of
the defaults. The specification of the number of data files and their
sizes has to match in my.cnf and
my2.cnf. If the last data file is specified as
auto-extending in my.cnf, then it has to be specified as
auto-extending also in my2.cnf.
The number of log files and their size have to be explicitly
specified, but it is not required that their number and size match in
my.cnf and my2.cnf.
An example: Suppose your my.cnf contains the
following:
Note that ibbackupnever writes over any
files. The backup directory has to be clean of old backups so
that it can create new files there and write to them.
The call ibbackup –help tells everything essential about
the usage:
$ ibbackup --help
InnoDB Hot Backup version 2.0-beta3; Copyright 2003 Innobase Oy
License A00001 is granted to Innobase Oy
Type ibbackup –license for detailed license terms, –help for help
Usage:
ibbackup [–sleep ms] [–suspend-at-end] [–compress [level]]
[–include regexp] my.cnf backup-my.cnf
or
ibbackup –apply-log [–use-memory mb] [–uncompress] backup-my.cnf
or
ibbackup –restore [–use-memory mb] [–uncompress] backup-my.cnf
The first command line call above reads the data file and log file
information from my.cnf, and stores the backup data files and a backup
log file (named ‘ibbackup_logfile’) in the directories specified in the
backup-my.cnf file.
The .cnf files must contain explicit values of (ibbackup is not aware of defaults):
datadir=…
innodb_data_home_dir=…
innodb_data_file_path=…
innodb_log_group_home_dir=…
set-variable = innodb_log_files_in_group=…
set-variable = innodb_log_file_size=…
If –apply-log is specified, then the program prepares a backup for
starting mysql server on the backup. It applies the log records in
‘ibbackup_logfile’ to the data files, and creates new log files as
specified in backup-my.cnf.
If –include regexp is specified, only those per-table data files which
match the given regular expression are included in the backup.
For each table with per-table data file a string of the form
db_name.table_name is checked against the regular expression.
If the regular expression matches the complete string db_name.table_name,
the table is included in the backup. The regular expression should be of
the POSIX 1003.2 “extended” form. Example: expression ’sales\.den.*’
matches all tables starting with “den” in database “sales”.
Note that on Unix (not on Windows) the regular expression should be placed
in single quotes to prevent interpretation by the shell. This feature
is implemented with Henry Spencer’s regular expression library.
–restore is an obsolete synonym for –apply-log. The use of –restore
is deprecated, because it may be dropped in the future.
–sleep ms instructs the program to sleep ms milliseconds after each 1 MB
of copied data. You can use this parameter to tune the additional disk i/o
load the backup program causes on the computer. ms must be < 1000000.
The default for ms is 0.
–suspend-at-end makes ibbackup to behave like this: when the backup
procedure is close to ending, ibbackup creates a file called
‘ibbackup_suspended’ to the log group home dir specified in backup-my.cnf
and waits until the user deletes that file ‘ibbackup_suspended’.
You can use this option if you want to write a script which locks
and backs up your MyISAM tables at the end of ibbackup. In that way you
get a consistent snapshot of both InnoDB and MyISAM tables.
–use-memory mb is relevant only when –apply-log is specified.
It tells ibbackup that it can use mb megabytes of memory in recovery.
The default is 100 MB.
–compress instructs the program to compress the backup copies of data
files. Compressed data files are named by adding suffix ‘.ibz’ to the
file name. Compression level can be specified as an optional argument
following –compress option. Compression level is an integer
between 0 and 9: 1 gives fastest compression, 9 gives best compression,
and 0 means no compression. If compression level is not given,
the default level 1 (fastest compression) is used.
–uncompress is relevant only when –apply-log is specified.
It tells ibbackup to recover data files from compressed copies.
Compressed data files are named with suffix ‘.ibz’.
The backup program does NOT make a backup of the .frm files of the tables,
and it does not make backups of MyISAM tables. You should make backups
of the .frm files with the Unix ‘tar’ or the Windows WinZip or an equivalent
tool both BEFORE and AFTER ibbackup finishes its work, and also store the
MySQL binlog segment which is generated between the moment you copy the .frm
files to a backup and the moment ibbackup finishes its work. For extra
safety, also use
mysqldump -l -d yourdatabasename
to dump the table CREATE statements in a human-readable form before
ibbackup finishes its work.
From the binlog segment you see if any of the .frm files changed between the
moment you took a .frm files backup and the moment ibbackup finished
its work.
Please send order inquiries and bug reports to innodb_sales_ww@oracle.com.
2 Making a backup
ibbackup makes a backup of InnoDB tables and indexes. It
does not, however, copy .frm files, MyISAM tables, or MyISAM
indexes to the backup. See section 2.3
for details on how to make a complete backup.
2.1 Example: Making an uncompressed backup
We have prepared an options file
/home/pekka/.backup-my.cnf which defines the location of the
backup (as described in the previous section). The options file
/home/pekka/.my.cnf defines the MySQL installation we want to
back up. We run ibbackup:
$ ibbackup /home/pekka/.my.cnf /home/pekka/.backup-my.cnf
InnoDB Hot Backup version 2.0-beta3; Copyright 2003 Innobase Oy
License A00001 is granted to Innobase Oy
Type ibbackup –license for detailed license terms, –help for helpContents of /home/pekka/.my.cnf:
innodb_data_home_dir got value /sqldata/simple
innodb_data_file_path got value ibdata1:100M;ibdata2:200M;ibdata3:500M:autoextend
datadir got value /sqldata/simple
innodb_log_group_home_dir got value /sqldata/simple
innodb_log_files_in_group got value 3
innodb_log_file_size got value 10485760
Contents of /home/pekka/.backup-my.cnf:
innodb_data_home_dir got value /sqldata-backups
innodb_data_file_path got value ibdata1:100M;ibdata2:200M;ibdata3:500M:autoextend
datadir got value /sqldata-backups
innodb_log_group_home_dir got value /sqldata-backups
innodb_log_files_in_group got value 3
innodb_log_file_size got value 10485760
ibbackup: Found checkpoint at lsn 0 268331310
ibbackup: Starting log scan from lsn 0 268331008
040121 17:35:46 ibbackup: Copying log…
040121 17:35:47 ibbackup: Switching to log file 2, lsn 0 272584704
040121 17:35:49 ibbackup: Log copied, lsn 0 282171935
ibbackup: We wait 10 seconds before starting copying the data files…
040121 17:35:59 ibbackup: Copying /sqldata/simple/ibdata1
040121 17:35:59 ibbackup: Switching to log file 0, lsn 0 283068416
040121 17:36:42 ibbackup: Copying /sqldata/simple/ibdata2
040121 17:38:19 ibbackup: Copying /sqldata/simple/ibdata3
ibbackup: A copied database page was modified at 0 284263243
ibbackup: Scanned log up to lsn 0 291666654
ibbackup: Was able to parse the log up to lsn 0 291666654
ibbackup: Maximum page number for a log record 3127
040121 17:42:15 ibbackup: Full backup completed!
The backup directory contains now a
backup log file and copies of InnoDB data files:
$ ls -lh /sqldata-backups
total 824M
-rw-r—– 1 pekka dev 22M Jan 21 17:42 ibbackup_logfile
-rw-r—– 1 pekka dev 100M Jan 21 17:36 ibdata1
-rw-r—– 1 pekka dev 200M Jan 21 17:38 ibdata2
-rw-r—– 1 pekka dev 500M Jan 21 17:42 ibdata3
2.2 Example: Making a compressed backup
Many users want to save disk space by compressing backup data
files. The –compress option instructs ibbackup to
compress backup data files:
$ ibbackup --compress /home/pekka/.my.cnf /home/pekka/.backup-my.c
InnoDB Hot Backup version 2.0-beta3; Copyright 2003 Innobase Oy
License A00001 is granted to Innobase Oy
Type ibbackup –license for detailed license terms, –help for helpContents of /home/pekka/.my.cnf:
innodb_data_home_dir got value /sqldata/simple
innodb_data_file_path got value ibdata1:100M;ibdata2:200M;ibdata3:500M:autoextend
datadir got value /sqldata/simple
innodb_log_group_home_dir got value /sqldata/simple
innodb_log_files_in_group got value 3
innodb_log_file_size got value 10485760
Contents of /home/pekka/.backup-my.cnf:
innodb_data_home_dir got value /sqldata-backups
innodb_data_file_path got value ibdata1:100M;ibdata2:200M;ibdata3:500M:autoextend
datadir got value /sqldata-backups
innodb_log_group_home_dir got value /sqldata-backups
innodb_log_files_in_group got value 3
innodb_log_file_size got value 10485760
ibbackup: Found checkpoint at lsn 0 411259884
ibbackup: Starting log scan from lsn 0 411259392
040121 18:35:03 ibbackup: Copying log…
040121 18:35:03 ibbackup: Log copied, lsn 0 411393274
ibbackup: We wait 10 seconds before starting copying the data files…
040121 18:35:13 ibbackup: Copying /sqldata/simple/ibdata1
040121 18:36:00 ibbackup: Copying /sqldata/simple/ibdata2
040121 18:37:21 ibbackup: Copying /sqldata/simple/ibdata3
040121 18:38:10 ibbackup: Switching to log file 1, lsn 0 419356672
ibbackup: A copied database page was modified at 0 415285076
ibbackup: Scanned log up to lsn 0 423500074
ibbackup: Was able to parse the log up to lsn 0 423500074
ibbackup: Maximum page number for a log record 9407
ibbackup: Compressed 800 MB of data files to 25 MB (compression 96%).
040121 18:40:24 ibbackup: Full backup completed!
The backup directory is shown below. Compressed data files have the
suffix .ibz. Typically compression ratios of more than 70%
are achieved:
$ ls -lh /sqldata-backups
total 38M
-rw-r—– 1 pekka dev 12M Jan 21 18:40 ibbackup_logfile
-rw-r—– 1 pekka dev 14M Jan 21 18:35 ibdata1.ibz
-rw-r—– 1 pekka dev 8.8M Jan 21 18:37 ibdata2.ibz
-rw-r—– 1 pekka dev 2.2M Jan 21 18:40 ibdata3.ibz
2.3 Making a sharp backup of both MyISAM and
InnoDB tables
The new Perl script innobackup automates the procedure
presented in this section. See Section
9.
The –suspend-at-end option (introduced in
ibbackup-1.05) can be used to obtain a sharp backup of both
MyISAM and InnoDB tables. The idea is that we should lock and flush
all MyISAM tables when an ibbackup run is close to ending,
and then copy the MyISAM tables to a backup. Since the backup taken
by ibbackup corresponds to the time at the end of the run,
and we freeze the MyISAM tables by locking them, we get a snapshot of
MyISAM tables at the same timepoint. The algorithm:
ibbackup –suspend-at-end …;
when the script notices the file ibbackup_suspended appears:
FLUSH TABLES WITH READ LOCK;
copy all .frm files and MyISAM tables from database directories
to a backup location;
resume the ibbackup run by deleting the file
ibbackup_suspended;
UNLOCK TABLES;
3 Applying the InnoDB log to the backup
The backup you have now taken does not correspond to any specific
log sequence number of InnoDB, because we copy different database
pages at different times. During a backup run ibbackup also
copies the accumulated InnoDB log to a file called
ibbackup_logfile. By applying that file to the backed up
data files we can roll forward them so that every page in the data
files corresponds to the same log sequence number of the InnoDB log.
The option for applying the log is –apply-log. In versions
prior to 2.0, this option was called –restore, but the word
restore is misleading, since we do not copy the data files back
to where they originally came from. Therefore, the option
–apply-log was added as a synonym for –restore in
the version 2.0 of ibbackup, and the option name
–restore will become deprecated. The new name
describes better what the operation does.
This phase also creates new ib_logfiles which correspond to the
data files.
3.1 Example: Applying the log to a backup
The backup directory looks like this before applying the log to the
backup.
$ ls -lh /sqldata-backups
total 814M
-rw-r—– 1 pekka dev 13M Jan 21 20:46 ibbackup_logfile
-rw-r—– 1 pekka dev 100M Jan 21 20:40 ibdata1
-rw-r—– 1 pekka dev 200M Jan 21 20:42 ibdata2
-rw-r—– 1 pekka dev 500M Jan 21 20:46 ibdata3
We run ibbackup to roll forward the data files so that
they correspond to the same log sequence number:
$ ibbackup --apply-log /home/pekka/.backup-my.cnf
InnoDB Hot Backup version 2.0-beta3; Copyright 2003 Innobase Oy
License A00001 is granted to Innobase Oy
Type ibbackup –license for detailed license terms, –help for help
Contents of /home/pekka/.backup-my.cnf:
innodb_data_home_dir got value /sqldata-backups
innodb_data_file_path got value ibdata1:100M;ibdata2:200M;ibdata3:500M:autoextend
datadir got value /sqldata-backups
innodb_log_group_home_dir got value /sqldata-backups
innodb_log_files_in_group got value 3
innodb_log_file_size got value 10485760
040121 22:57:16 ibbackup: ibbackup_logfile’s creation parameters:
ibbackup: start lsn 0 445538304, end lsn 0 459128394,
ibbackup: start checkpoint 0 445538409
InnoDB: Doing recovery: scanned up to log sequence number 0 450781184
InnoDB: Doing recovery: scanned up to log sequence number 0 456024064
InnoDB: Doing recovery: scanned up to log sequence number 0 459128394
InnoDB: Starting an apply batch of log records to the database…
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42
43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94
95 96 97 98 99 Setting log file size to 0 10485760
Setting log file size to 0 10485760
Setting log file size to 0 10485760
ibbackup: We were able to parse ibbackup_logfile up to
ibbackup: lsn 0 459128394
ibbackup: Last MySQL binlog file position 0 204358527, file name ./binlog.000002
ibbackup: The first data file is ‘/sqldata-backups/ibdata1′
ibbackup: and the new created log files are at ‘/sqldata-backups/’
040121 22:57:37 ibbackup: Full backup prepared for recovery successfully!
The contents of the backup directory after applying the
log. ibbackup created InnoDB log files (ib_logfile*) and
applied log records to the InnoDB data files (ibdata*):
$ ls -lh /sqldata-backups
total 844M
-rw-r—– 1 pekka dev 13M Jan 21 20:46 ibbackup_logfile
-rw-r—– 1 pekka dev 100M Jan 21 22:57 ibdata1
-rw-r—– 1 pekka dev 200M Jan 21 22:57 ibdata2
-rw-r—– 1 pekka dev 500M Jan 21 20:46 ibdata3
-rw-r—– 1 pekka dev 10M Jan 21 22:57 ib_logfile0
-rw-r—– 1 pekka dev 10M Jan 21 22:57 ib_logfile1
-rw-r—– 1 pekka dev 10M Jan 21 22:57 ib_logfile2
3.2 Example: Applying the log to a
compressed backup
If the backup is compressed, we must give the –uncompress
option to ibbackup when we apply the log to the backup:
$ ls -lh /sqldata-backups
total 38M
-rw-r—– 1 pekka dev 12M Jan 21 18:40 ibbackup_logfile
-rw-r—– 1 pekka dev 14M Jan 21 18:35 ibdata1.ibz
-rw-r—– 1 pekka dev 8.8M Jan 21 18:37 ibdata2.ibz
-rw-r—– 1 pekka dev 2.2M Jan 21 18:40 ibdata3.ibz
$ ibbackup --apply-log --uncompress /home/pekka/.backup-my.cnf
InnoDB Hot Backup version 2.0-beta3; Copyright 2003 Innobase Oy
License A00001 is granted to Innobase Oy
Type ibbackup –license for detailed license terms, –help for help
Contents of /home/pekka/.backup-my.cnf:
innodb_data_home_dir got value /sqldata-backups
innodb_data_file_path got value ibdata1:100M;ibdata2:200M;ibdata3:500M:autoextend
datadir got value /sqldata-backups
innodb_log_group_home_dir got value /sqldata-backups
innodb_log_files_in_group got value 3
innodb_log_file_size got value 10485760
ibbackup: Uncompressing data file ‘/sqldata-backups/ibdata1.ibz’
ibbackup: Progress in MB: 100
ibbackup: Uncompressing data file ‘/sqldata-backups/ibdata2.ibz’
ibbackup: Progress in MB: 100 200
ibbackup: Uncompressing data file ‘/sqldata-backups/ibdata3.ibz’
ibbackup: Progress in MB: 100 200 300 400 500
040121 23:29:02 ibbackup: ibbackup_logfile’s creation parameters:
ibbackup: start lsn 0 411259392, end lsn 0 423500074,
ibbackup: start checkpoint 0 411259884
InnoDB: Doing recovery: scanned up to log sequence number 0 416502272
InnoDB: Doing recovery: scanned up to log sequence number 0 421745152
InnoDB: Doing recovery: scanned up to log sequence number 0 423500074
InnoDB: Starting an apply batch of log records to the database…
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97
98 99 Setting log file size to 0 10485760
Setting log file size to 0 10485760
Setting log file size to 0 10485760
ibbackup: We were able to parse ibbackup_logfile up to
ibbackup: lsn 0 423500074
ibbackup: Last MySQL binlog file position 0 190482265, file name ./binlog.000002
ibbackup: The first data file is ‘/sqldata-backups/ibdata1′
ibbackup: and the new created log files are at ‘/sqldata-backups/’
040121 23:29:40 ibbackup: Full backup prepared for recovery successfully!
The contents of the backup directory after successfully applying
the log to the backup is shown below. The ibbackup output
above shows that data files were rolled forward to log sequence number
(lsn) 423500074. Notice that ibbackup does not modify any of
the original files in the backup (compressed data files and ibbackup
log file). This means that nothing is lost if the apply-log operation
fails for some reason (insufficient disk space, for example). After
fixing the problem the apply-log operation can be safely tried
again.
$ ls -lh /sqldata-backups
total 869M
-rw-r—– 1 pekka dev 12M Jan 21 18:40 ibbackup_logfile
-rw-r—– 1 pekka dev 100M Jan 21 23:29 ibdata1
-rw-r—– 1 pekka dev 14M Jan 21 18:35 ibdata1.ibz
-rw-r—– 1 pekka dev 200M Jan 21 23:29 ibdata2
-rw-r—– 1 pekka dev 8.8M Jan 21 18:37 ibdata2.ibz
-rw-r—– 1 pekka dev 500M Jan 21 23:29 ibdata3
-rw-r—– 1 pekka dev 2.2M Jan 21 18:40 ibdata3.ibz
-rw-r—– 1 pekka dev 10M Jan 21 23:29 ib_logfile0
-rw-r—– 1 pekka dev 10M Jan 21 23:29 ib_logfile1
-rw-r—– 1 pekka dev 10M Jan 21 23:29 ib_logfile2
3.3 Starting mysqld on a restored backup
After applying the log and creating the new ib_logfiles we
are ready to start mysqld on our backup database.
The backup database directory looks like this before we start
mysqld. Notice that the backup directory contains database
subdirectories mysql, test, test115. These
are not created by ibbackup, but they are created either
manually by the user or automatically by the innobackup
script (see Section 9).
$ ls -lh /sqldata-backups
total 872
-rw-r—– 1 pekka dev 41M Jan 22 15:33 ibbackup_logfile
-rw-r—– 1 pekka dev 100M Jan 22 15:38 ibdata1
-rw-r—– 1 pekka dev 200M Jan 22 15:38 ibdata2
-rw-r—– 1 pekka dev 500M Jan 22 15:32 ibdata3
-rw-r—– 1 pekka dev 10M Jan 22 15:40 ib_logfile0
-rw-r—– 1 pekka dev 10M Jan 22 15:38 ib_logfile1
-rw-r—– 1 pekka dev 10M Jan 22 15:38 ib_logfile2
drwxr-xr-x 2 pekka dev 4.0k Jan 22 15:33 mysql
drwxr-xr-x 2 pekka dev 4.0k Jan 22 15:33 test
drwxr-xr-x 2 pekka dev 4.0k Jan 22 15:33 test115
We start mysqld on the backup database:
$ mysqld --defaults-file=/home/pekka/.backup-my.cnf
040122 15:41:57 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.InnoDB: Reading tablespace information from the .ibd files…
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer…
040122 15:41:57 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 646218252.
InnoDB: Doing recovery: scanned up to log sequence number 0 646218252
InnoDB: 2 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 18 row operations to undo
InnoDB: Trx id counter is 0 239872
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx with id 0 239533, 16 rows to undo
InnoDB: Rolling back of trx id 0 239533 completed
InnoDB: Rolling back trx with id 0 239532, 2 rows to undo
InnoDB: Rolling back of trx id 0 239532 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Last MySQL binlog file position 0 27183537, file name ./binlog.000005
040122 15:41:57 InnoDB: Flushing modified pages from the buffer pool…
040122 15:41:59 InnoDB: Started; log sequence number 0 646218252
040122 15:41:59 mysql.user table is not updated to new password format;
Disabling new password usage until mysql_fix_privilege_tables is run
mysqld: ready for connections.
Now mysqld is running on the restored backup database and
ready to serve clients.
If you originally took the backup from a MySQL replication slave, then
mysqld will print also the master database binlog position when you
start mysqld on the backup.
4 Advanced operations
4.1 Point-in-time recovery from a hot backup
InnoDB only stores the binlog position information to its
tablespace at a transaction commit. To make InnoDB aware of
the current binlog position you must run at least one transaction
while binlogging is enabled. When you run ibbackup
–apply-log on your backup, ibbackup versions >= 1.03
print the latest MySQL binlog position the backup knows of. Also
mysqld prints it when you start it on the backup after the
–apply-log:
$ mysqld --defaults-file=/home/pekka/.backup-my.cnf040122 15:41:57 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
…
InnoDB: Last MySQL binlog file position 0 27183537, file name ./binlog.000005
…
mysqld: ready for connections.
The MySQL version has to be >= 3.23.48 or >= 4.0.2.
The printed position is the MySQL binlog byte position from the
moment when InnoDB Hot Backup finished the copying of your data files.
Then you can apply the binlog file(s) starting from that position to
the restored database:
$ mysqlbinlog --position=27183537 /sqldata/binlog.000005 | mysql
If you want to recover the database to a specific timepoint, you
can direct the output of mysqlbinlog to an output file,
instead of piping it directly to mysql. The output file
contains timestamps for all SQL statements in the binlog. Use an
editor to cut off the end of the output file and redirect the output
file to mysql, like this:
$ mysql < youroutputfile
4.2 Setting up a new slave from a hot backup
in replication
If you use MySQL replication to replicate InnoDB tables,
InnoDB Hot Backup allows you to set up a slave database without
stopping the master because you can make a restored hot backup a new
slave database.
Take the backup, use ibbackup –apply-log to restore it,
and put the restored backup and the log files to the right places for
the new slave.
Edit the my.cnf file of the new slave so that you put
skip-slave-start to the
class="cnf">[mysqld] section.
Start the new slave mysqld (version >= 3.23.48 or
>= 4.0.2). It prints the latest MySQL binlog position the backup
knows of.
…InnoDB: Last MySQL binlog file position 0 128760128, file name ./hundin-bin.006…
Note that InnoDB only stores the binlog position information to its
tablespace at a transaction commit. To make InnoDB aware of the
current binlog position you must run at least one transaction while
binlogging is enabled.
Use the CHANGE MASTER SQL command on the slave to init it
right. For example:
Start replication in the new slave with the SLAVE START
SQL command.
Remove the line skip-slave-start from
the my.cnf file of the slave.
4.3 Restoring a master database in replication
Let us assume a master database gets corrupt.
Use the backup of the master database, do ibbackup –apply-log
yourbackupmy.cnf and put the ibdata and
ib_logfile files to the right places. Then put the
.frm files to the right place. Let us assume you have a tar
file of the .frm files: cd mysqldatadir; tar
xvf yourtarfile
Edit the master my.cnf file so that you comment out
log-bin in it so that the slaves do not
receive twice the binlog needed to recover the master.
Replication in the slaves must be stopped temporarily when you do the
piping of the binlog to the master. In the slaves do
mysql> STOP SLAVE;
Start the master mysqld on the restored backup:
$ mysqld…
InnoDB: Doing recovery: scanned up to log sequence number 0 64300044
InnoDB: Last MySQL binlog file position 0 5585832, file name
./omnibook-bin.002…
InnoDB printed the binlog file and position it was able to recover to.
Next you should pipe the remaining binlog files to the restored
backup:
$ mysqlbinlog --position=5585832mysqldatadir/omnibook-bin.002 | mysql
$ mysqlbinlog /mysqldatadir/omnibook-bin.003 | mysql
The master database is now recovered. Shut down the master and edit
my.cnf so that you uncomment log-bin.
Start the master again.
Start replication in the slaves again:
mysql> START SLAVE;
5 innobackup—a Perl script
which automates the backup of both MyISAM and InnoDB tables (not available for Windows)
WARNING: a CRITICAL bug in MySQL-4.0.22 and 4.1.7 causes the
2nd run of the innobackup Perl script to freeze the
whole mysqld server.
The bug was introduced in FLUSH TABLES WITH READ LOCK.
The bug does not affect pure ibbackup.
This bug is fixed in innobackup version
1.1.0. See Section 7.4 and
<href=”#history.innobackup”>Section 9.2 for more information.
innobackup is an easy-to-use tool for making a complete
backup of both MyISAM and InnoDB tables. It is used in combination
with ibbackup, which is run as a child process by
innobackup. In addition to creating backups,
innobackup 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.
IMPORTANT: the innobackup Perl script is not a
true online backup tool, because it issues the command
FLUSH TABLES WITH READ LOCK at the end of the backup run.
innobackup will work well only if:
you do not run long SELECT or other SQL queries at
the time of the backup run, and
your MyISAM tables are small.
Then the locked phase at the end of an innobackup 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.
Download the latest innobackup source
code from this linkinnobackup is published under the
GNU GPL license Version 2, June 1991. Those who
buy a license of InnoDB Hot Backup can use innobackup under
the same standard commercial license as InnoDB Hot Backup, and are not
bound by the GPL restrictions.
Please note that Innobackup is not available for Windows.
innobackup is a Perl script which 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 innobackup:
The –user and the –password you specify are
used in connecting with the mysql client program to the
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 to the
mysql system database in the server (see
href=”#innobackup.privileges”>example 5.5 for details on the required
MySQL privileges). In a backup run innobackup places the
backup to 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.
You also have to make sure that the user or the cron job running
innobackup has the rights to start mysql and
copy files from the MySQL database directories to the backup
directory.
Make sure that your connection timeouts are long enough so
that the script can keep the mysql client connection open for
the duration of the backup run. innobackup tries to keep the
mysql client connection open by sending keep-alive messages
whenever connection is idle for more than 30 minutes. You can change
this timeout by editing the value of
class="perl">mysql_keep_alive_timeout parameter in the
innobackup script.
When innobackup is run, it first tests a connection with the
mysql SQL client to the MySQL server. Then it calls
ibbackup and takes an online backup of InnoDB type tables:
this phase does not disturb normal database processing. When the
ibbackup run has almost completed, innobackup
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, innobackup lets ibbackup run to
completion and UNLOCKs the tables.
Known bugs and limitations:Warning: a CRITICAL bug in MySQL-4.0.22 and 4.1.7 causes the
2nd run of the innobackup Perl script to freeze
the whole mysqld server. The bug was introduced in
FLUSH TABLES WITH READ LOCK. The bug does not affect
pure ibbackup. This bug is fixed in innobackup
version 1.1.0. See Section 7.4 and
href=”#history.innobackup”>Section 9.2 for more information.
There is one known outstanding bug in the backup algorithm.
MySQL versions < 4.1.2 do not prevent CREATE TABLE or
RENAME TABLE during the locked phase. Therefore, there is
a small chance that the .frm files of newly created empty
tables do not correspond to the binlog position of the backup.
Currently, innobackup works only on Unix-like systems, and
it requires Perl 5.005 or newer.
See also Section 7.2 about Perl regexp bugs with
UTF-8 characters.
IMPORTANT NOTICE
For a large database a backup run may take a long time. You should
always check that innobackup has completed successfully. This
can be done by either verifying that innobackup process has returned
with exit code 0, or by observing that innobackup has printed
the text “innobackup completed OK!”.
5.1 Usage
$ perl innobackup --helpUsage:innobackup [–sleep=MS] [–compress[=LEVEL]] [–include=REGEXP] [–user=NAME][–password=WORD] [–port=PORT] [–socket=SOCKET]
MY.CNF BACKUP-ROOT-DIR
innobackup –apply-log [–use-memory=MB] [–uncompress] MY.CNF BACKUP-DIR
innobackup –copy-back MY.CNF BACKUP-DIR
The first command line above makes a hot backup of a MySQL database.
It creates a backup directory (named by the current date and time)
in the given backup root directory. This command makes a complete
backup of all MyISAM and InnoDB tables and indexes in all databases.
The created backup contains .frm, .MYD, .MYI, and InnoDB data and log files.
The MY.CNF options file defines the location of the database.
This command connects to the MySQL server using mysql client program,
and runs ibbackup (InnoDB Hot Backup program) as a child process.
The command with –apply-log option prepares a backup for starting a MySQL
server on the backup. This command expands InnoDB data files as specified
in BACKUP-DIR/backup-my.cnf using BACKUP-DIR/ibbackup_logfile,
and creates new InnoDB log files as specified in BACKUP-DIR/backup-my.cnf.
The BACKUP-DIR should be a path name of a backup directory created by
innobackup. This command runs ibbackup as a child process, but it does not
connect to the database server.
The command with –copy-back option copies data, index, and log files
from backup directory back to their original locations.
The MY.CNF options file defines the original location of the database.
The BACKUP-DIR is a path name of a backup directory created by innobackup.
On success the exit code of innobackup process is 0. A non-zero exit code
indicates an error.
Options:
–help Display this helpscreen and exit.
–version Print version information and exit.
–apply-log Prepare a backup for starting mysql server on the backup.
Expand InnoDB data files as specified in
backup-dir/backup-my.cnf, using backup-dir/ibbackup_logfile,
and create new log files as specified in
backup-dir/backup-my.cnf.
–copy-back Copy data and index files from backup directory back to
their original locations.
–use-memory=MB
This option is passed to the ibbackup child process.
It tells ibbackup that it can use MB megabytes of
memory in restoration.
Try ‘ibbackup –help’ for more details on this option.
–sleep=MS This option is passed to the ibbackup child process.
It instructs the ibbackup program to sleep
MS milliseconds after each 1 MB of copied data.
You can use this parameter to tune the additional
disk i/o load the ibbackup program causes on the computer.
Try ‘ibbackup –help’ for more details on this option.
–compress[=LEVEL]
This option is passed to the ibbackup child process.
It instructs ibbackup to compress the backup copies of
InnoDB data files. Compression level can be
specified as an optional argument. Compression level is
an integer between 0 and 9: 1 gives fastest compression,
9 gives best compression, and 0 means no compression.
If compression level is not given, the default level 1 is used.
Try ‘ibbackup –help’ for more details on this option.
–include=REGEXP
This option is passed to the ibbackup child process.
It tells ibbackup to backup only those per-table data
files which match the given regular expression. For
each table with a per-table data file a string of the
form db_name.table_name is checked against the regular
expression. If the regular expression matches the
complete string db_name.table_name, the table is
included in the backup. The regular expression should
be of the POSIX 1003.2 “extended” form.
Try ‘ibbackup –help’ for more details on this option.
–uncompress
This option is passed to the ibbackup child process.
It tells ibbackup to uncompress compressed InnoDB data files.
Try ‘ibbackup –help’ for more details on this option.
–user=NAME This option is passed to the mysql child process.
It defines the user for database login if not current user.
Try ‘mysql –help’ for more details on this option.
–password=WORD
This option is passed to the mysql child process.
It defines the password to use when connecting to database.
Try ‘mysql –help’ for more details on this option.
–port=PORT This option is passed to the mysql child process.
It defines the port to use when connecting to local database
server with TCP/IP.
Try ‘mysql –help’ for more details on this option.
–socket=SOCKET
This option is passed to the mysql child process.
It defines the socket to use when connecting to local database
server with UNIX domain socket.
Try ‘mysql –help’ for more details on this option.
5.2 Example: Creating a backup
In this example innobackup takes two arguments: the
options file of the MySQL installation to be backed up and a backup
root directory. The last command line argument
/backups is the root directory in which
innobackup creates the backup directory.
$ perl innobackup /home/pekka/.my.cnf /backups
InnoDB Backup Utility v1.0-beta6; Copyright 2003 Innobase Oy
This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
innobackup: Using mysql Ver 12.18 Distrib 4.0.11-gamma, for pc-linux (i686)
innobackup: Using InnoDB Hot Backup version 2.0-beta3
innobackup: Created backup directory /backups/2004-02-03_13-27-09
innobackup: Starting mysql with options: –unbuffered
innobackup: Connected to database with mysql child process (pid=10450)
innobackup: Connection to database server closed
innobackup: Starting ibbackup with command: ibbackup –suspend-at-end /home/pekka/
.my.cnf /backups/2004-02-03_13-27-09/backup-my.cnf
innobackup: Waiting for ibbackup to suspend
innobackup: Suspend file ‘/backups/2004-02-03_13-27-09/ibbackup_suspended’
InnoDB Hot Backup version 2.0-beta3; Copyright 2003 Innobase Oy
License A00001 is granted to Innobase Oy
Type ibbackup –license for detailed license terms, –help for help
Contents of /home/pekka/.my.cnf:
innodb_data_home_dir got value /sqldata/simple
innodb_data_file_path got value ibdata1:100M;ibdata2:200M;ibdata3:500M:autoextend
datadir got value /sqldata/simple
innodb_log_group_home_dir got value /sqldata/simple
innodb_log_files_in_group got value 3
innodb_log_file_size got value 10485760
Contents of /backups/2004-02-03_13-27-09/backup-my.cnf:
innodb_data_home_dir got value /backups/2004-02-03_13-27-09
innodb_data_file_path got value ibdata1:100M;ibdata2:200M;ibdata3:500M:autoextend
datadir got value /backups/2004-02-03_13-27-09
innodb_log_group_home_dir got value /backups/2004-02-03_13-27-09
innodb_log_files_in_group got value 3
innodb_log_file_size got value 10485760
ibbackup: Found checkpoint at lsn 0 649595444
ibbackup: Starting log scan from lsn 0 649595392
040203 13:27:13 ibbackup: Copying log…
040203 13:27:14 ibbackup: Switching to log file 2, lsn 0 649998336
040203 13:27:14 ibbackup: Log copied, lsn 0 654335006
ibbackup: We wait 10 seconds before starting copying the data files…
040203 13:27:24 ibbackup: Copying /sqldata/simple/ibdata1
040203 13:27:46 ibbackup: Switching to log file 0, lsn 0 660482048
040203 13:27:55 ibbackup: Copying /sqldata/simple/ibdata2
040203 13:28:56 ibbackup: Copying /sqldata/simple/ibdata3
040203 13:29:17 ibbackup: Switching to log file 1, lsn 0 670965760
040203 13:30:07 ibbackup: Switching to log file 2, lsn 0 681449472
ibbackup: You had specified the option –suspend-at-end
040203 13:31:31 ibbackup: Suspending the backup procedure to wait
ibbackup: until you delete the marker file /backups/2004-02-0
3_13-27-09/ibbackup_suspended
innobackup: Continuing after ibbackup has suspended
innobackup: Starting mysql with options: –unbuffered
innobackup: Connected to database with mysql child process (pid=10462)
innobackup: All tables locked and flushed to disk
innobackup: Starting to backup .frm, .MYD and .MYI files in
innobackup: subdirectories of ‘/sqldata/simple’
innobackup: Backing up files ‘/sqldata/simple/mysql/*.{frm,MYD,MYI}’ (19 files)
innobackup: Backing up files ‘/sqldata/simple/test/*.{frm,MYD,MYI}’ (22 files)
innobackup: Backing up files ‘/sqldata/simple/test115/*.{frm,MYD,MYI}’ (57 files)
innobackup: Finished backing up .frm, .MYD and .MYI files
innobackup: Resuming ibbackup
040203 13:31:53 ibbackup: Suspension ends. Continuing the backup procedure.
040203 13:31:53 ibbackup: Copying of the last data file is close to ending…
ibbackup: We still once copy the latest flushed log to ibbackup_logfile.
ibbackup: A copied database page was modified at 0 662242440
ibbackup: Scanned log up to lsn 0 683781754
ibbackup: Was able to parse the log up to lsn 0 683781754
ibbackup: Maximum page number for a log record 9790
040203 13:31:53 ibbackup: Full backup completed!
innobackup: All tables unlocked
innobackup: Connection to database server closed
innobackup: Backup created in directory ‘/backups/2004-02-03_13-27-09′
innobackup: innobackup completed OK!
Now we see the backup directory under the BACKUP-ROOT-DIR
we specified. The directory name for each new backup is formed from
the date and the clock time when the backup run was started, in the
local time zone.
$ ls -l /backups
total 16
drwxr-xr-x 2 pekka dev 4096 Jan 23 00:20 2004-01-23_00-20-54
drwxr-xr-x 5 pekka dev 4096 Jan 23 00:25 2004-01-23_00-21-17
drwxr-xr-x 5 pekka dev 4096 Feb 3 13:24 2004-01-29_16-43-01
drwxr-xr-x 5 pekka dev 4096 Feb 3 13:31 2004-02-03_13-27-09
The backup directory contains the backed-up ibdata files
and ibbackup_logfile. Its subdirectories mysql,
test, test115 are copies of the database
directories, and contain copies of .frm, .MYD, and
.MYI files:
$ ls -lh /backups/2004-02-03_13-27-09
total 834M
-rw-r–r– 1 pekka dev 405 Feb 3 13:27 backup-my.cnf
-rw-r—– 1 pekka dev 33M Feb 3 13:31 ibbackup_logfile
-rw-r—– 1 pekka dev 100M Feb 3 13:27 ibdata1
-rw-r—– 1 pekka dev 200M Feb 3 13:28 ibdata2
-rw-r—– 1 pekka dev 500M Feb 3 13:31 ibdata3
drwxr-xr-x 2 pekka dev 4.0k Feb 3 13:31 mysql
-rw-r–r– 1 pekka dev 0 Feb 3 13:31 mysql-stderr
-rw-r–r– 1 pekka dev 426 Feb 3 13:31 mysql-stdout
drwxr-xr-x 2 pekka dev 4.0k Feb 3 13:31 test
drwxr-xr-x 2 pekka dev 4.0k Feb 3 13:31 test115
A hint for trouble-shooting:innobackup
redirects the standard output of the mysql child process to
the file mysql-stdout file and the error output to
mysql-stderr. If all SQL commands issued by
innobackup succeed, the file mysql-stderr will be
empty. In case it is not empty, something has gone wrong with the
mysql process, or an SQL command has failed. You should look
at mysql-stderr for details on why the backup failed.
5.3 Example: Starting mysqld on a backup
This example continues from where we ended up at the end of the
previous example. We prepare the backup for starting the data base
server on it by applying the log records (in file
ibbackup_logfile) to the InnoDB data files
(ibdata*).
$ innobackup --apply-log /home/pekka/.my.cnf /backups/2004-02-03_13-27-09
InnoDB Backup Utility v1.0-beta6; Copyright 2003 Innobase OyThis software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
innobackup: Using InnoDB Hot Backup version 2.0-beta3
innobackup: Starting ibbackup with command: ibbackup –restore /backups/2
004-02-03_13-27-09/backup-my.cnf
InnoDB Hot Backup version 2.0-beta3; Copyright 2003 Innobase Oy
License A00001 is granted to Innobase Oy
Type ibbackup –license for detailed license terms, –help for help
Contents of /backups/2004-02-03_13-27-09/backup-my.cnf:
innodb_data_home_dir got value /backups/2004-02-03_13-27-09
innodb_data_file_path got value ibdata1:100M;ibdata2:200M;ibdata3:500M:autoextend
datadir got value /backups/2004-02-03_13-27-09
innodb_log_group_home_dir got value /backups/2004-02-03_13-27-09
innodb_log_files_in_group got value 3
innodb_log_file_size got value 10485760
040203 14:24:56 ibbackup: ibbackup_logfile’s creation parameters:
ibbackup: start lsn 0 649595392, end lsn 0 683781754,
ibbackup: start checkpoint 0 649595444
InnoDB: Doing recovery: scanned up to log sequence number 0 654838272
InnoDB: Doing recovery: scanned up to log sequence number 0 660081152
InnoDB: Doing recovery: scanned up to log sequence number 0 665324032
InnoDB: Doing recovery: scanned up to log sequence number 0 670566912
InnoDB: Doing recovery: scanned up to log sequence number 0 675809792
InnoDB: Doing recovery: scanned up to log sequence number 0 681052672
InnoDB: Doing recovery: scanned up to log sequence number 0 683781754
InnoDB: Starting an apply batch of log records to the database…
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97
98 99 Setting log file size to 0 10485760
Setting log file size to 0 10485760
Setting log file size to 0 10485760
ibbackup: We were able to parse ibbackup_logfile up to
ibbackup: lsn 0 683781754
ibbackup: Last MySQL binlog file position 0 12438592, file name ./binlog.000008
ibbackup: The first data file is ‘/backups/2004-02-03_13-27-09/ibdata1′
ibbackup: and the new created log files are at ‘/backups/2004-02-03_13-27-09/’
040203 14:25:44 ibbackup: Full backup prepared for recovery successfully!
innobackup: innobackup completed OK!
The contents of the backup directory after successfully applying
the log to the backup is shown below. The ibbackup output
above shows that data files were rolled forward to log sequence number
(lsn) 683781754.
$ ls -lh /backups/2004-02-03_13-27-09
total 864M
-rw-r–r– 1 pekka dev 405 Feb 3 13:27 backup-my.cnf
-rw-r—– 1 pekka dev 33M Feb 3 13:31 ibbackup_logfile
-rw-r—– 1 pekka dev 100M Feb 3 14:25 ibdata1
-rw-r—– 1 pekka dev 200M Feb 3 14:25 ibdata2
-rw-r—– 1 pekka dev 500M Feb 3 13:31 ibdata3
-rw-r—– 1 pekka dev 10M Feb 3 14:25 ib_logfile0
-rw-r—– 1 pekka dev 10M Feb 3 14:25 ib_logfile1
-rw-r—– 1 pekka dev 10M Feb 3 14:25 ib_logfile2
drwxr-xr-x 2 pekka dev 4.0k Feb 3 13:31 mysql
-rw-r–r– 1 pekka dev 0 Feb 3 13:31 mysql-stderr
-rw-r–r– 1 pekka dev 426 Feb 3 13:31 mysql-stdout
drwxr-xr-x 2 pekka dev 4.0k Feb 3 13:31 test
drwxr-xr-x 2 pekka dev 4.0k Feb 3 13:31 test115
We are now almost ready to start a server on this backup. But before we
can do this, we must prepare a valid my.cnf file for the backup. The
backup-my.cnf in the backup directory can not be used as such
because it contains only the six parameters required by
ibbackup. We must create a my.cnf file where these six
parameters are copied from backup-my.cnf file, and the other
parameters are defined by the original my.cnf file (which is
/home/pekka/.my.cnf in this example). We make a copy of
the original file and add the changes in backup-my.cnf.
Now we have a valid my.cnf file
(/backups/2004-02-03_13-27-09/my.cnf), and
we can start a server on the backup:
$ mysqld --defaults-file=/backups/2004-02-03_13-27-09/my.cnf
040203 15:26:53 Warning: Changed limits: max_connections: 200 table_cache: 407
InnoDB: The log file was created by ibbackup –restore at
InnoDB: ibbackup 040203 14:25:44
040203 15:26:53 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files…
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer…
040203 15:26:54 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 683782156.
InnoDB: Doing recovery: scanned up to log sequence number 0 683782156
InnoDB: Last MySQL binlog file position 0 12438592, file name ./binlog.000008
040203 15:26:54 InnoDB: Flushing modified pages from the buffer pool…
040203 15:26:54 InnoDB: Started; log sequence number 0 683782156
TODO: make sure MySQL sets field->query_id right in prepare/execute
040203 15:26:54 mysql.user table is not updated to new password format; Disabling
new password usage until mysql_fix_privilege_tables is run
mysqld: ready for connections.
5.4 Example: Restoring a database at its
original location
The log files have been applied to the backup (in
/backups/2004-02-03_13-27-09). We copy
InnoDB and MyISAM indexes, and .frm files back to their original
locations (defined by /home/pekka/.my.cnf file).
$ ls -lh /backups/2004-02-03_13-27-09
total 864M
-rw-r–r– 1 pekka dev 405 Feb 3 13:27 backup-my.cnf
-rw-rw—- 1 pekka dev 79 Feb 3 15:26 binlog.000001
-rw-rw—- 1 pekka dev 16 Feb 3 15:26 binlog.index
-rw-r—– 1 pekka dev 33M Feb 3 13:31 ibbackup_logfile
-rw-r—– 1 pekka dev 100M Feb 3 15:28 ibdata1
-rw-r—– 1 pekka dev 200M Feb 3 15:28 ibdata2
-rw-r—– 1 pekka dev 500M Feb 3 15:28 ibdata3
-rw-r—– 1 pekka dev 10M Feb 3 15:28 ib_logfile0
-rw-r—– 1 pekka dev 10M Feb 3 14:25 ib_logfile1
-rw-r—– 1 pekka dev 10M Feb 3 14:25 ib_logfile2
drwxr-xr-x 2 pekka dev 4.0k Feb 3 13:31 mysql
-rw-r–r– 1 pekka dev 0 Feb 3 13:31 mysql-stderr
-rw-r–r– 1 pekka dev 426 Feb 3 13:31 mysql-stdout
drwxr-xr-x 2 pekka dev 4.0k Feb 3 13:31 test
drwxr-xr-x 2 pekka dev 4.0k Feb 3 13:31 test115
$ innobackup --copy-back /home/pekka/.my.cnf /backups/2004-02-03_13-27-09
InnoDB Backup Utility v1.0-beta6; Copyright 2003 Innobase Oy
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
innobackup: Starting to copy InnoDB and MyISAM tables, indexes, and .frm
innobackup: files in ‘/backups/2004-02-03_13-27-09′
innobackup: back to original data directory ‘/sqldata/simple’
innobackup: Copying file ‘/backups/2004-02-03_13-27-09/ibdata1′
innobackup: Copying file ‘/backups/2004-02-03_13-27-09/ibdata2′
innobackup: Copying file ‘/backups/2004-02-03_13-27-09/ibdata3′
innobackup: Copying file ‘/backups/2004-02-03_13-27-09/ib_logfile0′
innobackup: Copying directory ‘/backups/2004-02-03_13-27-09/mysql’
innobackup: Copying directory ‘/backups/2004-02-03_13-27-09/test’
innobackup: Copying directory ‘/backups/2004-02-03_13-27-09/test115′
innobackup: Copying file ‘/backups/2004-02-03_13-27-09/ib_logfile1′
innobackup: Copying file ‘/backups/2004-02-03_13-27-09/ib_logfile2′
innobackup: Copying file ‘/backups/2004-02-03_13-27-09/binlog.000001′
innobackup: Copying file ‘/backups/2004-02-03_13-27-09/binlog.index’
innobackup: Finished copying back files.
innobackup: innobackup completed OK!
The original database directory is now restored from the backup,
and we can start a server on it:
$ ls -lh /sqldata/simple
total 831M
-rw-rw—- 1 pekka dev 79 Feb 3 15:26 binlog.000001
-rw-rw—- 1 pekka dev 16 Feb 3 15:26 binlog.index
-rw-r—– 1 pekka dev 100M Feb 3 15:28 ibdata1
-rw-r—– 1 pekka dev 200M Feb 3 15:28 ibdata2
-rw-r—– 1 pekka dev 500M Feb 3 15:28 ibdata3
-rw-r—– 1 pekka dev 10M Feb 3 15:28 ib_logfile0
-rw-r—– 1 pekka dev 10M Feb 3 14:25 ib_logfile1
-rw-r—– 1 pekka dev 10M Feb 3 14:25 ib_logfile2
drwxr-xr-x 2 pekka dev 4.0k Feb 3 13:31 mysql
drwxr-xr-x 2 pekka dev 4.0k Feb 3 13:31 test
drwxr-xr-x 2 pekka dev 4.0k Feb 3 13:31 test115
5.5 Example: Setting MySQL privileges for innobackup
When innobackup creates a backup, it sends SQL commands to
MySQL server. The required minimum SQL privileges for the user running
innobackup are RELOAD on all databases and
tables, and CREATE, INSERT, and
DROP on ibbackup_binlog_marker table in
mysql database. You can set these for user dba
connecting from localhost as follows:
$ mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19 to server version: 4.1.1-alpha-debug-log
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> GRANT RELOAD ON *.* TO 'dba'@'localhost';
Query OK, 0 rows affected (0.10 sec)
mysql> GRANT CREATE, INSERT, DROP ON mysql.ibbackup_binlog_marker
-> TO 'dba'@'localhost';
Query OK, 0 rows affected (0.06 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.15 sec)
6 Making a partial backup
When the multiple tablespaces feature is enabled, it is possible to make a partial
backup of the InnoDB tables. The
<href=”http://dev.mysql.com/doc/mysql/en/Multiple_tablespaces.html”>multiple tablespaces feature
allows storing each InnoDB table in a separate tablespace holding the
data and indexes of one table only. A single-table tablespace
consists of one autoextending datafile named
table_name.ibd in the database directory of the
table. Those InnoDB tables for which the
href=”http://dev.mysql.com/doc/mysql/en/Multiple_tablespaces.html”>multiple tablespaces feature
is not enabled, are stored as usual in the system tablespace defined
by the innodb_data_file_path and
class="cnf">innodb_data_home_dir parameters in the
my.cnf file.
With its –include option, ibbackup (v2.0 or
higher) can be instructed to make a partial backup excluding some of
the per-table datafiles. A partial backup contains the system
tablespace and per-table datafiles of those tables which match the
include pattern.
For each table with a per-table data file a string of the form
db_name.table_name is checked
against the regular expression specified with the –include
option. If the
href=”http://en.wikipedia.org/wiki/Regular_expression”>regular
expression matches the complete string
db_name.table_name, the table is
included in the backup. The regular expression should be of the
href=”http://www.unix.org/version3/ieee_std.html”>
title=”Portable Operating System Interface”>POSIX
extended form. Note that on Unix-like systems, the regular
expression should be quoted appropriately in order to prevent
interpretation of shell meta-characters. This feature has been
implemented with Henry Spencer’s regular expression library.
Notice that even though the innobackup Perl script
supports taking partial backups you should be careful when restoring a
database from a partial backup. innobackup copies also the
.frm files of those tables which are not included in the
backup. If you use innobackup with –include option,
you should delete the .frm files of those tables that were
not included in the backup before trying to restore the database.
6.1 Example: Making an uncompressed
partial backup
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 is 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).
$ ibbackup --include 'test\.ib.*' /home/pekka/.my.cnf /home/pekka/.backup-my.cnf
InnoDB Hot Backup version 2.0-beta3; Copyright 2003 Innobase OyLicense A00001 is granted to Innobase Oy
Type ibbackup –license for detailed license terms, –help for helpContents of /home/pekka/.my.cnf:
innodb_data_home_dir got value /sqldata/mts
innodb_data_file_path got value ibdata1:10M:autoextend
datadir got value /sqldata/mts
innodb_log_group_home_dir got value /sqldata/mts
innodb_log_files_in_group got value 3
innodb_log_file_size got value 10485760
Contents of /home/pekka/.backup-my.cnf:
innodb_data_home_dir got value /sqldata-backups
innodb_data_file_path got value ibdata1:10M:autoextend
datadir got value /sqldata-backups
innodb_log_group_home_dir got value /sqldata-backups
innodb_log_files_in_group got value 3
innodb_log_file_size got value 10485760
Value of include option: ‘test\.ib.*’
ibbackup: Found checkpoint at lsn 4 2177623762
ibbackup: Starting log scan from lsn 4 2177623552
040127 15:46:37 ibbackup: Copying log…
040127 15:46:37 ibbackup: Log copied, lsn 4 2177623762
ibbackup: We wait 10 seconds before starting copying the data files…
040127 15:46:47 ibbackup: Copying /sqldata/mts/ibdata1
040127 15:47:34 ibbackup: Copying /sqldata/mts/test/ibstest0.ibd
040127 15:47:45 ibbackup: Copying /sqldata/mts/test/ibtest09.ibd
ibbackup: A copied database page was modified at 4 2177623762
ibbackup: Scanned log up to lsn 4 2177623762
ibbackup: Was able to parse the log up to lsn 4 2177623762
ibbackup: Maximum page number for a log record 0
040127 15:48:00 ibbackup: 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-backups/test
ibstest0.ibd ibtest09.ibd
6.2 Example: 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 of which name starts with alex or
blob. The contents of the database directory for
test database is shown below.
We run ibbackup with the –compress and –include
options:
$ ibbackup --compress --include '.*\.(alex|blob).*'/home/pekka/.my.cnf /home/pekka/.backup-my.cnf
InnoDB Hot Backup version 2.0-beta3; Copyright 2003 Innobase Oy
License A00001 is granted to Innobase Oy
Type ibbackup –license for detailed license terms, –help for help
Contents of /home/pekka/.my.cnf:
innodb_data_home_dir got value /sqldata/mts
innodb_data_file_path got value ibdata1:10M:autoextend
datadir got value /sqldata/mts
innodb_log_group_home_dir got value /sqldata/mts
innodb_log_files_in_group got value 3
innodb_log_file_size got value 10485760
Contents of /home/pekka/.backup-my.cnf:
innodb_data_home_dir got value /sqldata-backups
innodb_data_file_path got value ibdata1:10M:autoextend
datadir got value /sqldata-backups
innodb_log_group_home_dir got value /sqldata-backups
innodb_log_files_in_group got value 3
innodb_log_file_size got value 10485760
Value of include option: ‘.*\.(alex|blob).*’
ibbackup: Found checkpoint at lsn 4 2177623762
ibbackup: Starting log scan from lsn 4 2177623552
040127 17:11:04 ibbackup: Copying log…
040127 17:11:04 ibbackup: Log copied, lsn 4 2177623762
ibbackup: We wait 10 seconds before starting copying the data files…
040127 17:11:14 ibbackup: Copying /sqldata/mts/ibdata1
040127 17:11:56 ibbackup: Copying /sqldata/mts/test/alex2.ibd
040127 17:11:56 ibbackup: Copying /sqldata/mts/test/alex3.ibd
040127 17:11:56 ibbackup: Copying /sqldata/mts/test/blobt3.ibd
040127 17:12:25 ibbackup: Copying /sqldata/mts/test/alex1.ibd
ibbackup: A copied database page was modified at 4 2177623762
ibbackup: Scanned log up to lsn 4 2177623762
ibbackup: Was able to parse the log up to lsn 4 2177623762
ibbackup: Maximum page number for a log record 0
ibbackup: Compressed 246 MB of data files to 51 MB (compression 79%).
040127 17:12:49 ibbackup: Full backup completed!
The backup directory for the database test is shown
below. The .ibz files are compressed per-table
datafiles.
$ ls /sqldata-backups/test
alex1.ibz alex2.ibz alex3.ibz blobt3.ibz
6.3 Restoring a single .ibd file
A table with a table-specific tablespace (stored in an .ibd
file) can be restored individually without taking down the MySQL
server. If you have a “clean” backup of an .ibd file, you
can restore it to the MySQL installation from which it originated as
follows:
Block writes to the table to be restored. This prevents users from
modifying the table when the restore is in progress.
LOCK TABLES tbl_name WRITE;
Issue this ALTER TABLE statement:
ALTER TABLE tbl_name DISCARD TABLESPACE;
Caution: This deletes the current .ibd file.
Put the backup .ibd file back in the proper database directory.
Issue this ALTER TABLE statement:
ALTER TABLE tbl_name IMPORT TABLESPACE;
Restore is now complete and the write lock can be released:
UNLOCK TABLES;
In this context, a “clean” .ibd file backup means:
There are no uncommitted modifications by transactions in the .ibd
file.
There are no unmerged insert buffer entries in the .ibd file.
Purge has removed all delete-marked index records from the .ibd file.
mysqld has flushed all modified pages of the
.ibd file from the buffer pool to the file.
You can make such a clean backup .ibd file with the following method:
Stop all activity from the mysqld server and commit all
transactions.
Wait until SHOW INNODB STATUS shows that there are no
active transactions in the database, and the main thread status of
InnoDB is Waiting for server activity. Then
you can make a copy of the .ibd file.
Another method for making a clean copy of an .ibd file is to
use ibbackup:
Use ibbackup to back up the InnoDBinstallation.
Run ibbackup –apply-log to create a consistent version of the
backup database.
Start a second (dummy) mysqld server on the backup and
let it clean up the .ibd files in the backup. Wait for the
cleanup to end.
Shut down the dummy mysqld server.
Take a clean .ibd file from the backup.
It is in the TODO to also allow moving clean .ibd files to
another MySQL installation. This requires resetting of transaction IDs
and log sequence numbers in the .ibd file.
7 Known Bugs
Please refer to the InnoDB Hot Backup version history in
href=”#history”>Section 9 for a list of fixed ibbackup and
innobackup bugs.
7.1 Linux-2.4.18 kernel/driver bugs
An InnoDB Hot Backup user reported that in a 2-way Dell computer
with a Red Hat kernel 2.4.18 concurrent running of mysqld and
ibbackup could cause mysqld to crash. Crashes did
not happen in a non-Dell computer. An upgrade to a Linux stock
kernel 2.4.20 fixed the problem.
7.2 Perl bugs
Perl 5.8.0 has problems with regular expressions in UTF-8
locales. As a result of this innobackup cannot read MySQL
options files. Below is a sample run of innobackup
demonstrating the problem:
# perl innobackup --user=admin --compress /etc/my.cnf /storage/test
InnoDB Backup Utility v1.0-beta7; Copyright 2003 Innobase Oy
This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
innobackup: Using mysql Ver 12.22 Distrib 4.0.16, for pc-linux (i686)
Malformed UTF-8 character (unexpected continuation byte 0xb7, with no
preceding start byte) in split at innobackup line 703.
Malformed UTF-8 character (unexpected continuation byte 0xb7, with no
preceding start byte) in split at innobackup line 703.
Malformed UTF-8 character (unexpected continuation byte 0xb7, with no
preceding start byte) in split at innobackup.pl line 703.
innobackup: Using InnoDB Hot Backup version 2.0-beta4
innobackup:: Warning: Ignored unrecognized line 19 in options file
‘/etc/my.cnf’: ‘port = 3306′
innobackup:: Warning: Ignored unrecognized line 20 in options file
‘/etc/my.cnf’: ’socket = /tmp/mysql.sock’
innobackup:: Warning: Ignored unrecognized line 26 in options file
‘/etc/my.cnf’: ‘bind-address=10.10.10.25′
innobackup:: Warning: Ignored unrecognized line 27 in options file
‘/etc/my.cnf’: ‘replicate-ignore-db=ads’
This problem can be solved by upgrading to Perl 5.8.1 or Perl
5.8.3. Another quick fix is to set the LANG environment variable to ‘en_US’ rather than ‘en_US.UTF-8′. Note also that Perl releases prior to 5.8.0 do not have this
problem.
7.3 Known ibbackup and innobackup bugs
If you take a backup when there are TEMPORARY tables
in the database, and you use those temporary tables to update or insert
into normal tables, then the MySQL binlog application to a backup can fail.
That is, you may not be able to roll forward the backup using the MySQL binlog.
The reason is that TEMPORARY tables are not copied to the backup. And, actually
we cannot copy them to the backup, because the names of temporary table files
#sql….frm do not correspond to the logical table names that MySQL writes to
the binlog. This problem will be removed in the future, when MySQL will
implement so-called ‘row-level binlogging’.
MySQL versions < 4.1.2 do not prevent CREATE TABLE or
RENAME TABLE during the locked phase. Therefore, there is
a small chance that the .frm files of newly created empty
tables do not correspond to the binlog position of the backup.
innobackup cannot back up HEAP, or
BDB type tables.
Currently, innobackup requires that
innodb_data_file_pathcontains only plain files, not paths.
That is, specifications like innodb_data_file_path=/dir1/ibdata1:100M
will not work.
7.4 MySQL bugs affecting innobackup
A Bug in MySQL 4.0.22 and 4.1.7 (
href=”http://bugs.mysql.com/bug.php?id=6732″>Bug #6732)
Warning: a CRITICAL bug in MySQL 4.0.22 and 4.1.7 causes the
2nd run of the innobackup Perl script to freeze
the whole mysqld server. The bug was introduced in
FLUSH TABLES WITH READ LOCK. The bug does not affect
pure ibbackup.
This bug has been fixed in innobackup version 1.1.0. The bug
fix has a little flaw in that there is a short time period in the
backup run during which MyISAM table operations may cause
innobackup to report a wrong binlog position to the user. The
time period is between COMMIT and FLUSH TABLES WITH
READ LOCK SQL statements which are issued by mysql_lockall
subroutine of the innobackup script. This bug fix flaw
affects only MySQL versions 4.0.22 and 4.1.7.
A Bug in MySQL 4.0.21 (
href=”http://bugs.mysql.com/bug.php?id=5949″>Bug #5949)
A bug in MySQL version 4.0.21 affects innobackup backup
operation. During a backup run innobackup issues the
following SQL commands to MySQL client to temporarily lock
all database tables:
USE mysql;DROP TABLE IF EXISTS ibbackup_binlog_marker;CREATE TABLE ibbackup_binlog_marker(a INT) ENGINE=INNODB;
SET AUTOCOMMIT=0;
INSERT INTO ibbackup_binlog_marker VALUES (1);
FLUSH TABLES WITH READ LOCK;
COMMIT;
A bug in MySQL server (mysqld) may cause the commit to
fail in a server while MySQL client reports success to
innobackup script. If this happens, the locking of all
database tables fails and the backup created by innobackup
may be broken! In this case both BEGIN and COMMIT are labeled with
error code 1223 (’Can’t execute the query because you have a
conflicting read lock’) in the MySQL binlog, though they execute
without any errors in the MySQL client.
This bug has been fixed in MySQL versions 4.0.23 and 4.1.8. So this problem
can be solved simply by upgrading from MySQL version 4.0.21 to version
4.0.23 or to version 4.1.8.
7.5 Corruption problems
What to do if ibbackup refuses to run because a data file page is
corrupt?
Sometimes the operating system or the hardware can corrupt a data file
page. If the page is not used in InnoDB tables, then mysqld
may run ok. But when you try to back up the tablespace with
ibbackup, you get errors like below and ibbackup
refuses to make the backup:
ibbackup: Re-reading page at offset 0 3185082368 in /sqldata/mts/ibdata15ibbackup: Re-reading page at offset 0 3185082368 in /sqldata/mts/ibdata15ibbackup: Error: page at offset 0 3185082368 in /sqldata/mts/ibdata15 seems corrupt!
Below is a link to a little C program,
innodb_page_checksum_reset.c, that you can use to reset the
lsn and the checksum fields in one page, so that ibbackup
will no longer complain. Of course, your main task is to find out
what is wrong with the OS or the hardware that causes corrupt pages to
appear.
Download
innodb_page_checksum_reset.c source code from this
link
The sample program will reset page 22357 in a datafile
ibdata1. You should edit these values according to your
problem case.
To compile on Linux:
$ gcc -o ibreset innodb_page_checksum_reset.c
If your data file is larger than 2 GB, you should compile with large
file support:
The command should produce an executable file called
ibreset.
7.6 Compatibility with older MySQL/InnoDB
versions
From time to time changes are made to the format of data and log
files of MySQL/InnoDB. These changes may make older InnoDB Hot Backup
versions incompatible with the new MySQL/InnoDB version.
Currently there are five major MySQL/InnoDB versions: 3.23 (first
released in May 12, 2001), 4.0 (December 23, 2001), 4.1 (April 3,
2003), 5.0 (December 24, 2003), and 5.1 (November 29, 2005).
MySQL/InnoDB 3.23 requires InnoDB Hot Backup 1.40 or newer.
MySQL/InnoDB 4.0 requires InnoDB Hot Backup 1.40 or newer.
MySQL/InnoDB 4.1 requires InnoDB Hot Backup 2.0 or newer.
MySQL/InnoDB 5.0 requires InnoDB Hot Backup 3.0 or newer.
MySQL/InnoDB 5.1 requires InnoDB Hot Backup 3.0 or newer.
InnoDB Hot Backup 2.0 works with every MySQL/InnoDB version up to
MySQL/InnoDB 4.1, but it is not compatible with MySQL/InnoDB 5.0 or
newer.
InnoDB Hot Backup 3.0 is compatible with every MySQL/InnoDB version
from 3.23 to 5.1.
IMPORTANT: MySQL/InnoDB 5.0 requires InnoDB Hot Backup 3.0
or newer.
8 InnoDB Hot Backup TODO list
2006 Q2:
Add support for remote backup to ibbackup.
Long term:
Store also the .frm files and the MySQL system tables inside
the InnoDB tablespace. Taking hot backups becomes easier.
9 Software version history
9.1 InnoDB Hot Backup (ibbackup)
InnoDB Hot Backup (ibbackup) makes a consistent snapshot
of InnoDB tables in a live MySQL database.
InnoDB Hot Backup 3.0.0 for Solaris 10 on AMD64, June 21, 2007
InnoDB Hot Backup 3.0.0 is released for Solaris 10 on AMD64
This is the first InnoDB Hot Backup release for Solaris 10 on AMD64
which is compatible with MySQL/InnoDB 5.0. This version is also
compatible with all older InnoDB/MySQL versions down to 3.23. This
is a 64-bit AMD x86-64 binary using shared libraries.
InnoDB Hot Backup 3.0.0 for Linux on PowerPC, December 14, 2006
InnoDB Hot Backup 3.0.0 is released for Linux on PowerPC.
This is the first InnoDB Hot Backup release for Linux on PowerPC
which is compatible with MySQL/InnoDB 5.0. This version is also
compatible with all older InnoDB/MySQL versions down to 3.23. This
is a 32-bit application which can run also on a 64-bit system if the
32-bit compatibility mode is enabled.
InnoDB Hot Backup 3.0.0 for Solaris 10 on x86, June 29, 2006
InnoDB Hot Backup 3.0.0 is released for Solaris 10 on x86
This is the first InnoDB Hot Backup release for Solaris 10 on 86
which is compatible with MySQL/InnoDB 5.0. This version is also
compatible with all older InnoDB/MySQL versions down to 3.23. This
is a 32-bit x86 binary compatible with both the 32-bit MySQL server (x86)
and the 64-bit MySQL server (AMD64/Intel EM64T).
InnoDB Hot Backup 3.0.0 for HP-UX 11, June 15, 2006
InnoDB Hot Backup 3.0.0 is released for HP-UX 11 on
PA-RISC1.1. This is the first InnoDB Hot Backup release for HP-UX 11
which is compatible with MySQL/InnoDB 5.0. This version is also
compatible with all older InnoDB/MySQL versions down to 3.23. This
binary is compatible with all PA-RISC 1.1 and 2.0 workstations and
servers.
InnoDB Hot Backup 3.0.0 for FreeBSD 5.x, March 21, 2006
InnoDB Hot Backup 3.0.0 is released for FreeBSD 5.x on x86. This
is the first InnoDB Hot Backup release for FreeBSD 5.x which is
compatible with MySQL/InnoDB 5.0. This version is also compatible
with all older InnoDB/MySQL versions down to 3.23.
InnoDB Hot Backup 3.0.0 for Linux on x86 and Linux on AMD64,
February 21, 2006
InnoDB Hot Backup 3.0.0 is released for Linux on x86 and for
Linux on AMD64 (=x86_64). This is the first stable 3.0 release for these
platforms. This release is compatible with MySQL/InnoDB 5.0 and all
older InnoDB/MySQL versions down to 3.23.
Bug fix: This version fixes a bug (introduced in 3.0-beta2) which
caused restore to crash occasionally under very heavy load.
InnoDB Hot Backup 3.0-beta3 for Windows, February 9, 2006
InnoDB Hot Backup 3.0-beta3 is released for Windows (as Win32
executable). This is the first InnoDB Hot Backup release for Windows
which is compatible with MySQL/InnoDB 5.0. This version is also
compatible with all older InnoDB/MySQL versions down to 3.23.
InnoDB Hot Backup 3.0-beta2 for AIX 4.3 and AIX 5.2, January 26, 2006
InnoDB Hot Backup 3.0-beta2 is released for IBM AIX 4.3 and IBM
AIX 5.2 on PowerPC (RISC System/6000 V3.1). This is the first InnoDB
Hot Backup release for AIX which is compatible with MySQL/InnoDB
5.0. This version is also compatible with all older InnoDB/MySQL
versions down to 3.23.
Bug fix: These binaries are built with 64-bit file API, which
supports InnoDB data and log files larger than 2 GB. The previous
2.0.0 AIX binaries were incorrectly built with 32-bit file API, which
limited InnoDB data and log files to 2 GB.
InnoDB Hot Backup 3.0-beta2 for Solaris 8 and Solaris 10 on
SPARC, January 13, 2006
InnoDB Hot Backup 3.0-beta2 is released for Solaris 8 and Solaris
10 on SPARC. This is the first InnoDB Hot Backup release for Solaris
which is compatible with MySQL/InnoDB 5.0. This version is also
compatible with all older InnoDB/MySQL versions down to 3.23.
InnoDB Hot Backup 3.0-beta2 for Linux on x86, November
25, 2005
InnoDB Hot Backup 3.0-beta2 is released for Linux on x86.
This is the first InnoDB Hot Backup release which is compatible with
MySQL/InnoDB 5.0. This version is also compatible with all older
InnoDB/MySQL versions down to 3.23.
InnoDB Hot Backup 2.0.1 for Solaris 10 on SPARC and x86, November
15, 2005
InnoDB Hot Backup 2.0.1 is released for Solaris 10 on SPARC
version 1 and Solaris 10 on x86. The Solaris 10 on SPARC binary is a
32-bit application. The Solaris 10 on x86 binary is a 32-bit application
that works also on 64-bit Solaris 10 on AMD64.
InnoDB Hot Backup 2.0.1, February 15, 2005
This is the first 2.0 version that has been ported to Windows.
Print progress of data file copying at each 100 MB.
Only wait 5 seconds before starting the copying of data files.
InnoDB Hot Backup 2.0.0 for OS X, January 21, 2005
InnoDB Hot Backup 2.0.0 is released for Apple Mac OS X 10 on powerPC.
The release was built and tested on Darwin 6.8 kernel.
InnoDB Hot Backup 2.0.0 for HP-UX 11 and Solaris 8, January 4, 2005
InnoDB Hot Backup 2.0.0 is released for HP-UX 11 on PA-RISC1.1
and Solaris 8 on SPARC. The HP-UX binary is a 32-bit application, which
works also on 64-bit HP-UX 11. The Solaris 8 version works also on
Solaris 9. It is a 32-bit application that works also on 64-bit
Solaris SPARC.
InnoDB Hot Backup 2.0.0, May 14, 2004
This is the first stable 2.0 version of ibbackup. The
2.0.0 version is essentially the same as 2.0-beta5. Compared to the
version 1.40, the version 2.0.0 has two new features: support for
the MySQL feature of
href=”http://dev.mysql.com/doc/mysql/en/Multiple_tablespaces.html”>multiple tablespaces, and,
the new –include option, which allows partial backups of
databases.
With the introduction of the version 2.0.0 ibbackup
starts to use GNU version numbering scheme. In this scheme the
version number is of the form
major.minor.patch, where each field
is a decimal number. The major version number only increases with
time. The other two fields are reset to 0 when the field to their
left is incremented; otherwise, they, too, only increase with
time. (So, version 2.6.2 is newer than version 2.5.8, and version 3.0.0
is newer than both.) The minor version number is increased when a new
feature is added within a major version. The patch number is
increased for each bug fix release.
This version is available for Linux.
InnoDB Hot Backup 2.0-beta5, March 16, 2004
Fixed a bug: ibbackup now keeps scanning the log files also
during the ’suspended’ phase. Earlier, if it took the innobackup Perl script a long time to copy the MyISAM tables, or there were long-running
queries, so that FLUSH TABLES WITH READ LOCK took a long time
to execute, it could happen that InnoDB had already circularly written over
its log files, and ibbackup said that the backup failed.
InnoDB Hot Backup 2.0-beta4, February 17, 2004
ibbackup now supports the MySQL feature of
href=”http://dev.mysql.com/doc/mysql/en/Multiple_tablespaces.html”>multiple tablespaces.
Partial backups of a database can be made by specifying the new
–include option.
InnoDB Hot Backup 1.40, June 24, 2003
Bug fixes
The page checksum formula was corrected. In combination with
MySQL/InnoDB versions >= 4.0.14 and > 4.1.1,
ibbackup will be more reliable. This ibbackup release also works with older versions of MySQL/InnoDB.
An evaluation version for Apple Mac OS X is available.
InnoDB Hot Backup 1.31, May 22, 2003
–compress now uses about 60 % less CPU time. You can
specify a numeric parameter 1…9 after the
–compress keyword if you want better compression
(9 means better, but slower compression).
In Windows, you can now have spaces in the
class="cnf">innodb_data_home_dir directory string
without a need to put the double quotes around the
string. An example:
class="cnf">innodb_data_home_dir=C:\Program files\mysql
InnoDB Hot Backup 1.22, May 2, 2003
An evaluation version for QNX6 is available.
A new ibbackup license generator system was introduced.
This change is transparent to users; only the internal code of
ibbackup was changed.
InnoDB Hot Backup 1.21, April 16, 2003
Added new options: –compress and –uncompress.
InnoDB Hot Backup 1.05, March 18, 2003
Added a new option: –suspend-at-end. You can use the
option to write a script which flushes and copies your MyISAM tables
when ibbackup is finishing its job. In that way, you can
take a snapshot of your MyISAM tables at the same precise timepoint
as the InnoDB backup.
InnoDB Hot Backup 1.04, November 9, 2002
Bug fixes
When parsing the log, ibbackup checks also that the
page numbers in log records are within the size of the tablespace.
Note: the combined log file size in InnoDB versions < 3.23.55
or < 4.0.9 must be < 2 GB for ibbackup to work. This
is due to a bug in earlier InnoDB versions.
An evaluation version for HP-UX 10.20 is available.
InnoDB Hot Backup 1.03b for FreeBSD, September 28, 2002
An evaluation version for FreeBSD is available.
InnoDB Hot Backup 1.03b, September 4, 2002
Bug fixes
A glitch in the usage of sprintf of
ibbackup was fixed.
This may remove the problem with libsafe mentioned in
the installation notes.
InnoDB Hot Backup 1.03, August 1, 2002
If you run >= MySQL-3.23.52 or >= MySQL-4.0.3, you have to
upgrade to this ibbackup version because the log format
changes in InnoDB-3.23.52.
InnoDB now stores a checksum to each log block so that it
notices possible log file write/read errors. ibbackup
understands also the log format of older InnoDB versions. Thus you
do not need to upgrade your MySQL server if you use 1.03.
ibbackup now prints timestamps when a backup is taken.
You can compare the end timestamp to the last modification times of
the .frm files in the .frm file backup you take
after ibbackup finished its work. In that way you can
determine if the .frm files correspond to the snapshot of
backed up InnoDB tables.
ibbackup –restore now prints the the binlog position
which corresponds to the snapshot in the backup.
For extra safety, ibbackup now parses the copied log
already during the backup process. This checks beforehand that the
log scan and parse in –restore will succeed.
Bug fixes
(This fix is already in version 1.02b.) If you restored a
backup, started mysqld on it, and tried to take another
backup of this restored database, then ibbackup failed
complaining it cannot scan the log files far enough. This failure
happened until the log files had been written full again and the
header of the first log file was rewritten by mysqld.
If the last data file was specified as auto-extending,
ibbackup would still truncate the unused end from the data
file. This can confuse auto-extension when the backup is taken
into production.
One user has reported repeated log corruption warnings on Linux
when running –restore on a 120 GB database. The reason not
known yet. If the problem is in Linux file i/o, the checksums in log
blocks in InnoDB-3.23.52 should fix the problem.
InnoDB Hot Backup 1.02, June 7, 2002
Bug fixes
A potential crash in –restore was eliminated.
InnoDB Hot Backup 1.01, May 18, 2002
ibbackup no longer checks the hostname if you run with
the –restore option. Thus you can use it to restore
backups on any computer. Only when taking a backup the hostname has
to correspond to the license.
If you run >= MySQL/InnoDB-3.23.52, and start mysqld
on a restored backup, it will print a timestamp that indicates when
the log file was created by ibbackup: this is useful in
eliminating mix-ups of log files.
Bug fixes
ibbackup now ignores my.cnf lines after a hash
(comment) character #.
ibbackup now prints an error message if you specify
the log file size in my.cnf differently from the real log
file size ibbackup measures when it copies a log file.
Previously ibbackup reported an assertion failure near
line 259 of back0back.c.
InnoDB Hot Backup 1.00, May 5, 2002
Innobase Oy releases InnoDB Hot Backup as a stable version 1.00.
InnoDB Hot Backup 0.35, April 10, 2002
The auto-extending data file in MySQL/InnoDB-3.23.50 is now
supported.
InnoDB Hot Backup 0.34, March 13, 2002
Bug fixes
Linux large file support was not enabled in version 0.33. If
you have InnoDB data files >= 2 GB, or if you are backing up a
very lively big database where the backup log file
class="cnf">ibbackup_logfile would grow past 2 GB, then you
must upgrade to this version.
InnoDB Hot Backup 0.33, March 8, 2002
This is the first beta release.
9.2 The innobackup script
innobackup is a Perl script which automates the backup of
both MyISAM and InnoDB type tables and .frm files. It is an
adaptable front-end for ibbackup. See
Section 5 for more information.
innobackup-1.4.0, November 29, 2007
Support for triggers and database options files was added (.TRG,
.TRN, and db.opt files). Printouts were improved: now innobackup
prints more timing information about its actions.
Bug fixes
Backup might copy some extra files from the database
subdirectories to the backup.
This version has been tested on Linux with Perl 5.8.8. It might
not work with Perl versions older than 5.005. Note that innobackup
does not work with Perl 5.8.0 in UTF-8 locales, because of bugs in Perl regular
expressions. See section 7.2 for details.
innobackup-1.3.0, July 5, 2005
The –ibbackup command-line option was added. The
–ibbackup option can be used to specify which
ibbackup (InnoDB Hot Backup) binary should be used. By
default innobackup invokes ibbackup with
ibbackup command. This feature was contributed by Frank
Sonntag.
Bug fixes
The –copyback option had a bug, which caused
copy-back to fail to copy all subdirectories of the backup back to
their original locations. The subdirectories with names of length
1 and 2 two characters were incorrectly ignored in the copy-back
operation.
This version has been tested on Linux with Perl 5.005, 5.6.0,
5.6.1, 5.8.0, and 5.8.1. It might not work with Perl versions older
than 5.005. Note that innobackup does not work with Perl
5.8.0 in UTF-8 locales, because of bugs in Perl regular
expressions. See section 7.2 for details.
innobackup-1.2.0, February 1, 2005
The –no-timestamp command-line option was added. By default
innobackup creates a backup directory (named by the current
date and time) in the given backup root directory. With the new
–no-timestamp option it does not create a time-stamped backup
directory, but it puts the backup in the given directory (which must
not exist). This feature was contributed by Bruce Hoult.
Bug fixes
innobackup failed to report MySQL server version
correctly when –user, –password,
–port, or –socket command-line options were
used to specify the user identity and location of the MySQL
server. innobackup either failed to report MySQL server
version or it reported a wrong version (if it connected to some
other server of different version). This bug fix was contributed
by Bruce Hoult.
This version has been tested on Linux with Perl 5.005, 5.6.0,
5.6.1, 5.8.0, and 5.8.1. It might not work with Perl versions older
than 5.005. Note that innobackup does not work with Perl
5.8.0 in UTF-8 locales, because of bugs in Perl regular
expressions. See section 7.2 for details.
innobackup-1.1.0, November 30, 2004
Support for MERGE tables was added. The
.MRG files are now included in the backup and copy-back
operations.
The last MySQL binlog position is written to file
ibbackup_binlog_info in the backup directory. The last
binlog position is also printed to standard output at the end of the
backup run.
The backup operation displays now also MySQL server version
along with MySQL client and InnoDB Hot Backup versions.
Bug fixes
A critical bug in MySQL 4.0.22 and 4.1.7 caused the 2nd
run of the innobackup Perl script to freeze the whole
mysqld server. The bug was introduced in FLUSH
TABLES WITH READ LOCK. The bug fix has a little flaw in that
there is a short time period in the backup run during which MyISAM
table operations may cause innobackup to report a wrong
binlog position to the user. See Section 7.4 for details on the bug.
This version has been tested on Linux with Perl 5.005, 5.6.0,
5.6.1, 5.8.0, and 5.8.1. It might not work with Perl versions older
than 5.005. Note that innobackup does not work with Perl
5.8.0 in UTF-8 locales, because of bugs in Perl regular
expressions. See section 7.2 for details.
innobackup-1.0.0, August 20, 2004
This is the first stable 1.0 version of innobackup. The
1.0.0 version is essentially the same as 1.0-beta10.
This version has been tested on Linux with Perl 5.005, 5.6.0,
5.6.1, 5.8.0, and 5.8.1. It might not work with Perl versions older
than 5.005. Note that innobackup does not work with Perl
5.8.0 in UTF-8 locales, because of bugs in Perl regular
expressions. See section 7.2 for details.
innobackup-1.0-beta10, May 25, 2004
Bug fixes
Now –copy-back option copies InnoDB data files and
InnoDB log files back to their original locations also when InnoDB
data home directory (my.cnf option
class="cnf">innodb_data_home_dir) or InnoDB log home
directory (my.cnf option
class="cnf">innodb_log_group_home_dir) is not the same as
MySQL data directory (my.cnf option
class="cnf">datadir). Previously InnoDB data and log files
were always copied back to the original data directory.
This version has been tested on Linux with Perl 5.005, 5.6.0,
5.6.1, 5.8.0, and 5.8.1. It might not work with Perl versions older
than 5.005. Note that innobackup does not work with Perl
5.8.0 in UTF-8 locales, because of bugs in Perl regular
expressions. See section 7.2 for details.
innobackup-1.0-beta9, April 20, 2004
Bug fixes
Now ibdata files can be in any subdirectory of a
directory tree rooted at InnoDB data home directory. Previously
backup failed if ibdata files did not reside in the InnoDB
data home directory (my.cnf option
class="cnf">innodb_data_home_dir), because
innobackup did not create the subdirectories in the path
from the backup InnoDB data home directory to the backup
ibdata file.
This version has been tested on Linux with Perl 5.005, 5.6.0,
5.6.1, 5.8.0, and 5.8.1. It might not work with Perl versions older
than 5.005. Note that innobackup does not work with Perl
5.8.0 in UTF-8 locales, because of bugs in Perl regular
expressions. See section 7.2 for details.
innobackup-1.0-beta8, April 6, 2004
Bug fixes
Innobackup fails now immediately after ibbackup child
process has encountered a fatal error. Previously innobackup got
stuck in an infinite wait if ibbackup died before
suspending itself.
This version has been tested on Linux with Perl 5.005, 5.6.0,
5.6.1, 5.8.0, and 5.8.1. It might not work with Perl versions older
than 5.005. Note that innobackup does not work with Perl
5.8.0 in UTF-8 locales, because of bugs in Perl regular
expressions. See section 7.2 for details.
innobackup-1.0-beta7, February 17, 2004
Options files can now contain comments starting with
class="cnf"># in the middle of a line (as of MySQL 4.0.14).
Option values can be enclosed in single or double quotes.
Bug fixes
The clean-up of child processes after an error in innobackup
is now working. The ibbackup and mysqld child processes
are now always killed after a failed innobackup
execution. Previously a failed innobackup execution could leave
orphaned or zombie child processes.
Innobackup fails now immediately after an SQL error is reported
by mysql child process. Previously SQL errors were
written only to mysql-stderr file in the backup directory,
and innobackup execution continued after an error.
This version has been tested on Linux with Perl 5.005, 5.6.0,
5.6.1, 5.8.0, and 5.8.1. It might not work with Perl versions older
than 5.005. Note that innobackup does not work with Perl
5.8.0 in UTF-8 locales, because of bugs in Perl regular
expressions. See section 7.2 for details.
innobackup-1.0-beta6, January 13, 2004
Support for partial backup of per-table datafiles was added
(–include option) making this version fully compatible
with ibbackup version 2.0.
The timeout for SQL requests (the
class="perl">mysql_response_timeout parameter in the
innobackup script) was increased to 900 seconds.
innobackup was made to run also on Perl 5.005.
This version has been tested on Linux with Perl 5.005, 5.6.0,
5.6.1, 5.8.0, and 5.8.1. It might not work with Perl versions older
than 5.005.
innobackup-1.0-beta5, January 8, 2004
Bug fixes
The .frm file of the ibbackup_binlog_marker table was not included in the backup. This table is created and used
internally by ibbackup.
innobackup-1.0-beta4, November 11, 2003
Bug fixes
On some platforms, innobackup did not properly flush
the output buffer (MYSQL_WRITER). This
caused a bogus timeout for the connection to mysql. This
bug fix was contributed by Erik R. Agee.
innobackup-1.0-beta3, October 28, 2003
Works currently only on Unix-like systems.
This version has been tested on Linux with Perl 5.6.0, 5.6.1,
5.8.0, and 5.8.1. It might not work with Perl versions older than
5.6.0.
innobackup-1.0-beta2, October 21, 2003
Works currently only on Unix-like systems.
innobackup-1.0-beta, October 20, 2003
Works currently only on Unix-like systems.
innobackup-0.1-alpha, July 9, 2003
Works currently only on Unix-like systems.
10 Installing ibbackup
Installing InnoDB Hot Backup tool, or ibbackup, is very simple. The
ibbackup binary is usually delivered to you by email as a compressed
attachment. Before you can start using ibbackup, you should do the
following:
Copy the compressed ibbackup binary to the host where you want to
install it.
Uncompress the compressed binary and rename it. Usually the
binary is compressed with gzip and has the file extension
.gz. If, for example, the compressed ibbackup binary file is
ibbackupA01698.gz, you can uncompress it and rename it to
ibbackup on Unix as follows:
On Windows you should rename the binary to ibbackup.exe.
On Unix you should set proper file permissions for the
ibbackup binary. Remember to set the execute permission! You
can do this as follows:
$ chmod +x ibbackup$ ls -l ibbackup-rwxr-xr-x 1 heikki dev 1852595 Mar 26 18:24 ibbackup
Put the binary to somewhere in your PATH (PATH
is the shell environment variable that defines directories where the
shell looks for binaries or program files. On Unix you can check the
PATH and put ibbackup in it as follows:
The installation is now complete. Now InnoDB Hot Backup tool can be
launched from the shell with ibbackup command:
$ ibbackup --version InnoDB Hot Backup version 2.0-beta5; Copyright 2003 Innobase OyLicense A00001 is granted to Innobase Oy
…
11 Error codes of InnoDB Hot Backup 3.0
InnoDB Hot Backup (ibbackup) process return code is 0 if the backup or restore run succeeds.If the run fails for any reason, the return code is 1.
If ibbackup fails, because an operating system call fails, ibbackup usually displays the operating systems error code along with a detailed error message.On Linux and other Unix-like systems, the operating system error codes are POSIX error codes. Those POSIX error codes that are possible with ibbackup are shown in table 11.1.
A complete list of all POSIX errors is available in file “/usr/include/sys/errno.h” on your system.
TABLE 11.1: The codes of the operating system errors that are possible with InnoDB Hot Backup
on Linux and other Unix-like systems.
Error code Value Description
EPERM 1 Operation not permitted
ENOENT 2 No such file or directory
ESRCH 3 No such process
EINTR 4 Interrupted system call
EIO 5 I/O error
ENXIO 6 No such device or address
EBADF 9 Bad file number
EAGAIN 11 Try again
ENOMEM 12 Out of memory
EACCES 13 Permission denied
EBUSY 16 Device or resource busy
EEXIST 17 File exists
ENODEV 19 No such device
ENOTDIR 20 Not a directory
EMFILE 24 Too many open files
EFBIG 27 File too large
ENOSPC 28 No space left on device
EROFS 30 Read-only file system
ENAMETOOLONG 36 File name too long
ENODATA 61 No data available
ETIME 62 Timer expired
EBADFD 77 File descriptor in bad state
EDQUOT 122 Quota exceeded
On Windows ibbackup uses Win32 API calls. The Windows System Error codes possible with ibbackup are listed in table 2. A complete list of all Windows System errors is available at http://msdn2.microsoft.com/en-us/library/ms681381%28VS.85%29.aspx
TABLE 11.2: The codes of the operating system errors that are possible with InnoDB Hot Backup on Windows.
Error code Value Description-
ERROR_SUCCESS 0 The operation completed successfully.
ERROR_FILE_NOT_FOUND 2 The system cannot find the file specified.
ERROR_PATH_NOT_FOUND 3 The system cannot find the path specified.
ERROR_TOO_MANY_OPEN_FILES 4 The system cannot open the file.
ERROR_ACCESS_DENIED 5 Access is denied.
ERROR_NOT_ENOUGH_MEMORY 8 Not enough storage is available to
process this command.
ERROR_OUTOFMEMORY 14 Not enough storage is available to
complete this operation.
ERROR_INVALID_DRIVE 15 The system cannot find the drive specified.
ERROR_WRITE_PROTECT 19 The media is write protected.
ERROR_BAD_UNIT 20 The system cannot find the device specified.
ERROR_NOT_READY 21 The device is not ready.
ERROR_SEEK 25 The drive cannot locate a specific
area or track on the disk.
ERROR_WRITE_FAULT 29 The system cannot write to the specified device.
ERROR_READ_FAULT 30 The system cannot read from the
specified device.
ERROR_GEN_FAILURE 31 A device attached to the system is not
functioning.
ERROR_HANDLE_DISK_FULL 39 The disk is full.
ERROR_BAD_NETPATH 53 The network path was not found.
ERROR_DEV_NOT_EXIST 55 The specified network resource or
device is no longer available.
ERROR_FILE_EXISTS 80 The file exists.
Document Revision History
Updated December 14, 2006
Added the change notes for InnoDB Hot Backup 3.0.0 to
9 Software version history. This is the first Linux
on PowerPC version of InnoDB Hot Backup 3.0.
Updated June 29, 2006
Added the change notes for InnoDB Hot Backup 3.0.0 to
9 Software version history. This is the first Solaris
10 on x86 version of InnoDB Hot Backup 3.0.
Updated June 15, 2006
Added the change notes for InnoDB Hot Backup 3.0.0 to
9 Software version history. This is the first HP-UX
11 version of InnoDB Hot Backup 3.0.
Updated March 21, 2006
Added the change notes for InnoDB Hot Backup 3.0.0 to
9 Software version history. This is the first FreeBSD
5.x version of InnoDB Hot Backup 3.0.
Added the change notes for InnoDB Hot Backup 3.0.0 to
9 Software version history. This is the first stable
version of InnoDB Hot Backup 3.0.
Updated February 9, 2006
Added the change notes for InnoDB Hot Backup 3.0-beta3 to
9 Software version history. This is the first Windows
version of InnoDB Hot Backup 3.0.
Updated January 26, 2006
Added the change notes for InnoDB Hot Backup 3.0-beta2 to
9 Software version history. This is the first AIX
version of InnoDB Hot Backup 3.0.
Updated January 13, 2006
Added the change notes for InnoDB Hot Backup 3.0-beta2 to
9 Software version history. This is the first Solaris
version of InnoDB Hot Backup 3.0.
Updated November 17, 2005
Added section 7.6 about the
compatibility of InnoDB Hot Backup with older MySQL/InnoDB versions.
IMPORTANT: MySQL/InnoDB 5.0 requires InnoDB Hot Backup 3.0
or newer.
Updated July 7, 2005
Added section 7.5 about
solving data file corruption problems. The section 7.5 contains a
link to a little C program to reset the lsn and the checksum of a
data file page if ibbackup refuses to back it up because
the page is corrupt.
Added to section 7.4 a link to a
little C program to reset the lsn and the checksum of a data file
page if ibbackup refuses to back it up because the page is corrupt.
Updated February 14, 2005
Added the change notes for InnoDB Hot Backup 2.0.1 to
9 Software version history. This is the first Windows
version of InnoDB Hot Backup 2.0.
Support