15.7 Backup and Recovery

It's happened to all of us: your hard disk crashes, your machine dies, somebody steals your box, or you get horribly hacked. In these cases, the only way to recover your application is to restore a backup copy of your database (after you've reinstalled MySQL!). There are also less catastrophic events that can occur from which you need to recover: indexes and tables can become corrupt because of a power failure or MySQL unexpectedly dying, your operating system might crash, or a disk may become unreliable.

To protect against catastrophic events, you should make regular backups and store these offsite. There are many different ways you can backup your MySQL installation, and different ways you can automate the process. The simplest technique is to automate the dumping of your database as SQL statements into a file using the mysqldump utility, and this is the approach we focus on in this section.

In a Unix environment, you can also use mysqlhotcopy to do backups; it's a Perl script that works only for MyISAM tables and is described in more detail in Section 4.8.7 of the MySQL manual. Other approaches you can use in all environments are the BACKUP TABLE and RESTORE TABLE statements from within the command interpreter or a PHP script, and simply copying the database files when the database is offline. We don't discuss these approaches here.

All backup techniques have in common that they result in one or more files that are the backup of the database. You could burn these files onto a CD or other media (if they'll fit), copy them across a network to a backup server, or use a tape or removable disk backup unit to make a copy (and perhaps also backup other user data and the operating system). Ideally, you should then take the backup offsite.

To recover from less catastrophic events, such as a power failure, MySQL has utilities for repairing tables. We also discuss these in this section.

15.7.1 Backup

The simplest way to backup all of your databases is to run the following command in a Unix environment from a shell prompt:

% /usr/local/mysql/bin/mysqldump -uroot -ppassword --all-databases 

  --opt > /tmp/backup

This writes the backup to the file /tmp/backup.

In Microsoft Windows, choose the Run option in the Start menu and type:

"C:\Program Files\EasyPHP1-7\mysql\bin\mysqldump.exe" -uroot -ppassword

  --all-databases --opt > c:\windows\temp\backup

This writes the backup to the file C:\windows\temp\backup.

Leave your MySQL server running while you issue these commands. Both commands assume you've followed our installation instructions in Appendix A through Appendix C.

These commands write everything you need into the backup file and you can then copy this file elsewhere for safe keeping; you could automate the backup and copying process using the techniques discussed in Section 15.5. If you inspect the backup file, you'll find it's the SQL statements that create and insert all of the databases, tables, and data that's in your MySQL installation.

The --opt option to mysqldump writes a file that's fast to load when restored, and it also locks all tables in a database before dumping it (and so avoids concurrency issues within a database). If you want to lock all tables across all databases then add the --first-slave option to the mysqldump command; we don't recommend this if your application is online, and it isn't necessary if your applications have only one database each.

15.7.2 Selective Backups

The winestore database that you've loaded into your MySQL installation is stored in the file winestore.database. The file was created by dumping the data from our MySQL server using mysqldump (and then neatening up the file so it's organized a little better). We dumped it initially with MySQL 3.23, and we've maintained it manually since.

You can dump individual databases using the mysqldump command line utility. For example, to dump the winestore database to the file ws-dump in a Unix environment, you can use:

% /usr/local/mysql/bin/mysqldump --opt -uroot -ppassword

  --databases winestore > /tmp/ws-dump

To do the same thing in Microsoft Windows, type the following in the Run dialog that's accessible from the Start menu:

"C:\Program Files\EasyPHP1-7\mysql\bin\mysqldump.exe" --opt -uroot

  -ppassword --databases winestore > C:\windows\temp\ws-dump

The --databases option automatically adds a CREATE DATABASE IF NOT EXISTS winestore and USE winestore to the beginning of the file. These statements make loading of the file straightforward using the techniques we discuss next.

Leave MySQL running while you issue these commands. Both commands assume you've followed our installation instructions in Appendix A through Appendix C.

15.7.3 Restore

You can load a backup produced with mysqldump into MySQL to restore a database. On any platform, run the command interpreter and then type:

mysql> SOURCE filename

For example, on a Unix-based MySQL, if the file is stored as /tmp/ws-dump use:

mysql> SOURCE /tmp/ws-dump

For Microsoft Windows, if the file is stored as c:\windows\temp\ws-dump, you can use:

mysql> SOURCE C:\temp\ws-dump

Be careful: this will overwrite the database or databases in your MySQL installation. Also, make sure your application is offline when you do this, as it's likely to result in unpredictable results for unsuspecting users.

As an alternative, you can pipe a file to the command interpreter. This has the advantage that it can be added to a script file. For example, on a Unix system, you could type:

% /usr/local/mysql/bin/mysql -uroot -ppassword

 < /tmp/ws-dump

In a command window on a Microsoft Windows machine you can use:

C:\> type c:\windows\temp\ws-dump | c:\progra~1\easyph~1\mysql\bin\mysql -uroot -

ppassword

These commands assume you've followed our installation instructions in Appendix A through Appendix C.

15.7.4 Checking and Fixing Tables

Sometimes, your MySQL server may stop without being able to carry out its normal shutdown processes. Possible causes include machine and power failures, operating system errors, and MySQL internal problems. If your MySQL server does die, you should take two basic steps: first, check the error log to see if information has been recorded that can help you fix the problem; and, second, check your databases and tables for errors.

The error log is a text file and you can open it in your text editor to inspect it; to do this, you usually need to log in as the root or administrator user. The file has a .err extension and is found in the var or data subdirectory of your MySQL installation. The name of your machine usually precedes the .err extension. If you've followed our Linux installation instructions, you'll find the error file in /usr/local/mysql/var. On Mac OS X, it is in /usr/local/mysql/data, and on Microsoft Windows in C:\Program Files\EasyPHP1-7\mysql\var. You'll find that the textual explanations usually explain clearly what problem has caused your MySQL to stop or fail to start, and it's obvious what actions to take to rectify the problem.

There are other situations in which you should check your databases and tables. You should check your tables if strange results begin appearing from queries, such as unexpected end of file, can't find file, or table handler errors. It's possible that tables haven't been closed properly, that the indexes are corrupted, or that data modifications to tables weren't completed. It's also possible that a component in your system is about to fail, such as a hard disk. If so, you'll need to carry out repairs. If your system is about to fail, after this you should attempt to backup using the techniques we've discussed so far.

The CHECK TABLE statement checks a table, and works for MyISAM and InnoDB tables; since Heap tables are an in-memory structure, they don't need to be checked and repaired. For example, to check the customer table, use:

CHECK TABLE customer;

It'll report a message such as:

+--------------------+-------+----------+----------+

| Table              | Op    | Msg_type | Msg_text |

+--------------------+-------+----------+----------+

| winestore.customer | check | status   | OK       |

+--------------------+-------+----------+----------+

1 row in set (0.06 sec)

Everything is fine if the Msg_Type reported is status and the Msg_text is one of OK or Table is already up to date. If an error is found through CHECK TABLE, it'll probably report more than one row in the results, and the rows will list information, warnings, and errors in the Msg_type column and explanatory text in the Msg_text column. If this happens, you need to repair the table as discussed later in this section.

The CHECK TABLE statement has an optional parameter that adjusts how quick and superficial, or slow and thorough, the checks are. By default, it uses the MEDIUM setting, but you can specify QUICK, FAST, MEDIUM, EXTENDED, or CHANGED. We recommend using the default of MEDIUM, and then rerunning the EXTENDED option if the default reports errors. EXTENDED does a slow and thorough test of the table and its indexes. For example, to use EXTENDED on the customer table, type:

CHECK TABLE customer EXTENDED;

If you find an error in a MyISAM table, you can use the REPAIR TABLE statement to attempt a repair. Here's an example that repairs the customer table:

REPAIR TABLE customer;

You can also use a more thorough EXTENDED option that recreates the indexes in a slow but careful manner:

REPAIR TABLE customer EXTENDED;

In almost all cases, this should repair a MyISAM table. If it doesn't, or errors keep occurring, you should look elsewhere for the problem: perhaps your hard disk has become unreliable and is about to crash. Section 4.4.6.9 of the MySQL manual discusses table repair in more detail, and discusses what to do in the unlikely event that REPAIR TABLE doesn't fix your problem.

You can't use REPAIR TABLE on an InnoDB table. However, they don't usually have errors. Because of InnoDB's transactions and logging (and checkpointing) discussed in Section 15.6. it's very robust in recovering from power and database server failures, and this is a key feature of InnoDB. However, in the unlikely event that something does go wrong and an error is reported by CHECK TABLE, Section 7.5.4.1 of the MySQL manual shows you how to get InnoDB to boot safely. This'll maximize the chances of you being able to export your data from the database before (probably) your hard disk crashes or system fails.

15.7.5 Exporting Data to Other Environments

Data can also be dumped from a database using SQL. MySQL supports the SELECT ... INTO OUTFILE statement that allows you to write out data in a regular format, such as a comma-delimited file that can be read into a spreadsheet program. Consider an example query that exports a report on customer orders into the file /tmp/orders-file:

SELECT customer.cust_id, surname, firstname, orders.order_id, sum(price)

  INTO OUTFILE "/tmp/orders-file"

  FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'

  LINES TERMINATED BY "\n" 

  FROM customer INNER JOIN orders USING (cust_id) 

  INNER JOIN items USING (cust_id, order_id)

  GROUP BY cust_id, order_id;

On Microsoft Windows, you could replace /tmp/orders-file with a Windows path and file such as c:\windows\temp\orders-file.

In part, the output file contains the following output:

1,"Rosenthal","Joshua",1,11.56

1,"Rosenthal","Joshua",2,375.58

1,"Rosenthal","Joshua",3,51.31

1,"Rosenthal","Joshua",4,487.35

2,"Serrong","Martin",1,367.04

2,"Serrong","Martin",2,532.12

2,"Serrong","Martin",3,251.62

2,"Serrong","Martin",4,75.57

2,"Serrong","Martin",5,308.72

The statement is complementary to the LOAD DATA INFILE statement discussed in Section 15.3.2. More detail on both statements can be found in Sections 6.4.1 and 6.4.9 of the MySQL manual.