Import MySQL Dumpfile, SQL Datafile Into My Database

by on August 23, 2006 · 62 comments· LAST UPDATED January 6, 2012

in , ,

How can I import a MySQL dumpfile into my database? I'm using CentOS Linux 5 server. My old hosting provider gave me a data.sql file and I do have access to my Unix / Linux server via ssh. So How do I restore my data using command line over the ssh session?

You can easily restore or import MySQL data with the mysql command itself. First you need to login to your system using ssh or putty client.

Step #1: Upload File To MySQL Server

You can upload data.sql file using the sftp or scp command, enter:
$ scp data.sql vivek@example.cyberciti.biz:/home/vivek
The data.sql file will be uploaded to /home/vivek directory. Avoid using /tmp or Apache document directory such as /var/www/html as anyone can see your data on the remote server.

Step #2: Login To Remote Server

Type the following command at the shell prompt:
$ ssh loginname@example.cyberciti.biz
Replace example.cyberciti.biz with actual server name or an IP address.

Step#3: Import Datafile

Type the following command to import sql data file:
$ mysql -u username -p -h localhost DATA-BASE-NAME < data.sql
In this example, import 'data.sql' file into 'blog' database using vivek as username:
$ mysql -u vivek -p -h localhost blog < data.sql

If you have a dedicated database server, replace localhost hostname with with actual server name or IP address as follows:
$ mysql -u username -p -h 202.54.1.10 databasename < data.sql
OR use hostname such as mysql.cyberciti.biz
$ mysql -u username -p -h mysql.cyberciti.biz database-name < data.sql

If you do not know the database name or database name is included in sql dump you can try out something as follows:
$ mysql -u username -p -h 202.54.1.10 < data.sql

A Note About Creating A New Database and Importing Data

In this example create a mysql database called foo and import data from bar.sql.gz as follows:

mysql -u root -p -h localhost

Sample outputs:

mysql> create database foo;
mysql> exit;

Import bar.sql.gz:

gunzip bar.sql.gz
ls -l
mysql -u root -p -h localhost foo <bar.sql

You can also create a username and password for foo database using the following syntax:

 
mysql -u root -p -h localhost
 

Sample outputs:

mysql> GRANT ALL ON foo.* TO NEW-USERNAME-HERE@localhost IDENTIFIED BY 'YOUR-PASSWORD-HERE';
### allow access from 192.168.1.5 too ##
mysql> GRANT ALL ON foo.* TO NEW-USERNAME-HERE@192.168.1.5 IDENTIFIED BY 'YOUR-PASSWORD-HERE';
mysql> quit;

Page last updated at 4:37 PM, January 6, 2012.

TwitterFacebookGoogle+PDF versionFound an error/typo on this page? Help us!

{ 62 comments… read them below or add one }

1 Cameron James October 13, 2006 at 4:31 pm

If you’re having troubles try removing the space between -u and the username (eg: -uusername), -p and the password (eg: -ppassword), -h and the host (eg: -h11.11.11.11)

Reply

2 Steven Richards July 4, 2008 at 1:59 am

Worked like a charm! I did have to remove the spaces as Cameron suggested (thanks). I’m running Fedora Core 7.

Reply

3 mosh August 12, 2008 at 5:10 pm

I’m so stupid ;)
I have decicated server and I was using all the time phpMyAdmin … that was terrible….

now I import DB in 2 sek :D

THX a lot for this very important for me command :)

Greetings, mosh

Reply

4 John December 4, 2011 at 11:38 am

Same here. phpmyadmin needed 10 mins. And now…well below 3 seconds. So awesome.

Reply

5 Cephalex November 4, 2008 at 10:45 pm

if you want to import an UTF8 database you can use this command

$mysql -u databaseuser -p -h localhost –default-character-set=utf8 databasename < sqlfile.sql

Reply

6 Anon August 25, 2011 at 7:03 am

This fixed a ‘2006 Mysql went away’ error for me. When I was importing a large dump file. Thanks!

Reply

7 Maple Syrup January 16, 2009 at 8:55 am

Thanks to Cameron James. Removing the spaces did it for me as well. Fought with that sucker for almost 4 hours…

Reply

8 kiran and niraj February 5, 2009 at 10:17 am

thanx a lot for this script. and thanks cephalex for utf8 option.

Reply

9 lady February 13, 2009 at 1:24 am

can anyone help me how to start using php in linux?i already installed it but i do not know how to run it using command line.
and one more thing how to convert data from pcap to mysql?
please give me a hint on how to start the program

Reply

10 fmuteam March 24, 2009 at 5:27 pm

Very nice tut ! I just moving from another host to a new one. This commands works perfectly :)

Reply

11 Nordes June 17, 2009 at 2:33 am

Thx a lot ;) Very usefull tips

Reply

12 Brandon Broga June 29, 2009 at 5:18 pm

If Your Using Red Hat Linux,
take the $ out.

and try this,

mysql -u username -p -h localhost data-base-name < data.sql

Thanks

Reply

13 Bhargav July 28, 2009 at 8:28 pm

tnx … easy to understanc

Reply

14 Sandeep Tagore July 30, 2009 at 9:39 am

go to mysql folder and execute this command:
[root@localhost mysql]# bin/mysql [Database Name] < /home/sandeep/software/[file-name].sql

Reply

15 Prashant October 13, 2010 at 11:31 am

[root@localhost’ is not recognized as an internal or external command,
perable program or batch file.

Reply

16 Luis Santana November 7, 2011 at 6:38 pm

/bin/mysql [Database Name] < /home/sandeep/software/[file-name].sql

That is the proper command

Reply

17 hammad September 4, 2009 at 1:12 am

I installed oracle 11g, but I want to know who do I create database table in it and also describe me about how to type command to see the database table in Sql.

Reply

18 bhavesh October 23, 2009 at 6:39 am

I want to add a “login” feature to my website, that consists of an email address, password and a unique user ID. The feature should also be able to store the names, send an email verification reply, provide for emailing the user for forgotten passwords, etc.

plz replay me on my mail id.

I am totally new at mysql data base and php script.

bksondagar

Reply

19 hary December 30, 2009 at 9:25 am

thx a lot, it’s very helpfull for me.

Reply

20 Wild_Bill January 5, 2010 at 5:24 pm

Thanks alot for these. Its helped me out of a big jam that I was in..

Reply

21 Aashish January 8, 2010 at 5:26 pm

mysql -u username -p data-base-name < data.sql

the above statement running but tables are not created in database

Reply

22 Aashish January 8, 2010 at 5:45 pm

mysql -u username -p data-base-name < data.sql
gives error , can u help me
ERROR 1227 (42000) at line 500: Access denied; you need the SUPER privilege for this operation on win

Reply

23 sasikala January 27, 2010 at 8:01 am

How can I import a MySQL dumpfile into my database?
am using windows..

Reply

24 Trajcevski Filip September 12, 2011 at 7:48 am

You have to get the mySql dump file.
Option 1:
-Copy the dump file to MySql instalation directory\MySql Server x.x\bin
-Open command prompt, and go to MySql instalation directory\MySql Server x.x\bin directory.
-Run “mysql -uroot -ppassword databaseName < dumpFileName.sql"
IMPORTANT: dont leave spaces between -u and user or -p and password…
Option 2:
-add mysql from MySql instalation directory\MySql Server x.x\bin to system enviorment variables
-Open command prompt and go to the directory where your dump file is
-Run "mysql -uroot -ppassword databaseName < dumpFileName.sql"
IMPORTANT: dont leave spaces between -u and user or -p and password…

Reply

25 sascha February 7, 2010 at 8:28 pm

yeah, everywhere instructions how to import sql file when it is stored in web server, but nowhere when it is stored in desktop. absolutly no one give us command how to upload stupid mysql file from desktop to web server without phpmyadmin.
I use Joomla 1.0.x CMS, and they have plugin to export but there is no import button in plugin and I don’t have command.

Reply

26 nixCraft February 8, 2010 at 5:29 am

Upload file to your server using ftp or sftp client. Once uploaded run above mysql command.

Reply

27 Don Carroll July 16, 2010 at 5:25 am

OK, nearly there, so thanks to the author and those who followed up in the comments. One question, though–how to add my sql dump (call it create.sql) to a specific table in the DB?

I have four existing tables in the database and don’t need/want to change them. I also have a newly created table (call it $xx_table) in which I wish to dump the data from create.sql. Any ideas?

Reply

28 nixCraft January 6, 2012 at 11:05 am

The commands are same. Let us say your table name is page, database name is wikidb and dump file is called page.sql. To import page.sql into wikidb.page table type:

mysql -u root -h localhost -p wikidb < page.sql

The above command will delete all data from page table and will import a new set of data from page.sql.

Hope this helps!

Reply

29 sascha February 18, 2011 at 10:05 am

here is detailed instruction:)
I had to upload rdesign.sql file to my root folder at server, then I had to find PATH for it, it is usually written in FTP client (I use WinSCP), path is this srv/www/virtual/blabla, instead of blabla write your domain name. instead of localhost rdesign write localhost your database name. so:

mysql -u yourdatabaseusername -p -h localhost rdesign < /srv/www/virtual/org.blabla.blabla/rdesign.sql

after that I had to type password for database, and job is finished.

my problem is that I don't have phpmyadmin and server upload is limited to 5MB (therefore I can't use Joomla mysql plugins) and my database file is 9MB, therefore I had to use Putty.

Reply

30 dibish July 22, 2010 at 5:40 am

you did a great job dear……..its worked ……..thanks a lot …………

Reply

31 sqldumb August 17, 2010 at 12:39 pm

Thanks man, that tip saved me lots of frustrations.

Reply

32 Jayashree August 24, 2010 at 4:51 am

can anybody know how to import vhdl(x-vhdl) document which is mysql dump file i have downloaded from server into mysql database ?

Reply

33 DarkCoder November 10, 2010 at 3:32 pm

For me (xampp on Xp) this worked:

mysql -uusername -ppassword -hlocalhost data-base-name < data.sql

Note: your *.sql file must be in /xampp/

Reply

34 glenbot December 9, 2010 at 4:07 pm

You only really need to remove the space from the -p option.

mysql -u user -ppassword dabatase < database.sql

Reply

35 feylip December 10, 2010 at 3:29 pm

or just omit the password altogether and let mysql prompt you for it ie
mysql> -u user -p <database.sql

then enter password at prompt

Reply

36 binu January 10, 2011 at 10:33 am

how can i import php db into linux.
i am using ubuntu.

Reply

37 Andy Konecny January 15, 2011 at 10:44 pm

The only thing missing is how to tell mysql where to put the extract of the dump file.
I already have space assigned for my database, but it isn’t /var/lib/mysql where it keeps trying to extract it so I run out of room on root. The mount point with lots of room is in the same path as where the database was on the old box and the –datadir also points to that mount point.

Reply

38 Andy Konecny February 21, 2011 at 6:20 pm

follow up
the command “mysql_install_db –datadir=”
and related command line instructions are what had it dumping the databases in the old home
properly defining the location in the my.conf file ‘broke’ mysql all together in this instance (couldn’t read .tmp, or create/write to just some files in the destination, though it did create a bunch)

Root cause: assuming that mysql could work in a proper security model of rights assignments. While it had full rights to the directory within the mount point, no rights of any sort (=000) had been assigned at the mount point. I haven’t tested what the minimum rights are, but clearly they are well beyond what should be needed as from a security point of view, the intermediate directories should not require any rights. Novell has had this proper model working in NetWare for decades, why can’t we do this in the Linux world?

Reply

39 vikas gautam February 23, 2011 at 7:27 am

thank for hte information but where is the script

Reply

40 Farshid March 30, 2011 at 9:22 pm

Thanks. This was very helpful. A quick reminder.
Do keep up the good work.

Reply

41 Ejth May 19, 2011 at 2:12 pm

DO NOT direct the contents of a large SQL dump to mysql with like in this article:
mysql -u username -p -h localhost data-base-name < data.sql

This will probably fail if your database is huge (like mine, its about 1.5gb)

instead use this approach:

mysql -h localhost -u [mysqlusername]
Enter password: [your pass]
use database_name
source dump.sql

This will handle huge files with no problems.

Reply

42 Sean P. O. MacCath-Moran October 8, 2011 at 11:45 pm

Thanks Ejth – I was scouring the web for a large file solution and had completely forgot about this approach; your refresher was timely and HUGELY helpful. =o)

Reply

43 bunty May 26, 2011 at 8:12 am

i used “mysql -u username -p pass databasename < file_new.sql"

and found error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near..

please help

Reply

44 mtg169 May 31, 2011 at 8:52 pm

use mysql -u username -p databasename <file_new.sql
That will prompt you for the password and is more secure as the password will not be stored in plain-text in your bash history. If you want to supply the password, simply remove the space after -p and the the password, so -pyourpassword.

Reply

45 Raphael August 31, 2011 at 8:37 am

Hi,
I saw lots of articles showing how you can import the dump file back to mySQL when the dump file is .sql.

What happen to me is that my provider provided a XML version of the dump file using the -X switch of the mysqldump program and I have totally no idea how I can import that entire database of the dump file which is > 3GB in compressed tar form!

Any help would be greatly appreciated.

Reply

46 mohsen September 3, 2011 at 8:26 am

Thank you for the guidance.
It seems what really works is
mysql -u user -ppassword
and not
mysql -u user -p password

I hope the author can change the initial document. It took me about 30 minutes to understand I should remove the space

Reply

47 joomlavui September 8, 2011 at 8:39 am

ERROR 1153 (08S01) at line 17005: Got a packet bigger than ‘max_allowed_packet’ bytes

Reply

48 EJTH September 8, 2011 at 9:24 am

use this approach instead, as i mentioned in my previous post the method mentioned in this article has problems with huge imports:

mysql -h localhost -u [mysqlusername]
Enter password: [your pass]
use database_name
source dump.sql

Reply

49 florin September 27, 2011 at 5:57 pm

Just wanna thank EJTH – only his method worked for me.
Thanks,

Reply

50 magi September 13, 2011 at 9:19 am

How can I make a sql file, where is stored my database, using mysql command line?
Thanks.

Reply

51 Rajgopal H.G. September 22, 2011 at 11:37 am

Thanks a lot. I was missing the -h in the command
$ mysql -u username -p -h localhost data-base-name < data.sql

Reply

52 Justin DoCanto January 6, 2012 at 4:18 am

forgot how to do this off the top of my head. this post came up on google. just saved my ass. thanks guys

Reply

53 PHP Redstorm January 6, 2012 at 10:12 am

It was really helpful to import database using command line.

Thank you very much ;-)

Keep Posting!!!

Reply

54 waqas January 24, 2012 at 1:00 pm

Tried everything, I am having the following error while importing a mysql db dump on command line. the file is around over 300 MB. I have imported the same file in centos a while ago. But now having this problem. Any help plz, I can’t change the db dump file.

“error 1064 (42000) you have an error in your sql syntax; check the manual that corresponds to your mysql server version.”

thanks

Reply

55 EJTH January 24, 2012 at 2:12 pm

As i stated in my previous comment this method is more correct, and works better on large database dumps:

# mysql -h localhost -u [mysqlusername]
Enter password: [your pass]

Then type in:
use database_name
source dump.sql

Reply

56 NT February 15, 2012 at 7:01 pm

Under what circumstances would I use mysqlimport rather than mysql -u -p < file.sql?
I make changes to my database locally, dump the relevant tables to a file, upload it to the server and import the dumped file to the existing database.
So far I've noticed the tables that get dumped are the only ones to be affected on the production server, which is what I want, so why is there a different command (mysqlimport) to do the same function mysql is doing??

Reply

57 vijay September 6, 2012 at 10:59 am

Hi,

How to import a dmp (data.dmp) file into MySql or oracle database.

can any one tell me the exact way, how to import it…..

Reply

58 Vitaly September 6, 2012 at 1:17 pm

Very good post. Reallly appreciate it.

But may you give me instructions what do I need to change to make the /%postname%.html structure works?

Reply

59 Navid September 28, 2012 at 9:32 am

Hello,
I am using Fedora 17.
I had a dump(.xz extension) of approximately 5GB.
I extracted it, it unpacked around 127GB.
Now I started imported it with command,

mysql -u root -p -h localhost < multidb.sql

it prompted for password, I typed password,
since then around 17 hours has been passed upto now,
it has stuck there, no further process is happening, no info, but system has become little bit slow.
May anybody please let me suggest some way to ensure whether some process is happening or not?
Where does the imported data stored in filesystem?
If i would interrupt the process, will all the imported would be lost? is it a atomic process?

Reply

60 Stephen November 14, 2012 at 5:28 pm

When you import a file is there a way to include the comments?

Reply

61 Manoj December 21, 2012 at 11:57 am

I used the mysql -u root -p -h localhost databasename < sqlfile_from_mysqldump.sql and it worked. :)

Reply

62 Udayakumar Sarangapani June 18, 2014 at 2:25 am

Hi Experts,
I use the below command in linux shell
mysql -u xxx -p < somefile.sql
somefile.sql has only a set of update queries.
How do I ensure all the update performed perfectly?
Any idea?
Thanks.
-Uday

Reply

Leave a Comment

Tagged as: , , , , , , , , , , , , , , , , , , , , , , , , , ,

Previous Faq:

Next Faq: