Today I had to take a MySQL database backup from one database and then restore it to a different database name (for development/testing purposes). I tried a number of different tricks that I found online through Google without any success. The issue was that the user I was using to restore the data was NOT an administrator (root) on the database so it was failing with permission errors – always with an error related to the name of the database I restored FROM.

Okay, time to go old-school. So… I opened the .sql file that was created by the MySQL backup and behold – it’s text! How great is that!? Not some cryptic gibberish like a SQL Server database backup file format.

When looking at the file a little, right there near the top I found the issue. The MySQL backup files by default assume you want to restore to the exact same file name. So then have this line:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;

In that line the database that I restored from was actually named “test”. As you can see, it checks for the existence of the database and if it doesn’t exist, it creates the database. Well, in this case I didn’t want that database created and my user didn’t have permissions to perform that action anyway.

So I changed the name of the old database (“test”) to match the name I wanted for the new database (“newTest”).

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `newTest` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;

Great – one error avoided! But thankfully, before existing the .sql file, I noticed the very next line in the file:

USE `test`;

Well, that’s no good. That’s going to use the ORIGINAL database that I performed the restore from. Yikes. If my user had permissions to that database it would have overwritten the original. Not good at all. So, let’s change that…

USE `newTest`;

I did a quick scan through the rest of the file and nothing stood out to me as an issue. I performed the restore (in this case using MySQL Workbench but any tool – even a command line – would have worked fine) and everything went nice and smoothly. Like magic I now have the data from the “test” database restored to my new database named “newTest”.

I hope this is helpful! Cheers, and happy hosting!