I want to automate MySQL database dump in the production environment

Middleware & DevOps mysql mysql-dump-automate 261 times2 answers1 follower
0
Nisha Verma 15 Rep. Brenno answered

I want to automate MySQL database dump in a production environment which includes the below features.

  • The dump should be created automatically everyday at 8 PM.
  • Dump should be stored in /opt/dump
  • Dump should be stored as bzip2 to save disk space and easy network transfer.
  • The gz archive should be stored with the Date and time when it was created.
  • All old dump should be deleted except last three.
  • Send email to me, after successful dump creation.
♥ Spread the love ♥
  •  
  • 1
  • 8
  •  
  •  
  •  
  •  
  •  
  •  
    9
    Shares
0
Avishek Kumar 449 Rep. Avishek Kumar edited answer

Automating a backup process, not only saves you from the repetitive task (which I personally find boring), but also helps in minimizing human error and save time. Here is what I did to automate my MySQL database schema on all servers in production. Save the below content to a file say mysql-dump-automate.sh

For more details on sending emails in Linux Terminal refers How can i send emails from my Linux box?

Make the script executable

Cron Job

To run the above automatically everyday at 8 PM, you may schedule a cron job simply as

  1. Edit crontab
  2. Add the below line at the end of the crontab file. Save and exit.
  3. If everything goes as suggested in the write-up, you should now be able to automate any mysql database like a cakewalk.

    ♥ Spread the love ♥
    •  
    • 1
    • 8
    •  
    •  
    •  
    •  
    •  
    •  
      9
      Shares
0
Brenno 11 Rep. Avishek Kumar edited answer

Look at ‘automysqlbackup’, see: https://sourceforge.net/projects/automysqlbackup/
It will do everything you want… And more!
It is part of most linux distributions like Debian and Ubuntu. You can use their package managers to install it. The configuration file is extensively documented..  Look around and have fun!

♥ Spread the love ♥
  •  
  • 1
  • 8
  •  
  •  
  •  
  •  
  •  
  •  
    9
    Shares
×

Login