Problem with restoring MySQL database - mysqldump question

Question

I made a backup of my_database like this:

mysqldump --add-drop-database --databases 
          --user=my_username --password=my_password my_database > backup.sql

Then I deleted the database completely, and tried to restore it like this:

mysql --user=my_username --password=my_password my_database < backup.sql

I got the following error:

ERROR 1049 (42000): Unknown database 'my_database'

What am I doing wrong ?

I need to be able to restore the database if it was changed somehow or removed completely.

Bonus question: is that possible to read the password from a file rather than providing it in the command line ?

Answer 1

If I understand correctly, what you are doing, then you do not need a 'database' argument for the 'mysql' invocation, since the dump should contain the create database statements.

As for the bonus: you want a --defaults-extra-file option

Answer 2

The mysqldump command does not include the create database statement in its output. You need to create the my_database database before running the second command.