ZALOHA MYSQL

[Petr Vileta]

>> Ako njednoduchsie odzalohovat databzu mysql nejak pochopitelne aj pre
>> laika.
>
> mysqldump - viz. dokumentace k MySQL.
Proc to vsichni delaji tak slozite? :-)
Ja na to pouzivam perlovsky skript:


#!/usr/bin/perl
use CGI qw(:cgi);
use DBI;
$pathcfg = "/var/backup/"; # kam se ulozi uzivatelske databaze
$pathbkp = "/var/backup/MySQL/"; #kam se ulozi databaze MySQL
open STDERR,">&STDOUT";
print "---------Backup databazi---------\n";
$host= "localhost";
$myddb="";
$opt_user="root";
$opt_password="heslo";
$dbh = DBI->connect("DBI:mysql:$mydb:$host",$opt_user,$opt_password) or die
"Nelze se pripojit: $DBI::errstr\n";
$sth=$dbh->prepare("show databases");
$sth->execute() or print "Nelze ziskat seznam databazi\n";
$dbfs=$sth->fetchall_arrayref([0]);
$sth->finish;
for $d (0 .. $#{$dbfs})
    {
    $dbf=$dbfs->[$d][0];
    next if($dbf eq 'mysql');
    $dir=$pathbkp . $dbf;
    if(stat($dir)*1==0)
        {
        mkdir $dir;
        chown((getpwnam('mysql'))[2,3],$dir);
       }
    $sth=$dbh->do("use $dbf");
    $sth=$dbh->prepare("show tables");
    $sth->execute() or print "Nelze ziskat seznam tabulek z databaze
$dbf\n";
    $tables=$sth->fetchall_arrayref([0]);
    $sth->finish;
    for $t (0 .. $#{$tables})
       {
       $tbl=$tables->[$t][0];
       $sth=$dbh->do("lock tables $tbl write");
       $sth=$dbh->prepare("backup table $tbl to \'$dir\'");
       $sth->execute() or print "Nelze zalohovat tabulku $t do $dir\n";
       $row=$sth->fetchrow_arrayref;
       print "$row->[0] $row->[2] $row->[3]\n";
       $sth->finish;
       $sth=$dbh->do("unlock tables");
       }
    }
$rc=$dbh->disconnect;
print "---------Backup databazi konec---------\n";


Ty printy je mozne vynechat.
No a pokud potrebuji ze zalohy obnovit, tak pouziju:

USE nejaka_databaze;
DROP TABLE nejaka_tabulka;
RESTORE TABLE zalohovana_tabulka FROM '/cesta_k_zaloze';


--
Petr

Na mail uvedeny v headeru zpravy nema cenu nic posilat, konci to v PR* :-)
Odpovidejte na petr na practisoft cz