How to export and import very large mySQL databases

I had a very large web site (30,000+ pages) that I wanted to move from one web host to another. Normally I would use phpMyAdmin to export tables or the entire database. This mySQL database was of course too large for that and phpMyAdmin would timeout trying to export, even if I exported only one table at a time (one table had 1.5 million records). It’s very easy to quickly export and import very, very large mySQL databases with even hundreds of thousands of records.

To accomplish this we can’t do it via web page tools or methods. So, we have to use the command line in UNIX. This will require a telnet (preferably secure telnet) account and a basic understanding on simple UNIX commands. You will also enough disk space free to extract your database. If you don’t have telnet access to your web site, you can always submit a support ticket telling them what to do based on these instructions. You will need to know in advance your database name, your database username and password, and whether or not your server is ‘localhost’ or a server name (and what it is). This information is normally already entered in your site’s database config or settings file. If you are using Wordpress it’s in your /wp-config.php file in the root of your site, if you’re using Drupal it’s stored in your /sites/default/settings.php file.

Here are the instructions to export your mySQL database into one file on the UNIX command line:

• login to your web site using telnet
• navigate to the directory where you want to store your extracted database file
• run the following on the command line:
  mysqldump -a -u USERNAME -p DATABASE > FILENAME.mysql
• check the size of your file (command: ls -al)
• gzip your file to make the download faster using the command: gzip FILENAME.mysql
• download your file in ftp to your local pc to upload and import into your new database

Again, if you don’t have telnet access you can probably submit a ticket at your web host and they will extract the file for you so you can download it. Once downloaded you need to upload the file to your new web host so you can import it into a fresh database. First, I want to make you aware of a PHP script called Big Dump that does a ’staggered import’ of mysql files into a new database. Even though it’s staggered (importing in chunks) it has it’s limits. I have successfully used it many times on databases under 50MB. It has also failed miserably for me on a 191MB database. If your database is too big to import using phpMyAdmin but under 50MB - give it a try first. Otherwise you’ll have to import on the command. If you use Big Dump you just enter your mysql server settings into the file itself, and I have had the best results uploading the mysql file to the server (same dir you place bigdump.php in) and hardcode the filename into the file before trying the import.

Now, if your file is >50MB or bigdump.php doesn’t work - I’m going to show you how to do a manual import of your mysql file:

• upload your mysql file to your new web site
• create a new blank database using myPHPAdmin
• note your database username and password, and your mysql servername
• unzip your database file with this command: gzip -d FILENAME.mysql
• run the following on the command line to import:
  mysql -u USERNAME -p DATABASE < FILENAME.mysql
• check your database in myPHPAdmin to make sure all the tables imported properly

That’s it! You’ve now learned how to successfully export and import large mySQL databases when myPHPAdmin or other tools won’t work! Again, if you don’t have telnet access, you can always ask your (new) web host to import the file for you. I did when I migrated from a host I used for 9 years to goDaddy. They don’t offer telnet access, so I uploaded the 191MB mysql file and gave them a call to submit a ticket. In less than 2 hours the import was done and I was very happy!

I’ve had a lot of people ask my why I use goDaddy, and I have to tell you…I have hosted with dozens of companies. I actually setup a small account with goDaddy 6 months ago just to try it out (it was only $3.99 per month). Now, 6 months later I’ve moved everything to them. All of my domain registrations, and all of my sites are completely hosted with goDaddy as of today (that big web site was the last one!). I was paying $29.99 per month for a professional webmaster account. I get the exact same thing (and more) with goDaddy for $14.99 per month. They have deals all the time where I pickup new domains for $1.99, and they had a special just today (when I called about the database import) where in July all web hosting was 25% off. It’s not just 25% off for new customers - it’s 25% off for ALL customers, whether you upgrade or just want to pay ahead. So I paid up a few months in advance to save even more money. That’s my shameless self-promotion for the day - use the host I use: www.GoDaddy.com

*UPDATE*

I almost forgot to add why this was a ‘blogging mistake’!  I was going to migrate all of my web sites to goDaddy last month.  I setup a new account a moved all but 2 or 3 sites - mainly because I couldn’t figure out how to get those huge databases off my old server and into my goDaddy account.  Because I couldn’t figure it out my monthly hosting renewal came up, and I ended up paying for another month.  Since I had a dedicated server (that I was grossly under utilizing), that blogging mistake ended up costing me $214!!  Then I finally did a quick google search for “export large mysql database” and figured it out in an hour.  My mistake was not doing enough research to fix the problem - it’s not like I was tackling something others hadn’t been through (and documented) before.  Google is your friend - always search for solutions to your problems!