Project 8-2 Back Up a Database

This project gives you experience backing up both an entire database and specified tables within a database, by using the BACKUP TABLE command and the mysqldump script.

Step by Step

1. Open a command-line window and log in to the MySQL Client as root.

2. Change to the duckwear database. Back up the duck_cust and duck_title tables using the BACKUP TABLE command, saving the results to a directory called BackDuck. The following commands show the BackDuck directory located on a different hard drive (D:\) than the database hard drive (C:\) for increased data security. Your command may need to indicate the path to another location.

    USE duckwear; BACKUP TABLE duck_cust, duck_title TO "D:/BackDuck/"; 

   Caution  

   The directory that you indicate as the location to place the files resulting from a BACKUP TABLE command must exist before you run the command. It will not create it for you.

3. Open your system s file navigation tool, such as Windows Explorer, and examine the contents of the directory you indicated in the command in step 2.

4. Exit from the MySQL Client to the command-line prompt. If you are not already in the /mysql/bin/ directory, go there.

5. Make a backup of the duckwear database using the following mysqldump command:

    MYSQLDUMP duckwear > dw20090317.sql 

   The resulting file will be saved in the /mysql/bin/ directory.

Project Summary

This project produced backup files for the duck_cust and duck_title tables and for the entire duckwear database. You will use these files in Project 8-3 to restore tables, after you simulate damage to the database.