Fannie includes a default task for creating MySQL database
backups. For other database systems, you're on your own.
This document is tailored towards MySQL in a Linux/UNIX
environment. Making it work in Windows should be possible though.
Nothing overly fancy is going on here. Backups are performed using
mysqldump. The main advantage is utilizing fannie's configuration
so there's one master file with database connection info
that all tools and scripts rely on. If you rotate database passwords
periodically, that means just one file to update.
Fannie's main install/config page exposes a few configuration options
for database backups:
Once the configuration is set, just enable and schedule the job
nightly.db.backup.php via Fannie Dayend Polling => Scheduled Tasks.
- Path to backups is where to store the backups. Each
database (default is4c_op, is4c_trans, & trans_archve) is
given its own subdirectory here. This path should be writeable
and of course have adequate free space
- Path to mysqldump is your system directory for mysql-related
programs. The default, /usr/bin/, should be fine on most systems. Some
systems given cron a very limited shell, so full paths with binaries
provide some error-proofing.
- Number of backups is how many backups, per database, to
store. Backups are named with a date stamp (e.g., is4c_opYYYYMMDD.sql).
When the number of existing backups exceeds this setting, the oldest
one(s) are deleted.
- Compress backups is optional. Turning this one just passes
the backup through gzip. The normal mysql dumps are plain text, so
space savings from compression are pretty significant. For very large
databases, the CPU overhead of compression is also significant though.
For multi-GB databses, a bigger disk may be a better solution.
Restoring a Backup
First, if you used compression, decompress the desired backup file. In most
graphical environments, that's just double-clicking it. On the command line,
try gzip -d <backup file>.
Next, load the backup file. If you're using any sort of GUI tool to manage
your MySQL server, just find it's backup/restore option and restore from your
backup file. The format's pretty universal, so any tool should handle it. Working
on the command line, try mysql -u <mysql username> -p <mysql password>
-h <mysql host IP> < <backup file>. Example for clarity:
mysql -u root -p secret_passcode -h localhost < is4c_op20120101.sql.