How To Move A Large MySQL Database

Recently, we were confronted with the task of moving a database-driven website to a new domain. Typically, this is a fairly easy job, however, the situation was complicated by two main factors: (1) the size of the database and (2) the limitations of the web host. So if you use shared web hosting for your website, then you will probably want to read on.

How Large is a Large Database?

The answer to this question depends on your web host. But typical large file limits are set around 8,000 kilobytes to 52,000 kilobytes. But we have seen limits set as low as 2,000 kilobytes. To find the restrictions for your webhost, you should probably navigate to the database import form. Otherwise, simply call the support provided by your host.

For most websites, the restrictions are reasonable and will not prevent importing and exporting databases. However, there are times, such as with large CMS websites or forum websites, when these limits are troublesome. (Depending on your web host, you might also encounter some bandwidth limitations when moving your database.)

How To Move Your Large Database

There are a few options that you have for moving your large database.

1. Contact your web host to see if they can temporarily increase your import limits.

Sometimes web hosts can modify the settings on your account to allow you to upload larger files. If this is not possible, perhaps they can import the database for you. This is probably the easiest solution and requires the least amount of work.

2. Import tables separately.

For small databases, the entire database can be imported via a single upload. However, it may be necessary to import each table separately. This can drastically reduce the file size.

3. Import groups of records.

Sometimes there is one large table which cannot be imported all at once. In this case, you will need to import the table, a few records at a time. Fortunately, phpMyAdmin (a tool for administering MySQL databases) has an easy solution for this problem. See the image below.

SQL Import To import groups of records, use the Partial Import option. This option allows some records to be imported. When the script interrupts, due to file size, you can import the database file again, but skip the already-imported records, to avoid redundancy.

One other option for importing a certain amount of records at a time is by creating multiple export files for large tables, each containing a small amount of records. See the image below.

SQL ExportAfter navigating to the Export tab, you will see an option for dumping a certain number of rows. You can use this to create a series of import files for a large database table. You will then need to import these files one-by-one into the new database.

 

In most cases, moving a large database will be a rare task. However, if you regularly have the need to move and import databases, it might be a good idea to find a web host with more lenient upload restrictions. Need help on finding a web host? See our article Web Hosting We Like and Heartily Recommend.

Tags: ,

3 Responses to “How To Move A Large MySQL Database”

  • Roger November 6, 2008 at 4:27 pm

    Nice tutorial, I kept all my web designs in a very big DB and know is time of moving it, this info will be very usefull for me thanks

  • Digital Datasets December 18, 2010 at 5:47 pm

    Hi, doing the transfer manually will result is timeout due to server and PHP parameters, even tweaking the parameters. To accomplish the task I used a script called BIGDUMP, hope it is available on the web for free. It uses time delay and data chucks. Hope it helps.
    Sergei from Edigitalz.

  • Brady January 3, 2011 at 12:38 pm

    Guys if you are moving a large database in WordPress there is a plugin that is very handy. I believe it is called WP Migrate DB.

Trackbacks

Trackback URL:

Leave a Reply

Want us to work on your project?

Contact us today for a quote. Click here to submit details regarding your project.

If you are making a general inquiry, send an email to info@velvetblues.com

Go Daddy Deal of the Week: Cheap .COM Domains! Offer expires soon!