The Easy Way

I previously posted my script for transferring from SQLite3 to MySQL here with involved step by step instructions. I’m going to mention it again with ultra simplified instructions to make it easier to digest and to save time for people who know what they are doing.

Here they are:

  1. Set up a development DB using SQLite3 (presumably you already have one from working locally)
  2. Set up a production DB using MySQL
  3. (optionally) Make sure they work
  4. Install the plugin in the vendor/plugins folder
  5. Run the script with rake db:backup:to_db TARGET_DB=production

Tip If you don’t have a development DB and set up your local install as production, just change your database.yml file to say that production is development…

Tip I typically bootstrap my production DB before I migrate over. It works and all the bootstrap data gets wiped.

Tip Use this carefully and please back up your original DB before you transfer over. If you get the syntax backwards you will wipe your data.

Tip Both databases have to be the same structure before doing the transfer. So you have to migrate your extensions and make sure your source DB is using the same version of Radiant as the destination DB.

Link to Script…

How to Transfer from SQLite to MySQL

I noticed something while working on a couple Radiant sites. I found that when I was building them locally, I prefer to work with SQLite3 databases. Then when I deploy them they use MySQL. Since so much of the configuration and site template lives in the database, I wanted an easy way to go from SQLite to MySQL. In the end I built it!

Wouldn’t it be Nice… to Backup from MySQL to SQLite? And vice-versa?

At the same time, I always thought it would be nice to be able to create a backup of the live database in SQLite. That way, if a client ever wanted their site ‘packaged up and portable’ as it were, it would be easy, just dump the production DB into a SQLite development DB, zip up the radiant folder and you have the whole site ready to run.

My ‘Plugin’ – Installation

I created this as a plugin for Radiant. Just download the package (at the end of this entry), uncompress into your plugins folder and you’re ready to go.

How to Use it

It’s fairly simple. Make sure you have SQLite and MySQL set up and running. Then configure your installation to use a SQLite DB for development and a MySQL DB for production.

  adapter: sqlite3
  database: db/development.sqlite3
  adapter: mysql
  database: myradiant_production
  username: radixhound
  password: paSSwerd
  host: localhost

Then make sure your target DB is pre-created. So if you don’t have a production DB up to this point, you can do this.

rake db:migrate
rake db:migrate:extensions

Then when you want to transfer from development to production, you can do this…

rake db:backup:to_db TARGET_DB=production

To do the reverse, you will do this…

rake production db:backup:to_db

Or Backup to another SQLite DB altogether…

You can also configure a third, fourth or fifth DB in your database.yml file and use that to back-up to, in case you don’t want to wipe out your development DB. Just add something like this to your database.yml file…

  adapter: sqlite3
  database: db/development.sqlite3

Then you can do this..

rake production db:backup:to_db TARGET_DB=backup

Download the Goods

PS: I also have a version that works for Mephisto sites. you only get it in zip format ‘cause I have somewhere to be right now.

PPS: I figured this out from someone else’s script that did something similar-ish but not quite this. I don’t remember who or what though, sorry.