≡ Menu

Import MySQL Dumpfile, SQL Datafile Into My Database

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.

Tweet itFacebook itGoogle+ itPDF itFound an error/typo on this page?

{ 62 comments… add one }

  • Cameron James October 13, 2006, 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)

  • Steven Richards July 4, 2008, 1:59 am

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

  • mosh August 12, 2008, 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

    • John December 4, 2011, 11:38 am

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

  • Cephalex November 4, 2008, 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

    • Anon August 25, 2011, 7:03 am

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

  • Maple Syrup January 16, 2009, 8:55 am

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

  • kiran and niraj February 5, 2009, 10:17 am

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

  • lady February 13, 2009, 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

  • fmuteam March 24, 2009, 5:27 pm

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

  • Nordes June 17, 2009, 2:33 am

    Thx a lot ;) Very usefull tips

  • Brandon Broga June 29, 2009, 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

  • Bhargav July 28, 2009, 8:28 pm

    tnx … easy to understanc

  • Sandeep Tagore July 30, 2009, 9:39 am

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

    • Prashant October 13, 2010, 11:31 am

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

      • Luis Santana November 7, 2011, 6:38 pm

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

        That is the proper command

  • hammad September 4, 2009, 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.

  • bhavesh October 23, 2009, 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

  • hary December 30, 2009, 9:25 am

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

  • Wild_Bill January 5, 2010, 5:24 pm

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

  • Aashish January 8, 2010, 5:26 pm

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

    the above statement running but tables are not created in database

  • Aashish January 8, 2010, 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

  • sasikala January 27, 2010, 8:01 am

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

    • Trajcevski Filip September 12, 2011, 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…

  • sascha February 7, 2010, 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.

    • nixCraft February 8, 2010, 5:29 am

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

      • Don Carroll July 16, 2010, 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?

        • nixCraft January 6, 2012, 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!

      • sascha February 18, 2011, 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.

  • dibish July 22, 2010, 5:40 am

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

  • sqldumb August 17, 2010, 12:39 pm

    Thanks man, that tip saved me lots of frustrations.

  • Jayashree August 24, 2010, 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 ?

  • DarkCoder November 10, 2010, 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/

  • glenbot December 9, 2010, 4:07 pm

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

    mysql -u user -ppassword dabatase < database.sql

  • feylip December 10, 2010, 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

  • binu January 10, 2011, 10:33 am

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

  • Andy Konecny January 15, 2011, 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.

    • Andy Konecny February 21, 2011, 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?

  • vikas gautam February 23, 2011, 7:27 am

    thank for hte information but where is the script

  • Farshid March 30, 2011, 9:22 pm

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

  • Ejth May 19, 2011, 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.

    • Sean P. O. MacCath-Moran October 8, 2011, 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)

  • bunty May 26, 2011, 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

  • mtg169 May 31, 2011, 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.

  • Raphael August 31, 2011, 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.

  • mohsen September 3, 2011, 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

  • joomlavui September 8, 2011, 8:39 am

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

    • EJTH September 8, 2011, 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

      • florin September 27, 2011, 5:57 pm

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

  • magi September 13, 2011, 9:19 am

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

  • Rajgopal H.G. September 22, 2011, 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

  • Justin DoCanto January 6, 2012, 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

  • PHP Redstorm January 6, 2012, 10:12 am

    It was really helpful to import database using command line.

    Thank you very much ;-)

    Keep Posting!!!

  • waqas January 24, 2012, 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

    • EJTH January 24, 2012, 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

  • NT February 15, 2012, 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??

  • vijay September 6, 2012, 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…..

  • Vitaly September 6, 2012, 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?

  • Navid September 28, 2012, 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?

  • Stephen November 14, 2012, 5:28 pm

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

  • Manoj December 21, 2012, 11:57 am

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

  • Udayakumar Sarangapani June 18, 2014, 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

Leave a Comment