Backup your database with mysqldump



Using Automysqlbackup

I recently decided to take backups of my mysql database and I found a very good solution for this: http://sourceforge.net/projects/automysqlbackup/

Automysqlbackup is a bash script that uses mysqldump to take daily, weekly and monthly backups of your database. I found many bash-scripts while searching but this script was the best one I found and it is even in the Ubuntu repositories 🙂.

To install:

apt-get install automysqlbackup

Per default it saves the backups to /var/lib/automysqlbackup but you can change that (and the other settings) in the config file /etc/default/automysqlbackup.

The script is run daily by cron but you can also take a manual backup if you wish by running

automysqlbackup

Great and simple 🙂.

Using a single terminal command

If you do not feel like installing a cronscript and just want a one time solution you can use one of these lines:

All databases - Custom username and pass

mysqldump -u username -ppassword --all-databases | gzip -9 > all-databases-$(date +%Y-%m-%d_%Hh%M)-.sql.gz

The -9 argument to gzip tells it to compress as much as possible.

All databases - Using the Debian system maintanance login

mysqldump --defaults-file=/etc/mysql/debian.cnf --all-databases | gzip -9 > all-databases-$(date +%Y-%m-%d_%Hh%M)-.sql.gz

This will only work if you are running some Debian system i guess. However I like this approach as I can use the same command on all Debian servers and I do not have to specify the username and password all the time.

One database - Using the debian system maintanance login

mysqldump --defaults-file=/etc/mysql/debian.cnf mydatabasename > mydatabasename-$(date +%Y-%m-%d).sql