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