Moving a large WordPress Multisite Database to Hostgator Shared Hosting
Recently we were faced with the challenge of moving a large WordPress Multisite from Amazon EC2 over to another shared web host with CPanel on it. We knew this was going to be a problem as quite often shared web hosts have an upload limit for SQL databases – In this case it was 50MiB. The size of the file we were trying to import was 264MiB when zipped.
So how did we overcome this limitation? We can’t use the shell being shared hosting. We used an open source PHP program aptly named “Big Dump”. I know there’s certain images that spring to mind when you first hear of this program. Luckily when I got to the website to download the file we were presented with an image of a piece of earthmoving equipment – Thank heavens for that!
What this software promises to do is upload chunks of the SQL database at a time (default is 3000 lines) to avoid the PHPMyadmin import limit and it does just that!
All you need to do is download and follow the instructions on the Big Dump Page located here. It’s quite simple really.
- Upload the SQL database and Big Dump script to a folder on your website (make sure you transfer the files using ASCII or binary when it says to)
- Edit the file with your database details
- Run the PHP Script
We did come across one problem though, we still hit a resource limit. All we did in this change the settings to pause for 5 seconds in between each 3000 lines and we were good to go. Essentially we uploaded our 264MiB file in under an hour and it works perfectly.