Sean Jho

mysql使用binlog恢复数据

 ·  3 分钟阅读  ·  数据库

目录

通过MySQL的binlog进行数据恢复需要遍历binlog文件并提取指定时间段的SQL操作。以下是两种实现方式。

方法一：Bash脚本

逐个处理binlog文件，提取指定的操作记录：

#!/bin/bash
mpath="/data1/mysql5022_3306/binlog"
for ((a=989;a < 1360;a++))
do
  if [[ $a -lt 1000 ]];then
    b="0$a"
  else
    b=$a
  fi
  filename="${mpath}/mysql-log.00${b}"
  if [[ -f $filename ]];then
    echo "$filename"
    # 注：不指定临时目录时，大量临时数据会产生在/tmp，容易引起磁盘爆满
    # 使用--local-load指定临时目录：
    mysqlbinlog --local-load=/data1/mysql5022_3306/tmp ${filename} >temp.log
    cat temp.log|grep "version_property" >>result.log
  fi
done

方法二：Perl脚本

按时间范围提取指定表的所有SQL操作：

#!/usr/bin/perl
#################################################################
# parse_binlog.pl: find proper mysql binlog file and print lines
# match a giving regex.
# Author: Curu Wong
# License: GPL v2
# Date: 2012-10-31
#################################################################

use strict;
use warnings;
use File::Glob ':glob';
use File::stat;
use Getopt::Long;

sub parse_time{
  my $time_str = shift;
  my $ts = qx(date -d "$time_str" +%s);
  return $ts;
}

#return file atime and mtime
sub get_amtime{
  my $file = shift;
  my $stat = stat($file);
  return ($stat->atime,$stat->mtime);
}

sub select_binlog_files{
  #$logbin is mysql --log-bin param
  my $logbin = shift;
  my $time_start = shift;
  my $time_end = shift;
  my @result;
  my @files = bsd_glob("$logbin.[0-9]*");
  for my $f (@files){
    my ($atime, $mtime) = get_amtime($f);
    push @result, $f if ($mtime >= $time_start && $atime <= $time_end);
  }
  return @result;
}

sub get_sql_from_binlog{
  my $time_start = shift;
  my $time_end = shift;
  my $db = shift;
  my $tbl = shift;
  my @binlogfiles = @_;
  my $mysqlbinlog_cmd="mysqlbinlog --start-datetime='$time_start' --stop-datetime='$time_end' @binlogfiles";
  print STDERR "$mysqlbinlog_cmd\n";
  open(my $BINLOG, "$mysqlbinlog_cmd|")
    or die "unable to run mysqlbinlog:$!";
  #separate record by binlog header
  my $record_sep = "# at ";
  local $/ = "\n$record_sep";
  my $cur_db = "";
  my $use_db = 0;
  print '*' x 80,"\n";
  print "db:$db, tbl:$tbl\n";
  print '*' x 80,"\n";
  my @tbl_regex = (
    qr/^\s*CREATE\s+(?:TEMPORARY)?\s*TABLE\s*(?:IF\s+NOT\s+EXISTS)?\s*`?\b$tbl\b'?/i,
    qr/^\s*ALTER\s+(?:IGNORE)?\s*TABLE\s+`?\b$tbl\b`?/i,
    qr/^\s*(?:CREATE|DROP).+?\s+INDEX\s+.*?\s+ON\s+`?\b$tbl\b`?/i,
    qr/^\s*DROP\s+(?:TEMPORARY)?\s*TABLE\s+.*?`?\b$tbl\b`?/i,
    qr/^\s*RENAME\s+TABLE.*?\b$tbl\b/i,
    qr/^\s*TRUNCATE\s+(?:TABLE)?\s*`?\b$tbl\b`?/i,
    qr/^\s*DELETE.*?\s+FROM\s+`?\b$tbl\b`?/i,
    qr/^\s*(?:INSERT|REPLACE)\s+(?:LOW_PRIORITY|DELAYED|HIGH_PRIORITY)?(?:\s+IGNORE)?(?:INTO\s+)?\s*`?\b$tbl\b`?/i,
    qr/^\s*LOAD\s+DATA.*?INTO\s+TABLE\s+`?\b$tbl\b`?/i,
    qr/^\s*UPDATE\s+.*?`?\b$tbl\b`?/i,
  );
  while(my $record = <$BINLOG>){
    #skip next 2 line
    $record = substr($record, index($record, "\n") + 1);
    $record = substr($record, index($record, "\n") + 1);
    my @statements = split /;\n/, $record;
    for my $stmt (@statements){
      if($stmt =~ /use\s+`?(\S+)`?/){
        $cur_db = $1;
        $use_db = 1;
      }
      next if($db && ($db ne $cur_db));
      if($use_db){
        $use_db = 0;
        print "$stmt;\n";
        next;
      }
      print "$stmt;\n" if (!$tbl or grep { $stmt =~ m/$_/ } @tbl_regex);
    }
  }
  close($BINLOG);
}

sub usage{
  print "Usage: $0 [-i] <log-bin> <start_time> <stop_time> <db_name> [tbl_name]\n";
  print "eg:\n";
  print " $0 /var/lib/mysql/mysql-bin '2012-10-20 00:00:00' '2012-10-21 00:00:00' 'd_qqpcmgr_crash_raw'\n";
  print "\n";
  print "Note: <start_time> or <stop_time> must be in valid mysql DATETIME or TIMESTAMP format\n";
  print " use -i option to ignore case in <regex>\n";
  print " <log-bin> is the full file prefix of mysql binlog, not just filename prefix\n";
  exit(1);
}

#main
my $ignore_case = 0;
#parse options
GetOptions('help|h' => \&usage);
if(@ARGV < 5){
  usage();
}
my ($dir, $time_start, $time_end, $db, $tbl) = @ARGV;
my @binlog = select_binlog_files($dir, parse_time($time_start), parse_time($time_end));
get_sql_from_binlog($time_start, $time_end, $db, $tbl, @binlog);

#mysql

相关文章