Backing Up and Restoring InnoDB Tables

It is currently impossible to do a standard online backup of an InnoDB table while the server is running with the standard distribution. This will be changing soon, though, so keep an eye on the MySQL documentation.

There is a paid-for tool that allows online backup of InnoDB tables, called InnoDB HotBackup. Go to www.innodb.com/hotbackup.html for details.

Ordinarily, to make a backup, you need to either take the database server down or shut out access from clients. There are two main ways to back up, and for critical data you should use both methods. One is to use mysqldump (the same as for MyISAM tables), with no write access permitted for the duration of the backup. This creates a text file with the SQL statements needed to restore the tables. The second is to make copies of the binary database files. To do this, you need to shut down the database without any errors and then copy the data files, InnoDB log files, configuration file (my.cnf or my.ini file), and the definition files (.frm) to a safe place:

% mysqladmin shutdown % ls -l total 76145 drwx------   2 mysql    mysql        2048 Jun  1 21:01 firstdb -rw-rw----   1 mysql    mysql       25088 May  4 20:08 ib_arch_log_0000000000 -rw-rw----   1 mysql    mysql     5242880 Jun  1 21:04 ib_logfile0 -rw-rw----   1 mysql    mysql     5242880 May  4 20:08 ib_logfile1 -rw-rw----   1 mysql    mysql    67108864 Jun  1 21:04 ibdata1 drwxrwx---   2 mysql    mysql        1024 May  4 20:07 mysql drwxrwx---   2 mysql    mysql        1024 Dec 23 17:44 test -rw-rw----   1 mysql    mysql          98 May 19 15:03 test-bin.001 -rw-rw----   1 mysql    mysql       30310 Jun  1 21:04 test-bin.002 -rw-rw----   1 mysql    mysql          30 May 19 15:09 test-bin.index -rw-r--r--   1 mysql    mysql        7292 Jun  1 21:04 test.dummymysql.co.za.err

You should copy all the files from the data directory starting with ib, as these are the InnoDB logs and data. For instance:

 % cd /usr/local/mysql/data/ % cp ib*/db_backups/ 

Now copy the configuration files (remember to copy them all if you have more than one):

 % cp /etc/my.cnf /db_backups/ 

Then copy the definition files, in this case innotest inside the firstdb directory (all definition files, as well as MySQL data and index files, exist inside a directory with the same name as the database):

 % cp firstdb/innotest.frm /db_backups/ 

Now, let's restart the server in order so that a malicious user can destroy the data:

 % mysqld-max % Starting mysqld daemon with databases from /usr/local/mysql/data % mysql firstdb mysql> TRUNCATE innotest; Query OK, 11 rows affected (0.00 sec)

All the data has been deleted. Your phone will soon start ringing, and it's time to restore the backup. Once again you need to bring down the server to prevent interference:

 % mysqladmin shutdown 020601 21:20:34  mysqld ended % cp /db_backups/ib* /usr/local/mysql/data/ cp: overwrite '/usr/local/mysql/data/ib_arch_log_0000000000'? y cp: overwrite '/usr/local/mysql/data/ib_logfile0'? y cp: overwrite '/usr/local/mysql/data/ib_logfile1'? y cp: overwrite '/usr/local/mysql/data/ibdata1'? y 

There's no need in this case to restore the configuration or definition files, as these have remained unscathed. In the case of hardware failure, you would have to restore these as well:

 % mysqld-max % Starting mysqld daemon with databases from /usr/local/mysql/data % mysql firstdb mysql> SELECT * FROM innotest; +------+------+ |   f1 | f2   | +------+------+ |    1 | NULL | |    2 | NULL | |    3 | NULL | |    4 | NULL | |    5 | NULL | |    6 | NULL | |    7 | NULL | |    8 | NULL | |    9 | NULL | |   10 | NULL | +------+------+ 10 rows in set (0.12 sec)

The data has been successfully restored.

In the case of a server crash to restore InnoDB data you simply need to restart the server. If general logging and log archiving are on (which is recommended), the InnoDB tables will automatically restore themselves from the MySQL logs (the MySQL logs are the "ordinary" logs, not the InnoDB logs). Any uncommitted transactions present at the time of the crash will be rolled back. The output will look similar to this:

InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 24785115 InnoDB: Doing recovery: scanned up to log sequence number 0 24850631 InnoDB: Doing recovery: scanned up to log sequence number 0 24916167 InnoDB: 1 uncommitted transaction(s) which must be rolled back InnoDB: Starting rollback of uncommitted transactions InnoDB: Rolling back trx no 982 InnoDB: Rolling back of trx no 98 completed InnoDB: Rollback of uncommitted transactions completed InnoDB: Starting an apply batch of log records to the database... InnoDB: Apply batch completed InnoDB: Started mysqld: ready for connections 

Tip 

InnoDB files are not as portable as MyISAM files. They can only be used on other platforms if that machine has the same floating-point number format as the machine on which they were generated. This means, for example, you can move the files between Intel x86 machines, no matter what operating systems you're using.



Mastering MySQL 4
Mastering MySQL 4
ISBN: 0782141625
EAN: 2147483647
Year: 2003
Pages: 230
Authors: Ian Gilfillan

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net