Add / Import .SQL file To MySQL Database Server

Author: Vivek Gite Last updated: July 25, 2012 12 comments

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

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

Fig.01: phpMyAdmin Database Selection

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

Fig.02: phpMyAdmin Importing .SQL File

🐧 Get the latest tutorials on SysAdmin, Linux/Unix, Open Source/DevOps topics:

RSS feed or Weekly email newsletter
Share on Twitter • Facebook • 12 comments... add one ↓

UP NEXT

Category	List of Unix and Linux commands
File Management	cat
Firewall	Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04
Network Utilities	dig • host • ip • nmap
OpenVPN	CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04
Package Manager	apk • apt
Processes Management	bg • chroot • cron • disown • fg • jobs • killall • kill • pidof • pstree • pwdx • time
Searching	grep • whereis • which
User Information	groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w
WireGuard VPN	Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04

ADVERTISEMENTS

12 comments… add one

Gustavo Jul 27, 2012 @ 3:10

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 Link
DRG Jan 11, 2013 @ 6:03

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

Reply Link
bunga Feb 7, 2013 @ 7:05

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

Reply Link
yasir hashmi Feb 21, 2013 @ 4:46

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

Reply Link
nickchacha Jun 10, 2013 @ 17:07

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 Link
sejal Jul 21, 2013 @ 14:11

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 Link
- 442955 Aug 20, 2013 @ 7:51
  
  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 Link
n0rmal Jul 23, 2013 @ 9:11

This website is simply awesome :)

Reply Link
Jeff Aug 6, 2013 @ 15:46

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 Link
charlott Dec 18, 2014 @ 6:27

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 :)

Reply Link
Charmander Jan 11, 2015 @ 6:25

Thank you so much this was the perfect tutorial! :)

Reply Link
Ngeshlew Oct 12, 2015 @ 15:17

Hi,

Thank you guys so much!! I had been working in importing a WordPress site from a localhost (PC) to a live LINUX Server. Your solution is amazing!

Reply Link