The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Cron job to back up SQL databases regularly

Discussion in 'Database Discussions' started by JenkinsLaw, Jul 14, 2004.

  1. JenkinsLaw

    JenkinsLaw Registered

    Joined:
    Jul 14, 2004
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    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?
     
  2. mickalo

    mickalo Well-Known Member

    Joined:
    Apr 16, 2002
    Messages:
    765
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    N.W. Iowa
    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
     
  3. SalaTar

    SalaTar Member

    Joined:
    Jun 8, 2004
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    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
     
  4. nickn

    nickn Well-Known Member
    PartnerNOC

    Joined:
    Jun 15, 2003
    Messages:
    619
    Likes Received:
    1
    Trophy Points:
    18
Loading...

Share This Page