MySQL Import File / Database Command

by on December 7, 2009 · 2 comments· LAST UPDATED November 5, 2013

in , ,

How do I import data stored in a .sql file created by mysqldump command on Linux or UNIX like operating systems? How do I restore mysql database backup using bash shell based command line option?

Tutorial details
DifficultyEasy (rss)
Root privilegesNo
Requirementsmysql client
Estimated completion time2m
The mysqldump command line client is a backup program and it can be used to dump a database (backup database) or a collection of databases for backup or transfer to another SQL server. The dump typically contains SQL statements to create the table, populate it, or both. A common use of mysqldump is for making a backup of an entire database:

 
mysqldump db_name > backup-db_name.sql
mysqldump -u userNameHere db_name > backup-db_name.sql
mysqldump -u userNameHere -p db_name > backup-db_name.sql
 

You can import such a file using mysql command.

Syntax

The syntax is as follows to import the data created by mysqldump command:

 
mysql -u {DB-USER-NAME} -p {DB-NAME} < {db.file-name.sql}
mysql -u {DB-USER-NAME} -h {MySQL-SERVER-HOST-NAME} -p {DB-NAME} < {backup.db.file-name.sql}
mysql -e "source /path/to/backup-db.sql" db_name_here
mysql -u DB_USER_NAME -p -e "source /path/to/backup-db.sql" db_name_here
 

Example: Load the dump file back into the server

In this example import (restore) a file called sales.sql for salesdb1 user and sales database, enter:
$ mysql -u salesdb1 -p sales < sales.sql
If database salesdb1 does not exists, first create it and than import it as follows:
$ mysql -u root -p -e 'create database salesdb1;'
$ mysql -u salesdb1 -p sales < sales.sql

You can also use the following syntax to load the dump file back into the server:

##############################################
## DB Name: sales
## DB User: salesdb1
## DB backup file (import file name): /root/sales.sql 
##############################################
mysql -e "source /root/sales.sql" sales
mysql -u salesdb1 -p  -e "source /root/sales.sql" sales
 

Please note that a large size .sql database import may take some time to finish the job. Never store import file in /tmp or import .sql files from /tmp/ folder.

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

{ 2 comments… read them below or add one }

1 andres April 19, 2013 at 1:35 pm

nice article

Reply

2 Kathy November 5, 2013 at 3:51 pm

Thank you so much for this, helped me restore a database that was too big to restore via phpMyAdmin!

Reply

Leave a Comment

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

Previous Faq:

Next Faq: