Large MySQL Imports with GoDaddy: How to get your database imported

Feb 15 2010

Every once in a while, I have some project that requires working with one of GoDaddy's servers. By far, the biggest frustration for me when dealing with GoDaddy is getting MySQL databases uploaded. I've tried all kinds of crazy tricks, from exporting MySQL databases in "bite sized chunks" to writing SQL processors that break large imports into smaller ones.

But today I think I found the Right Solution (TM) to the problem: Use GoDaddy's database restoration tool to load a large SQL file. Basically, instead of treating this as an initial import, we treat it as a restoration from a backup file.

Here's how you do it. <!--break-->

Generating a SQL file to upload

First, you need to get a SQL-based dump of the database you are going to load. Typically, you do this with the program mysqldump:

$ mysqldump -u myusername -p mydatabase > mydatabase.sql

In the code above, replace myusername with your database user name, and mydatabase (both instances) with the name of your database.

Important: If the database on GoDaddy has a different name than the database on your other machine, change mydatabase.sql to the name of the GoDaddy database, e.g. myGoDaddyDB.sql. This is a requirement from GoDaddy.

Loading the file

Assuming you have a file called mydatabase.sql we can now load it into GoDaddy. The main trick is to tell GoDaddy that we are restoring from backup. That bypasses the normal limitations for timeouts, file size, and the like.

Here's how you do it:

  1. Log into the Hosting Control Center for you site.
  2. Go to Content > File Manager and look for the directory dbbackups. (You can, of course, get to this directory using FTP or SFTP, or whatever else you have enabled for you account.) See the image at the top of this article for an example.
  3. Save the mydatabase.sql file into the dbbackups folder.
  4. Go the the Databases > MySQL tab and find the appropriate database (mydatabase in this example).
  5. Click on the pencil icon ("Edit/View Details")
  6. When the details page loads, click on the Restore icon (database with a wrench next to it)
  7. It should find the mydatabase.sql file. You can simply tell it to restore from that file.

This process will kick your database into Pending Restore mode until the SQL file has been imported. But once the import is done, all of you data should be available.

Why does this work?

Basically, GoDaddy allows you to make backups of your database, and also allows you to restore from those backups. In MySQL, the common form of a "backup" file is actually just plain old SQL. So what we are doing here is uploading a "backup" of a database that never existed on the server.

From there, GoDaddy sees our SQL file as a perfectly legitimate source for importing into the database.

Other strategies to try

If for some reason the above does not work for you, you can try the following other mechanisms:

Using phpMyAdmin

Go to the phpMyAdmin site for your domain and try loading from the Import tab. You may experience all kinds of issues, like timeouts, file size limitations, and "Server is not there" errors. I have experienced moderate success by splitting files into 1MiB chunks and uploading one at a time. However, to do that I had to tune mysqldump, and then write a ruby script to break the file down.

Getting a shell account

It is my understanding that some accounts have shell access. If that is the case, you may have more luck by using the mysql command line program.