D-Down is coding

#!/usr/bin/perl -w

use strict;
use DBI;

use constant DB_USER => "xxxx";
use constant DB_PASSWD => "xxxx";
use constant DB_DRIVER => "DBI:mysql:database=";

my $dbh;
my $stmt_db;
my $dbase;
my $to_optimize;
my $tables;

$dbh = DBI->connect(
        DB_DRIVER . "mysql",
        DB_USER,
        DB_PASSWD,
        {"RaiseError" => 1}
);

$stmt_db = $dbh->prepare("show databases") or {FNAMEL}.html">die $dbh->errstr;
$stmt_db->execute() or {FNAMEL}.html">die $dbh->errstr;

while ($dbase = $stmt_db->fetchrow_hashref())
{
        next if ($dbase->{'Database'} eq "information_schema");

        {FNAMEL}.html">print "\nOptimizing database: $dbase->{'Database'}\n";
        {FNAMEL}.html">print "-------------------------------------------\n";
        $to_optimize = DBI->connect(
                DB_DRIVER . $dbase->{'Database'},
                DB_USER,
                DB_PASSWD,
                {"RaiseError" => 1}
        );

        $tables = $to_optimize->prepare("show tables")
                or {FNAMEL}.html">die $to_optimize->errstr;
        $tables->execute() or {FNAMEL}.html">die $to_optimize->errstr;

        my $table;

        while ($table = $tables->fetchrow_arrayref())
        {
                {FNAMEL}.html">print "Optimize table: $table->[0]\n";
                $to_optimize->do("optimize table $table->[0]")
                        or {FNAMEL}.html">die $to_optimize->errstr;
        }
}

$stmt_db->finish();
$tables->finish();
$dbh->disconnect();
 

Anbei ein kleines Shell-Skript für das Backup der MySQL Datenbanken.

#!/bin/bash

# Diese skript sichert alle datenbanken ab.

#Password fuer die MySQL datenbank
DB_PASSWD="DASPASSWORD"

#Das Datum
NOW=`date +%Y%m%W`

#Das Ziel
TARGETDIR="/PFAD/ZUM/BACKUP/"`date +%Y`"/mysql/"

DBASE_LIST=`mktemp`

#Erstelle das Verzeichnis falls nicht vorhanden
if [ ! -d $TARGETDIR ]
then
        echo "Erstelle das Verzeichnis $TARGETDIR"
        mkdir -p $TARGETDIR
        chmod 0700 $TARGETDIR
fi;

mysqlshow -p$DB_PASSWD | awk '{print $2}' | grep -vi databases | sort > $DBASE_LIST

if [ ! -d "$TARGETDIR/$NOW" ]
then
        echo "Erstelle das Verzeichnis $TARGETDIR/$NOW"
        mkdir -p "$TARGETDIR/$NOW"
        chmod 0700 "$TARGETDIR/$NOW"
fi;

cd "$TARGETDIR/$NOW"

for x in `cat $DBASE_LIST`
do
        echo "Datenbank: $x sichern"
        mysqldump --opt -p$DB_PASSWD $x > "$x.sql"
        chmod 700 "$x.sql"
done;

echo "Alte .gz-Dateien loeschen:"
rm .gz
echo "Dateien zippen:"
gzip