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:loadThe 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`.sqlThis 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/*.sqlCreating 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`.sqlWindows 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.