Here is my simple bash script that creates a fresh MySQL database backup every day for the last 30 days, and a monthly backup for the last 12 months, and a yearly backup for the last 10 years !
It saves the backups on the same server (locally) and it does not notify you by email if an error happens, it's up to you to implement those features if you want. (it does keep track of every success/errors in a log file tho).
This script is meant to be executed once every day in a cronjob (but can be executed more than once per day without problems, if desired).
To schedule it's execution, simply call
crontab -e in a terminal (while being logged in as the user that will run the script), and add this :
30 2 * * * bash /path/to/script/backup-database.bash
This will run the script once every day at 2:30 AM.
PS. Before writing this script, I read this article (which contains good alternatives btw), but I just didn't want to install and setup postfix on my server so... I wrote this script.
I hope it helps you !