Import SQL dump into MySQL

Question

I'm confused how to import a SQL dump file.

I can't seem to import the database without creating the database first in MySQL. This is the error displayed when database_name has not yet been created:

username = username of someone with access to the database on the original server.
database_name = name of database from the original server

$ mysql -u username -p -h localhost database_name < dumpfile.sql   
Enter password:  
ERROR 1049 (42000): Unknown database 'database_name'

If I log into MySQL as root and create the database, database_name

mysql -u root  
create database database_name;  
create user username;# same username as the user from the database I got the dump from.  
grant all privileges on database_name.* to username@"localhost" identified by 'password';  
exit mysql

then attempt to import the sql dump again:

$ mysql -u username -p database_name < dumpfile.sql  
Enter password:  
ERROR 1007 (HY000) at line 21: Can't create database 'database_name'; database exists

How am I supposed to import the SQL dumpfile?

Answer 1

Open the sql file and comment out the line that tries to create the existing database.

Answer 2

It sounds like your database dump includes the information for creating the database. So don't give the MySQL command line a database name. It will create the new database and switch to it to do the import.

Answer 3

This is a known bug at MySQL.

http://bugs.mysql.com/bug.php?id=42996 http://bugs.mysql.com/bug.php?id=40477

As you can see this has been a known issue since 2008 and they have not fixed it yet!!!

WORK AROUND
You first need to create the database to import. It doesn't need any tables. Then you can import your database.

1. first start your MySQL command line (apply username and password if you need to)
   C:>mysql -u user -p

2. Create your database and exit

   mysql> DROP DATABASE database
   mysql> CREATE DATABASE database
   mysql> Exit

3. Import your selected database from the dump file
   C:>mysql -u user -p -h localhost -D database -o < dumpfile.sql

You can replace localhost with an IP or domain for any MySQL server you want to import to. The reason for the DROP command in the mysql prompt is to be sure we start with an empty and clean database.

// Max