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:
datadir=… innodb_data_home_dir=… innodb_data_file_path=… innodb_log_group_home_dir=… innodb_log_files_in_group=… innodb_log_file_size=…
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:
[mysqld] datadir = /home/heikki/data innodb_data_home_dir = /home/heikki/data innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /home/heikki/data set-variable = innodb_log_files_in_group=2 set-variable = innodb_log_file_size=20M
You want to take a backup to directory /home/heikki/backup. Then my2.cnf should be like the following:
datadir = /home/heikki/backup innodb_data_home_dir = /home/heikki/backup innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /home/heikki/backup set-variable = innodb_log_files_in_group=2 set-variable = innodb_log_file_size=20M
Note that ibbackup never 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.
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.
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 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 /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
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 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 /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
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:
FLUSH TABLES WITH READ LOCK;UNLOCK TABLES;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.
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
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
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 872M -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.
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.cnf 040122 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
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.
skip-slave-start to the [mysqld] section.… 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.
CHANGE MASTER SQL command on the slave to init it
right. For example:
CHANGE MASTER TO MASTER_LOG_FILE='hundin-bin.006', MASTER_LOG_POS=128760128;
SLAVE START
SQL command.skip-slave-start from
the my.cnf file of the slave.Let us assume a master database gets corrupt.
log-bin in it so that the slaves do not
receive twice the binlog needed to recover the master.mysql> STOP SLAVE;
$ 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.
$ mysqlbinlog --position=5585832 mysqldatadir/omnibook-bin.002 | mysql $ mysqlbinlog /mysqldatadir/omnibook-bin.003 | mysql
log-bin.mysql> START SLAVE;
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 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:
SELECT or other SQL queries at
the time of the backup run, andThen 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 link
innobackup 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:
$ perl innobackup --user=dba --password=xyz --compress /etc/my.cnf /backups
Or, if we rely on the operating system to find us a Perl interpreter:
$ innobackup --user=dba --password=xyz --compress /etc/my.cnf /backups
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 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 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 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!".
$ perl innobackup --help
Usage:
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.
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 under
the 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.
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 Oy This 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.
$ cat /home/pekka/.my.cnf /backups/2004-02-03_13-27-09/backup-my.cnf >\ /backups/2004-02-03_13-27-09/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.
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
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)
When the multiple
tablespaces feature is enabled, it is possible to make a partial
backup of the InnoDB tables. The 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 multiple tablespaces feature
is not enabled, are stored as usual in the system tablespace defined
by the innodb_data_file_path and 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 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
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.
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
. The contents of the database directory for
ibtest 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).
$ ls /sqldata/mts/test alex1.frm alex2.ibd blobt3.frm ibstest0.ibd ibtest11a.frm ibtest11d.frm alex1.ibd alex3.frm blobt3.ibd ibtest09.frm ibtest11b.frm alex2.frm alex3.ibd ibstest0.frm ibtest09.ibd ibtest11c.frm
We run ibbackup with the --include option.
$ ibbackup --include 'test\.ib.*' /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: '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
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
or
alex
. The contents of the database directory for
blobtest database is shown below.
$ ls /sqldata/mts/test alex1.frm alex2.ibd blobt3.frm ibstest0.ibd ibtest11a.frm ibtest11d.frm alex1.ibd alex3.frm blobt3.ibd ibtest09.frm ibtest11b.frm alex2.frm alex3.ibd ibstest0.frm ibtest09.ibd ibtest11c.frm
We run 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
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:
LOCK TABLES tbl_name WRITE;
ALTER TABLE statement:
ALTER TABLE tbl_name DISCARD TABLESPACE;Caution: This deletes the current .ibd file.
ALTER TABLE statement:
ALTER TABLE tbl_name IMPORT TABLESPACE;
UNLOCK TABLES;
In this context, a clean
.ibd file backup means:
You can make such a clean backup .ibd file with the following method:
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:
InnoDB
installation.
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.
Please refer to the InnoDB Hot Backup version history in Section 9 for a list of fixed ibbackup and innobackup 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.
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 under the 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 environment variable LANG=C to override the default UTF-8 locale. Note also that Perl releases prior to 5.8.0 do not have this problem.
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_path contains only plain files, not paths.
That is, specifications like innodb_data_file_path=/dir1/ibdata1:100M
will not work.
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 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.
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/ibdata15 ibbackup: Re-reading page at offset 0 3185082368 in /sqldata/mts/ibdata15 ibbackup: 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:
$ gcc -D_XOPEN_SOURCE=600 \ -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE \ -o ibreset innodb_page_checksum_reset.c
The command should produce an executable file called ibreset.
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.
InnoDB Hot Backup (ibbackup) makes a consistent snapshot of InnoDB tables in a live MySQL database.
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_data_home_dir directory string
without a need to put the double quotes around the
string. An example: innodb_data_home_dir=C:\Program files\mysqlsprintf of
ibbackup was fixed.
#.ibbackup_logfile would grow past 2 GB, then you
must upgrade to this version.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.
CREATE TABLE with
deprecated TYPE syntax.db_name[.table_name]
db_name1[.table_name1] …. If
this option is not specified, all databases containing MyISAM and
InnoDB tables will be backed up. In case the list is very long,
this can be specified in a file and the full path of the file can be
specified instead of the list. This feature was contributed by Paddy
Sreenivasan of Zmanda, Inc. (paddy at zmanda.com).CHANGE MASTER command. A new slave for this
master can be set up by starting a slave server on this backup and
issuing a CHANGE MASTER command with the binary log
position saved in the ibbackup_slave_info file. This
feature was contributed by Patrick "CaptTofu" Galbraith
(patg at patg.net).MERGE tables was added. The
.MRG files are now included in the backup and copy-back
operations. 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.innodb_data_home_dir) or InnoDB log home
directory (my.cnf option innodb_log_group_home_dir) is not the same as
MySQL data directory (my.cnf option datadir). Previously InnoDB data and log files
were always copied back to the original data directory.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. # in the middle of a line (as of MySQL 4.0.14).
Option values can be enclosed in single or double quotes.mysql_response_timeout parameter in the
innobackup script) was increased to 900 seconds.ibbackup_binlog_marker
table was not included in the backup. This table is created and used
internally by ibbackup.MYSQL_WRITER). This
caused a bogus timeout for the connection to mysql. This
bug fix was contributed by Erik R. Agee.Installing InnoDB Hot Backup tool, or ibbackup, is very simple. The ibbackup binary is usually delivered to you by email as a download link. Before you can start using ibbackup, you should do the following:
$ mv ibbackupA01698 ibbackupOn 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
$ echo $PATH /home/heikki/bin:/usr/local/bin:/usr/bin:/usr/X11R6/bin:/bin $ cp ibbackup /home/heikki/bin
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 Oy License A00001 is granted to Innobase Oy …
The return code of the InnoDB Hot Backup (ibbackup) process 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 1. A complete list of all POSIX errors is available in the file /usr/include/errno.h on your system.
| 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 Microsoft 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(VS.85).aspx.
| 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. |
InnoDB is a trademark of Innobase Oy. Use of the trademark InnoDB is prohibited without a permission from Innobase Oy.