Add / Import .SQL file To MySQL Database Server

by on July 25, 2012 · 9 comments· LAST UPDATED July 25, 2012

in

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 vivek@serer1.cyberciti.biz:~/
Login into a remote server using ssh client:
$ ssh vivek@server1.cyberciti.biz
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

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

{ 9 comments… read them below or add one }

1 Gustavo July 27, 2012 at 3:10 am

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

Reply

2 DRG January 11, 2013 at 6:03 am

Thanks for the tutorial :) helped me a lot :)

Reply

3 bunga February 7, 2013 at 7:05 am

nice. thanx for the info. Additional info: Importing from PMA is limited in file size. nor with the shell

Reply

4 yasir hashmi February 21, 2013 at 4:46 am

nice tutorial thanx for the great info. you saved my time.

Reply

5 nickchacha June 10, 2013 at 5:07 pm

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.

Reply

6 sejal July 21, 2013 at 2:11 pm

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

Reply

7 442955 August 20, 2013 at 7:51 am

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

Reply

8 n0rmal July 23, 2013 at 9:11 am

This website is simply awesome :)

Reply

9 Jeff August 6, 2013 at 3:46 pm

What would cause the MyPHPAdmin to stop working?

All I get is a screen refresh. I am not using any popup blockers and it used to work just fine.

Reply

Leave a Comment

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

Previous Faq:

Next Faq: