[mysql] How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line.

I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and I tried this command

database_name < file.sql

It is not working. I get syntax errors.

• How can I import this file without a problem?
• Do I need to create a database first?

If importing data into a Docker container use the following command. Adjust user(-u), database(-D), port(-P) and host(-h) to fit your configuration.

mysql -u root -D database_name -P 4406 -h localhost --protocol=tcp -p < sample_dump.sql

For Windows OS, you can use the below command to import data from an SQL dump.

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -u<> -p<> DBName < filelocation\query.sql

Where -u is the username, and -p is the MySQL password. Then enter your password and wait for data to import.

If your folder has multiple SQL files, and you've installed Git Bash you can use this command to import multiple files:

cd /my-project/data

cat *.sql | /c/xampp/mysql/bin/mysql -u root -p 1234 myProjectDbName

A solution that worked for me is below:

Use your_database_name;
SOURCE path_to_db_sql_file_on_your_local;

While most answers here just mention the simple command

mysql -u database_user -p [db_name] < database_file.sql

today it's quite common that databases and tables have utf8-collation where this command is not sufficient. Having utf8-collation in the exported tables it's required to use this command:

mysql -u database_user -p --default-character-set=utf8 [db_name] < database_file.sql

Surley this works for other charsets too, how to show the right notation can be seen here:

https://dev.mysql.com/doc/refman/5.7/en/show-collation.html

One comment mentioned also that if a database never exists an empty database had to be created first. This might be right in some cases, but depends on the export file. If the exported file includes already the command to create the database then the database never has to be created in a separated step, which even could cause an error on import. So on import it's advisable to have a look first in the file to know which commands are included there, on export it's advisable note the settings, especially if the file is very large and hard to read in an editor.

There are still more parameters for the command which are listed and explained here:

https://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html

If you use another database-version consider searching for the corresponding version of the manual too. The mentioned links refer to MySQL version 5.7.

EDIT:
The same parameters are working for mysqldump too. So while the commands for export and import are different, the mentioned parameters are not.

Easiest way to import into your schema:

Login to mysql and issue below mention commands.

mysql> use your_db_name;

mysql> source /opt/file.sql;

To import a database, use the following command.

  mysql> create new_database;
  mysql> use new_database;
  mysql> source (Here you need to import the path of the sql file);
 eg:
  mysql> source E:/test/dump.sql;

You need to use forward slashes (/) even on Windows, e.g. E:/test/dump.sql instead of E:\test\dump.sql

Or double backslashes (\\) because of escaping, i.e. E:\\test\\dump.sql

Similarly to vladkras's answer to How do import an SQL file using the command line in MySQL?.

Key differences for me:

1. The database has to exist first
2. No space between -p and the password

shell> mysql -u root -ppassword #note: no space between -p and password
mysql> CREATE DATABASE databasename;
mysql> using databasename;
mysql> source /path/to/backup.sql

I am running Fedora 26 with MariaDB.

Regarding the time taken for importing huge files: most importantly, it takes more time because the default setting of MySQL is autocommit = true. You must set that off before importing your file and then check how import works like a gem.

You just need to do the following thing:

mysql> use db_name;

mysql> SET autocommit=0 ; source the_sql_file.sql ; COMMIT ;

Among all the answers, for the problem above, this is the best one:

 mysql> use db_name;
 mysql> source file_name.sql;

Providing credentials on the command line is not a good idea. The above answers are great, but neglect to mention

mysql --defaults-extra-file=etc/myhost.cnf database_name < file.sql

Where etc/myhost.cnf is a file that contains host, user, password, and you avoid exposing the password on the command line. Here is a sample,

[client]
host=hostname.domainname
user=dbusername
password=dbpassword

If you are importing to your local database server, you can do the following:

mysql -u database_user -p < database_file.sql

For a remote database server do the follwing:

mysql -u database_user -p -h remote_server_url < database_file.sql

Using MySQL Secure Shell:

mysqlsh -u <username> -p -h <host> -D <database name> -f dump.sql

To import a single database, use the following command.

mysql -u username -p password dbname < dump.sql

To import multiple database dumps, use the following command.

mysql -u username -p password < dump.sql

You do not need to specify the name of the database on the command line if the .sql file contains CREATE DATABASE IF NOT EXISTS db_name and USE db_name statements.

Just make sure you are connecting with a user that has the permissions to create the database, if the database mentioned in the .sql file does not exist.

simple just use this command in cmd

use databasename
\. C:/test/data.mysql 

Use:

mysql -u root -p password -D database_name << import.sql

Use the MySQL help for details - mysql --help.

I think these will be useful options in our context:

[~]$ mysql --help
mysql  Ver 14.14 Distrib 5.7.20, for osx10.12 (x86_64) using  EditLine wrapper
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Usage: mysql [OPTIONS] [database]
  -?, --help          Display this help and exit.
  -I, --help          Synonym for -?
  --bind-address=name IP address to bind to.
  -D, --database=name Database to use.
  --delimiter=name    Delimiter to be used.
  --default-character-set=name Set the default character set.
  -f, --force         Continue even if we get an SQL error.
  -p, --password[=name] Password to use when connecting to server.
  -h, --host=name     Connect to host.
  -P, --port=#        Port number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306).
  --protocol=name     The protocol to use for connection (tcp, socket, pipe,
  -s, --silent        Be more silent. Print results with a tab as separator, each row on new line.
  -v, --verbose       Write more. (-v -v -v gives the table output format).
  -V, --version       Output version information and exit.
  -w, --wait          Wait and retry if connection is down.

What is fun, if we are importing a large database and not having a progress bar. Use Pipe Viewer and see the data transfer through the pipe

For Mac, brew install pv

For Debian/Ubuntu, apt-get install pv.

For others, refer to pv - Pipe Viewer

pv import.sql | mysql -u root -p password -D database_name

1.45GiB 1:50:07 [339.0KiB/s]   [=============>      ] 14% ETA 11:09:36
1.46GiB 1:50:14 [ 246KiB/s]     [=============>      ] 14% ETA 11:09:15
1.47GiB 1:53:00 [ 385KiB/s]     [=============>      ] 14% ETA 11:05:36

I'm using Windows 10 with PowerShell 5 and I found almost all "Unix-like" solutions not working for me.

> mysql -u[username] [database-name] < my-database.sql
At line:1 char:31
+ mysql -u[username] [database-name] < my-database.sql
+                               ~
The '<' operator is reserved for future use.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : RedirectionNotSupported

I ends up using this command:

> type my-database.sql | mysql -u[username] -h[localhost] -p [database-name]

And it works perfectly, and hopefully it helps.

Thanks to @Francesco Casula's answer, BTW.

The following command works for me from the command line (cmd) on Windows 7 on WAMP.

d:/wamp/bin/mysql/mysql5.6.17/bin/mysql.exe -u root -p db_name < database.sql

You can use:

mysql -u<user> -p<pass> <db> < db.sql

Example:

mysql -uroot -proot db < db.sql

1) Go to your wamp or xampp directory Example

cd d:/wamp/bin/mysql/mysql5.7.24/bin

2) mysql -u root -p DATABASENAME < PATHYOUDATABASE_FILE

If you are using xampp then go to xampp > mysql > bin and open cmd here and paste this.

mysql -u root -p dbname < dbfilename.sql

Export particular databases:

 djimi:> mysqldump --user=root --host=localhost --port=3306 --password=test -B CCR KIT > ccr_kit_local.sql

This will export CCR and KIT databases...

Import all exported databases to a particular MySQL instance (you have to be where your dump file is):

  djimi:> mysql --user=root --host=localhost --port=3306 --password=test < ccr_kit_local.sql

Sometimes the port defined as well as the server IP address of that database also matters...

mysql -u user -p user -h <Server IP> -P<port> (DBNAME) < DB.sql 

Import a database

1. Go to drive:

    command: d:
   

2. MySQL login

    command: c:\xampp\mysql\bin\mysql -u root -p
   

3. It will ask for pwd. Enter it:

    pwd
   

4. Select the database

    use DbName;
   

5. Provide the file name

    \.DbName.sql
   

Go to the directory where you have the MySQL executable. -u for username and -p to prompt for the password:

C:\xampp\mysql\bin>mysql -u username -ppassword databasename < C:\file.sql

In Ubuntu

 mysql -u root -p
 CREATE database dbname;
 use dbname;
 source /home/computername/Downloads/merchantapp.sql
 exit;

In Windows

Download the SQL file and save it in C:\xampp\mysql\bin.

After that, open the command prompt with C:\xampp\mysql\bin:

 C:\xampp\mysql\bin> mysql -u username -p database_name < file.sql

You can try this query.

Export:

mysqldump -u username –-password=your_password database_name > file.sql

Import:

mysql -u username –-password=your_password database_name < file.sql

and detail following this link:

https://chartio.com/resources/tutorials/importing-from-and-exporting-to-files-using-the-mysql-command-line/

I think it's worth mentioning that you can also load a gzipped (compressed) file with zcat like shown below:

zcat database_file.sql.gz | mysql -u username -p -h localhost database_name

To dump a database into an SQL file use the following command.

mysqldump -u username -p database_name > database_name.sql

To import an SQL file into a database (make sure you are in the same directory as the SQL file or supply the full path to the file), do:

mysql -u username -p database_name < database_name.sql

If you are using MAMP on Mac OS X, this may be helpful:

/applications/MAMP/library/bin/mysql -u MYSQL_USER -p DATABASE_NAME < path/to/database_sql/FILE.sql

MYSQL_USER is root by default.

Add the --force option:

mysql -u username -p database_name --force < file.sql

The following steps help to upload file.sql to the MySQL database.

Step 1: Upload file.sql.zip to any directory and unzip there
Note: sudo apt-get install unzip : sudo apt-get unzip file.sql.zip
Step 2: Now navigate to that directory. Example: cd /var/www/html

Step 3: mysql -u username -p database-name < file.sql
Enter the password and wait till uploading is completed.

I thought it could be useful for those who are using Mac OS X:

/Applications/xampp/xamppfiles/bin/mysql -u root -p database < database.sql

Replace xampp with mamp or other web servers.

We can use this command to import SQL from command line:

mysql -u username -p password db_name < file.sql

For example, if the username is root and password is password. And you have a database name as bank and the SQL file is bank.sql. Then, simply do like this:

mysql -u root -p password bank < bank.sql

Remember where your SQL file is. If your SQL file is in the Desktop folder/directory then go the desktop directory and enter the command like this:

~ ? cd Desktop
~/Desktop ? mysql -u root -p password bank < bank.sql

And if your are in the Project directory and your SQL file is in the Desktop directory. If you want to access it from the Project directory then you can do like this:

~/Project ? mysql -u root -p password bank < ~/Desktop/bank.sql

For backup purposes, make a BAT file and run this BAT file using Task Scheduler. It will take a backup of the database; just copy the following line and paste in Notepad and then save the .bat file, and run it on your system.

@echo off
for /f "tokens=1" %%i in ('date /t') do set DATE_DOW=%%i
for /f "tokens=2" %%i in ('date /t') do set DATE_DAY=%%i
for /f %%i in ('echo %date_day:/=-%') do set DATE_DAY=%%i
for /f %%i in ('time /t') do set DATE_TIME=%%i
for /f %%i in ('echo %date_time::=-%') do set DATE_TIME=%%i

"C:\Program Files\MySQL\mysql server 5.5\bin\mysqldump" -u username -ppassword mysql>C:/%DATE_DAY%_%DATE_TIME%_database.sql

This line import the dump file in local database, under linux.

mysql -u dbuser -p'password including spaces' dbname < path/to/dump_file.sql

This line import the dump file in remote database, under linux. Note: -P is for the port, and is required if mysql port is different than default.

mysql -h dbhost -u dbuser -p'password including spaces' -P 3306 dbname < path/to/dump_file.sql

Note: the password include spaces and this is the reason of single quotes. Just change path style for using the command under windows (C:\windows\path\dump_file.sql)

-> Create a database in MySQL.

-> then go to your computer directory C:\xampp>mysql>bin . and write cmd on address bar, then hit enter.

-> Unzip your sql file

-> then wirte:- mysql -u root -p dbname, then press enter.

-> write:- source sql.file. Like Source C:\xampp\htdocs\amarbazarltd\st1159.sql Done

I kept running into the problem where the database wasn't created.

I fixed it like this

mysql -u root -e "CREATE DATABASE db_name"
mysql db_name --force < import_script.sql

If you already have the database, use the following to import the dump or the sql file:

mysql -u username -p database_name < file.sql

if you don't you need to create the relevant database(empty) in MySQL, for that first log on to the MySQL console by running the following command in terminal or in cmd

mysql -u userName -p;

And when prompted provide the password.

Next, create a database and use it:

mysql>create database yourDatabaseName;
mysql>use yourDatabaseName;

Then import the sql or the dump file to the database from

mysql> source pathToYourSQLFile;

Note: if your terminal is not in the location where the dump or sql file exists, use the relative path in above.

mysql --user=[user] --password=[password] [database] < news_ml_all.sql

Go to the directory where you have MySQL.

 c:\mysql\bin\> mysql -u username -p password database_name <
 filename.sql

Also to dump all databases, use the -all-databases option, and no databases’ name needs to be specified anymore.

mysqldump -u username -ppassword –all-databases > dump.sql

Or you can use some GUI clients like SQLyog to do this.

For importing multiple SQL files at one time, use this:

# Unix-based solution
for i in *.sql;do mysql -u root -pPassword DataBase < $i;done

For simple importing:

# Unix-based solution
mysql -u root -pPassword DataBase < data.sql

For WAMP:

#mysqlVersion replace with your own version
C:\wamp\bin\mysql\mysqlVersion\bin\mysql.exe -u root -pPassword DataBase < data.sql

For XAMPP:

C:\xampp\mysql\bin\mysql -u root -pPassword DataBase < data.sql

If you use XAMPP on windows:

My XAMPP installation location is C:\xampp and my file is at address C:\Users\file.sql

Just set username and database_name

Note: First, you need to create the database(database_name) manually

Run this command:

cd C:\xampp\mysql\bin && mysql -u username -p database_name < C:\Users\file.sql

And then enter the password

For information, I just had the default root + without password. It didn't work with all previous answers.

• I created a new user with all privileges and a password. It worked.

• -ppassword WITHOUT SPACE.

Try this:-

C:\xampp\mysql\bin>mysql -u root -p database_name --force < C:\file.sql

Import into the database:

mysql -u username -p database_name < /file path/file_name.sql

Export from the database:

mysqldump -u username -p database_name > /file path/file_name.sql

After these commands, a prompt will ask for your MySQL password.

For exporting a database:

mysqldump -u username -p database_name > file.sql

For importing a database:

mysql -u username -p database_name < file.sql

To import a database via the terminal

Navigate to folder where the .sql file is located

Then run the below command:

mysql -u database_user_name -p database_name < sql_file_name.sql

It will ask for a password. Enter the database password. It will take a few seconds to import the data into the database.

1. Open the MySQL command line
2. Type the path of your mysql bin directory and press Enter
3. Paste your SQL file inside the bin folder of mysql server.
4. Create a database in MySQL.
5. Use that particular database where you want to import the SQL file.
6. Type source databasefilename.sql and Enter
7. Your SQL file upload successfully.

A common use of mysqldump is for making a backup of an entire database:

shell> mysqldump db_name > backup-file.sql

You can load the dump file back into the server like this:

UNIX

shell> mysql db_name < backup-file.sql

The same in Windows command prompt:

mysql -p -u [user] [database] < backup-file.sql

PowerShell

C:\> cmd.exe /c "mysql -u root -p db_name < backup-file.sql"

MySQL command line

mysql> use db_name;
mysql> source backup-file.sql;