• Sales: 1-800-961-2888
  • Support: 1-800-961-4454

How do I import a large MySQL Database?

You should be able to use the Online Manager (see Working with a MySQL database) to handle imports for databases which are less than 16 MB in size without issue.

However, there may be cases where you need to import database data that exceeds 16 MB.

One of the many options you have to handle this task in the Cloud Sites environment is a script which you can schedule via a Cron job.

Here is an example to assist you with setting this up:

mysql -h DB_HOST -u DB_USER -p'DB_PASSWORD' DB_NAME < /path/to/file/db_restore.sql

Create a new text file and add the above code to it and save it as restore.sh

In the above script, you will need to replace the portions in ALL CAPS with your actual information as follows ::

/path/to/file/ - This is the absolute path to your files. You can find this path by clicking on Hosting, Cloud Sites, Features, and scrolling down. This will be the "Linux Path" listed there. An example would be: /mnt/target02/123456/www.domain.com (Note, the filename is not included in that path)

DB_HOST - Database Host an example would be mysql5-9.wc1

DB_PASSWORD - the password of the target database

DB_USER - the database username

DB_NAME - the name of the database you are restoring to

Upload this file to your Cloud Site (for security purposes, we recommend keep this file outside your webroot) and create a "perl" cron job to schedule the task (the Perl option runs shell scripts as well). For step by step instructions on how to create a cron, please review this article How_do_I_enable/disable_a_cron_job?

Put the name of the file, which, in our example is restore.sh in the "Command To Run" field. Select Perl as the Command Language and then select the minimum interval in which you wish to run your restore script (5 minutes).

There are logs available in your /logs/ folder each time a cron task is performed and you receive an email confirmation letting you know if the task was successful or not.

Please note: The Cron task has 15 minutes to complete. If the script takes longer than 15 minutes to complete, it will timeout. If this is a problem for you please break up the import into smaller pieces (.sql files) and re-schedule the task as necessary.

For more information on Cron and related tasks, please see the following articles:

Cron FAQ's:

What is a cron job?

How do I enable/disable a cron job?

How do I schedule a cron job?

How do I create a cron job to backup my MySQL database?

© 2011-2013 Rackspace US, Inc.

Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License

See license specifics and DISCLAIMER