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
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
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.
Wow Mary! This is a great tutorial!
I know a lot of people will have to use phpMyAdmin for importing an SQL file. To do so, you would log into your phpMyAdmin interface, select the database you wish to import the SQL file into, select “IMPORT” from the menu options, and either paste the SQL statements or upload your SQL file and run it as so.
Also, there’s the Linux folk (like me) who would run the command from the terminal:
mysql -u username -ppassword db_name
Bo, I have read that phpMyAdmin doesn’t deal too well with larger files. I don’t know what the file limit would be where phpMyAdmin would not be the best tool for the job.
Does the command you have there create the database and then import a .sql file into the database directly, if the sql file has all the necessary queries to create the tables and table structures? Or would you have to first create the database and then run the command? Since I am working on a windows computer, I am going to experiment with this command and see what I come up with.
Thanks again for the tips. I do need to get my act together and install phpMyAdmin, even though I really like the command line too!!
This is a very good tutorial it was very helpful, at the moment I’m importing a 600MB .sql dump… hope it works ! thanks !
Thanks for your comment, I am glad you enjoyed the tutorial and found it useful. Let us know how the import goes!!
The import was a success, thanks for the knowledge !
hi.. i’m so grateful i saw this tutorial on how to convert csv file into sql file.. but somehow im really confused t\how to do it… could someone help me plz in more detail tutorials..
my plan would be to convert excel file with multiple lines and columns then to be converted into sql. how will i do that? the data will be store in my database? thank you.. sorry for my question.. i’m just a newbie in this language.
just what I needed, thanks.