Import MySQL Dumpfile, SQL Datafile Into My Database

last updated in Categories , , , ,

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.

Posted by: Vivek Gite

The author is the creator of nixCraft and a seasoned sysadmin, DevOps engineer, and a trainer for the Linux operating system/Unix shell scripting. Get the latest tutorials on SysAdmin, Linux/Unix and open source topics via RSS/XML feed or weekly email newsletter.


64 comment

  1. 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)

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

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

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

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

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

    and try this,

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


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

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

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


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

    1. 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…

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

      1. 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?

        1. 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 < 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!

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

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

  13. For me (xampp on Xp) this worked:

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

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

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

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

    1. 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?

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

    1. 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)

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

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

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

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

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

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


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

  22. 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??

  23. 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?

  24. 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?

  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?

  26. 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 Comment