mysql backup

Skapad 3 år sedan, skriven i Perl, med 356 visningar.
URL http://kod.perl.se/view/ba4e081e Bädda in
Ladda hem koden eller visa koden i råformatVisa bredare version
  1. #!/usr/bin/perl
  2. use DBI;
  3. use DBD::mysql;
  4. use Time::localtime;
  5. use Getopt::Long;
  6.  
  7. my $help = 0;
  8.  
  9. my %o = (
  10.  backupDir => "/data/backups",
  11.  server => '127.0.0.1',
  12.  username => 'root',
  13.  password => '',
  14. );
  15.  
  16. GetOptions(
  17.  "h" => \$help,
  18.  "u=s" => \$o{username},
  19.  "p=s" => \$o{password},
  20.  "d=s" => \$o{backupDir},
  21.  "s=s" => \$o{server},
  22. );
  23.  
  24. if ($help) {
  25.  print "Usage: mysql-backup.pl -s [server] -u [username] -p [password] -d [backupDir] [-h]\n";
  26.  exit;
  27. }
  28.  
  29.  
  30. my $serverDir = "$o{backupDir}/$o{server}";
  31. `mkdir -p $serverDir` unless -d $serverDir;
  32.  
  33. my $dbh = DBI->connect("DBI:mysql:database=mysql;host=$o{server}",$o{username},$o{password}) || die "Couldn't connect to sql server $o{server}";
  34.  
  35. my @databases = @{$dbh->selectcol_arrayref("show databases")};
  36. $dbh->disconnect();
  37.  
  38. unless (@databases) {
  39.  die "ERROR, didn't find any databases on server $o{server}";
  40. }
  41. my $ok = 1;
  42. foreach my $db (@databases) {
  43.  next if $db eq 'information_schema';
  44.  next if $db eq 'lost+found';
  45.  my $newFile = "$serverDir/$db.sql.new";
  46.  print "Backing up db $db to $newFile\n";
  47.  my $out = `mysqldump --ignore-table=mysql.event --single-transaction --extended-insert --quick --disable-keys --user=$o{username} --password=$o{password} --host=$o{server} --result-file '$newFile' $db && echo "OK"`;
  48.  if ($out !~ /^OK/) {
  49.   warn "ERROR dumping mysql database $db on $o{server}: $out\n";
  50.   $ok = 0;
  51.   next;
  52.  }
  53.  unless (`stat -c '%s' $newFile` > 20) {
  54.   warn "ERROR dumping mysql database $db on ${server}: file too small\n";
  55.   $ok = 0;
  56.   next;
  57.  }
  58.  #Rotate
  59.  my $rotate1 = `mv $serverDir/$db.sql $serverDir/$db.sql.2 2>&1`;
  60.  my $rotate2 = `mv $serverDir/$db.sql.new $serverDir/$db.sql 2>&1`;
  61.  
  62.  unless (-f "$serverDir/$db.sql") {
  63.   $ok = 0;
  64.   warn "ERROR rotating db file, rotate1: $rotate1, rotate2: $rotate2\n";
  65.  }
  66. }
  67.  
  68. exit $ok;

Svara på "mysql backup"

Här kan du skriva ett svar till kodsnutten ovan