Automatically Backup MySQL database on linux server

Question

I need a script that automatically makes a backup of a MySql Database. I know there are a lot of posts and scripts out there on this topic already but here is where mine differs.

1. The script needs to run on the machine hosting the MySql database (It is a linux machine).
2. The backups must be saved onto the same server that the database is on.
3. A backup needs to be made every 30 minutes.
4. When a backup is older than a week it is deleted unless it is the very first backup created that week. i.e out of these backups backup_1_12_2010_0-00_Mon.db, backup_1_12_2010_0-30_Mon.db, backup_1_12_2010_1-00_Mon.db ... backup_7_12_2010_23-30_Sun.db etc only backup_1_12_2010_0-00_Mon.db is kept.

Anyone have anything similar or any ideas where to start?

Answer 1

Answer: A cron

Description:

Try creating a file something.sh with this:

 #!/bin/sh
 mysqldump -u root -p pwd --opt db1.sql > /respaldosql/db1.sql
 mysqldump -u root -p pwd --opt db2.sql > /respaldosql/db2.sql
 cd /home/youuser/backupsql/
 tar -zcvf backupsql_$(date +%d%m%y).tgz *.sql
 find -name '*.tgz' -type f -mtime +2 -exec rm -f {} \;

Give the adequate permission to the file

 chmod 700 mysqlrespaldo.sh

or

 sudo chmod 700 something.sh

and then create a cron with

 crontab -e

setting it like

 **0 1 * * *** /home/youruser/coolscripts/something.sh

Remember that the numbers or '*' characters have this structure:

Minutes (range 0-59)
Hours (0-23)
Day of month (1-31)
Month (1-12)
Day of the week (0-6 being 0=Domingo)
Absolute path to script or program to run