In Search of Everything All at Once

Introduction

This is a MySQL backup script I wrote for our own internal use at OCS Solutions and have since released it as open source via the GPL.

It queries the MySQL server to fetch a list of the databases, and then calls mysqldump to back up each one individually.

It's well suited for servers with many databases, but not ideal for databases that are very large (greater than 15 GB or so).

Requirements

• Ruby 1.8.2 or later
• MySQL Ruby Gem
• .my.cnf file setup properly in /root/.my.cnf

Download

The script involves two parts, the Ruby script itself, and the configuration file:

• mysql-backup
• mysql-backup.yml

The configuration file needs to live in /etc/mysql-backup.yml, and the script can go anywhere (but I recommend /usr/local/bin. You also must make the script executable by running chmod a+x on it, and make the configuration file visible only to root by running chmod 0600 /etc/mysql-backup.yml.

Usage

Simply run mysql-backup and the script will backup all of your databases to the folder specified in the configuration file. You can run (on most systems):

ln -s /usr/local/bin/mysql-backup /etc/cron.daily/mysql-backup

to make the backup script run nightly. You may have to adjust the path above depending on where your mysql-backup script lives.

Troubleshooting

If you get an error like this when trying to run mysql-backup:

/usr/local/bin/mysql-backup:4:in `require': no such file to load -- mysql (LoadError)
        from /usr/local/bin/mysql-backup:4

change the first line of the mysql-backup script from:

#/usr/bin/ruby

to

#/usr/bin/ruby -rubygems