Tuesday, November 9, 2021

"ERROR 1049 (42000): Unknown database mydatabasename" when importing sql data into mysql/mariadb

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.


No comments: