MySQL-mysql上如何实现增量备份，又如何实现按时间点还原

Question

请教mysql上如何实现增量备份，又如何实现按时间点还原。大家有什么好的方法？

Answer 1

要做增量备份首先要做完全备份，mysqldump --single-transaction --all-databases>xxx.sql,这是在线非块备份，不会干扰对表的读写.要想进行增量备份，我们需要保存增量更改。应使用--log-bin选项启动MySQL服务器，以便更新数据时将这些更改保存到文件中。该选项启用二进制 日志，因此服务器写将每个更新数据的SQL语句写入MySQL二进制日志。指定恢复时间,可以在mysqlbinlog语句中通过——start-date和——stop-date选项指定DATETIME格式的起止时间。这篇文章会对你有所帮助，mysql增量备份及恢复

Answer 2

应该没有现成的增量备份恢复工具，不过可以这样做

设置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 gtowey@gmail.com
# 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 namen";
$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/[rn]/ /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;
}