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
DifficultyEasy (rss)
Root privilegesNo
Requirementsmysql client
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:

ADVERTISEMENTS

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 SysAdmin, Linux/Unix, Open Source/DevOps topics:
CategoryList of Unix and Linux commands
File Managementcat
FirewallCentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig 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 VPNCentOS 8 Debian 10 Firewall Ubuntu 20.04

ADVERTISEMENTS
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>, <code>...</code> and <kbd>...</kbd> for code samples.