Cron job to back up SQL databases regularly

[JenkinsLaw]

I would like to set up a cron job to backup my MySQL databases.

Has anyone written a perl script that you are using to generate these backups that they are willing to share?

[mickalo]

I would like to set up a cron job to backup my MySQL databases.

Has anyone written a perl script that you are using to generate these backups that they are willing to share?

Here's one we use to backup databases for many of our customers, executed via a cronjob each nite

#!/usr/bin/perl
use warnings;
use strict;

# Set connection values
my $DATABASE   = 'database_name';
my $DUMP_DIR   = '/path/to/backup_folder/';
my $MYSQLADMIN = '/usr/bin/mysqldump';
my ($DUMP_FILE,$DUMP_SCRIPT);
######################################

  $DUMP_FILE = $DUMP_DIR . $DATABASE . ".sql";
  if(-e "$DUMP_FILE") { unlink("$DUMP_FILE"); }
  $DUMP_SCRIPT = $MYSQLADMIN . " --defaults-file=/home/<username>/.my.cnf" . ' --opt' . " $DATABASE > $DUMP_FILE";
  system("$DUMP_SCRIPT");

  exit(0);

If your fimilar with Perl, you can adjust it for your needs I always use the ~/.my.cnf so we don't need to pass username/passwords.

Hope it helps
Mickalo

[SalaTar]

#! /usr/bin/env python2

import os
import sys
import time

database = sys.argv[1]
location = sys.argv[2]

location = location.replace("DAY", time.strftime("%d"))
location = location.replace("MONTH", time.strftime("%m"))
location = location.replace("YEAR", time.strftime("%y"))
location = location.replace("HOUR", time.strftime("%H"))
location = location.replace("MIN", time.strftime("%M"))
location = location.replace("SEC", time.strftime("%S"))

command = "mysqldump "+database+" > "+location+".sql"
print command
os.system(command)
command = "tar -czvf "+location+" "+location+".sql"
print command
os.system(command)
command = "rm -f "+location+".sql"
print command
os.system(command)

crontab


0 4 * * * path to above file dbtoback /home/dbtobackbackup_DAY-MONTH-YEAR.tar.gz

[nickn]

One of our users created a pretty decent guide for backing up MySQL databases, check out:

http://www.classicwebdesign.com/db_backup/