Troubleshooting database imports on hostgator or other shared hosts

Posted on by

When restoring an old MySQL-based site on Hostgator, I tried to use phpMyAdmin to import the database dump-file. It failed to import no matter how I cut the file up into segments or compressed it. It just kept erroring out, suggesting the file was too large – even when it definitely wasn’t. Here’s how to get around this problem:

You will have to enable SSH Access on your hosting account. For hostgator, the instructions for getting started with SSH are here:

http://support.hostgator.com/articles/hosting-guide/lets-get-started/how-do-i-get-and-use-ssh-access#connect

Once SSH is available to you, upload the dump file to your website.
Open a terminal window and type after the prompt:
hostgatorusername$ ssh -p 2222 hostgatorusername@WEBSITE.IP.ADDRESS

where “hostgatorusername” is the username on your hosting account.

and 2222 is the port number (it may differ for your host).
So for example, my session went like this:
My-iMac:~ hostgatorusername$ ssh -p 2222 hostgatorusername@WEBSITE.IP.ADDRESS
hostgatorusername@WEBSITE.IP.ADDRESS's password: [type password here]
Last login: Sat Jun 29 11:01:24 2013 from c-95-260-217-8.hsd1.mi.comcast.net
It will then prompt you with something like:

-jailshell-4.1$

Then type:

mysql -u hostgatorusername_databaseuser -p -f hostgatorusername_databasename < /home/sitename/path/to/backupfile.sql
where
hostgatorusername_databaseuser is the complete database username

and
hostgatorusername_databasename is the complete database name

and
/home/sitename/path/to/backupfile.sql

is the correct local system path to your database dump file.

Sample session:

 jailshell-4.1$ mysql -u hostgatorusername_databaseuser -p -f hostgatorusername_databasename < /home/sitename/path/to/backupfile.sql
Enter password: [type password here]