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
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 -u username -p database_name < file.sql
Check MySQL Options.
Note-1: It is better to use the full path of the SQL file
--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
-n option), before you can import it.
Read more... Read less...
A common use of mysqldump is for making a backup of an entire database:
shell> mysqldump db_name > backup-file.sql
You can load the dump file back into the server like this:
shell> mysql db_name < backup-file.sql
The same in Windows command prompt:
mysql -p -u [user] [database] < backup-file.sql
C:\> cmd.exe /c "mysql -u root -p db_name < backup-file.sql"
MySQL command line
mysql> use db_name; mysql> source backup-file.sql;
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 ;
Among all the answers, for the problem above, this is the best one:
mysql> use db_name; mysql> source file_name.sql;
We can use this command to import SQL from 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 ~/Desktop ? mysql -u root -p password bank < bank.sql
And if your 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:
~/Project ? mysql -u root -p password bank < ~/Desktop/bank.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;