Project 8-3 Simulate Damage and Restore a Table

In this project, you are going to simulate damage to your database and use a variety of methods to verify that damage and repair it. You will use the CHECK TABLE , REPAIR TABLE , OPTIMIZE TABLE , and RESTORE TABLE commands, as well as use the MySQLCC GUI to restore a database.

Step by Step

Either from the command line or from the MySQL Administrator, stop your MySQL Server. Open a window to view the /mysql/data directory. Make a copy of the entire duckwear directory, saving it outside the /mysql path .
Open the duck_cust.myi file in a word processor. It should look like gibberish. Delete four or five characters from the middle of the file, save the changes, and then close the file. If the word processor asks if it is all right to save the file as .txt and lose all formatting, agree. Using whatever method you choose, restart your MySQL Server.
Open a command-line window and log in to the MySQL Client as root. Run the following commands:
```
 USE duckwear; CHECK TABLE duck_cust, duck_title, duck_sales QUICK; 
```
You will see that duck_title and duck_sales check out okay, but duck_cust returns errors and is identified as corrupt.
Execute the following REPAIR TABLE command to attempt to repair duck_cust , and then check the tables again to verify the repair.
```
 REPAIR TABLE duck_cust USE_FRM; CHECK TABLE duck_cust, duck_title, duck_sales QUICK; 
```
The REPAIR TABLE command should return that the repair status is OK , and then all three tables should show that check status is OK .
Use the OPTIMIZE command on the duck_cust table.
```
 OPTIMIZE TABLE duck_cust; 
```
None of the tables you have made so far have had enough transactions to require optimization, so the result should show that the optimize status is OK .
Once again, shut down the MySQL Server so you can simulate damage. When it is stopped , go to the /mysql/data.duckwear directory and delete the duck_title.MYD, duck_title.frm, and duck_title.MYI files. Restart the server.
In the MySQL Client, execute a SHOW TABLES command. The return will indicate the server has been disconnected ”a result of stopping the server ”but sending the same command again will usually result in a new connection. The duck_title table will no longer be listed in the SHOW TABLES report.
Begin repairing the duckwear database by using the RESTORE TABLE command in conjunction with the backup files that resulted from the BACKUP TABLE command you used in Project 8-2. The following code uses the path in the example in Project 8-2. Your path to the backup files may be different.
```
 RESTORE TABLE duck_title FROM "D:/BackDuck"; SHOW TABLES; 
```
The return of the RESTORE TABLE command should indicate that the restore status is OK , and the return of the SHOW TABLES command should list the duck_title table once more.
Stop the MySQL Server and go to the /mysql/data/duckwear directory. Delete the duck_sales.MYD, duck_sales.frm, and duck_sales.MYI files. Restart the MySQL Server.
Open the MySQLCC GUI and connect to your MySQL Server. Click the SQL icon button, which is the third button from the left side of the toolbar. The Query window should open.
Select File Open or click the Open folder icon button. Browse to /mysql/bin and select the dw20090317.sql file, from the mysqldump script you used in Project 8-2. Click the Open button, and the file will open in the main viewing area of the Query window.

Note
The file will be clearly color -coordinated in the Query window to make the SQL command it contains easy to read.
Click the button with the exclamation mark icon (the fifth button from the right side of the toolbar) to execute the SQL commands in the .sql file. In the Messages area of the Query window, you will see a variety of error messages regarding the tables that are still there and do not need to be restored, and you may hear an error sound.
Send the SHOW TABLES command to the MySQL Client. You will get a disconnected message. Send the command again, and it will connect and return the list of tables. The duck_sales table is present once again.

Project Summary

This project gave you hands-on experience with CHECK TABLE , REPAIR TABLE , RESTORE TABLE , and OPTIMIZE TABLE . You also used the file from the mysqldump script in conjunction with the MySQLCC GUI to restore a missing table. These examples are simple because of the relatively small size of the database you are working with, but the ease of recovery is similar on larger databases.