Rackspace cloud sites: Hidden SQL file upload cheat brought to you by META Q
Interior Ad 2 brought to you by The META Q

How to upload large SQL files onto Rackspace cloud sites

As with all service providers, Rackspace Cloud has a few unique quirks which can be quite frustrating at times. Take for example their Cloud Site service. Did you know that with a Cloud Site, you don't have SSH access? SSH access for the vast majority of Cloud Sites is not often needed, but the fact is, that if it were needed, it's flat-out not available. You can't even pay for an upgraded option.

Just because you don't have access to SSH, doesn't mean that you can't do some pretty cool stuff with the tools you do have access to.

Here's an example, which builds off of a previous article I wrote titled Uploading Large SQL Files.

With Rackspace, you do have access to phpMyAdmin, however, the maximum .sql file that you are allowed to upload is capped out at 16 megabytes. That's fairly reasonable for most sites you might encounter, but there always seems to be that one website you're working on that's just too big.

Now, because you don't have access to SSH with Rackspace, you won't have access to the phpMyAdmin core files, so, you can't manually increase the file upload limit for phpMyAdmin. Of course, since you don't have SSH access, you also can't access the MySQL command line to manually upload your .sql file to the database.

If you are a Rackspace user and you find yourself with no SSH access and a large .sql file, what are you supposed to do?

Let's get creative!

Overcoming the file upload limit

File configuration

To start out, you are going to need to have your .sql dump file available.  If you are reading this article, I'm assuming that you have a large .sql file you're working with and don't really need a walk through the process of creating the dump file through phpMyAdmin (of course, that's a pretty big assumption. If you find that you need help with this process, send me an email or post a comment below.  I would be happy to outline the process for you).

So, you have your .sql dump file, now you need to upload it.

To get everything ready to go, let's first start by setting up the files you will need in your web directory. Go ahead and SFTP into your account and, in the root directory (not the web root), create a folder titled 'scripts' and inside of that folder create another folder titled "database." Now, upload your .sql dump file to the database folder you just created. After that's finished, create a file in the scripts folder called "restore.sh" (you can name the file whatever you would like – just be sure to include the .sh extension).

Your file structure should look like the following:

Now that you have your .sql file with all of the records you want to load in to the database, let's go ahead and add a quick line of code to the restore.sh file so that the file "knows" what to do when executed.

Open the restore.sh file in your favorite text editor and add the following:

#!/bin/sh
   mysql -h <host-name> -u <user-name> -p'<password>' <database-name> < /full/path/to/rackspace/cloud/site/account/scripts/database/backup.sql

Where:

<host-name> is your hostname as provided by Rackspace
<user-name> is database username for this cloud site
<password> is the password associated with your username
<database-name> is your database name

Be sure to change the path to your scripts folder.

Once complete, your restore.sh file should look similar to the following:

Once logged in, click on "Hosting" in the left hand navigation, and then select "Cloud Sites" in the list that appears. Now, find the name of the site you want to work with in the list that appears and click on the link.

At this point, you should have all of the files that you need to load up the database. Let's go ahead and finish the process by configuring the final bit.

Server configuration

At this point, you will need to log in to your Rackspace Cloud Control Panel. Once you are logged in, select the following in the left hand navigation:

Hosting → Cloud Sites → Select the site you are working with.

Once you have selected the site you want to work with, your screen should look like the following:

Now, click on the "Features" tab. When this page opens, scroll down to the section titled "Scheduled Tasks ( also known as Cron jobs)" and click on the "Add New Task" button.

You should see a screen that looks like the following:

Now, in the Task Name, enter a meaningful name about this task. "Import Database" makes sense, but you can use what ever you would like here.

A 'Cron Job' or 'Cron' as it's commonly called is a process or task that runs at a specifically scheduled time on Unix based systems.

Select whether or not you would like to receive an email after the Cron is run (I'd recommend that you do, just in case). Next you will select the command language that is being used in the Cron. This would be the language that your shell script is written in. In this case, I used Perl, so go ahead and select “Perl” from the drop down list.

Finally, under "Command to Run," enter the path to your scripts/restore.sh file that you created via SFTP.  If you created this file in the root directory of your site, you will only need to enter "/scripts/restore.sh" in this text box.  Rackspace will prepend the Cloud Site directory information to the beginning of the path you enter here.

Configuring the Cron

Last but not least, let's configure when the Cron will run. Since you most likely only want this task to run once (when loading the large database file), set this to "Repeat by the hour" (don't worry, you can just turn the Cron job off after it has executed, so it doesn't continue to run).

Under frequency, I have selected "Once every hour" to ensure that the Cron is run at a time I specify.  And finally, under "Time" I have specified that it will run at 40 minutes past the hour.

Go ahead and click "Save."

The Cron job will be saved and at 40 minutes past the hour (or what ever time you specified), it will execute the code you entered into restore.sh, which will load your database file.

As soon as the Cron has run (if you selected to receive an email), Rackspace will send you an email to let you know. Once you have received the email confirmation, it's important remember to turn your Cron Job off, otherwise, it will run every hour which will overwrite any work you have done in the past hour. 

To turn off the Cron, in the Rackspace Control Panel, click "Edit Cron," and set the Status to "OFF" then click save. Your Cron is now turned off and will not run until you turn it back on again.

Questions? Comments? Tips? Share below.

Photo credit: Andrew-Hyde


Clinton Reeves's avatar

Clinton Reeves

Back-end developer at Q Digital Studio

Clinton Reeves is a back-end developer at Q Digital Studio. He is a self-proclaimed nerd and happily embraces said nerdiness. He lives his life in a world of text (code code code), and even dreams in code. Clinton's day isn't complete without at least eight cups of coffee.

Posted

8.21.2012

Categories

Code > Tips > Code Tips

Tags

2 comments >

What others are saying

@wesrice

I’ve found that it is a lot easier to connect to your database via a desktop database client (like Sequel Pro for mac, I’m not sure for PC) and import the database through there.

To do this, you will need to use the alternative host IP for your database’s connection credentials. I’ve added 100+MB databases this way in a matter of seconds.

Clinton Reeves

@wesrice - Funny you mention that - I was actually looking into that very process yesterday.  In all truth, if you have the remote ip address, I would completely agree with you.


Speak your mind