将 SQL 转储导入 MySQL 时出错: 未知数据库/无法创建数据库

我对如何导入 SQL 转储文件感到困惑。如果不先在 MySQL 中创建数据库，我似乎无法导入数据库。

这是尚未创建 database_name时显示的错误:

username = 可以访问原始服务器上数据库的人的用户名。
database_name = 来自原始服务器的数据库名称

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

如果我以 root 用户身份登录 MySQL 并创建数据库 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

然后再次尝试导入 sql 转储:

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

我应该如何导入 SQL 转储文件？

186820 次浏览
小开
最佳答案

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

小开

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.

小开

This is a known bug at MySQL.

bug 42996
bug 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.

小开

If you create your database in direct admin or cpanel, you must edit your sql with notepad or notepad++ and change CREATE DATABASE command to CREATE DATABASE IF NOT EXISTS in line22

小开

I create the database myself using the command line. Then try to import again, it works.


提交答案