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
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
Replace 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 databasename < data.sql
OR use hostname such as
$ mysql -u username -p -h 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 < 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:

### allow access from too ##
mysql> quit;

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

🐧 Get the latest tutorials on Linux, Open Source & DevOps via RSS feed or Weekly email newsletter.

🐧 64 comments so far... add one

CategoryList of Unix and Linux commands
Disk space analyzersdf ncdu pydf
File Managementcat cp mkdir tree
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network UtilitiesNetHogs dig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
64 comments… add one
  • Cameron James Oct 13, 2006 @ 16:31

    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 Jul 4, 2008 @ 1:59

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

  • mosh Aug 12, 2008 @ 17:10

    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 Dec 4, 2011 @ 11:38

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

  • Cephalex Nov 4, 2008 @ 22:45

    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 Aug 25, 2011 @ 7:03

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

  • Maple Syrup Jan 16, 2009 @ 8:55

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

  • kiran and niraj Feb 5, 2009 @ 10:17

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

  • lady Feb 13, 2009 @ 1:24

    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 Mar 24, 2009 @ 17:27

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

  • Nordes Jun 17, 2009 @ 2:33

    Thx a lot ;) Very usefull tips

  • Brandon Broga Jun 29, 2009 @ 17:18

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

    and try this,

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


  • Bhargav Jul 28, 2009 @ 20:28

    tnx … easy to understanc

  • Sandeep Tagore Jul 30, 2009 @ 9:39

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

    • Prashant Oct 13, 2010 @ 11:31

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

      • Luis Santana Nov 7, 2011 @ 18:38

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

        That is the proper command

  • hammad Sep 4, 2009 @ 1:12

    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 Oct 23, 2009 @ 6:39

    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.


  • hary Dec 30, 2009 @ 9:25

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

  • Wild_Bill Jan 5, 2010 @ 17:24

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

  • Aashish Jan 8, 2010 @ 17:26

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

    the above statement running but tables are not created in database

  • Aashish Jan 8, 2010 @ 17:45

    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 Jan 27, 2010 @ 8:01

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

    • Trajcevski Filip Sep 12, 2011 @ 7:48

      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 Feb 7, 2010 @ 20:28

    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 Feb 8, 2010 @ 5:29

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

      • Don Carroll Jul 16, 2010 @ 5:25

        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 Jan 6, 2012 @ 11:05

          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 table type:

          mysql -u root -h localhost -p wikidb 

          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 Feb 18, 2011 @ 10:05

        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 Jul 22, 2010 @ 5:40

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

  • sqldumb Aug 17, 2010 @ 12:39

    Thanks man, that tip saved me lots of frustrations.

  • Jayashree Aug 24, 2010 @ 4:51

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

  • DarkCoder Nov 10, 2010 @ 15:32

    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 Dec 9, 2010 @ 16:07

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

    mysql -u user -ppassword dabatase < database.sql

  • feylip Dec 10, 2010 @ 15:29

    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 Jan 10, 2011 @ 10:33

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

  • Andy Konecny Jan 15, 2011 @ 22:44

    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 Feb 21, 2011 @ 18:20

      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 Feb 23, 2011 @ 7:27

    thank for hte information but where is the script

  • Farshid Mar 30, 2011 @ 21:22

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

  • Ejth May 19, 2011 @ 14:12

    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 Oct 8, 2011 @ 23:45

      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

    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 @ 20:52

    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 Aug 31, 2011 @ 8:37

    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 Sep 3, 2011 @ 8:26

    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 Sep 8, 2011 @ 8:39

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

    • EJTH Sep 8, 2011 @ 9:24

      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 Sep 27, 2011 @ 17:57

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

  • magi Sep 13, 2011 @ 9:19

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

  • Rajgopal H.G. Sep 22, 2011 @ 11:37

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

  • Justin DoCanto Jan 6, 2012 @ 4:18

    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 Jan 6, 2012 @ 10:12

    It was really helpful to import database using command line.

    Thank you very much ;-)

    Keep Posting!!!

  • waqas Jan 24, 2012 @ 13:00

    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.”


    • EJTH Jan 24, 2012 @ 14:12

      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 Feb 15, 2012 @ 19:01

    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 Sep 6, 2012 @ 10:59


    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 Sep 6, 2012 @ 13:17

    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 Sep 28, 2012 @ 9:32

    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 Nov 14, 2012 @ 17:28

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

  • Manoj Dec 21, 2012 @ 11:57

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

  • Udayakumar Sarangapani Jun 18, 2014 @ 2:25

    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?

  • Prakash Jun 10, 2015 @ 7:13

    Thanks for your help…


  • sandeep Jul 24, 2016 @ 18:20

    I have a existing database my_demo whose table data and number of table count are different from my_demo_pre , ie number of table are less in my_demo_pre than my_demo . when i try to load the hole database dump file ie my_demo_pre .sql in my_demo database it load the data, the no of table in my_demo should be same my_demo_pre database ,but it have more table than my_demo_pre ie similar th my_demo (i use both by mysql and source command). i want the solution who to load data in a existing database that newly loaded database have same like the database whose dump i load. Plz help me in this case……

Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre> for code samples. Still have questions? Post it on our forum