Database backup scripts

1. Database backup bash script
2. Perl email script
3. Cron job configuration to run the script

Here are some scripts that will help you make a database backup and than
another script that will send you an email, to confirm that the backup was made and probably if you want you can make the script send you the backup attached via email…
Then finally a cron configuration to run the database backup script everyday, week, month or once a year.

So here is the step by step tutorial…

First Lets make some directories..

1. Make a directory in /var/backups/ called sqlbackup

mkdir /var/backups/sqlbackup

2. Now we have to make our scripts:
2.1 The Backup script made in bash:
Copy paste this text in a file that you should call db_backup.sh
and send it to /usr/local/bin/

#!/bin/bash

mysqldump -u USER -pYOURPASSWORD --opt YOURDBNAME	> /var/backups/sqlbackup/db_backup.sql
mysqldump -u USER -pYOURPASSWORD --opt YOURDBNAME2	> /var/backups/sqlbackup/db_backup2.sql

cd /var/backups/sqlbackup/
tar -cvzf sqldata.tar.gz *.sql
rm /var/backups/sqlbackup/*.sql
cd /usr/local/bin/
perl email_backup.pl

2.2 To make the email script made in perl work you have to enable a module of MIME:Lite
Note: accept all requirements with (yes)

cpan MIME::Lite

2.3 The email script made in perl:
Copy paste this text in a file that you should call email_backup.pl
and send it to /usr/local/bin/

#!/usr/bin/perl -w

# Perl scrip for sending via email the backup file "sqldata.tar.gz"
# located in /var/backups/sqlbackup/ to the administrators of the server

use MIME::Lite;

$msg = MIME::Lite->new(
  From    => "mysqlbackup\@example.com",
  To      => "example\@example.com, example2\@example.com",
  Subject => "MySQL Backup from - servername",
  Type    => "text/plain",
  Data    => "Dear administrator, your mysql dat has been backed up!");

#If you want to send that tar.gz backup file via attachment on your email than uncomment the lines below

#$msg->attach(
#Type            => "application/x-tar",
#Path            => "/var/backups/sqlbackup/sqldata.tar.gz",
#Filename        => "sqldata.tar.gz");

$msg->send;

3. Now we have to configure a Cron job to automatically run this scrip:
3.1 First create a file in /etc/cron.d/ named sql_backup

nano /etc/cron.d/sql_backup

and copy paste this text below and than save it:

#These lines explain the configuration of your cron so if you have to make your script run everyday, month week etc
#You have to use these below.. so just look at the Command numbers

# Special string            Meaning                       Command

# @reboot       ----    Run once, at startup.
# @yearly       ----    Run once a year         ----    "0 0 1 1 *"
# @annually     ----    (same as @yearly)       ----    "0 0 1 1 *"
# @monthly      ----    Run once a month,       ----    "0 0 1 * *"
# @weekly       ----    Run once a week,        ----    "0 0 * * 0"
# @daily        ----    Run once a day,         ----    "0 0 * * *"
# @midnight     ----    (same as @daily)        ----    "0 0 * * *"
# @hourly       ----    Run once an hour,       ----    "0 * * * *"

#So the configuration strings are lined up like this:
# m h dom mon dow user  command
# month hour dayofthemonth month dayoftheweek user command

#In this case we are going to do @daily backups, that will run our script everyday at the same time !

0 0 * * *       root    /usr/local/bin/db_backup.sh

# What the lines above do is:
# it runs the script located in /usr/local/bin/db_backup.sh (that we created earlier) everyday