Backup von Datenbanken-dumps in Subversion Repository

Einleitung

Anbei ein einfaches Script, welches Datenbanken dumpt und in ein vorher erstelltes Repository einpflegt.

Vorbereitung

Datenbank-Benutzer einrichten

Damit die Daten aus den Datenbanken geladen werden können, muss ein spezieller Benutzer mit speziellen Rechten bestehen

CREATE USER '[BENUTZERNAME]'@'[HOST]' IDENTIFIED BY '***';
 
GRANT USAGE ON
  * . *
TO
  '[BENUTZERNAME]'@'[HOST]'
IDENTIFIED BY
  '***'
WITH
  MAX_QUERIES_PER_HOUR 0
  MAX_CONNECTIONS_PER_HOUR 0
  MAX_UPDATES_PER_HOUR 0
  MAX_USER_CONNECTIONS 0;

Rechte für eine Datenbank hinzufügen

Achtung: Diese Rechte müssen für jede Datenbank, die vom Script gedumpt werden soll, gesetzt werden!
Das Script wertet die Mysql-Dumps nicht aus und zeigt keinen Fehler, da das Programm mysqldump trotzdem eine Datei, jedoch ohne entsprechenden Inhalt, zurück gibt!

GRANT
  SELECT, LOCK TABLES, SHOW VIEW
ON
  `[DATENBANK_NAME]` . * TO '[BENUTZERNAME]'@'[HOST]';

Script

#!/usr/bin/perl
 
##############################################################################
#
# Backup-Script von Joerg Pannbacker
#
# Change Date: 15.09.2009
#
##############################################################################
#
# Funktionsbeschreibung
#
# Speichert die angegebenen Verzeichnisse als Tar-Archiv in einem Verzeichnis
# Diese Dateien koennen anschliessend auf einen FTP-Server kopiert werden
#
##############################################################################
 
 
use strict;
use File::Basename;
 
 
######### Hier veraenderbar!
 
# Log-Datei
# Sample: "/var/log/backup.log"
my $logfile = "[LOGDATEI]";
 
# SVN
# Sample: https://svn.myserver.local/svn/mysql_backup/"
my $svn_url = "[SUBVERSION_REPOSITORY_URL]";
my $svn_usr = "--username [SUBVERSION_USERNAME]";
my $svn_pas = "--password [SUBVERSION_PASSWORD]";
my $svn_checkout_command = "svn co";
my $svn_add_command = "svn add --force";
my $svn_update_command = "svn update --no-auth-cache";
my $svn_commit_command = "svn commit --no-auth-cache -m";
 
# Mysql
# Sample: "/tmp/backup_checkout/"
my $mysql_backupdir = "[SUBVERSION_CHECKOUT_DIRECTORY]";
my $mysql_user = "-u[MYSQL_USERNAME]";
my $mysql_pass = "-p[MYSQL_PASSWORD]";
my $mysql_command = "mysqldump --opt --dump-date=false";
 
# Die folgenden Arrays müssen die gleiche Anzahl Einträge beinhalten
# Jeder Eintrag in mysql_databases und mysql_databases_folder gehört zusammen
# Es ist dem Anwender überlassen, wie er sein Repository strukturiert
my @mysql_databases = (
  # "testdb",
  "[MYSQL_DATABASE_NAME]",
);
my @mysql_databases_folder = (
  # "mysql/mydomain.local/",
  "[RELATIVE_SUBFOLDER]",
);
 
##########
 
 
 
# Backup-Routine
 
dolog($logfile, "+++ Starte Backup");
 
# Existiert das Verzeichnis, wird lediglich ein Update gemacht
if (-d ($mysql_backupdir . "etc/webmin/virtual-server/collected"))
{
  # SVN Update
  dolog($logfile, "SVN UPDATE \"svn update " . $mysql_backupdir . "\"");
  system($svn_update_command . " " . $svn_usr . " " . $svn_pas . " " . $mysql_backupdir);
}
# existiert es nicht, ein Checkout
else
{
  # SVN Checkout
  dolog($logfile, "MYSQL_SVN CHECKOUT \"svn co " . $svn_url . " " . $mysql_backupdir . "\"");
  system("svn co " . $svn_usr . " " . $svn_pas . " " . $svn_url . " " . $mysql_backupdir);
}
 
 
# Mysql-Backup
dolog($logfile, "MYSQL_SVN START \"Backup\"");
 
for (my $x=0; $x<=$#mysql_databases; $x++)
{
  if (!(-d ($mysql_backupdir . $mysql_databases_folder[$x])))
  {
    system("mkdir " . $mysql_backupdir . $mysql_databases_folder[$x]);
  }
 
  # MySQL-Dump-Datei erstellen
  dolog($logfile, "MYSQL CREATE DUMPFILE \"" . $mysql_databases_folder[$x] . $mysql_databases[$x] . ".sql\"");
  system($mysql_command . " " . $mysql_user . " " . $mysql_pass . " " . $mysql_databases[$x] . " > " .
    $mysql_backupdir . $mysql_databases_folder[$x] . $mysql_databases[$x] . ".sql");
}
 
# SVN Add ausführen
dolog($logfile, "SVN ADD \"" . $mysql_backupdir . "mysql/*\"");
system($svn_add_command . " " . $mysql_backupdir . "mysql/*");
 
# SVN Commit ausführen
dolog($logfile, "SVN COMMIT \"" . $mysql_backupdir . "mysql/\"");
system($svn_commit_command . " \"MySQL-Backup " . getTimeForAUX() . "\" " . $svn_usr . " " .
  $svn_pas . " " . $mysql_backupdir . "mysql/");
 
dolog($logfile, "MYSQL STOP \"Backup\"");
 
 
 
# SVN Files
 
# etc-Verzeichnis
dolog($logfile, "DIR COPY \"cp -r -p /etc/* " . $mysql_backupdir . "etc/\"");
system("cp -r -p /etc/* " . $mysql_backupdir . "etc/");
 
# Verzeichnisse, die wir nicht speichern möchten
if (-d ($mysql_backupdir . "etc/webmin/virtual-server/collected"))
{
  system("rm -r " . $mysql_backupdir . "etc/webmin/virtual-server/collected");
}
if (-d $mysql_backupdir . "etc/webmin/virtual-server/history/")
{
  system("rm -r " . $mysql_backupdir . "etc/webmin/virtual-server/history/");
}
 
dolog($logfile, "SVN ADD \"" . $mysql_backupdir . "etc/*\"");
system($svn_add_command . " " . $mysql_backupdir . "etc/*");
 
dolog($logfile, "SVN COMMIT \"" . $mysql_backupdir . "\"");
system($svn_commit_command . " \"DIR-Backup /etc " . getTimeForAUX() . "\" " . $svn_usr . " " .
  $svn_pas . " " . $mysql_backupdir . "etc/");
 
 
# Cacti-Verzeichnis
 
dolog($logfile, "DIR COPY \"cp -r -p /usr/share/cacti/* " . $mysql_backupdir . "cacti/\"");
system("cp -r -p /usr/share/cacti/* " . $mysql_backupdir . "cacti/");
 
# Verzeichnisse löschen
if (-d $mysql_backupdir . "cacti/rra/")
{
  system("rm -r " . $mysql_backupdir . "cacti/rra/");
}
if (-d $mysql_backupdir . "cacti/log/")
{
  system("rm -r " . $mysql_backupdir . "cacti/log/");
}
if (-d $mysql_backupdir . "cacti/logs/")
{
  system("rm -r " . $mysql_backupdir . "cacti/logs/");
}
 
dolog($logfile, "SVN ADD \"" . $mysql_backupdir . "cacti/*\"");
system($svn_add_command . " " . $mysql_backupdir . "cacti/*");
 
dolog($logfile, "SVN COMMIT \"" . $mysql_backupdir . "\"");
system($svn_commit_command . " \"DIR-Backup /usr/share/cacti " . getTimeForAUX() . "\" " . $svn_usr . " " .
  $svn_pas . " " . $mysql_backupdir . "cacti/");
 
 
dolog($logfile, "+++ Beendet Backup!\n");
 
 
 
 
 
 
 
 
 
################### Functions
 
sub getTimeForAUX()
{
  my ($sec, $min, $std, $day, $month, $year) = gmtime();
 
  $month++;
  $year = $year + 1900;
 
  if ($month < 10)
  {  $month = "0" . $month; }
  if ($day < 10)
  {  $day = "0" . $day; }
  if ($std < 10)
  {  $std = "0" . $std; }
  if ($min < 10)
  {  $min = "0" . $min; }
  if ($sec < 10)
  {  $sec = "0" . $sec; }
 
  my $ret = $year . "." . $month . "." . $day . " - " . $std . ":" . $min . ":" . $sec;
 
  $ret;
}
 
sub getPostgresqlDatabases
{
    my @databases;
    #my $dsn = "dbi:mysql:DATABASE:HOST:PORT";
    #my $connect = DBI->connect($dsn, USER, PASS);
    my $dsn = "dbi:Pg:host=$_[0]";
    my $connect = DBI->connect($dsn, $_[2], $_[3]);
 
    if ($connect)
    {
        #my $query = "show databases";
        my $query = "select datname from pg_database";
        my $query_h = $connect->prepare($query);
 
        $query_h->execute();
 
        my $dbname;
        $query_h->bind_columns(undef, \$dbname);
 
        while ($query_h->fetch())
        {
            push(@databases, $dbname);
        }
 
        $connect->disconnect();
    }
    else
    {
        push(@databases, "ERROR");
        push(@databases, "CONNECTION_DATA_WRONG");
    }
 
    return @databases;
}
 
sub getMysqlDatabases
{
    my @databases;
    #my $dsn = "dbi:mysql:DATABASE:HOST:PORT";
    #my $connect = DBI->connect($dsn, USER, PASS);
    my $dsn = "dbi:mysql::$_[0]:$_[1]";
    my $connect = DBI->connect($dsn, $_[2], $_[3]);
 
    if ($connect)
    {
        my $query = "show databases";
        my $query_h = $connect->prepare($query);
 
        $query_h->execute();
 
        my $dbname;
        $query_h->bind_columns(undef, \$dbname);
 
        while ($query_h->fetch())
        {
            push(@databases, $dbname);
        }
 
        $connect->disconnect();
    }
    else
    {
        push(@databases, "ERROR");
        push(@databases, "CONNECTION_DATA_WRONG");
    }
 
    return @databases;
}
 
sub getTimeStamp
{
    return str_replace(`date "+\[%Y\/%m\/%d\ %H\/%M\/%S\]"`, "\n", "");
}
 
sub convert_time
{
    my ($sec, $min, $std, $day, $month, $year) = localtime();
 
    $month++;
    $year = $year + 1900;
 
    if ($month < 10)
    {  $month = "0" . $month; }
    if ($day < 10)
    {  $day = "0" . $day; }
 
    my $ret = $year . "_" . $month . "_" . $day;
 
    return $ret;
}
 
sub get_del_time
{
    my $tag = 60*60*24;
    my $t = time();
 
    if ($_[0] == 1)
    {  $t = $t - $tag * $_[1]; }
    elsif ($_[0] == 2)
    {  $t = $t - $tag * 7 * $_[1]; }
 
    my ($sec, $min, $std, $day, $month, $year) = localtime($t);
 
    $month++;
    $year = $year + 1900;
 
    if ($month < 10)
    {  $month = "0" . $month; }
    if ($day < 10)
    {  $day = "0" . $day; }
 
    my $ret = $year . "_" . $month . "_" . $day;
 
    return $ret;
}
 
 
sub dolog
{
    if ($_[1] ne "")
    {
        my $time = getTimeStamp();
        open(FILE, ">>$_[0]");
        print FILE "\n" . $time . " " . $_[1];
        close(FILE);
    }
}
 
 
sub str_replace
{
    my $string = $_[0];
    my $find = $_[1];
    my $replace = $_[2];
 
    $string =~ s/$find/$replace/g;
 
    return $string;
}