Tips & Tricks

MySQL Databases - Export From Old Web Host / Import To New Web Host

Once again, something that seems simple turns out to have unexpected complexities.

Okay, you are moving your web site to a new host and want to take the contents of your MySQL database along for the ride. You quickly ascertain that you cannot simply "publish" the database along with the rest of your site, nor can you grab a copy of the database file and FTP it to the new host. No, it seems that the data must be exported from the old database and then imported into the new.

You log onto to your old host's control panel and then the database administration tool - most likely your host uses phpMyAdmin for this purpose - and, right there in the main frame is a link titled "Export". How much easier can it get?

A few minutes after clicking the "Export" link and studying the next screen, you have happily downloaded to your local drive a file which contains all the information necessary to recreate the database on your new web host.

Now at the new host's control panel, you open a similar session of phpMyAdmin, click the "Import" link, browse to the file on your local drive, and then click the "Go" button. Hmmm...

Error

SQL query:

-- phpMyAdmin SQL Dump
-- version 2.7.0-pl2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 16, 2007 at 02:45 AM
-- Server version: 5.0.24
-- PHP Version: 4.4.1
--
-- Database: `test_db`
--
CREATE DATABASE `test_db` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;

MySQL said:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci'

Some Internet research suggests deleting the offending "DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci" from your input file and giving it another go. The results are certainly different, but the Import has still failed:

Error

SQL query:

-- phpMyAdmin SQL Dump
-- version 2.7.0-pl2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 16, 2007 at 02:57 AM
-- Server version: 5.0.24
-- PHP Version: 4.4.1
--
-- Database: `test_db`
--
CREATE DATABASE `test_db` ;

MySQL said:

#1044 - Access denied for user: 'test_admin@localhost' to database 'test_db'

The problem is, you are trying to import (actually, Create) a copy of your old database when you should be trying to import your old data.

So, go back to your old host's control panel, load phpMyAdmin, and select a database from the dropdown list in the screen's left margin (or simply click the link if there is only one database present) then click the "Export" button at the top of the next screen.

In the screen which follows, select to highlight one or all of the data tables from the list in the left column. On the right, set the "SQL output compatibility" to an appropriate value (probably MYSQL3.23) and select the "Save as file" and "Zipped" options. Click "Go" to create the file and save to your local drive as before.

Now back at your new host, open phpMyAdmin and click the link ["test_db (0)" or some such] in the screen's left margin as you did above. On the following screen, click the "Import" button at the top, browse to the local file, and then click "Go".

Your existing table structure and data are imported into the previously created database on your new host.

Some hosts can be picky about naming conventions for databases, insisting that the first 8 characters of any database name be equal to the first eight characters of your domain name for example. This means there is a good chance that you will not be able to give your new database the same name as was used previously on your prior host. Suffice to say that you will most likely need to make adjustments to your code to accommodate the new database name.