ASPNetFAQ.com: What is ASP.NET?

Technology posts on ASP.NET, IIS, Windows (+ a little Linux), Cloud Servers, Hosting, and more!
  • Blog Home
Search the site...

Restore MySQL Dump File to a Different Database Name

Tweet
Share2
2 Shares

Restore MySQL Data to a Different DatabaseToday 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!

More from my site

  • Linking spam sent through shared IIS SMTP server to a userLinking spam sent through shared IIS SMTP server to a user
  • Update Your WordPress Database Connection StringUpdate Your WordPress Database Connection String
  • How to Backup a MySQL Database (Using Workbench)How to Backup a MySQL Database (Using Workbench)
  • Orchard: Custom Content in Sub-FoldersOrchard: Custom Content in Sub-Folders
  • Log Parser: Pulling Valuable Data From IIS LogsLog Parser: Pulling Valuable Data From IIS Logs
Tweet
Share2
2 Shares
cytanium, Hosting, MySQL, tip

2 comments on “Restore MySQL Dump File to a Different Database Name”

  1. GS test says:
    March 31, 2013 at 10:27 am

    Restore MySQL Dump File to a Different Database Name | Ponderings – Various Topical Thoughts by Brad Kingsley

Proverbs 19:20

"Get all the advice and instruction you can, so you will be wise the rest of your life."

A Note On WordPress Hosting

Our main focus is of course .NET, but with a mix of Linux, virtualization, and other technologies. But if you're really looking for the best WordPress hosting specifically, read my WordPress host review to save yourself hassle AND money!




Recent Posts

  • What makes good web hosting?
  • jQuery Mobile C# ASP.NET and N5 Networks Software Repository
  • Open Source Bug Tracking Software and the Orchard Project
  • ASP.NET Development with Dreamweaver MX: Visual QuickPro Guide
  • Kendo UI Sample, ASP.NET Ajax Tutorial & More

Tags

.NET ASP.NET Automation blog centos cloud CMS css cytanium Development/Coding Email gmail Hosting htaccess IIS javascript Learning Linux logparser MVC MySQL Orchard OrcsWeb performance PHP PowerShell redirect RHEL security server SherWeb smtp SQL/Databases System Administration tip Troubleshooting Ubuntu virtualization Visual Studio web farm web hosting WebMatrix Windows windows server Wordpress

Categories

  • ASP.net development
  • Development/Coding
  • Hosting
  • IIS (Internet Information Services)
  • SQL/Databases
  • System Administration
  • Virtualization
(c) 2019 ASPNETFAQ.com