Dec 242008
 
Part of the Wonderful WordPress Wednesdays Series - Previous in series         Next in series

Welcome to the tenth installment of my Wonderful WordPress Wednesday series. Oh, and Merry Christmas Eve to all those celebrating today!

Over the weekend I was moving several websites around (see my Moving Your WordPress Blog To A New Server In 10 Easy Steps for a walkthru on this process) and discovered that one of my (seldom used) blogs had a database file that was too large to import using phpMyAdmin at my web host. It was just under four megabytes, so easily within their 51MB limit, but still, it kept timing out. Now, WiredTree (my webhost) is very accommodating and I’m sure if I had contacted support they would have gladly (and easily) imported the file for me. However, I knew there had to be a solution and I wanted to figure this one out on my own. Well, on my own with Google’s help, of course 😉

The first “solution” I came across was to use a program called MySQLDumper which I dutifully downloaded, unzipped, uploaded and accessed only to discover that in order for it to work I would need to actually create the backup in MySQLDumper.  Well, I wasn’t interested in a solution that would require me to install a program multiple times on multiple servers run by multiple webhosts, so I deleted that bad boy and kept looking.

Next I found the simple and elegant solution: BigDump by Alexey Ozerov.  BigDump is a single php file that you download from Alexey’s site.  You then change a few paramaters within the file, upload it and your backup, and voila!  You’re all set.  Here’s what I did, step by step…

  1. Visit the BigDump: Staggered MySQL Dump Importer page and download the file.  I used ver. 0.29b (beta)
  2. Unzip BigDump.zip and open the file (BigDump.php) in a text editor.
  3. Modify the following lines to match your database and backup-
    • Line 40 – $db_server   = ‘localhost’;
    • Line 41 – $db_name     = ”;
    • Line 42 – $db_username = ”;
    • Line 43 – $db_password = ”;
    • Line 47 – $filename         = ”;     // Specify the dump filename to suppress the file selection dialog
    • Line 67 – $db_connection_charset = ”;
  4. Upload the modified BigDump.php and your SQL backup file to the webhost where the new database is located
  5. Visit http://www.YOUR-DOMAIN.com/BigDump.php
  6. Click “Start Import”
  7. IMPORTANT: When the script has successfully run, delete bigdump.php and your dump files from your server.

A couple of notes…

  • BigDump has the charset defaulted to ‘latin1’ while most phpMyAdmin’s I’ve encountered have it set to utf8, so make sure you set this correctly.
  • When creating your backup file make sure to uncheck ‘Extended Inserts’ as BigDump isn’t able to split such SQL queries.  This will make your backup (or dump) file much larger (mine almost doubled from just under four megs to just over 7 megs), but will allow BigDump to install it smoothly.

There ya go!  No more worries about moving large databases.  Not only did it handle my 4MB to 7MB blog backup easily, but it imported my 120MB+ forum database dump just as smoothly.  To give you some idea of the size of the forum database I have the fastest DSL connection available to homes and it took more than 25 minutes just to upload the sql backup file.  But it only took BigDump around 7 minutes to successfully install it – something that phpMyAdmin was simply not created to handle.  Man, I just loves me some freeware 🙂

Until next time, if you have any questions, thoughts, or concerns, please comment below and remember that giving this article a Thumbs Up with StumbleUpon will up your karma quotient for the day 😉

Part of the Wonderful WordPress Wednesdays Series - Previous in series        Next in series

  No Responses to “Importing Large MySQL Databases With BigDump”

  1. […] presents Importing Large MySQL Databases With BigDump posted at Philaahzophy, saying, “Moving a database-driven site (like a blog) to a new server […]

  2. […] » WordPress MySQL exercisesPython Script to Backup MySql Databases (WordPress or other databases)Importing Large MySQL Databases With BigDumpMySQL Upgrade: 1and1 is not the best host in the world | TheGarage […]

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)