Mysql load file linux

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

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.

55 Answers 55

mysql -u username -p database_name < file.sql 

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.

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 ."

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:

The same in the Windows command prompt:

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

MySQL command line

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

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.

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.

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 ; 

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

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

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.

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

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

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; 

I was trying to import a dump from a database with a different name but with the same structure, the right answer picked by the author didn't work, it created a new database named after the database in the dump file. This answer right here did what I wanted, thanks man

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

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 

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

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

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.

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 solution that worked for me is below:

Use your_database_name; SOURCE path_to_db_sql_file_on_your_local; 

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

While most answers here just mention the simple command

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:

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:

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

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 

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 

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 

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

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/…

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 

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

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

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

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

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

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

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

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

mysqldump -u username -p database_name > file.sql 

mysql -u username -p database_name < file.sql 

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 

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

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

C:\xampp\mysql\bin\mysql -u root -pPassword DataBase < data.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.

mysql -u root -p password -D database_name  

[~]$ 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. 

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