How do I start automatic database backups on ?

  • Posted February 19, 2015

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.


Submit an answer
You can type!ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

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.

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

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:



#your database info####
#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

#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";

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

       #if running in -v mode(verbose),  give some output
       if [ "$MODE" = "-v" ]; then
       echo "remove db files older than 1 day";
       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";

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

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

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

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.