Backing Up and Restoring Data

Let's now look at how we can export and import a database in MySQL. This is a relatively simple process, but it is extremely important for backing up data.

Backing Up a Database to a File

When we back up a database from MySQL, it is written to a text file and is simply a list of the SQL statements that are required to recreate the database.

Let's now try to export the gamedata database that we created in the previous section to a text file called gamedata.txt. First we need to open up a command line by first clicking Run under the Start button in Windows. When the Run dialog appears, type in command and press the OK button.

Note 

On Windows 2000/XP, instead of typing command, you need to type cmd.

A command-line window will now appear. Next, you need to go to the bin directory of MySQL using the following command:

cd C:\mysql\bin

Note that you may have to change the above line if you modified the default MySQL installation directory. Here is a screen shot of how this should look:

Figure 15-59: The command-line window (MS-DOS)

Now that we are in the correct directory, we will use a utility called mysqldump, which exports a specified database to a file of our choice. Here is how we would export our gamedata database to a text file called gamedata.txt:

mysqldump gamedata > gamedata.txt

If we now open up the text file (which is located in the mysql/bin/ directory), we can see that it contains many SQL statements and comments added by the mysqldump utility. Here is a listing of our exported database text file:

# MySQL dump 8.16 #@code = # Host: localhost    Database: gamedata #-------------------------------------------------------- # Server version    3.23.47     # # Table structure for table 'playerdata' #     CREATE TABLE playerdata (   username char(255) NOT NULL default '',   password char(255) default NULL,   age int(11) default NULL,   datecreated timestamp(14) NOT NULL,   PRIMARY KEY  (username),   UNIQUE KEY username (username)  ) TYPE=MyISAM;     # # Dumping data for table 'playerdata' #     INSERT INTO playerdata VALUES ('Andrew','qwerty',20,20020209203741); INSERT INTO playerdata VALUES ('Henry','letmein',34,20020209203741); INSERT INTO playerdata VALUES ('Sandra','dra33',19,20020209203741); INSERT INTO playerdata VALUES ('John','j12d',23,20020209203741); INSERT INTO playerdata VALUES ('Jenny','jen123',34,20020209203741);     # # Table structure for table 'relateenemies' #     CREATE TABLE relateenemies (   player char(255) default NULL,   enemy char(255) default NULL ) TYPE=MyISAM;     # # Dumping data for table 'relateenemies' #     INSERT INTO relateenemies VALUES ('Andrew','Sandra'); INSERT INTO relateenemies VALUES ('Henry','Jenny'); INSERT INTO relateenemies VALUES ('Henry','John');     # # Table structure for table 'relatefriends' #     CREATE TABLE relatefriends (   player char(255) default NULL,   friend char(255) default NULL ) TYPE=MyISAM;     # # Dumping data for table 'relatefriends' #     INSERT INTO relatefriends VALUES ('Henry','Sandra'); INSERT INTO relatefriends VALUES ('Andrew','Henry'); INSERT INTO relatefriends VALUES ('Andrew','John'); INSERT INTO relatefriends VALUES ('Andrew','Jenny'); INSERT INTO relatefriends VALUES ('Sandra','Jenny');

Restoring a Backed Up Database

Now that we can back up a database, let's look at how we would go about restoring it. First let's drop our gamedata database from MySQL using the following statement:

mysql> DROP DATABASE gamedata;

Now that we have removed our gamedata database, we need to create a new, empty database in which to import our data. Do this now with the following statement:

mysql> CREATE DATABASE newgamedata; 

Next, we need to open up a command-line window again (by using the Run dialog and entering command). Now change to the mysql\bin\ directory, as we did previously when we exported the data, and then type in the following command to import the data from our gamedata.txt text file into our newgamedata database:

mysql newgamedata < gamedata.txt

Here is a screen shot of the command-line window:

Figure 15-60: Importing a text file into MySQL