I recently had an experience trying to move an OpenCart store from a Linux based server to a Windows one and thought I would share the methods I adopted to get everything moved across easily. I learnt a few things about PHP and especially phpMyAdmin which offers some hidden gems to make it easier. Although this was actually me moving an OpenCart store, there is nothing stopping you applying the methods to any eCommerce software like PrestaShop or osCommerce.

So, the store itself is huge. No matter what settings I applied to the Windows (destination) server it just just had a fit and directed me to some documentation on MySQL which didn’t really make any sense. Note: I rarely get involved with software beyond web applications and frameworks!

 

I think the total database export encompassed over a million rows and ran up to about 200MB, so here are the steps I took to get the file imported all in one go rather than having to break the huge SQL file into pieces and lose days of my life doing it one by one.

Create a new Upload directory in phpMyAdmin and change the config settings

So, the route we are going to take is creating a new folder, telling phpMyAdmin that the folder contains an export file which it can use instead of asking for you to upload a file directly through the web browser. All you need to do is navigate to the root phpMyAdmin folder and create a new folder called “upload”. It technically doesn’t have to be called that but for the purposes of this blog post, we will keep it simple.

Once you have your folder ready navigate to:

• phpmyadmin/libraries/config.default.php

Open it up in your text edit or choice and do a simple Find for the term “Upload”. Keep going until you see the following line:

$cfg['UploadDir'] = '';

This line is telling phpMyAdmin that there is no folder currently associated with a place to store SQL files, all we need to do is change it so it points to the folder we’ve just created. So, it should look like:

$cfg['UploadDir'] = 'upload';

Remember: it’s case sensitive so if you have named your folder Upload then you will need to put the capitalised name into the setting line. Also, if you have put it anywhere other than the root phpMyAdmin folder then you will need to put the entire filepath in there rather than just the folder name.

Allow for higher execution times in PHP Settings

If you’re using a Windows based server then go to your PHP manager and find the “PHP Runtime Limits” or, if you’re on Linux, go to the php.ini file and find the “max_execution_time” setting then up it to something like 900 (15 minutes). Even without having to upload the file everytime, there will be a long processing time because of the number of queries the import will make so this just gives you more time to get it imported without it timing out. After you’re done, I would recommend putting it back to how it was before.

Backup and Import Your Old Database

OK, so now go to your old website and export everything into a single SQL file, no matter how big and wait for it to download entirely. Once it has, use FTP to add the file into the new “upload” folder you’ve just created, now go into the phpMyAdmin section of your new server and click “Import”. Now you will see a new dropdown menu below the file upload button which allows you to pick your SQL backup file to use rather than uploading a file through your browser like the image below:

You can add as many files as you like to the this folder but do yourself a favour and just use the one at a time and that reduces the chance of human error! Leave it running for a while and if it still times out then go back to your max_execution_time setting and increase it from 900 to whatever your need. Then, it should import and there is no limit to the size of the file so you don’t need to import sections or break up export files or nightmares like that. Nice and easy!

If you have any problems, or want to share how you got on then let me know in the comments below!