MySQL Automatic backup tools

If you're on a server without a GUI, here's a package that will backup and rotate all of your MySQL databases daily by default.

sudo apt-get install automysqlbackup

That's it. The default configuration is sane so you're done unless you need something special. The backup files will be placed in /var/lib/automysqlbackup which you should rsync somewhere off-site.

MySQL Admin (discontinued - was available in natty) has a backup tool with a (basic) scheduler to make backups on a daily, weekly, or monthly basis. It is even included in Ubuntu: mysql-admin.

Random image from the web:

My favourite way ofcourse is command line and I found a backup script on UF.

#!/bin/bash
#Script to make a regular copy of a mysql database and gzip it into the SAVEDIR.

USER="authorized_user"
PASSWORD="the_password"
DATABASE="database_name"
SAVEDIR="/backup"

/usr/bin/nice -n 19 /usr/bin/mysqldump -u $USER --password=$PASSWORD --default-character-set=utf8 $DATABASE -c | /usr/bin/nice -n 19 /bin/gzip -9 > $SAVEDIR/$DATABASE-$(date '+%Y%m%d-%H').sql.gz

Edit the varibles, save it as .bkup.sh and run it in a crontab, then you have an automatic mysql backup. All the code for this script explained here. Kudos to kat_ams.