Error 1049 42000 unknown database root

I can’t seem to login to my tutorial database development environment:

Ayman$ mysql -u blog -p blog_development
Enter password: 
ERROR 1049 (42000): Unknown database 'blog_development'

I can login to the database fine without the blog_development portion:

Ayman$ mysql -u blog -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1858

Not sure what gives as I granted all access:

mysql> GRANT ALL PRIVILEGES ON blog_development.*
    -> TO 'blog'@'localhost'
    -> IDENTIFIED BY 'newpassword';
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR 'blog'@'localhost'
    -> ;
+----------------------------------------------------------------------------------------- --------------------+
 | Grants for blog@localhost                                                                                        |
 +----------------------------------------------------------------------------------------- --------------------+
| GRANT USAGE ON *.* TO 'blog'@'localhost' IDENTIFIED BY PASSWORD    '*FE4F2D624C07AAEBB979DA5C980D0250C37D8F63' |
| GRANT ALL PRIVILEGES ON `blog`.* TO 'blog'@'localhost'                                                        |
| GRANT ALL PRIVILEGES ON `blog_development`.* TO 'blog'@'localhost'                                           |
+----------------------------------------------------------------------------------------- --------------------+
3 rows in set (0.00 sec)

Anybody have a clue what to try? Thanks! Also, side note- is it weird I have multiple root users?:

mysql> select User from mysql.user;
+------+
| User |
+------+
| root |
| root |
|      |
| root |
|      |
| blog |
| root |
+------+
7 rows in set (0.00 sec)

Edit: for those asking- I created the database blog with the CREATE DATABASE command in MySql. Here are my active databases:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+  
| information_schema |
| blog               |
| mysql              |
| performance_schema |
| test               |
+--------------------+ 
5 rows in set (0.00 sec)

rcmysql stop
pkill mysql   (NOTE: wait until notification is given that mysqld ended, then hit <ENTER>)
/usr/bin/mysql_install_db

cd /var/lib/mysql
ls -al | grep mysql

< Back to Support Search

For questions or concerns with the SUSE Knowledgebase please contact: tidfeedback[at]suse.com

Mysqldump is a tool frequently used in creating a backup of a mariadb or mysql database. To use this tool is pretty straight forward, just run below command:

$ mysqldump -u root -p mydatabasename > mydatabasename.sql 

The above command is fine, and we can always restore the data from the sql file into a database provided we have the database already in place, using below command:

$ mysql -u root -p mydatabasename <  mydatabasename.sql

A problem appears when we are transferring the sql file to another server which does not have the database already created. If we try to import the sql file, without the database already existed, we will get below error:

ERROR 1049 (42000): Unknown database mydatabasename

We can prevent this by adding an option to our mysqldump command. The option is «—databases» or in short «-B». To test it out, we can use below commands (dump the db, drop the db, and import back the db from the sql file):

$ mysqldump -u root -p —databases mydatabasename > mydatabasename.sql

$ mysqladmin -u root -p drop mydatabasename

$ mysql -u root -p < mydatabasename.sql     

This time, you would not get the above error, since the «—databases» option will add «CREATE DATABASE» query into the sql file, and that query will create the database if the database is not already exist.

Often while performing WHM backups, users notice an error “mysqldump: Got error: 1049: Unknown database”. This can happen when the database does not exist in MySQL.

As a part of our Server Management Services, we help our customers with similar requests related to WHM/ cPanel.

Let us today, discuss the possible reasons and fixes for this error.

What causes “MySQLdump: got error :1049 :unknown database ” error

MySQLdump helps to perform the logical backups, generating a set of SQL statements like DDL, DML for reproduced backup Schema. It dumps one or more MySQL databases for backup or transfers to another SQL server.

We can also generate output in CSV, other delimited text or XML format. The main drawback of MySQLdump is that the restoration procedure can take a long time to execute if the database is very large.

While performing WHM backups, at times, we can see the following error in the backup log:

The backup process encountered the following error: The backup process on “hostname.example.server” encountered an error.
[2021-05-10 02:25:26 -0600] mysqldump: Got error: 1049: Unknown database ‘example_database’ when selecting the database

Generally, This error indicates that the related database exists in a cPanel user’s database map, but the database does not exist in MySQL.
 

How to fix “MySQLdump: got error :1049 :unknown database ” error

The first thing that our Support Engineers perform on seeing this error is to check whether the database exists within MySQL. They does this by running the following command as the root user via SSH:

mysql -e "show databases;" | grep example_database

Replace example_database with the database found within the backup error in the backup logs. We can find the backup logs within /usr/local/cpanel/logs/cpbackup.

If the above command does not display any results, it indicates that the database does not exist in MySQL.

Thus, In order to correct the backup errors, we have to remove the databases that do not actually exist in MySQL from cPanel.

For this, we initially log in to the cPanel account for the particular database user. Then, we navigate to the Databases section and then click on the MySQL Databases option.

Here, we just need to delete the corresponding database from the current database section.

 
[Need any further assistance to fix cPanel errors? – We’re available 24*7]

Conclusion

The “MySQLdump: got error :1049 :unknown database ” triggers while performing cPanel backups. This can happen when the database does not exist in MySQL. Today, we saw how our Support Engineers fix this error.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Я не могу войти в мою среду разработки учебной базы данных:

Ayman$ mysql -u blog -p blog_development
Enter password: 
ERROR 1049 (42000): Unknown database 'blog_development'

Я могу войти в базу данных отлично без части blog_development:

Ayman$ mysql -u blog -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1858

Не уверен, что дает, когда я предоставил весь доступ:

mysql> GRANT ALL PRIVILEGES ON blog_development.*
    -> TO 'blog'@'localhost'
    -> IDENTIFIED BY 'newpassword';
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW GRANTS FOR 'blog'@'localhost'
    -> ;
+----------------------------------------------------------------------------------------- --------------------+
 | Grants for [email protected]                                                                                        |
 +----------------------------------------------------------------------------------------- --------------------+
| GRANT USAGE ON *.* TO 'blog'@'localhost' IDENTIFIED BY PASSWORD    '*FE4F2D624C07AAEBB979DA5C980D0250C37D8F63' |
| GRANT ALL PRIVILEGES ON `blog`.* TO 'blog'@'localhost'                                                        |
| GRANT ALL PRIVILEGES ON `blog_development`.* TO 'blog'@'localhost'                                           |
+----------------------------------------------------------------------------------------- --------------------+
3 rows in set (0.00 sec)

Кто-нибудь знает, что попробовать? Благодарю! Кроме того, боковое примечание — это странно, у меня есть несколько пользователей root?:

mysql> select User from mysql.user;
+------+
| User |
+------+
| root |
| root |
|      |
| root |
|      |
| blog |
| root |
+------+
7 rows in set (0.00 sec)

Изменить: для тех, кто спрашивает: я создал блог базы данных с командой CREATE DATABASE в MySql. Вот мои активные базы данных:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+  
| information_schema |
| blog               |
| mysql              |
| performance_schema |
| test               |
+--------------------+ 
5 rows in set (0.00 sec)

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