Working with a .sql dump file – a newbie tutorial

I have some .sql dump files that I wanted to open and play with. After googling and doodling, I finally accepted that I was going to have to overcome my innate need for orderly progression and jump into doing stuff with mySQL before actually getting to that chapter in the book!!

So here are the steps I used. I have mySQL Server 5.0 installed on my laptop, and I will go step by step to describe what I did. You will need to use your directory path and file names to do this yourself.

My sql dump file was named directory.sql and was in a folder on my desktop. My mySQL install is under C:\Program Files\MySQL\MySQL Server 5.0. My ultimate goal is to get the data in the sql dump file into csv format. I know there are probably easier ways to do it, and many tools, but I figured going step by step through the method I am going to describe was a good way to entertain myself and learn some MySQL in the process.

So let’s do it:

1. First, of course, I got myself a cold coke.

2. I opened the mySQL command line and entered my root password. I have found that it’s easier to actually work from the Command Line Client in the mySQL menu on my Start menu than it is to actually open the command line and try to navigate to the directory where mySQL is installed.

Start -> All Programs -> mySQL -> mySQL Server 5.0 -> MySQL Command Line Client

If you have set root password, which you always should, the client will ask for your password and then proceed to the mysql> prompt.

2. Next step was to create the table that the data in my sql file would go into. To do this, I first opened the sql dump file in Wordpad and examined it to determine what to name the table. Don’t use notepad for this because it will be much harder to read. If you have Textpad installed it’s even better, but I used Wordpad. I then typed in the command prompt:

mysql> CREATE DATABASE MaryDIR;

3. If this is done correctly with no typos, you will get a success statement to the effect:

Query OK, 1 row affected <0.00 sec>

4. Switch to the database you just created using the USE command.

mysql> USE MaryDir;

You should see the text

Database Changed

5. So far so good. Next step is to determine if your sql file already has the statements to create the tables you need or if you have to do it yourself. In my case, the statements were already there, so it was simply a matter of invoking the sql file to create and populate the tables. For this you use the SOURCE command. You can make it easy and move your sql file to your mySQL directory, but to make it interesting, let’s leave the sql file on the desktop and specify the path:

mysql> SOURCE C:\Documents and Settings\Mueni\Desktop\directory\directory.sql;

Remember you need to replace these paths with your own path and filename.

You will then see on the screen a series of lines as mySQL executes the queries to create and populate your tables.

You can see the tables that have been created using:

mysql> SHOW TABLES;

Once the tables are listed, you can check the details of any particular table using the DESCRIBE command,. So for one of my tables named caterers I typed:

mysql> DESCRIBE Caterers;

So I now have a mySQL database that I can use. The mySQL databases are created, by default, in the data directory. So under C:\Program Files\MySQL\MySQL Server 5.0\data there is a folder titled MaryDir.

If you want a sample file to play with you can download one from the mySQL website here: http://dev.mysql.com/doc/

Next I will explore different ways to view and interact with the database I just created.