Smoothly import big WordPress mysql database with bigdump

phpmyadmin-sql-max-file-size-limit

You cannot import mysql database of file size more than 7.168KiB from your site’s PHPmyAdmin. Even though its compressed with gzip or zip, your_database.sql.gz shouldn’t exceed that maximum file size limit.

So how to import larger mysql database?

The workaround is importing it with Bigdump script. You can download the script from that linked page. Follow the steps below to get started,

STEP 1:
After you have exacted the downloaded bigdump zip file, open the bigdump.php, change the database configurations, hold on for a minute, read along, before you FTP it.

STEP 2:
The next step is to create a temporary folder, name it like ‘dump’ or ‘import’ etc…on your server. So that you can access the folder at yoursite.com/dump. You create this folder, so that when you complete importing, you can delete it.

STEP 3:
Now upload your_database.sql.gz or without compressed to that folder.

STEP 4:
Now before uploading the bigdump.php, I must warn you that bigdump by default processes your mysql database by considering every 300 lines as one query. So it must have a semicolon for every 300 lines if it hasn’t already for a smooth import.

This has been the problem with bigdump, especially with WordPress databases, the script aborts with an error “Stopped at the line 314. At this place the current query includes more than 300 dump lines. That can happen if your dump file was created by some tool which doesn't place a semicolon followed by a linebreak at the end of each query, or if your dump contains extended inserts. Please read the BigDump FAQs for more infos.

And the developer isn’t planning to do anything except recommend that you export the database with extended inserts turned off. Bigdump can’t handle databases with extended inserts. So instead of manually placing a semicolon after every 300th line, you can get away with changing the $max_query_lines to a total number of lines your mysql database has or greater than that. Do a search for $max_query_lines¬†on your bigdump.php, you will find it at line 95 to 98.

STEP 5:
Once you have done that, save your bigdump.php and FTP it to that temporary folder you created in STEP 2.

STEP 6:
The final step is to open the bigdump.php on your browser, the link should likely be http://www.your-site.com/dump/bigdump.php. You will see the uploaded database file there, import it.

That’s it, it should take about 5 to 10 minutes or longer depending on the size of your database.

After it’s been imported, make sure it’s indeed imported and working. Delete the temporary folder, as you don’t want to expose your database file to the public.

Leave a Reply

Your email address will not be published. Required fields are marked *