There have been times when all we had was a shared hosting account with limited functionalities. Those were early days but I wouldn’t be surprised if some of you are still struggling to import large SQL files when hopping from one host to another.
Back in the days, I use to run a Joomla website with a forum. The site had more than a thousand active users and by the second year, the database had grown considerably. Later on due to performance issues, I had to migrate to a better host. The only choice I had back then was to employ the manual backup and restoration method. I frantically searched for solutions and chanced upon one that I am going to share with you now.
phpMyAdmin is a very good tool to manage our database. However on Shared Hosting, when it comes to importing larger SQL files we face a lot of difficulties. There are many limitations that come with Shared Hosting such as memory and run-time limit that we cannot do anything about. It is practically impossible for beginners to import large SQL files on Shared Hosting without SSH access.
Having said that, I managed to migrate the website successfully with the help of BigDump.
So what is BigDump?
BigDump is a php script written by Alexey Ozerov. It allows you to import large SQL files regardless of the memory and run-time limit of the web server. It works on the principle that it restarts itself after importing a small part of the larger file. Yet, upon the next restart it resumes from where it left off. That is how BigDump is able to bypass the web server limitations.
How to import large SQL files using BigDump
- Download BigDump.
- Extract the downloaded file and upload bigdump.php to a folder inside htdocs or private_html. You can also create a new folder for this purpose but it should be accessible from a URL. For instance, yourdomain.com/dump/.
- Upload the SQL file via FTP to the same folder that contains bigdump.php.
- Open and edit bigdump.php using your File Manager by inserting the database name, username and password. This is the database that you want the SQL file to be imported into. You can also edit the file prior to upload using your favorite script editor.
- It is also recommended to insert the SQL file name that you have just uploaded.
- Open a new browser window and visit yourdomain.com/dump/bigdump.php.
- Click ‘start import’.
- Wait until its done without closing the window.
- Once you’re done importing, delete the folder that contains the SQL file and the BigDump script.
If you face any problems while importing make sure you follow these steps before exporting the SQL database:
- Login via phpMyAdmin to the database you want to export.
- Click Export.
- Select Custom under Export Method.
- Scroll down to ‘Object creation options’ and uncheck ‘IF NOT EXISTS’ under ‘ Create file options’.
- Scroll down further to ‘Data dump options’ and select ‘Neither of the above’ under ‘Syntax to use when inserting data’.
- Click Go and proceed with the download.
If you follow these steps before exporting SQL database, you’ll have lesser chances of errors with BigDump. Anyway, that is how you import large SQL files on Shared Hosting without Secure Shell.