How to import an SQL file using the command line in MySQL?

Question

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?

Vraisemblance

possible duplicate of Restore MYSQL Dump File with Command Line – Stoma

possible duplicate of #11407849 – Krick

Whats with these duplicate guys? This indeed is a helpful question with its own purpose – Hobbism

@ValentinoPereira have you checked original question dates before determine duplicate guys – Krick

Can you share a reproducable example? database < file.sql does not look like any command to me, and if you see some syntax errors, please share them – Fisken

After I have checked all answers below, I must say you missed a very important clue for those people who wants to help. You failed to specify the exact command when you dump data out of the database. – Grissel

To import efficiently (200 times faster), see Paresh Behede's answer. – Shorten

"I get syntax errors" - what does that mean? – Fisken

Answer 1

Try:

mysql -u username -p database_name < file.sql

Check MySQL Options.

Note 1: It is better to use the full path of the SQL file file.sql.

Note 2: Use -R and --triggers with mysqldump to keep the routines and triggers of the original database. They are not copied by default.

Note 3 You may have to create the (empty) database from MySQL if it doesn't exist already and the exported SQL doesn't contain CREATE DATABASE (exported with --no-create-db or -n option) before you can import it.

Ruthful

sudo mysql -u username -p database_name < file.sql works in some cases. – Sixtieth

So, -R and --triggers seem to be options for mysqldump, which wasn't immediately clear to me, based on the answer. Additionally, --triggers is enabled by default "This option is enabled by default; disable it with --skip-triggers." – Floria

What if I am using docker? :( – Drogheda

You can use docker exec -i <container_id> mysql -u <user> --password=<password> database_name < file.sql Reference: Backup (and Restore) MySQL Data in Docker – Ruthful

Answer 2

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

mysqldump db_name > backup-file.sql

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

Unix

mysql db_name < backup-file.sql

The same in the Windows command prompt:

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

PowerShell

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

MySQL command line

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

Gateshead

Is it me only one who has never been able to use < operator in mysql? (ubuntu18/20) – Mauchi

No idea why the Windows examples include params -u and -p while the Unix example does not. The interface for mysql is the same on both, so most likely you need the same command in Unix as is presented here for Windows. – Eupheemia

where we put backup-file.sql? what path it looks by default? – Keenan

I go to C:\Program Files\MySQL\MySQL Server 8.0\bin and run the mysql.exe. Login to MySQL and did the above changes. It worked. Thank you. – Songer

@StijndeWitt chances are that his linux distro has configured credentials in e.g. ~/.my.cnf file – Oden

I wasn't aware of the source command, so this was really helpful for me, esp when the SQL queries are so numerous that they can't be easily copy/pasted – Pentahedron

Answer 3

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 ;

Freedwoman

Is there a way to do that in a single command line on the mysql command used for import? – Hatteras

Best answer inho. It was the source command I have forgotten. Most of us want to do this while we are logged in as standalone command among other commands, not the standard login>inject>logout oneliner in top of google serps. – Chacon

I agree that this is the best answer. The autocommit=0 portion made a world of difference in terms of the speed. – Ecumenicity

will the autocommit=0 will work on larger files? like 8gb sql file. – Krona

It's not always necessary to turn off autocommit. It's worth checking the database dump in an editor, it might already begin with SET autocommit=0;. – Puckery

what about routines and triggers? – Gummous

@Hatteras { echo "SET autocommit=0;"; cat db.sql; echo "COMMIT;";} | mysql -u what -p - that's for posix-compliant command lines, not sure about windows – Llovera

This works, and not only for huge files. For a particular (very simple) 5 megabyte SQL file with about 30,000 rows for the single table, it improved the import time from 31 minutes 35 seconds to 11 seconds. That is nearly 200 times faster!!! – Shorten

For the sql path on windows, don't forget to use double backslash such as C:\\Users\\soner\\Desktop\\file.sql – Horntail

how would you do this when importing to a new server where the files are creating the db as part of the import process? since the db doesn't yet exist, how can you turn of autocommit during import? – Anchie

mysql -e "SET autocommit=0; SOURCE /build/dev-dump.sql; COMMIT;" and mysql -e "SOURCE /build/dev-dump.sql" had really no difference for me in MariaDB 10.6.16 - one took 1m26 and the other, 1m24. – Flosi

Answer 4

We can use this command to import SQL from the 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
mysql -u root -p password bank < bank.sql

And if you 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:

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

Perinephrium

There shouldn't be a space between -p and password – Disaster

Jap. This would not work. Correct would be mysql -u root -p"password" bank < bank.sql – Roundel

why you simply can't answer in one line? mysql -u username -ppassword db_name < file.sql – Glasshouse

while this is completely unrelated to this question/answer, when you're working with non-trivial databases, prefer NOT entering the password on the same command in plain text. Not specifying the password as part of the command will prompt you for password which you can enter securely – Botfly

Especially because of .bash_history – Boatyard

I agree. Anycase in mysql 8.0 is possile to do this: Disabling Interactive History : mysql supports disabling the interactive history partially or fully, depending on the host platform. – Sentimentality

not just because of .bash_history, but also because anyone with the ability to view your running command line (via /proc or ps, etc) can see the password you entered. just say no to command line passwords – Jezabelle

keeping the -p empty and putting a -D before the database name, it will ask for your password after you press enter, and your password won't be visible, nor on screen nor in your bash history. Like your example: mysql -u root -p -D bank < ~/Desktop/bank.sql – Feaster

@Feaster What is -D for? – Diarmid

@HashimAziz It is for selecting a database (in this case, 'bank'); If you have exported the whole database system (multiple databases) in the .sql file, this merely prevents using the content of the .sql file as password. – Feaster

Answer 5

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.

Bagger

Answer 6

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.

Staton

ype the path of your mysql bin directory and press – Hallowell

Answer 7

A solution that worked for me is below:

Use your_database_name;
SOURCE path_to_db_sql_file_on_your_local;

Coverture

This worked for me using MySQL Command Line Client, after placing my sql file in the proper /bin directory view windows explorer. Thanks – Propositus

Little slow but does not stop in between and don't say that MySQL server has gone away. – Aftermath

Answer 8

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

An according export can be done with

mysqldump -u database_user -p --default-character-set=utf8 [db_name] > database_file.sql

Surely 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 separate 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 to 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. Nevertheless there exists a special site in the manual that describes the options for mysqldump: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

Meteorite

Answer 9

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

Halfcock

Answer 10

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

Doane

On macOS, I had to use gzcat instead of zcat. – Transalpine

Answer 11

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

Hinder

I think it would be more helpful for the OP and further questions, when you add some explaination to your intension. – Bisayas

That would work only if you have mysql.exe defined in your windows environment variables. If not, you should type all the path to the mysql.exe file. And Your syntax is wrong. Eg: "d:\wamp\bin\mysql\mysql5.5.8\bin\mysql.exe -u YOUR_USERNAME -p DB_NAME < FILENAME.SQL" More info here: wpy.me/en/blog/… – Benzophenone

D:\xampp\mysql\bin>mysql -u root -p opfedu_campuses < C:\Users\Raham\Desktop\opfedu_campuses.sql – Fiend

Answer 12

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

E.g.:
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

Sardella

This actually worked for me. The suggestion with 4000+ votes didn't. – Bothy

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 – Insalivate

But this is not from the command line in the spirit of the question. It is using the MySQL shell interactively. – Shorten

source is not intended for importing databases, but rather for running a small number of SQL queries. stackoverflow.com/a/6163842 – Dipsomaniac

Answer 13

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

Leonilaleonine

Thanks! I was looking for this. Importing all MySQL database dumps at once. A more concise and short version of the above command mysql -u root -p < all.sql – Edaedacious

Answer 14

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

Mackmackay

Answer 15

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

But

What's the need to have --force here? – Cryogenics

Answer 16

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:

REM 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

Burleigh

Answer 17

For exporting a database:

mysqldump -u username -p database_name > file.sql

For importing a database:

mysql -u username -p database_name < file.sql

Deadbeat

Answer 18

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.

Mirthamirthful

Answer 19

Import a database

1. Go to drive:

    d:
   

2. MySQL login

    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
   

Designate

On Windows, presumably? Why is it necessary to change to drive D:? Is file DbName.sql presumed to be at the root of drive D:? Can you elaborate? Please respond by editing (changing) your answer, not here in comments (without "Edit:", "Update:", or similar - the question/answer should appear as if it was written today). – Shorten

Answer 20

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

Organize

For Centos: yum install pv – Mellisamellisent

There should be no space between "-p password" the command should be like "mysql -u username -ppassword dbname < sqlfile" else "mysql -u username -p db < sqlfile" this will prompt for password – Moonshiner

Answer 21

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.

Mimosaceous

Does it work with the strange dash (–) - near "all-databases" (also in the first revision)? – Shorten

Answer 22

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/

Inger

Answer 23

Add the --force option:

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

Aulos

Answer 24

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

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

Embarkation

Answer 25

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

Cattail

Answer 26

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

Makeyevka

Command-line is volatile though (and unless you have a keylogger or a man-behind-your-back I'd expect it to be safe when executed locally), whereas a file is permanent, thus should be a higher risk, esp. when it is in plain text – Carboxylase

...however, the mysql command does indeed warn "mysql: [Warning] Using a password on the command line interface can be insecure." – Carboxylase

Answer 27

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.

Utley

But this is not from the command line in the spirit of the question. It is using the MySQL shell interactively. – Shorten

Answer 28

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.

Badoglio

Answer 29

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.

Holub

Answer 30

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.

Centrifugate

Answer 31

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

Higginson

Answer 32

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.

Presber

Answer 33

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.

Collegiate

Answer 34

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

Stedman

I needed the host name to mention because localhost was not the DB host name in my case. So this syntax helped me. Voting it up. – Successive

Answer 35

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.

Dichotomize

Answer 36

This line imports the dump file in the local database, under Linux.

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

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

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

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

Sentimentality

Answer 37

Using MySQL Secure Shell:

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

Uncrowned

Answer 38

If you use XAMPP on the windows, first, you must manually create the database and then run the following commands:

cd C:\xampp\mysql\bin && 
mysql -u YOUR_USERNAME -p YOUR_DATABASE_NAME < PATH_TO_YOUR_SQL_FILE\YOUR_SQL_FILE.sql

And then enter the password

Rome

Answer 39

mysql -u myuser -p mydatabase < mydata.sql

Replace myuser with your MySQL username, mydatabase with the name of your MySQL database, and mydata.sql with the name of your SQL file.
Make sure that the SQL file is properly formatted and does not contain any syntax errors that could cause issues during import.

Wormwood

Answer 40

For example, you export the schema and data or only the schema of the tables of apple database to backup.sql with mysqldump as shown below. *My answer explains how to export the schema and data of the tables of a database:

mysqldump -u john -p apple > backup.sql

Or:

mysqldump -u john -p -d apple > backup.sql

Or, you export only the data of the specific tables person and animal of apple database to backup.sql with INSERT statement which has column names as shown below. *By default, INSERT statement doesn't have column names and my answer explains how to export only data more:

mysqldump -u john -p -t -c apple person animal > backup.sql

Then, you need to input a password after running the command above:

Enter password:

Now, you can import backup.sql into orange database with MySQL as shown below. *When importing the schema and data or only the schema, selected orange database must exist and when importing only the data, selected orange database and the tables must exist otherwise there is error and when importing only the data, you need to delete all the data from apple database otherwise there will be error. The documentation explains how to import databases and my answer explains how to create a database:

mysql -u john -p orange < backup.sql

Or:

mysql -u john -p -D orange < backup.sql

Or:

mysql -u john -p --database orange < backup.sql

Then, you need to input a password after running the command above:

Enter password:

Or, after login, you can import backup.sql into orange database with \. or source selecting orange database as shown below:

mysql -u john -p
...
mysql> USE orange;
mysql> \. backup.sql

Or:

mysql -u john -p
...
mysql> USE orange;
mysql> source backup.sql

Be careful, you cannot import backup.sql into orange database not selecting orange database as shown below:

So, this below gets error:

mysql -u john -p < backup.sql

ERROR 1046 (3D000) at line 22: No database selected

And, these below get error:

mysql -u john -p
...
mysql> \. backup.sql

Or:

mysql -u john -p
...
mysql> source backup.sql

ERROR 1046 (3D000): No database selected

In addition, you can import backup.sql into orange database without a password prompt by setting a password(e.g., banana) to -p(--password=) as shown below. *Don't put any space just after -p(--password=) because there is error and my answer explains how to import a database without a password prompt in detail:

mysql -u john -pbanana orange < backup.sql

Corrie

Are the two asterisks intentional? – Shorten

Answer 41

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.

Stygian

Answer 42

You can use:

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

Example:

mysql -uroot -proot db < db.sql

Niobous

Answer 43

• Create a database in MySQL.

• Then go to your computer directory C:\xampp\mysql\bin, write cmd in the address bar, and hit Enter.

• Unzip your SQL file

• Then write: mysql -u root -p dbname and press Enter.

• Write: source sql.file. Like Source C:\xampp\htdocs\amarbazarltd\st1159.sql

• Done

Fullmer

Upvote for the Windows trick of getting cmd to open in the correct directory. – Gerrald

Re "go to your computer directory": Do you mean in File Explorer? – Shorten

@PeterMortensen Yes File Explorer – Fullmer

Answer 44

Simple. Just use this command in cmd:

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

Kaitlinkaitlyn

There isn't a "use" in cmd (Windows) as far as I know (though there is NET USE). Do you mean inside the MySQL client? Or something else? Can you elaborate? Please respond by editing (changing) your answer, not here in comments (without "Edit:", "Update:", or similar - the answer should appear as if it was written today). – Shorten

Answer 45

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.

Psychologist

Answer 46

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

Wilfredowilfrid

Answer 47

Export particular databases:

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

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

Perceivable

Answer 48

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

Small

Answer 49

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

Pheasant

But this is not from the command line in the spirit of the question. It is using the MySQL shell interactively. – Shorten

Answer 50

If you are using XAMPP then go to folder xappp → mysql → bin. Open cmd here and paste this:

mysql -u root -p dbname < dbfilename.sql

Preventer

Answer 51

You can use these steps as easily.

1. Download the SQL file into your "mysql/bin" folder.

2. Open the "mysql/bin" folder using CMD.

3. If not exists required database, then create the database first.

4. Type this in the CMD and run:

   mysql -u <user> -p<password> <dbname> < file.sql

   "file.sql" is an SQL file that you want to insert into the target database. examples: If your "password" is "1234", "user" is "root", and "dbname" is "test":

    mysql -u root -p1234 test < file.sql
   

   If your "password" is null & "user" is "root" & "dbname" is "test"

    mysql -u root test < file.sql
   

5. Check the target data successfully uploaded or not.

This method can be used to upload the large size data using SQL files in the CMD.

Make sure in step 4, if you use that password, insert "-p" as a single word without any spaces.

Spartan

Answer 52

Most of the answers include > or < characters which is not a proper method for all the cases. I recommend using mysqlimport while you may make the dump file using mysqldump.

These tools will be installed with the mysql service and both are available for backup and restore in a database or multiple databases in MySQL.

Here is the way you could leverage it for importing to the mysql

mysqlimport -u database_admin -p database_name ~/path/to/dump_file.sql

In case you do not have it, please install it via:

sudo apt update sudo apt install mysql-client

In the same way, you make a backup to a dump file as follows:

mysqldump [options] --result-file=dump_file.sql

Donella

Answer 53

The fastest way

I've used the solution below to import a 100GB database (not a typo) of almost 300 million records in less than 10 hours, so it's safe to say it will rip through the average SQL dump in no time.

First, you should make sure your database is making the most of your hardware - most out-of-the-box SQL installations are set up to be as lightweight (and therefore inefficient) as possible. Assuming your database is using the InnoDB engine (which it is if you are using XAMPP), edit the following variables in your my.ini file:

innodb_buffer_pool_size=8G // Change to 50-75% of your system's RAM, depending on how much you need to keep free
innodb_log_file_size=2G // Change to 25% of the above value
innodb_log_buffer_size=8M

If you're using another database engine then you'll need to find the equivalent configuration options and tune them accordingly.

Once you've made those configuration changes, restart your server and run the following in the command-line:

mysql -u root -e "CREATE DATABASE IF NOT EXISTS <database_name>; USE <database_name>; \
SET FOREIGN_KEY_CHECKS = 0; SET UNIQUE_CHECKS = 0; SET AUTOCOMMIT = 0; \
source <file_name>; SET UNIQUE_CHECKS = 1; SET FOREIGN_KEY_CHECKS = 1; COMMIT;"

Although this uses the mysql CLI tool it's just executing plain MySQL so you can alternatively copy-paste the SQL code from it directly into your database tool's SQL tab (eg. PHPMyAdmin).

Diarmid

Answer 54

USE yourdb; // Database name  

SOURCE D:/your folder path/Folder/filetoimport.sql;

Designate

Can you explain your answer, please? Thanks in advance. Please respond by editing (changing) your answer, not here in comments (but *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** without *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** "Edit:", "Update:", or similar - the question/answer should appear as if it was written today). – Shorten

What if there are spaces in the SQL file path? – Shorten

Answer 55

1. copy the database file to C:\xampp\mysql\bin

2. open a terminal from the same path

3. type in the terminal

    .\mysql.exe -uroot
   

4. type use DB_NAME; // Change to your DB Name

5. type source DB_FILE.sql;

Kidney

Answer 56

Try this:

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

Dinse

Please add some explanation to your answer - why did the given call resolve syntax errors? – Fisken

Answer 57

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

Turenne