So we often have to upload databases on the fly through PHPmyAdmin or similar tools and it’s a pain in the proverbials when the database is over 50mb. There are a load of tools out there that offer splitting them up for you, and even on Mac OSx there are offerings too. That said we don’t always have great access to the server and at times we don’t have time to run scripts and troubleshoot any potential errors. But did you know that there is an easy way to do this that requires nothing more than a reasonable text editor? Follow the steps below to see how to upload a database that’s over 50mb easily?
Step 1: make a note of the file size of the downloaded .sql file and create a copy (just in case)
Step 2: work out how many splits you’ll have to make to ensure each file is smaller than 50 mb.
Step 3: Open the file in text editor (we use Text Wrangler on the Mac) and scroll down to the first approximate split point (so for 1 split scroll half way, for 2 scroll 1 third etc)
Step 4: Scroll up to the next ‘INSERT INTO’ Line and take all the content from BEFORE that point up to the 1st line and cut it. Paste those lines into a new document and title it file1.sql
Step 5: Repeat for each further split (ie file2.sql, file3.sql) or if it’s just one split needed then save your remaining sql file as final.sql and now you should have at least 2 sql files, each under 50mb. Import each one (in sequence) via PHPmyAdmin and you’re good to go.
It’s not rocket science but it works. If for some reason the sql file is just one long INSERT INTO then the split still works but you have to take the original ‘INSERT INTO’ line and paste it at the top of each split file (without doing step 4’s scroll up element). If you have a really big sql file of 300+ mb you might want to look at the automated systems but to be honest the method above with cut / paste is fast enough for most folks.