Mastering MySQL 4 [Electronic resources] نسخه متنی

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/hotbackupl 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