Large MySQL Imports with GoDaddy: How to get your database imported
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.
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 -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:
- Log into the Hosting Control Center for you site.
- Go to Content > File Manager and look for the directory
_db_backups. (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.
- Save the
mydatabase.sqlfile into the
- Go the the Databases > MySQL tab and find the appropriate database (
mydatabasein this example).
- Click on the pencil icon ("Edit/View Details")
- When the details page loads, click on the Restore icon (database with a wrench next to it)
- 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:
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.