How do I 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?

Solution 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 to keep the routines and triggers of 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 don't contain CREATE DATABASE (exported with --no-create-db or -n option), before you can import it.

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

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

Solution 4

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

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

Solution 5

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;

View more solutions

Comments

[Bill Karwin]

possible duplicate of Restore MYSQL Dump File with Command Line

[AZinkey]

possible duplicate of stackoverflow.com/questions/11407349/…

[Valentino Pereira]

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

[AZinkey]

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

[Nico Haase]

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

[Light.G]

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.

[Peter Mortensen]

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

[Nico Haase]

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

[Ejaz]

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

[Reporter]

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

[wappy]

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

[Volomike]

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

[Armin]

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

[Naveed]

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

[davidkonrad]

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.

[mehov]

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

[newbie]

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

[Ramesh Pareek]

ype the path of your mysql bin directory and press

[klewis]

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

[asgs]

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

[Jaskaran Singh]

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

[George Birbilis]

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

[George Birbilis]

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

[hashchange]

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

[Neil]

Especially because of .bash_history

[Akshay]

what about routines and triggers?

[Hayden Thring]

This will work without the 'use' command for dumps with multilpe db in it

[Wuelber Castillo]

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

[Sergei Zahharenko]

great! exaclty what i was looking for!

[Himanshu Upadhyay]

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.

[Jonny]

For Centos: yum install pv

[refex]

this also monitors the script execution, much better than the other answers

[Nico Haase]

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

[GabrieleMartini]

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.

[iateadonut]

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

[philraj]

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

[Mladen]

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

[Zdeněk Gromnica]

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

[Clinton]

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

[T.Todua]

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

[Stijn de Witt]

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.

[Peter Mortensen]

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

[Peter Mortensen]

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

[Peter Mortensen]

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

[Peter Mortensen]

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

[Peter Mortensen]

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

[Peter Mortensen]

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

[Peter Mortensen]

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!!!

[Peter Mortensen]

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

[Peter Mortensen]

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

[Peter Mortensen]

How is this different from previous answers?

[Peter Mortensen]

But still very slow(?).

[Manoj Kumar]

@PeterMortensen - will depend upon the size of data, else normally it is fast.

[Sergio Reis]

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

[Harshit Gangwar]

yep! works for me. I am using mysql 8.0.16

[nafischonchol]

@PeterMortensen Yes File Explorer

[panda-byte]

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

[Melebius]

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

[temirbek]

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

[BSUK]

Duplicate of above answer

[Tiago]

This command line is priceless xD

[Swati]

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.