How to import a .sql file in MySQL?

Question

I am trying to import a .sql file using MySQL Workbench and I get this error:

ERROR 1046 (3D000) at line 28: No database selected

I have first created an empty database called with the same name as the .sql file but it doesn't work. I have also tried to do it with the mysql command client using:

mysqldump -u root database > file.sql

But it says a have an error in my SQL syntax. Furthermore, I don't know the path where I have to set the file.sql.

I was able to import mysqldump using the below mysql> use mydatabase; mysql> source sql_file.sql; — Giridharan Venugopal, Jul 28 '17 at 02:03

juergen d · Answer 1 · 2016-04-15T19:51:28.450

69

Export:

mysqldump -u username –-password=your_password database_name > file.sql

Import:

mysql -u username –-password=your_password database_name < file.sql

edited Apr 15 '16 at 19:51

answered Sep 14 '12 at 11:46

juergen d

851
7
10

10

There has to be **no** space between `-p` and `password` – tombom Sep 14 '12 at 11:47
What if I don't have a password? – Sep 14 '12 at 11:57
Then skip password parameter. – Kazimieras Aliulis May 31 '13 at 10:35
Piping (`mysqldump db -uuser -ppass|mysql new_db -uuser -ppass`) removes the need to use an intermediate file. – Pacerier Oct 12 '15 at 10:05
3

The database has to exist in MySQL for this to work, I think. – Noumenon Feb 05 '16 at 02:02
ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4_0900_ai_ci' – Owl Jan 20 '22 at 13:31

score 42 · Answer 2 · edited Jul 11 '13 at 09:37

42

You can also import an .sql file as an already connected user to the database manner :

mysql> use your_database_name;

mysql> source file.sql;

edited Jul 11 '13 at 09:37

Nick Chammas

14,170
17
73
119

answered Jul 11 '13 at 08:44

tdaget

803
1
12
23

4

source D:/path/file.sql; works superb! remember the front slash instead of back slash. – Imdad Jan 09 '15 at 11:08

score 7 · Answer 3 · answered Jul 15 '15 at 07:17

Another way to import dump files when source <filename> doesn't work is to do the following:

Dump

~> mysqldump --user=<user> --password=<password> <db_name> > <export_file_name>.sql

Import

> mysql -u <user> -p <pass> <db_name>
mysql> USE <db_name>;  (if you didn't already select)
mysql> \. ~/<export_file_name>.sql

score 6 · Accepted Answer · edited Apr 13 '17 at 12:42

juergen d's answer is of course correct; however, considering your error message, you can also add to your SQL file at the beginning line like:

USE your_database_name;

This should also do the job and let you import under the Workbench.

When you are using mysqldump, the exported file will be saved in the current folder. It doesn't matter under what path it is. Just when importing from command line you need to be at the same folder or specify path to the file. But this isn't the case when you are using visual tool like Workbench, where you need to select the file from folder tree anyway.

score 4 · Answer 5 · answered Sep 14 '12 at 11:47

4

You are missing the password in command. Use the following.

mysql --u [username] --password=[password] [database name] < [dump file]

answered Sep 14 '12 at 11:47

Database name is not required if the SQL file contains DB creation + `USE [database name]`. – tukusejssirs Jul 28 '21 at 16:11

score 1 · Answer 6 · edited May 31 '13 at 10:01

For Windows, in the MySQL server installation directory (e.g. C:\Program Files\MySQL\MySQL Server 5.5), in the my.ini file, add the following line under [mysqld] in the server section:

max_allowed_packet = 16M

And save the changes. (If you are not allowed to save, then copy and paste to desktop and edit again, then paste it in the same location.)

After that, restart your MySQL server.

How to import a .sql file in MySQL?

6 Answers6