Cron job to back up SQL databases regularly

JenkinsLaw

Registered
Jul 14, 2004
1
0
151
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

Well-Known Member
Apr 16, 2002
782
5
318
N.W. Iowa
JenkinsLaw said:
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
Code:
#!/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

Member
Jun 8, 2004
6
0
151
Code:
#! /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