db_migrate

#!/bin/bash

# Exits script on error
set -e

# Export a backup from db1 into writable storage
mysqldump -h $DB1_HOST -P $DB1_PORT $DB1_NAME -u $DB1_USER -p$DB1_PASS > path/to/writable_dir/data_migration.sql

# Import the backup into db2
mysql -h $DB2_HOST -P $DB2_PORT -u $DB2_USER -p$DB2_PASS $DB2_NAME < path/to/writable_dir/data_migration.sql

# Remove the Backup File
rm -f path/to/writable_dir/data_migration.sql

Easy Cloud to Private Cloud Database Migration on Pagoda Box

Currently on Pagoda Box, the process of moving from a cloud database to a private cloud database requires a manual migration. This script will do the most of the important steps for you.

This script assumes that you have already created your private cloud database and that you currently have a db1 and db2 in your app. If the component IDs (db1, db2) are different, you will need to adjust this script to use the correct IDs/environment variables. It also assumes that you have existing shared_writable_dirs specified in your Boxfile. This is where the temporary backup file will be stored.

1. Include This Script in Your Repo

Pagoda Box doesn't give you native shell access to your app, but they do allow you to execute scripts through Cron Jobs. It's a little hacky, but totally works. Download this file, include it in your repo, and deploy it to your app.

You'll need to modify the script to use the correct component IDs and a valid filepath for one of your shared writable directories.

2. (Optional) Deploy a Maintenance Page

Because this is a manual migration, there is no sync process between the old and new databases. This means that data could be added to the database after the initial backup is taken. This data will not get imported into the new database and will be lost.

The best thing to do to prevent this is to simply take the site offline temporarily while you run the migration script. That way no data will be lost.

3. Run This Script as a Cron Job

Again, Pagoda Box doesn't give you native shell access to your app, but you can run commands as cron jobs, and that's what you should do with this script. After adding a cron, you have the ability to "Run Now" so you don't have to wait for the cron schedule to fire the script.

With this script deployed to your app, add the following cron job in your dashboard using the filepath where this script is in your repo:

bash path/to/db_migrate

Set the cron schedule to something that rarely runs, like "0 0 1 1 *" (you don't want this thing running accidentally).

I recommend including your email address in the cron output recipient list. That way you'll get an email with all the output when the cron job finishes.

After saving the cron job, click "Run Now" to fire the script and run the migration.

4. Update Your DB Credentials & Deploy

Once the data migration finishes, go ahead and update your database credentials in your code to point at the new db and deploy. If you put up a maintenance page, you'll want to take that down too. Once deployed, you should be all set.

5. Remove the Migration Cron Job (IMPORTANT)

Now that the migration is complete, you should remove the cron job. You don't want to forget that its there and have it run when the cron schedule triggers it. That would be....bad.