Question

How do I start automatic database backups on ?

Posted February 19, 2015 16.2k views

I want system should take automatic backups.

So if any issue occurs for database then we can restore the database from that recent backup taken by system.

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
3 answers

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
by Justin Ellingwood
MySQL is a popular database management system that is often used to manage the data from websites and applications. This guide will demonstrate how to backup the data in your MySQL databases, using a number of different methods.

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

  • and here is the line for crontab (assuming you place that script in a directory called .scripts)

    0 0 * * * /home/youruser/.scripts/backup
    
  • it a nice script and i want to try it, but if i want to backup only the database not include backing up folder, which part of the script i need to delete,

    many thanks

Submit an Answer