NAME: rsnapshotDB
Web: http://www.rsnapshot.org
Bugs: rsnapshot-discuss@lists.sf.net
License: GPL http://www.gnu.org/licenses/gpl.txt
Version: 1.2.1
AUTHOR: Anthony Ettinger
Email: aettinger<--at-->sdsualumni<--dot-->org
Blog: http://anthony.ettinger.name
DESCRIPTION:
This script was originally written to function as a MySQL database backup script in conjunction with the open source Perl/rsync backup program "rsnapshot". rsnapshot can be found at: http://www.rsnapshot.org/
In order to backup a database remotely, the necessary database user must be able to connect remotely to the database server from a trusted secure shell server. (some ISPs only allow access from an internal network - you may need to make sure you do have internal access from an internal ssh server to the database server).
IF YOU DON'T HAVE SSH KEYS, this program isn't for you. (see:man ssh-keygen).
It is extremely important that you secure the /etc/rsnapshotDB.conf file so only YOU (the user who's cronjob this is running from) can read the file, 'chmod 0600 /etc/rsnapshotDB.conf', as it will store the database passwords in plain text format.
If you don't know who YOU are - type 'whoami' or ask a friend.
For best results, configure and run this script from /etc/rsnapshot.conf. (see:'man rsnapshot', backup_script).
SEE ALSO:
INSTALL.txt, TODO.txt, CHANGES.txt
WARNING: type 'chmod 0600 /etc/rsnapshotDB.conf' Currently 'dbtype' supported can be either 'mysql' or 'pgsql' Functionality is similar to /etc/DBPASSWD, however passwords are stored in plain text and NOT encrypted <!-- comments --> are allowed in the following file:
Note: rsnapshotdb.list is deprecated in favor of XML config rsnapshotDB.conf and rsnapshotDB.xsd
WARNING:
Setting the "temporary" directory: 1) the db dump might get left behind on error 2) the temp directory could fill up, depending on size of db and quota of user or directory
DUMPERS:
Location of "dumper" program(s) type 'which <db-dumper>' to find the path (ie - 'which mysqldump') Note: the hash key here must match 'dbtype' field in $dbpasswd file.
END OF THE LINE:
If you've gotten this far with no "die" errors, you should be good to go with XML config rsnapshotDB.conf vs. flat list rsnapshotdb.list
Check the $localTmpDir or your /backups/.snapshot/foo/wherever you put your database backups using rsnapshot.conf.
rsnapshotDB.pm
parseXML()
Utitility to parse our XML file for values
LOGIN REMOTELY:
This is the section where you authenticate with the remote ssh server.
I'm pretty sure, you can just leave off the password flags if you know what you're doing in the XML Config file rsnapshotDB.conf.
Requirement: Net::SSH::Perl. If you don't have root, read about how to install a perl module as an under privileged user (it IS possible - /home/username/modules/).
SHOW DATABASES:
$self-\showDB();>
This <em>should</em> pull down the list of your database user's databases from the XML configuration file.
Note: This is done on the remote SSH server with db access. Since we're not writing or reading there isn't a lock on the table. The one restriction here is that you can actually access your database server remotely from an internal ssh server via ssh tunneling.
PATH:
Make sure your prompt binary (ie - mysql) and dumper binary (ie - mysqldump) are in your default path for the ssh user.
DUMP DATABASE:
This is the bulk of the app, via ssh tunneling, logs in to an internal ssh server with access to the database server. The main reason for speeding this application up was becauase a remote database pull is extremely inefficient directly over the internet.
The idea here is to use ssh-keygen from this account to your remote ssh server, then do the database dump, and secure copy ('man scp') the file back here locally.
The gained result here should be seconds vs. minutes.
SECURE COPY:
At this point it's necessary to use ssh-keygen to connect to the server, the local command is using SSH tunneling.
ARCHIVING:
The move from $localTmpDir to '/backups/.snapshot/database' is determined in rsnapshot.conf and backup rsnapshotDB.pl option (see 'man rsnapshot' for script usage).
VERBOSITY:
Typically, you would first want to test rsnapshotDB with verbosity set to 1 in the rsnapshotdb.pl see:$verbose => 1.
You can increase verbosity ie - 2 instead of 1. Typically, this will dump commands that are being execute remotely and/or locally.
LOG FILE:
/var/log/rsnapshotDB
MORE INFO:
see README.txt