Importing SQL dumps into MySQL through Command line

March 11th, 2008 by rvdavid Leave a reply »

UPDATE 2011: I’ve created another blog post on this subject which describes an easier way of importing SQL files directly from the command line with MySQL. The method on the new blog post describes how to do it in one step directly from the command line. Check it out!

Perhaps it’s because I’ve been in maintenance mode for a little bit that I’ve forgotten how to import a SQL file generated by applications like mysqldump into an existing database, but earlier today while dumping existing data from DevScripts and trying to install it on our testing server, I drew a complete blank.

So I guess this is more a note for me and anyone else who has a tendency to forget nifty little tricks like this one.

A quick way to duplicate MySQL databases is to do the following:

Step 1: Dump the database either through PHPMyAdmin’s export form or through mysqldump (as I mentioned above). Note (usually, it’s mysqldump <database-name> -h<hostname> -u<username> -p<password> if you ommit the -h switch it will default to “localhost”). After you enter the command below and press enter, you will be prompted for a password because you did not enter the password after the -p switch.

$ mysqldump <database-name> -u<username> -p >> somedatabasetable.sql

Step 2: Once you have the sql file, log into mysql and create a database or “use” an existing database – again, ommiting the -h will default to localhost and you will be prompted for a password if you don’t enter a password after the -p switch.

$  mysql -u<username> -p

Step 3: After logging in, you will need to either create a new database and select the database for use with the import, or use an existing database. For this example, we’ll create a new database. Remember to use backticks and not apostrophe’s to create your new MySQL database.

mysql> create database `somedb`

Step 4: Alright! Now we’re ready to start using! Issue a “use” statement to select the empty database

mysql> use `somedb`

Step 5: Remember where you put the SQL file and issue a “source” command:

mysql> source somedatabasetable.sql

The query is executed and if you didn’t mess around with the SQL produced by mysqldump, it should run things flawlessly.

For reference, the database version I was working with is MySQL 5.0.45 and the mysqldump version I was working with is 10.11 on my development rig. 

if you enjoyed this post, make sure you subscribe to my RSS feed!
You can also follow me on Twitter here.

Related posts:

  1. The Singleton Pattern with PHP
Advertisement

17 comments

  1. Ben Rowe says:

    As an alternative you can dump a sql file into the mysql command line like

    mysql -u -p << somedatabasetable.sql

    However the sql file will have to have the use `somedb` at the top of the file.

  2. Miles says:

    Just what I needed to find out. Thanks a lot.

  3. rvdavid says:

    You’re welcome Miles :) Glad the post helped you out.

  4. Mario says:

    How can I import multiple sql files using command line?
    Ive tried using source command with /*.sql but not is it correct.

  5. David says:

    @Mario:

    To get mysql to import multiple sql files, I first aggregated the files into a single sql file. On WinXP, I was able to use something like:

    C:\>Copy *.sql combined.sql

    And then within MySQL, I used the source command with the combined.sql file.

    I assume there is a similar thing on Unix systems to do the aggregation.

    Hope it helps. Cheers!

  6. phpdiva says:

    @ben,

    Your mysql file doesn’t need to specify the database to use; you can just add it to “mysql” command like so:
    $ mysql -u yourusername -p databasename < dump.sql

  7. sachin says:

    choose the database you want to dump in
    mysql> type show databases;

    mysql> use ….X… database;

    mysql> source path.sql

    Thats it …u r all set

  8. Vikram Shah says:

    It really helped me.. thanks a lot!!

  9. arun says:

    i got another problem ,

    I copied the site to www in ubuntu ,

    in terminal , mysql database copied.
    rewrite module done.

    now html page coming , when i type the site , saying “site offline with some technical problem ”

    Not even the home page is not displaying
    What to do,Please help

    Doing for one and half days ,,, no progress ….. if you want more details i will write

    thank you very much

    • rvdavid says:

      Check your logs. It could be a permissions issue. The site that you moved over must have some sort of web app that handles the error and fails gracefully.

  10. Ken says:

    Normally use PHPMyAdmin, but had to import a >50MB file which was too big for PHP limits so…
    This worked like a dream, thanks!

  11. asif jamal says:

    Thanks alot

  12. Pete says:

    This method works fine on MAMP and MAMP Pro, but you might have to call the application with something like this:
    /Applications/MAMP/Library/bin/mysqldump

  13. nipa says:

    its work.

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.