Writing a blog post about loading large databases on a shared host has been in the back of my mind for almost a year. It all started when I wanted to put a replica of MTTProNetwork.com on the web, for testing purposes. Then my client could do testing herself and only instruct me to go live with the changes when she was certain nothing had broken. (Note: As of this writing these changes are not yet live, for external reasons).

As a step in putting the replica on-line I had to put the site's Drupal database on-line. So I made a new blank database by going to my CPanel and clicking the MySQL Databases icon and went to PHPMyAdimin, selected the database, and then chose "Import". [Note: In order to make things more secure when I set up the site, I created a new database user and gave that user privileges only on the new database, but that's not part of this post.]

Importing the large database with PHPMyAdmin failed on my host. I ended up successfully importing that database dump with bigdump.php. So I was thinking that this post would be about those methods. [Basic instruction on PHPMyAdmin for import and export are also at drupal.org].

But when I was researching this post and tried importing that same large database onto my shared host it just worked. So what had changed? After a little poking around I determined that I had increased PHP's memory_limit to 128M as I described in a previous blog post. [In that post I actually used the limit 98M, but I had settled on 128M for myself.]

To make a long story short, I placed a copy of my host's php.ini with the line:

In my account's web root in directory public_html. See that previous blog post for details. That change apparently improved the ability of PHPMyAdmin in my CPanel to import large databases.

There is one other possibility for something that changed to cause my CPanel's PHPMyAdmin to become more capable. I have another php.ini in my home directory that is being used when command-line PHP starts on my web host. I increased the memory_limit in that one as well. I did this in following the plans in the first comment to that previous post to get the command-line tool drush working on my shared host.

Even though I have learned all these tricks to set up capable Drupal sites on my shared host, the bottom line is that it is still annoyingly time-consuming to manipulate Drupal sites on shared hosting, and I need to be able to set up whatever extra servers (e.g. RealChat for chatting or Apache Solr for facetted search) at will. Because of this, I have long ago learned to set up virtual Linux servers from scratch for Drupal and other things; I do most oif my development on a MAMP server on my laptop, and put things live for current clients on a VPS host rented from VPS.net. The only reason crotown.com is still on shared hosting is lag (a.k.a. "inertia") due to time-constraints. My plan is to move crotown.com to a Rackspace Cloud Server as my next big project for my business. I will blog about the change when it happens.

UPDATE Sept. 1, 2010:
See this excellent related blog post for details of command-line MySQL dumps and restores for large databases.