How do I start automatic database backups on ?

I use automysqlbackup and it works flawlessly for daily / weekly / monthly database backups.

Tutorial: Scroll down to the section that says “How to Backup MySQL Information using automysqlbackup”

Once you install it, you can run an on demand backup with this command:

sudo automysqlbackup

You could backup the database file itself… Or if using something like mysql, use mysqldump to dump the database to a .sql file and then archive that file off somewhere else.

I run a script on a cron job every night at midnight. You have to create a folder in your home directory called .backups
The script makes a dated dump of your database(s), and backs up a folder or folders of your choice.

The script sort of looks like this:

#!/bin/bash

#Options##############

#your database info####
   DBHOST='localhost'
   DBUSER='your_mysql_user'
   DBPW='youruserspassword'
#your databases
#for individual databases uncomment this and put in the name of your database :
   #DBNAME=(  'your_database' )
#for all databases the user has access to, keep this uncommented:
   DBNAME=( `echo "show databases" |  mysql --user=$DBUSER --password=$DBPW --host=$DBHOST  | tail -n+3 `)


#your folders to backup 
    FOLDERS=(  '/var/www'  )

 #Testmode# if you aren't sure just leave this #######
 #use MODE='-v' to see some output for testing
 #use MODE='-q' to run quiet once you have the script working
     MODE='-q'


#End Options #stop editing####



      #if running in -v mode(verbose),  give some output
       if [ "$MODE" = "-v" ]; then
       echo "check if local .backups directory exists. if not create it";
       fi

       #check if local .backups directory exists. if not create it
       if [ ! -d "~/.backups/$SCRIPTNAME/db" ]; then
       mkdir -p ~/.backups/$SCRIPTNAME/db ;
       fi

       #if running in -v mode(verbose),  give some output
       if [ "$MODE" = "-v" ]; then
       echo "remove db files older than 1 day";
       fi
       
       find ~/.backups/$SCRIPTNAME/db/*.sql -type f -daystart -mtime +0 -exec rm {} \;  

       #if running in -v mode(verbose),  give some output
       if [ "$MODE" = "-v" ]; then
       echo "get recent version of databases";
       fi

       #get recent version of databases from array
       for i in "${DBNAME[@]}"
       do
       mysqldump --opt --user=$DBUSER --password=$DBPW --host=$DBHOST $i --lock-tables=false   > ~/.backups/$SCRIPTNAME/db/$i.`date +\%Y-\%m-\%d_\%H-\%M-\%S`.sql
       done

       #if running in -v mode(verbose),  give some output
       if [ "$MODE" = "-v" ]; then
       echo "back up your folders to local .backups folder";
       fi
       
       for i in "${FOLDERS[@]}"
       do
	rsync -a -t -q $MODE  --delete  --links  $i  ~/.backups/$SCRIPTNAME/   
       done

       #if running in -v mode(verbose),  give some output
       if [ "$MODE" = "-v" ]; then
       echo "all done!"
       fi