How to Restore Your MySQL Data - dummies

By Steve Suehring, Janet Valade

At some point, one of your MySQL database tables might become damaged and unusable. It’s unusual, but it happens. For instance, a hardware problem or an unexpected computer shutdown can cause corrupted tables. Sometimes, an anomaly in the data that confuses MySQL can cause corrupt tables. In some cases, a corrupt table can cause your MySQL server to shut down.

Here’s a typical error message that signals a corrupted table:

Incorrect key file for table: 'tablename'.

You can replace the corrupted table(s) with the data stored in a backup copy.

However, in some cases, the database might be lost completely. For instance, if the computer on which your database resides breaks down and can’t be fixed, your current database is lost — but your data isn’t gone forever. You can replace the broken computer with a new computer and restore your database from a backup copy.

You can replace your current database table(s) with the database you’ve stored in a backup copy. The backup copy contains a snapshot of the data as it was when the copy was made. Of course, you don’t get any of the changes to the database since the backup copy was made; you have to re-create those changes manually.

If you access MySQL through an IT department or through a web hosting company, you need to ask the MySQL administrator to restore your database from a backup. If you’re the MySQL administrator, you can restore it yourself.

You build a database by creating the database and then adding tables to the database. The backup created by the mysqldump utility is a file that contains all the SQL statements necessary to rebuild the tables, but it doesn’t contain the statements you need to create the database itself.

To restore the database from the backup file, you must first edit the backup file (which is a text file). Then, you use the mysql client to create the database from the SQL statements in the backup file.

First, you edit the backup file by following these steps:

  1. Open the backup file in a text editor.

  2. Locate the line that shows the Server Versions.

  3. If you want to rebuild an entire database, add the following statement below the line that you locate in Step 2:

  4. Below the line in Step 3, add a line specifying which database to add the tables to:

    USE databasename
  5. Check the blocks of statements that rebuild the tables.

    If you don’t want to rebuild a table, add — (two hyphens) at the beginning of each line that rebuilds the table. The hyphens mark the lines as comments.

  6. Check the INSERT lines for each table.

    If you don’t want to add data to any tables, comment out the lines that INSERT the data.

  7. Save the edited backup file.

After the backup file contains the statements that you want to use to rebuild your database or table(s), you can use the mysql client to execute the SQL statements in the backup file. Just follow these steps:

  1. From a command line prompt, change to the bin subdirectory in the directory where MySQL is installed.

    In Windows, you open a command prompt window to use the mysql client.

    Type a cd command to change to the correct directory. For instance, you might type cd /usr/local/mysql/bin or cd c:Program FilesMySQLMySQL Server 5.0bin.

  2. Type this command (which sends the SQL queries in the backup file):

    mysql -u accountname -p < path/backupfilename

    You replace accountname with an account that has CREATE privilege. If the account doesn’t require a password, leave out the -p. If you use the -p, you’re asked for the password. Use the entire path and filename for the backup file. For instance, you could use this command to restore the ProductCatalog database:

mysql -u root -p < c:Program FilesMySQLMySQL Server 5.0binbakProductCatalog.bak

The tables might take a short time to restore. Wait for the command to finish. If a problem occurs, an error message appears. If no problems occur, you see no output. When the command is finished, the prompt appears.

Your database is now restored with all the data that was in it at the time the copy was made. If the data has changed since the copy was made, you lose those changes. For instance, if more data was added after the backup copy was made, the new data isn’t restored. If you know the changes that were made after creating the backup, you can make them manually in the restored database.