Add / Import .SQL file To MySQL Database Server

Posted on in Categories last updated July 25, 2012

How do I import a MySQL .SQL text file to MySQL database sever using command line or gui tools?

You can import a MySQL script (or .sql) file into MySQL server using

  1. Unix / Linux shell prompt.
  2. phpMyAdmin web based gui tool.

Unix / Linux shell prompt example

Copy a .sql file to a remote server using sftp or scp client:
$ scp foo.sql [email protected]:~/
Login into a remote server using ssh client:
$ ssh [email protected]
Type the following command to import a .sql file:

mysql -u USERNAME -p -h localhost YOUR-DATA-BASE-NAME-HERE < YOUR-.SQL.FILE-NAME-HERE

In this example, import a ‘foo.sql’ file into ‘bar’ database using vivek as username:

mysql -u vivek -p -h localhost bar < foo.sql

phpMyAdmin

Login to phpMyAdmin. Open a web-browser and type phpMyAdmin url:
http://server1.cyberciti.biz/phpmyadmin/
In phpMyAdmin, choose the database you intend to work with from the database menu list (located on the left side).

phpMyAdmin Database Selection
Fig.01: phpMyAdmin Database Selection

Choose the IMPORT tab > Click on Browse your computer “Choose file” > Select file > Choose Ok > Choose Go

phpMyAdmin Importing .SQL File
Fig.02: phpMyAdmin Importing .SQL File

Posted by: Vivek Gite

The author is the creator of nixCraft and a seasoned sysadmin and a trainer for the Linux operating system/Unix shell scripting. He has worked with global clients and in various industries, including IT, education, defense and space research, and the nonprofit sector. Follow him on Twitter, Facebook, Google+.

12 comment

  1. If you have a big database file. I recommend use always command line. Other solution zip your .sql file and improve your php.ini settings (Upload Max file Size, Max execution time,post max size, memory). Don’t forguet check the collation for your database. I suggest always use innodb and uft8_unicode_ci

  2. Hello great people,
    Someone please tell me what am doing wrong here : mysql -u root -p -h localhost yesu4everfinal < /media /FA48-1247 /yesu4evr-final /yesu4everfinal.sql

    Am trying to import the database and the .sql file is located on an external drive as you can see from the path.
    Much thanks.

  3. Error
    SQL query:


    — Database: `blogger`

    — ——————————————————–

    — Table structure for table `account`

    CREATE TABLE IF NOT EXISTS `account` (

    `id` INT( 10 ) NOT NULL AUTO_INCREMENT ,
    `username` VARCHAR( 20 ) NOT NULL ,
    `tag` VARCHAR( 50 ) NOT NULL ,
    `data` VARCHAR( 1000 ) NOT NULL ,
    `image` VARCHAR( 500 ) NOT NULL ,
    PRIMARY KEY ( `id` ) ,
    UNIQUE KEY `username` ( `username` , `tag` )
    ) ENGINE = INNODB DEFAULT CHARSET = latin1 AUTO_INCREMENT =1;

    MySQL said: Documentation

    #1046 – No database selected

    1. There is no USE statement for the database ‘blogger’. it requires the database to be created already.
      just insert:
      USE blogger;

      before the CREATE TABLE statement
      best regards

  4. Hi everyone! I got this error message:
    Error
    SQL query:

    CREATE TABLE `ASEAN MEMBER STATES` (
    `MS_KEY` INTEGER NOT NULL AUTO_INCREMENT,
    `MS_COUNTRY` VARCHAR(32) NOT NULL,
    `MS_LASTUPDATE` DATETIME,
    `PCU_KEY` INTEGER,
    `MS_FOCALP1` LONGTEXT,
    `MS_FOCALP2` LONGTEXT,
    `MS_BCAEXPERT1` LONGTEXT,
    `MS_BCAEXPERT2` LONGTEXT,
    `MS_REGEXPERT1` LONGTEXT,
    `MS_REGEXPERT2` LONGTEXT,
    `MS_REGBIOCONLIST1` LONGTEXT,
    `MS_REGBIOCONLIST2` LONGTEXT,
    `MS_REGPESTICIDELIST1` LONGTEXT,
    `MS_REGPESTICIDELIST2` LONGTEXT,
    `MS_IMPORTEXPERT1` LONGTEXT,
    `MS_IMPORTEXPERT2` LONGTEXT,
    `MS_IMPORTEDPESTICIDES1` LONGTEXT,
    `MS_IMPORTEDPESTICIDES2` LONGTEXT,
    `MS_REMARK` LONGTEXT,
    `MS_QUESTIONNAIRE1` LONGTEXT,
    `MS_QUESTIONNAIRE2` LONGTEXT,
    `MS_QUESTIONNAIRE3` LONGTEXT,
    `MS_QUESTIONNAIRE4` LONGTEXT,
    INDEX (`PCU_KEY`),
    INDEX (`MS_KEY`),
    PRIMARY KEY (`MS_KEY`)
    ) ENGINE=csv DEFAULT CHARSET=utf8;
    MySQL said: Documentation

    #1164 – The used table type doesn’t support AUTO_INCREMENT columns

    can you help? thanks a lot :)

Leave a Comment