Ruby on Rails Database Backups and Exports

This article will explain how to backup your database the "Rails way" and explain how to create backup scripts for doing routine backups via crontab.

For different versions of MySQL

If you're moving between different versions of MySQL, set the schema dump format to :sql in config/environment.rb.
Digg This Tutorial.

The Rails way of Exporting your Database

cd /path/to/myapp
rake db:schema:dump
cp db/schema.rb /path/to/otherapp/db/
cd /path/to/otherapp
rake db:schema:load

The MySQL Way of backing up your database

First step, create a backup account for your database. This is known as intelligent system administration. If you're going to cron up something with the password on the command line or mess with the database directly, you should create an account with nothing but read permission for doing so. grant select on *.* to 'backupuser'@'localhost' identified by 'backupuserspassword' Good Practice and will keep you/your script from accidentally fubaring your DB.

MySQL Commands for backing it up

mysqldump -A --user=whoever --password=whatever > /path/to/mysqldump/`date +%Y%m%d%H%M%S`.sql

This will create a mysql backup with the date of the backup at the end of the file! You may want to then compress the file, so go ahead and do:

gzip -9 /path/to/mysqldump/*.sql

Creating a crontab for your backups

This will do a daily backup for your rails application database. Remember to use the backupuser we discussed earlier for the user name.

0 0 * * * /usr/bin/mysqldump -h HOSTNAME DATABASE -uUSER -pPASSWORD > /path/to/mysqldump/`date +%Y%m%d%H%M%S`.sql

Windows rails users backups/exports

If on Windows, you can look up scheduling a job via AT. Simply copy the mysql data files to another drive. Your backup batch file can look something like this: copy mysqldata D:backupmysql

SQLite Exports/Backups

SQLite has a simple command for doing backups of its database. All you have to do is:

sqlite backup script: `cp -a /path/to/my.db /backup/date/`

PostgreSQL Database Backups/Exports

0 0 * * * /usr/local/pgsql/bin/pg_dump -f /web/service0/database-backup/service0_$(date +%Y-%m-%d).dmp service0
#And you can do it manually like this:
[root@yourserver root]# su - service0
[service0@yourserver service0]$ tar -cpsj --exclude /web/service0/etc/daemontools/supervise --file /tmp/service0-backup.tar.bz2 /web/service0/ 
tar: Removing leading `/' from member names
[service0@yourserver service0]$

Example PostgreSQL Database Recovery

[root@yourserver root]# su - service0
[service0@yourserver service0]$ cd /web
[service0@yourserver web]$ tar xjf /tmp/service0-backup.tar.bz2
[service0@yourserver web]$ chmod -R 700 service0
[service0@yourserver web]$ createdb service0
CREATE DATABASE
[service0@yourserver web]$ psql -f /web/service0/packages/acs-kernel/sql/postgresql/postgresql.sql service0
(many lines omitted)
[service0@yourserver web]$ psql service0 < /web/service0/database-backup/database-backup.dmp
(many lines omitted)

Serious MySQL system admins, read this..

Mysqldump is considered by some to be a terrible way to backup a mysql database. It is designed for the purpose of debugging, development, and migration tool, not a backup tool. With that said, an alternative is to just do a LOCK TABLES on all databases you have and then do a file copy of the actual data in /var/lib/mysql (or whereever you have it). If you want to avoid any downtime at all, you can lock the tables and perform the backup from a seperate mysql server replicated from your primary data store. If you don't want to run a second machine or second mysql server on the same machine and you can incur a second or two of downtime, you can put your data store onto an LVM or EVMS volume, LOCK TABLES, snapshot the disk, UNLOCK TABLES then backup from the volume snapshot.

Conclusion

Making routine backups of your ruby on rails programs and applications is very serious business. All your hard work populating that database could be easily lost in a blink of a hardrive failure. Make daily or at least weekly backups of anything you consider important and worth saving.
Digg This Tutorial.