Databases

Databases

Currently supported databases:

• MySQL
• PostgreSQL
• MongoDB
• Redis
• Riak

Examples

The following examples should be placed in your Backup configuration file.

Backup::Model.new(:my_backup, 'My Backup') do
  # examples go here...
end

MySQL

database MySQL do |db|
  # To dump all databases, set `db.name = :all` (or leave blank)
  db.name               = "my_database_name"
  db.username           = "my_username"
  db.password           = "my_password"
  db.host               = "localhost"
  db.port               = 3306
  db.socket             = "/tmp/mysql.sock"
  # Note: when using `skip_tables` with the `db.name = :all` option,
  # table names should be prefixed with a database name.
  # e.g. ["db_name.table_to_skip", ...]
  db.skip_tables        = ["skip", "these", "tables"]
  db.only_tables        = ["only", "these" "tables"]
  db.additional_options = ["--quick", "--single-transaction"]
  # Optional: Use to set the location of this utility
  #   if it cannot be found by name in your $PATH
  # db.mysqldump_utility = "/opt/local/bin/mysqldump"
end

MySQL databases are dumped as databases/MySQL/<db.name>.sql. If all databases are being dumped,
using the db.name = :all option, the resulting filename will be databases/MySQL/all-databases.sql.

If a Compressor has been added to the backup, the database dump will be piped through
the selected compressor. So, if Gzip is the selected compressor, the output would be databases/<db.name>.sql.gz.

PostgreSQL

database PostgreSQL do |db|
  db.name               = "my_database_name"
  db.username           = "my_username"
  db.password           = "my_password"
  db.host               = "localhost"
  db.port               = 5432
  db.socket             = "/tmp/pg.sock"
  db.skip_tables        = ['skip', 'these', 'tables']
  db.only_tables        = ['only', 'these' 'tables']
  db.additional_options = []
  # Optional: Use to set the location of this utility
  #   if it cannot be found by name in your $PATH
  # db.pg_dump_utility = '/opt/local/bin/pg_dump'
end

PostgreSQL databases are dumped as databases/PostgreSQL/<db.name>.sql. If a Compressor has been added to the backup, the database dump will be piped through the selected compressor. So, if Gzip is the
selected compressor, the output would be databases/<db.name>.sql.gz.

MongoDB

database MongoDB do |db|
  db.name               = "my_database_name"
  db.username           = "my_username"
  db.password           = "my_password"
  db.host               = "localhost"
  db.port               = 27017
  db.ipv6               = false
  db.only_collections   = ['only', 'these' 'collections']
  db.additional_options = []
  db.lock               = false
  # Optional: Use to set the location of these utilities
  #   if they cannot be found by their name in your $PATH
  # db.mongodump_utility = '/opt/local/bin/mongodump'
  # db.mongo_utility     = '/opt/local/bin/mongo'
end

MongoDB databases are dumped into the databases/MongoDB folder, which will create a structure of databases/MongoDB/<databases>/<collections>. If a Compressor has been added to the backup, the databases/MongoDB folder will be tar archived once the dump is completed, with the output being piped through the selected compressor. So, if Gzip is the selected compressor, the final output file would be databases/MongoDB-#####.tar.gz. The ##### in the file name here is a time-based identifier, in case multiple MongoDB Databases are configured for the backup.

Additional Notes

The db.lock is set to false by default. This is a new feature (introduced in version 3.0.15) which will first lock and fsync your database before it performs the actual dump. This removes the chance of having "out of sync" data. Dumping data without locking the database won't lead to corruption, but imagine that you have some kind of association between two collections with a foreign key. It could be that one of the associated objects are not dumped, while the other is, meaning a "broken" association. If you restore this, the data that was actually dumped will be restored, but the association doesn't exist anymore. So, setting the db.lock = true would first lock the database, preventing all write operations, then it'll fsync (flush) the data to the physical database files to ensure everything is synchronized, then it'll perform the dump as usual, and finally it'll unlock the database so data can be written to it again.

Redis

database Redis do |db|
  db.name               = "my_database_name"
  db.path               = "/usr/local/var/db/redis"
  db.password           = "my_password"
  db.host               = "localhost"
  db.port               = 5432
  db.socket             = "/tmp/redis.sock"
  db.additional_options = []
  db.invoke_save        = true
  # Optional: Use to set the location of this utility
  #   if it cannot be found by name in your $PATH
  # db.redis_cli_utility = '/opt/local/bin/redis-cli'
end

The Redis database dump file for the above configuration would be copied from /usr/local/var/db/redis/my_database_name.rdb to databases/Redis/my_database_name.rdb. If a Compressor has been added to the backup, then the database dump file would be copied using the selected compressor. So, if Gzip is the selected compressor, the result would be databases/Redis/my_database_name.rdb.gz.

Additional Notes

The db.name represents the database dump file on the filesystem in /usr/local/var/db/redis/. The .rdb (Redis dump extension) will be automatically added. So the above example will try to look for the database dump file in /usr/local/var/db/redis/my_database_name.rdb. The default name for the Redis dump file is just dump.rdb unless you change it in the configuration file youself.

If db.invoke_save is set to true, it'll perform a SAVE command on the Redis process before backing up the dump file, so that the dump file is at it's most recent state.

Riak

database Riak do |db|
  db.name   = "hostname"
  db.node   = "riak@hostname"
  db.cookie = "cookie"
  ##
  # Optional: (defaults shown)
  #
  # Use to set the location of this utility
  # if it cannot be found by name in your $PATH
  # db.riak_admin_utility = '/opt/local/bin/riak-admin'
  #
  # Set username for the riak-admin utility
  # db.user = 'riak'
  #
  # Set usergroup for the riak-admin utility
  # db.group = 'riak'
end

The Riak database dump file for the above configuration would be dumped to databases/Riak/<db.name>. (note: no extension added). If a Compressor has been added, then the resulting dump file will be compressed using the selected compressor. So, if Gzip is the selected compressor, the result would be databases/Riak/<db.name>.gz.

Default Configuration

If you are backing up multiple databases, you may want to specify default configuration so that you don't have to rewrite the same lines of code for each of the same database types. For example, say that the MySQL database always has the same username, password and additional_options. You could write this above the Model configurations.

Backup::Database::MySQL.defaults do |db|
  db.username           = "my_username"
  db.password           = "my_password"
  db.additional_options = ["--single-transaction"]
end

So now for every MySQL database you wish to back up that requires the username, password and additional_options to be filled in with the defaults we just specified above, you may omit them in the actual database block, like so:

database MySQL do |db|
  db.name = "my_database_name"
  # no need to specify username
  # no need to specify password
  # no need to specify additional_options
end

You can set defaults for MongoDB, by changing Database::MySQL to Database::MongoDB.

Backup::Database::MongoDB.defaults do |db|
  # ...and so forth for every supported database.
end