How to import a .sql file in MySQL?
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.
- 8 years ago
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:
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.
mysqldump -u username –-password=your_password database_name > file.sql
mysql -u username –-password=your_password database_name < file.sql
What if I don't have a password?
Piping (`mysqldump db -uuser -ppass|mysql new_db -uuser -ppass`) removes the need to use an intermediate file.
Another way to import dump files when
source <filename>doesn't work is to do the following:
~> mysqldump --user=<user> --password=<password> <db_name> > <export_file_name>.sql
> mysql -u <user> -p <pass> <db_name> mysql> USE <db_name>; (if you didn't already select) mysql> \. ~/<export_file_name>.sql
You are missing the password in command. Use the following.
mysql --u [username] --password=[password] [database name] < [dump file]
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.