Moving MySQL databases from one server to another is usually a pretty painless and relatively easy process when the phpMyAdmin tool is available on the hosting servers. An unforeseen hiccup recently presented itself to me when trying to move a WordPress MySQL database from one account to another on the same ISP. It was a situation I had never run into before as usually a move is from one ISP to another. The solution turned out to be rather simple but not necessarily obvious, so I thought I’d share it here!

MySQL Database Export / Import

When moving a MySQL database to a new server via the phpMyAdmin tool, the process consists of:

1. Exporting the current database, which creates a text file with a .sql extension
2. Creating a new database on the target server
3. Importing the .sql file into the newly created database on the target server

In the case I recently encountered though, upon attempting to import the .sql file into the new database I recieved this error:

After a little digging it appeared that the issue was related to the fact that the embedded database info from the exported file was recognizing the database as already existing… because the original existed still on the same ISP directory! (Remember, in this case we’re moving a database from one account to another on the same ISP.)

The Solution

So how did I avoid the access denied error and successfully import the database to it’s new home? Read on!

Open up the .sql file with any text editor and examine the first section, which looks something like this:
[codesyntax lang=”text” lines=”no”]

-- phpMyAdmin SQL Dump
-- version 2.6.4-pl3
-- http://www.phpmyadmin.net
--
-- Host: xxxxxxxxxxxxxxxxxxxxxx
-- Generation Time: Aug 21, 2012 at 08:03 AM
-- Server version: 5.0.91
-- PHP Version: 5.3.3-7+squeeze13
--
-- Database: `xxxxxxxxx`
--  

CREATE DATABASE `xxxxxxxxxx` DEFAULT CHARACTER SET latin1 COLLATE latin1_german2_ci;
USE xxxxxxxxx; 

-- --------------------------------------------------------

[/codesyntax]

 

Find and delete the entire line:
[codesyntax lang=”text” lines=”no”]

CREATE DATABASE `xxxxxxxxxx` DEFAULT CHARACTER SET latin1 COLLATE latin1_german2_ci;

[/codesyntax]

 

On the following line you’ll see the name of the database which was exported:
[codesyntax lang=”text” lines=”no”]

USE xxxxxxxxx;

[/codesyntax]

 

Change this to reflect the name of the NEW database into which you are importing the .sql file.

Now save the .sql file with these changes and then import it into the newly created database on the target server. Instead of that nasty #1044 Access Denied error, you should happily see:

Success!