MySQL Import File / Database Command

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
Difficulty level Easy
Root privileges No
Requirements mysql client
Est. reading time 2m
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
  • Man pages: mysqld(8)

🐧 Get the latest tutorials on Linux, Open Source & DevOps via RSS feed or Weekly email newsletter.

🐧 2 comments so far... add one

CategoryList of Unix and Linux commands
Disk space analyzersncdu pydf
File Managementcat
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network UtilitiesNetHogs dig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
2 comments… add one
  • andres Apr 19, 2013 @ 13:35

    nice article

  • Kathy Nov 5, 2013 @ 15:51

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

Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre> for code samples. Still have questions? Post it on our forum