Tuesday, March 11th, 2008
Importing SQL dumps into MySQL through Command line
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.
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.
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.
Step 4: Alright! Now we’re ready to start using! Issue a “use” statement to select the empty database
Step 5: Remember where you put the SQL file and issue a “source” command:
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.
on Tuesday, March 18th, 2008 at 2:01 pm:
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.
on Thursday, April 10th, 2008 at 12:45 am:
Just what I needed to find out. Thanks a lot.
on Thursday, April 10th, 2008 at 9:18 am:
You’re welcome Miles :) Glad the post helped you out.
on Thursday, October 23rd, 2008 at 2:16 am:
How can I import multiple sql files using command line?
Ive tried using source command with /*.sql but not is it correct.