php mysql бекапер

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
<?php
// Конфигурационные данные, менять нужно!
$username = "root";
$password = "password";
$backup_dir = "/mnt/backup";
$dump = "/usr/bin/mysqldump";
$grep = "/bin/grep";
$gzip = "/bin/gzip";
// С этого момента ничего не нужно менять!
function sql_dict($sql){
$x = mysql_query($sql);
if ($x) return mysql_fetch_assoc($x);
}
function cleanup($dir){
$d = dir($dir);
echo "Path: " . $d->path . "\n";
while (false !== ($entry = $d->read())) {
if ($entry=="." or $entry=="..") continue;
$e = str_replace(".sql.gz","",$entry);
$x = sql_dict("describe $e");
if (!$x) {
print "Removing old backup file [$entry]\n";
unlink("$dir/$entry");
}
}
$d->close();
}
function crc32_file($filename)
{
global $gzip;
$x = exec("$gzip --list --verbose $filename");
$x = explode(" ",$x);
return $x[1];
}
if (mysql_connect("localhost",$username,$password)) print "Connected.\n";
else die("Failed to connect to database.");
$dbs = mysql_query("show databases");
if ($dbs) while ($db = mysql_fetch_array($dbs, MYSQL_ASSOC)) {
$db = $db['Database'];
if ($db=="information_schema") continue;
if (mysql_select_db($db)) print "Selected [$db]\n";
else die("Failed to select db [$db]");
foreach (array("schema","data") as $pass){
$sql = mysql_query("show tables");
$day = date("l");
if ($pass=="schema") $dir = "/$backup_dir/$db/schema";
else $dir = "/$backup_dir/$db/$day";
if (!file_exists($dir)) system("mkdir -p $dir");
if (!file_exists($dir)) die("Couldn't Create $dir");
if ($pass=="data"){
$latest = "/$backup_dir/$db/latest";
unlink($latest);
system("/bin/ln -s \"$dir\" \"$latest\"");
}
cleanup($dir);
if ($sql) while ($s = mysql_fetch_assoc($sql)) {
if (!isset($s["Tables_in_{$db}"])) {
print "no result";
print_r($sql);
die();
}
$t = $s["Tables_in_{$db}"];
if (
$pass=="schema" ) $data = "--no-data";
else $data = "--lock-tables";
$tab = $t;
$lim = 30;
if (strlen($tab)>$lim) $tab = substr($tab,0,$lim-3)."...";
while (strlen($tab)<30) $tab .= " ";
print "BACKUP: $pass : $day : $db : $tab : ";
if ($pass=="data"){
print "Check : ";
$check = sql_dict("check table $t");
$check = $check['Msg_text'];
print "$check : ";
if ($check != "OK") {
print "Repair";
$repair = sql_dict("repair table $t");
$repair = $repair['Msg_text'];
print " : $repair : ";
}
if ($day=="Sunday"){
// Оптимизация
print "Optimize : ";
$type = sql_dict("show table status like '$t'");
$type = $type['Engine'];
if ($type=="MyISAM") sql("optimize table $t");
if ($type=="InnoDB") sql("alter table $t engine='InnoDB'");
}
}
if (isset($argv[1])){
print "Skipping dump\n";
} else {
$temp = "/tmp/backup.$t.sql.gz";
$out = "$dir/$t.sql.gz";
print "Dump : ";
$cmd = "$dump -u$username -p$password $data --quick --add-drop-table $db $t | $grep -v 'Dump completed' | $gzip -n > $temp";
system($cmd);
print "CRC32 : ";
if (!file_exists($out)){
print "Saving : ";
$cmd = "/bin/mv $temp $out";
system($cmd);
} else {
$md5 = crc32_file($temp);
$nmd5 = crc32_file($out);
if ($md5!=$nmd5) {
print "Saving : ";
$cmd = "/bin/mv $temp $out";
system($cmd);
} else {
print "Skipped : ";
unlink($temp);
}
}
$size = filesize($out);
print "[$size]\n";
}
}
}
}
?>