Answers
应该没有现成的增量备份恢复工具，不过可以这样做
设置mysql的general log,Mysql版本要在5.1.6之后
这里只介绍一种配置形式吧
添加到my.cnf中(重启mysql生效)
general_log=1
general_log_file=’/tmp/general.log’;
general.log里有所有的SQL记录以及SQL执行时间，再加个程序按天切割下日志
结合你每天的全备份，这样恢复的时候只要恢复一天的数据。
只需要过滤general.log里的SQL命令，下面是一个general.log的过滤程序，怎么导入SQL大家都知道吧
#!/usr/bin/perl
use strict;
use Data::Dumper;
use Getopt::Long;
# author: Gavin Towey, 2008 [email protected]
# todo, add "follow thread" capability
# so we can give a process name or thread id & see
# all activity in sequence for each thread
my %OPTIONS;
if (
!GetOptions( \%OPTIONS,
"help",
"type|t=s",
"pattern|p=s",
"preserve-newlines|n",
"separator|s=s" )
)
{
$OPTIONS{'help'}++;
}
if (!defined($OPTIONS{'type'})) {
$OPTIONS{'type'} = 'query';
} else {
$OPTIONS{'type'} = lc ($OPTIONS{'type'});
}
my $file = $ARGV[0];
if ( !$file ) {
print "missing log file name\n";
$OPTIONS{'help'}++;
}
if ( $OPTIONS{'help'} ) {
usage();
exit;
}
main();
exit;
my @LINEBUFFER;
sub get_next_query {
my ($FH) = shift;
my ( $query_found, $error, $in_block ) = ( 0, 0, 0 );
if ($#LINEBUFFER ==0 ) { $in_block = 1; }
while ( !$query_found && !$error ) {
$LINEBUFFER[ $#LINEBUFFER + 1 ] = <$FH>;
if ( !$LINEBUFFER[$#LINEBUFFER] ) {
return -1;
}
if ( !$in_block
&& $LINEBUFFER[$#LINEBUFFER] =~ /^(\d{6} [\d:]{8})?\s+(\d+)\s(\w+)(\s(.*))?/ )
{ # we have the beginning of a line
if ( $#LINEBUFFER == 0 ) { # begin block capture
$in_block = 1;
}
}
elsif ($in_block) {
if ( $LINEBUFFER[$#LINEBUFFER] =~ /^(\d{6} [\d:]{8})?\s+(\d+)\s(\w+)(\s(.*))?/ ) {
if ( $#LINEBUFFER > 0 ) { #end block
# return everything up to this statement
$query_found = '';
for ( my $i = 0 ; $i < $#LINEBUFFER ; $i++ ) {
$query_found .= $LINEBUFFER[$i];
}
$LINEBUFFER[0] = $LINEBUFFER[$#LINEBUFFER];
$#LINEBUFFER = 0;
}
} else {
}
}
else {
shift @LINEBUFFER;
}
}
return $query_found;
}
sub main {
open( FILE, $file );
my $done = 0;
while ( !$done ) {
my $query = get_next_query( \*FILE );
if ( $query eq -1 ) {
$done = 1;
}
else {
chomp($query);
$query =~ /^(\d{6} [\d:]{8})?\s+(\d+)\s(\w+)(\s+(.*))?/s;
my ($type, $query ) = (lc($3), $5);
if (!$OPTIONS{'preserve-newlines'}) { $query =~ s/[\r\n]/ /g; }
if ( $type eq $OPTIONS{'type'}) {
if (defined($OPTIONS{'pattern'})) {
if ( $query =~ /$OPTIONS{'pattern'}/ ) {
print $query . $OPTIONS{'separator'} . "\n";
}
} else {
print $query . $OPTIONS{'separator'}. "\n";
}
}
}
}
close FILE;
}
sub usage {
print <<EOF;
NAME
$0 - dump statement from mysql general log format
USAGE
$0 <options> [log file]
SYNOPISIS
For the most part, the general log is pretty straighforward,
except when SQL statements contain newline characters.
This script takes care of finding those boundaries and
extracting whole statements.
Most often some filter is passed to the program in order
to return only certain types of statements.
OPTIONS
--help
Display this screen
--type=s
-t
One of Query or Connect, default is Query
--pattern=s
-p
Regular expression to match statements against.
Usually something like ^SELECT
--preserve-newlines
-n
Keep original newlines in multiline queries default
is to make all queries single line.
-separator=s
-s
Add the separator after every query
EOF
exit;
}
salvia
answered 12 years, 7 months ago