Categories
command-line import mysql sql

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

2561

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?

9

  • 8

    possible duplicate of Restore MYSQL Dump File with Command Line

    Jul 16, 2013 at 0:47

  • 2

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

    – AZinkey

    Aug 29, 2017 at 14:01

  • 14

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

    Jun 29, 2018 at 12:59

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

    – AZinkey

    Jul 18, 2019 at 7:36

  • 1

    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.

    – Light.G

    Oct 10, 2020 at 16:26

4643

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.

4

959

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;

4

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

    – T.Todua

    Jun 10, 2021 at 17:58


  • 6

    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.

    Jul 21, 2021 at 12:55

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

    – temirbek

    Apr 11 at 9:33

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

    – Swati

    Jun 24 at 11:10


434

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 ;

8

  • 9

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

    – Volomike

    Jan 21, 2015 at 20:12

  • 33

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

    – mehov

    May 23, 2016 at 21:36

  • 2

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

    – newbie

    Dec 9, 2016 at 5:06


  • 3

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

    Jul 2, 2018 at 12:32

  • 3

    @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

    Mar 17, 2020 at 7:11