Why does Mysql or Mysqldump say Error 1049 no database found?

Question

I exported the SQL database for my WordPress before rebuilding my Debian 8 VPS because I wasn't able to fix some error messages I've been getting. I did the following.

$ mysql -u root -p wordpressDB < /var/www/html/example.com/backups/backup.sql

The output message I get is

ERROR 1049 (42000): Unknown database 'wordpressDB'

I also tried doing this.

MySQL -u wordpress -p wordpressDB < backup.sql

But when I put in the password, it said it was invalid. I opened up the SQL file in notepad, and the database name does exist. I exported this file using mysqldump, and I tried importing using MySQL and mysqldump, but both times it was unsuccessful.

Solution

Based on your comments below your question, it sounds like you probably omitted the --databases option when you used mysqldump to backup the database.

If you backup the databases by doing mysqldump <database-name>, then the backup does not re-create the database for you. When you restore from that backup, you need to create the database that you want to import into and then import into that database, which may or may not be the same name as the database you exported from earlier.

If you do not want to be required to do this, then, when creating your backup, you export like this: mysqldump --databases <database-name>. Doing that, the backup will have the command to re-create the database when it is imported back into MySQL.

There are also other differences between those two usages. If you use the first version, then any symbols after the database name are considered table names to include in the backup. As in: mysqldump MyDatabase Table1 Table2 Table3 to backup tables 1, 2, and 3, but no others.

In the second variation, all symbols after the initial database name are also treated as additional database names, so you can get multiple databases. mysqldump --databases HRDatabase WebsiteDatabase DevTestDatabase That should export all three databases.

But back to the main point: next time you use mysqldump, if you specify the --databases option then you will not need to manually create the database and use it before importing, as that would then be taken care of for you.

---

For importing the database you need to use mysql not mysqldump in order to create the database automatically (only in case you exported with --databases <database-name> option:
mysql -u root -p < /var/www/html/example.com/backups/backup.sql
(as mentioned in a previous comment by HBruijn,

Using mysqldump for importing your database will not create your database automatically even if you exported it with --databases <database-name> option, these will not work without creating the database first:

mysqldump -u root -p < /var/www/html/example.com/backups/backup.sql
mysqldump --all-databases -u root -p < /var/www/html/example.com/backups/backup.sql
mysqldump --databases mydatabase_name -u root -p < /var/www/html/example.com/backups/backup.sql

Comments

[HBruijn]

" I opened up the SQL file in notepad, and the database name does exist." - The problem is not in the SQL file, the error comes from the MySQL server and there the database 'wordpressDB' does not exist (yet)... Depending on how the backup was created backup.sql may already include the CREATE DATABASE statements and you can use mysql -u root -p < /var/www/html/example.com/backups/backup.sql without selecting a database on the commandline

[HeavenlyHarmony]

I tried that, and got ERROR 1046 (3D000) at line 22: No database selected

[HBruijn]

Then you may need to create the database first...

[Omar]

You can actually skip creating the database while importing by exporting the database in the way mentioned in the below answer by @Loduwijk. This will include the CREATE DATABASE command for you in the exported .sql file.