My Big Dump Experience Was Thrilling Beyond Words
Okay, the title sounds a bit nasty. But the big dump I’m talking about is a mySQL database dump.
If you own a WordPress blog that you are about to transfer to a new host, you, too, should consider the big dump. It’s one of the best freebies available on the Internet.
I’m in the midst of upgrading servers and cleaning up some blogs and web sites. One of the blogs I am transferring is an article feeder/live blogging type of site that people can submit their material to for back links. There’s an automated article pulling script that is part of the blog.
The Problem - Server Timeouts with mySQL Database
It’s a Wordpress blog that’s been running for over 2 years. There are over 20,000 blog entries, so you can imagine how big the database has become. Huge doesn’t even begin to describe it. Maybe monstrous is most appropriate.
I was using a VPS server that was constantly crashing and its security was so screwed up that I couldn’t even access my control panels because the server kept timing out. I decided to transfer the blog over to one of my reseller accounts at HostNine, which has been a reliable web hosting service for me over the years.
I downloaded my database using the download plugin available for WordPress and discovered that the mySQL database was over 175 megabytes. I decided to try a download without some of the “extras” that were a part of the blog, such as search statistics, that were slowing down the posting time anyway. This smaller database was a little over 50 megabytes.
The control panel of my hosting account has a restore function and from the mySQL function, it also has phpMyAdmin. While these functions can either restore or import data, the timeout limit was 51,200 KiB. I tried to import both databases with no success.
The Reaction - Split the mySQL File? Convert to Excel CSV?
I had no way to remotely transfer the data to the new host. In fact, the hosting service techs couldn’t handle it, either. In short, I had to figure out a way to transfer the mySQL database to the new host or condense the database somehow.
I thought about just starting the blog out fresh, but decided that it wouldn’t be fair to the contributing writers to just delete everything without notice.
I considered cleaning up the database and getting rid of old or poorly written articles in order to make it smaller. The super huge database would not open in any program I had because some of the data appeared to be corrupted. I was able to open a portion of the smaller 50 MB one in Excel with the hopes that I could edit it and then export it into a CSV file to see if CSV would be small enough to import from phpMyAdmin.
The problem here was that even the smaller mySQL database (at 50 MBs) was too large for Excel. I don’t remember what the limit was on lines, but in any event, there were way too many lines in my database for one Excel worksheet.
Then I thought about using a file splitting program to see if I could load up the database in chunks, but decided against that because I couldn’t find anything specifically having to do with mySQL file splitting.
So.
What to do?
The Solution - Big Dump
May the programming gods, if there are any, bless a guy named Alexey Ozerov, who created the Big Dump.
Big Dump is super easy to use. First, you download the free PHP file and then make some simple changes; i.e., include your database name and log in information.
Next, upload your Big Dump php file into a special “working folder” within your public_html folder (or /home/ or similar folder). Be sure to FTP it in text mode.
Next, transfer your mySQL file in binary mode to the special “working folder” on your server. In the example below, I transferred everything into a “bigdump” special working folder.
CAPTION: Upload your mySQL files and BigDump php file to a special folder.
CHMOD BigDump as 777 or 775 (777 would not read on my host, but 775 worked). Then, using your internet browser, go to your bigdump.php page. It will show your mySQL database that you want to dump.
Click the “Start Import” link and Big Dump goes to work.
CAPTION: BigDump at work.
My database had an error in it consisting of two of the exact same entries. At first, this scared me into thinking that I’d still have to figure out a way to edit the huge mySQL file. But all I needed to do was click the “Start from the Beginning” link at the bottom of the screen and the old tables were dumped. BigDump resumed its work. In about 5 minutes time, Big Dump successfully dumped the entire contents of my 50 MB mySQL WordPress database into my account.
CAPTION: BigDump successfully dumped the mySQL data.
Did it work? You bet. Here’s the WordPress blog, with all 20,000 plus posts.
Conclusion
If you have a WordPress blog or any other program that relies on a large mySQL database, make sure that you regularly backup your database to your hard drive in case of a crash. When it’s time to transfer, be sure to get Big Dump — and when you’re done, send Alexey a donation. For the headache that he saves all of us with the problem of exporting large mySQL files, he certainly deserves contributions!
![[del.icio.us]](http://images.del.icio.us/static/img/delicious.small.gif){kind=small}
