dbbu is a simple Python module to assist with remote database backups. It's designed to be used in conjunction with rsnapshot as a backup_script parameter, but can run standalone as well.
Supported Databases: PostgreSQL, MySQL
This has pretty limited use and is still a work in progress!
Installation and Basic Use
$ hg clone http://bitbucket.org/copelco/dbbu/ $ python dbbu/setup.py install $ dbbu-run.py dbbu.cfg
To use dbbu with rsnapshot, add a backup_script parameter like so:
backup_script dbbu-run.py /etc/dbbu.cfg databases/
Then setup /etc/dbbu.cfg with each host as described in "dbbu's Configuration File" below.
How Does It Work?
dbbu uses the subprocess module to ssh and execute commands on remote hosts (using Popen). It then pipes database dumps into files on the local filesystem.
Create a user for backup purposes only:
$ sudo useradd -d /home/bak -m bak
Since dbbu will connect over ssh, you should add your backup machine's public key to bak's .ssh/authorized_keys file.
PostgreSQL can be setup in a few different ways. One method is to allow bak to run commands as the postgres user (PostgreSQL is installed as the postgres user by default on Debain). Edit /etc/sudoers to allow bak to run specific commands without a password:
Cmnd_Alias POSTGRES_CMDS = /usr/bin/psql -l, /usr/bin/pg_dump, /usr/bin/pg_dumpall bak ALL = (postgres) NOPASSWD: POSTGRES_CMDS
For MySQL, create a .my.cnf file in bak's home directory:
[mysqldump] user=<user> password=<password>
Make sure that this file is only readable by bak (chmod 600 .my.cnf)!
dbbu's Configuration File
[default] # compress backup files (defaults to gzip) compression: bzip2 # backup destination directory (defaults to current working directory) # use default (leave blank) if using with rsnapshot dest: /backup/ # chmod backup files, must start with 0 (defaults to 0600) fmod: 0755 # logging level (defaults to INFO) log_level: DEBUG # uses RotatingFileHandler if defined log_file: /var/log/dbbu.log # host 1 # note that you need to prepend 'host_' to the domain name, so example.com becomes host_example.com. [host_example.com] # ssh user user: bak sudo_user: postgres # database specifications postgres: ALL mysql: ALL # host n [host_myserver.example.com]