Question

How do I start automatic database backups on ?

Posted February 19, 2015 17k 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.

×
4 answers

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 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.

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

You have several options to get this done:

  1. Rely on DigitalOcean Snapshot mechanism: easiest to get started with, but you won’t have control on when your backup is scheduled (only on a weekly base) nor where you store these backups
  2. Use a dedicated solution to do handle this: several exist (full disclosure, I have created one of these solution), easy to setup and can store your backups wherever you want and have control over schedule and what you want to backups (not necessarily your entire server, but just the database or some files…)
  3. Create your own backup script: depending on your needs and your skills you can quickly get started with your own backup script, which won’t offer all the flexibility you might need but can be helpful as well.

Here is a tutorial explaining you how to build your own MySQL backups script.

Laurent - SimpleBackups.io

Submit an Answer