Matt Connolly's Blog

my brain dumps here…

Tag Archives: mysql

Installing mysql2 gem on OpenIndiana

Quick one, with mysql 5.1 installed from standard OpenIndiana package repository, the ruby mysql2 gem can be installed with this command:

$ gem install mysql2 -v '0.2.18' -- --with-mysql-dir=/usr/mysql/5.1 --with-mysql-include=/usr/mysql/5.1/include/mysql

This one requires /usr/gnu/bin in the front(ish) of your path, so you may need an `export PATH=/usr/gnu/bin:$PATH` before you do this.




This also works for the latest version ‘0.3.11’:

$ gem install mysql2 -v '0.3.11' -- --with-mysql-dir=/usr/mysql/5.1 --with-mysql-include=/usr/mysql/5.1/include/mysql

[UPDATE 2 – ruby-2.0.0]

Ruby 2.0.0 compiles as a 64-bit executable, which means another bit needs to be added to the command line options:

$ gem install mysql2 -v '0.3.11'-- --with-mysql-dir=/usr/mysql/5.1/ --with-mysql-include=/usr/mysql/5.1/include/mysql --with-mysql-lib=/usr/mysql/5.1/lib/amd64/mysql

A Ruby gem to backup and migrate data from a Rails app

Ever wanted to quickly duplicate your database in a rails app so you can do some development work on real data, but not risk breaking it?


Rails has some very good strategies for managing your schema with rake tasks such as db:migrate, db:schema:load, etc. Switching between production and development environments lets you use different databases.

Great, but how do you get the data from one environment to another?

With MySQL you can duplicate a database (easy with phpMyAdmin), and with Sqlite3 you can simply copy the database.db file.

But what if I want to go from MySQL to Sqlite3 just for working??

There are some database differences, and there’s a rake task that’s already managing the database structure. I need something to move the data as well.

In my particular case, I’m using redmine as an issue tracker, hacking on it, and also evaluating the Chiliproject fork. So I’d like to be able to move all my data: schema, data and files easily from one to another.

The Rails-Backup-Migrate Gem

So, I put together a gem to do this. It’s inspired by Tobias Lütke’s backup.rake file he posted a good 5 years ago:

It appears to be for an early version of rails. It also had some issues with tables belonging to “many-to-many” relationships, and was also slow because it instantiated every single ActiveRecord object as it went.

I’ve sped the process up by skipping the ActiveRecord object steps, and updated the table definitions for rails 2.3 (redmine runs on this) and it appears to be working for rails 3.0 as well.

This gem provides the following rake tasks to a rails app:

rake site:backup[backup_file]                          # Backup everything: schema, database to yml, and all files in 'files' directory.
rake site:backup:db[backup_file]                       # Dump schema and entire db in YML files to a backup file.
rake site:backup:files[backup_file]                    # Archive all files in the `files` directory into a backup file.
rake site:backup:schema[backup_file]                   # Dump schema to a backup file.
rake site:restore[backup_file]                         # Erase and reload db schema and data from backup files, and restore all files in the 'files' directory.
rake site:restore:db[backup_file]                      # Erase and reload entire db schema and data from backup file.
rake site:restore:files[backup_file]                   # Restore all files in the 'files' directory.
rake site:restore:schema[backup_file]                  # Erase and reload db schema from backup file.

The default backup_file is "site-backup.tgz" and is relative to rails app root directory.


Installing manually:

$ gem install rails-backup-migrate

Installing with Bundler:

Add the following to your Gemfile:

gem rails-backup-migrate

And then

$ bundle install

Using rails-backup-migrate:

To use the gem in your rails app, simply add the following line to the end of your Rakefile:

require 'rails-backup-migrate'

Which will load the above tasks into your rails app’s task list. If you’re using bundler

$ # backup everything into the default file 'site-backup.tgz'
$ rake site:backup
$ # now, restore into another instance (eg: clone/checkout another copy of your project)
$ cd /path/to/another/instance/of/my/rails/app
$ rake site:restore[/path/to/original/instance/of/my/rails/app/site-backup.tgz]


The source code is available on github:

This is the first gem I’ve made and published, and I hope it makes life a little bit easier for someone!

Rails data backups, independent of database

With my continuing my interest in redmine / ChiliProject, I’m really wanting a way of backing up my data that is database independent. I did a bunch of searching around, and the best solution I found was here. However, it appears a little out dated, and didn’t work in Rails 2.3 for a few reasons:

  • There isn’t always a 1:1 mapping between tables and models. (Example: many-many relationships create extra tables).
  • The excluded tables list was outdated (probably from an earlier Rails)[1]

There were also some comments about the export being quite slow. This could have been because every record of every table was loaded up in an ActiveRecord model instance before being dumped to the YML files.

I addressed these issues and made a new version. Can’t attach a file to this blog, so it’s on pastie for now:

I’ve tested this getting data out of mysql and into sqlite3 and that works just fine. This is really handy for getting the data into a test environment quickly without having to set up more mysql databases, users, etc.

[1]: I’m testing this for Rails 2.3.11 as Redmine and ChiliProject are Rails 2.3.x apps.

If this saves you some time, or could be improved, please let me know.