The easiest way that I have found for an advanced backup of all databases on a web hosting server was using the automysqlbackup tool.
By default the tool creates backups with automatic daily, weekly and monthly rotation. The backup files are being compressed and will be placed into your configured backup folder. Email notifications and a lot of additional options can be configured.
Of course the backup folder would then have to be backed up onto some other medium like a tape or remote servers.
automysqlbackup can be installed by cloning my fork from https://github.com/mattanja/automysqlbackup or downloading the original files from http://sourceforge.net/projects/automysqlbackup/
git clone git@github.com:mattanja/automysqlbackup.git
After cloning, simply run the setup script:
cd automysqlbackup
./install.sh
The executable script will be placed in /usr/local/bin/automysqlbackup using the default settings. The default configuration will be placed in /etc/automysqlbackup.
Next, update your server configuration in /etc/automysqlbackup/automysqlbackup.conf and/or /etc/automysqlbackup/myserver.conf – details can be found in the projects README file.
A run script is provided in /etc/automysqlbackup/run which can be used to setup a daily cronjob with the example-cron-file located in the same directory.
# cat example-cron-file
# Put this file into /etc/cron.d and adjust to your needs.
# Run the mysql backup scripts every morning at 4.30 h:
30 4 * * * root /etc/automysqlbackup/run
With this simple setup you’ll have a very advanced backup of all MySQL databases on your server within minutes. Thank you “wipe_out“.