How to Restore Your Data?

At some point, one of your database tables might become damaged and unusable. It’s unusual, but it happens. For instance, a hardware problem or an unexpected shutdown of the computer 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 is 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. In some cases, the database might be lost completely. For instance, if the computer where 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 stored in a backup copy. The backup copy contains a snapshot of the data as it was when the copy was made. Any changes to the database since the backup copy was made are not included; you have to re-create those changes manually. Again, 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.
As I describe in Chapter 4, 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 does not contain the statements needed to create the database. Your database might not exist, or it could exist with one or more corrupted tables. You can restore the entire database or any single table. Follow these steps to restore a single table:

1. If the table currently exists, delete the table with the following SQL query: DROP TABLE tablename where tablename is the table that you want to delete.
2. Point your browser at mysql_send.php. For a description of mysql_send.php.
3. Copy the CREATE query for the table from the backup file into the form in the browser window. For instance, choose EditCopy and EditPaste.
4. Type the name of the database in which you are restoring the table. The form shows where to type the database name.
5. Click Submit. A new Web page shows the results of the query.
6. Click New Query.
7. Copy an INSERT query for the table from the backup file into the form in the browser window. For instance, choose EditCopy and EditPaste.
8. Type the name of the database in which you are restoring the table. The form shows where to type the database name.
9. Click Submit. A new Web page shows the results of the query.
10. Click New Query.
11. Repeat Steps 7–10 until all the INSERT queries from the backup file have been sent.

If you have so many INSERT queries for the table that sending them one by one would take forever — or if there are just a lot of tables — you can send all the queries in the backup file at once. First, you may need to edit the backup file, as follows:

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 after the line located in Step 2: CREATE DATABASE IF NOT EXISTS databasename
4. After 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 do not want to rebuild a table, comment out the lines that rebuild the table by adding -- (two hyphens) in front of each line.
6. Check the INSERT lines for each table. If you do not 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 need to perform a few more steps.
On Linux, Unix, and Mac:

1. Change to the bin subdirectory in the directory where MySQL is installed. Type a cd command to change to the correct directory. For instance, type cd /usr/local/mysql/bin.
2. Type the command that sends the SQL queries in the backup file: mysql -u accountname -p <>

Change to the bin subdirectory in the directory where MySQL is installed.

• a. Open a command prompt window. For instance, choose StartAll ProgramsAccessoriesCommand Prompt.
• b. Type a cd command to change to the correct directory.

For instance, type cd c:\Program Files\MySQL\MySQL Server 5.0\bin.

Type the command that sends the SQL queries in the backup file: mysql -u accountname -p <>