SQL stands for System Query Language. It is the way an applications program or web server talk to the database and you also can use the same language to communicate with a database. It talks through an engine called MySQL, the most widely used database query language.
In this post we will increase the import file size limit using a third party program that is small and compact and will split a database file into manageable chunks for upload. The program has been scattered about the internet and so is included here to download for convenience. The program is by Philip Lehmann-Böhm.
A MySQL database is the main component for WordPress and other CMS systems. Therefore this topic does not only cover WordPress but could be useful in several situations.
On a WAMP localhost server the database files are located at c:\wamp\bin\mysql\mysql-version\bin\wordpress-directory. This contains the database tables and executables. In theory you should be able to move this directory/folder from one WAMP server to another.
In order to download a copy of the database for backup or for transferring to another computer, it’s better to make one file with a .sql extension than move the individual tables and executables because it is much more likely to work. To do this you would export the database.
When you import a database, you are actually importing the structure and tables and so before importing you must first create a database. In phpMyAdmin create a new database and make sure it is selected when you click on the ‘Import’ tab.
The Import tab allows you to choose a file, then click on ‘Go’ and the information from the selected file will populate the new database.
If you look to the right of the button that imports a file you will see the max upload size, usually 128MiB. If your database file is larger than this max then you will not be able to upload the database. And now we come to the main point of this post.
Just like all the advice on the internet for solving the max_upload_size in a WordPress install, there’s just as much confusion with this issue too. The same three directives are used to increase the upload capacity but the settings for WordPress use the php.ini file in \wp-admin whereas to do the same for phpMyAdmin you amend the php.ini file as shown at the path above.
Unfortunately, on a hosting package it may be too tricky to alter the Apache values and you might have to contact the hosting provider. With a local host server, it’s just as tricky and no matter how many values you change that 128MiB number will not change in phpMyAdmin.
Despite trawling the internet for hours and hours, all you learn is that since phpMyAdmin version 2.7.0 the import engine was re–written and upload size problems should not happen. But you’ve checked your version and it’s like version 4.7.0.
Anyway, my point is if you absolutely want to waste your time don’t let me stop you – otherwise download the program and extract it. It’s a self running executable, nice and simple. What you should see if the image as shown below.
The program is pretty straight forward:
Step 1: Choose your .sql file
Step 2: Here you can select the max size of a part. So if your database is 550Mb and your upload limit is 218MiB then you could set your value to 1000 Kilobytes or (100 Megabytes).
Step 3: You can use your temp directory for this. This is where the parts will be placed.
Step 4: Click on that ‘execute’ button to start the process.
Back at the phpMyAdmin Import tab, import the part files in order. You’ll see a readme file too which tells you what to do.
Good luck with that.