Importing SQL dumps into MySQL through Command line

March 11th, 2008 by rvdavid Leave a reply »

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.

Advertisement

8 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!!

Leave a Reply