database - How to import an SQL file using the command line in MySQL? - Stack Overflo

Ask Question Modified 14 days ago Viewed 5.3m times 2806 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 ```plain text 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? - mysql - database - import - command-line - dump asked Jul 16, 2013 at 0:43 Jaylen 40.2k4242 gold badges136136 silver badges226226 bronze badges - 8 possible duplicate of Restore MYSQL Dump File with Command Line – Bill Karwin Commented Jul 16, 2013 at 0:47 - 2 possible duplicate of stackoverflow.com/questions/11407349/… – AZinkey Commented Aug 29, 2017 at 14:01 - 21 Whats with these duplicate guys? This indeed is a helpful question with its own purpose – Valentino Pereira Commented Jun 29, 2018 at 12:59 - @ValentinoPereira have you checked original question dates before determine duplicate guys – AZinkey Commented 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 Commented Oct 10, 2020 at 16:26 Show 3 more comments ## 58 Answers Sorted by: Highest score (default) Trending (recent votes count more) Date modified (newest first) Date created (oldest first) 1 2 Next 4994 Try: ```plain text 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. edited Jan 27, 2023 at 15:46 reformed 4,7711111 gold badges6565 silver badges9393 bronze badges bansi 56.9k66 gold badges4343 silver badges5252 bronze badges - sudo mysql -u username -p database_name < file.sql works in some cases. – Sergio Reis Commented Dec 29, 2021 at 16:31 - 8 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." – panda-byte Commented Mar 7, 2022 at 15:03 - What if I am using docker? :( – Raul Chiarella Commented Jun 23, 2023 at 19:29 - 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 – bansi Commented Jun 26, 2023 at 6:13 Add a comment 1065 A common use of mysqldump is for making a backup of an entire database: ```plain text mysqldump db_name > backup-file.sql ``` You can load the dump file back into the server like this: Unix ```plain text mysql db_name < backup-file.sql ``` The same in the Windows command prompt: ```plain text mysql -p -u [user] [database] < backup-file.sql ``` PowerShell ```plain text cmd.exe /c "mysql -u root -p db_name < backup-file.sql" ``` MySQL command line ```plain text mysql> use db_name; mysql> source backup-file.sql; ``` edited Oct 6, 2021 at 11:31 Peter Mortensen 31.6k2222 gold badges109109 silver badges133133 bronze badges vladkras 17.1k44 gold badges4949 silver badges5555 bronze badges - Is it me only one who has never been able to use < operator in mysql? (ubuntu18/20) – T.Todua Commented Jun 10, 2021 at 17:58 - 12 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. – Stijn de Witt Commented Jul 21, 2021 at 12:55 - where we put backup-file.sql? what path it looks by default? – temirbek Commented Apr 11, 2022 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 Commented Jun 24, 2022 at 11:10 - 1 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 – Christian Commented Mar 15, 2023 at 16:04 Show 1 more comment 514 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: ```plain text mysql> use db_name; mysql> SET autocommit=0 ; source the_sql_file.sql ; COMMIT ; ``` edited Oct 6, 2021 at 12:28 Peter Mortensen 31.6k2222 gold badges109109 silver badges133133 bronze badges Paresh Behede 6,26522 gold badges1919 silver badges1313 bronze badges - 10 Is there a way to do that in a single command line on the mysql command used for import? – Volomike Commented Jan 21, 2015 at 20:12 - 40 I agree that this is the best answer. The autocommit=0 portion made a world of difference in terms of the speed. – ᴍᴇʜᴏᴠ Commented May 23, 2016 at 21:36 - 5 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;. – hashchange Commented Jul 2, 2018 at 12:32 - 5 @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 – iateadonut Commented Mar 17, 2020 at 7:11 - 4 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 Commented Oct 6, 2021 at 13:17