趁着年轻

最近工作中需要用到数据库热备，虽然MYISAM表比较过时了，但是其索引性能还是有很大诱惑力的。

关于MYISAM表的备份，相对Innodb简单多了，因为其把表格式文件，数据文件，索引文件分开存放，容易管理。

表结构存放在**.frm , 数据文件在.MYD，索引文件在.MYI，关于数据库的索引 实现原理可以参考这篇文章，写的不错。

MYISAM表的备份简单来说就是拷贝走上面三类文件，但是之前需要保证着数据库内容已经完整写入到文件中，避免不一致的情况，所以在拷贝之前必须对表加读锁，阻塞写操作。

见到过如下错误的备份的情况：

echo "FLUSH TABLES WITH READ LOCK;" | mysql -uroot -phello -hlocalhost
mysql dump -uroot -phello -hlocalhost mydb >　mydb.sql
echo "UNLOCK TABLES;" | mysql -uroot -phello -hlocalhost

但是上面的方法是错误的，原因在于mysql对于锁的处理方式是基于session，当上面第一行脚本执行完后，其实这个客户端进程已经退出了，也就是连接端断开了，所以mysql会自动解锁的，所以上面的备份方法是错误的，会导致数据不一致。

然后在网上碰到有老外这么备份：就是在上面第一行的sql后面加一个sleep(111);的语句，让进程不退出，从而保证数据一致，然后拷贝完数据文件后，将该sql线程杀死来解锁。可以实现，但是总感觉不太靠谱。

后来想到了不用shell做，用php或者其他工具比如perl做，这样就可以在代码里面lock，然后拷贝数据，然后unlock， 之后退出。

于是从innobackupex 工具里面扣出mysql连接的相关代码，然后改改就OK了，不多说，看下面代码：

#!/usr/bin/env perl

use warnings FATAL => 'all';
use strict;
use Getopt::Long;
use File::Spec;
use Pod::Usage qw(pod2usage);
use POSIX "strftime";
use POSIX ":sys_wait_h";
use FileHandle;
use File::Basename;
use File::Temp;
use File::Find;
use File::Copy;
use English qw(-no_match_vars);
use Time::HiRes qw(usleep);

my $prefix = "db_backuper" ;
# check existence of DBD::mysql module
eval {
require DBD::mysql;
};
my $dbd_mysql_installed = $EVAL_ERROR ? 0 : 1;

my $option_mysql_password = 'hello';
my $option_mysql_user = 'root';
my $option_mysql_port = '3306';
my $option_mysql_host = '127.0.0.1';
my $option_defaults_file = '/etc/mysql/my.cnf';

my (%conf);
my %conf
%conf{"host"} = '127.0.0.1';

my %mysql;
my $now;

######################################################################
# program execution begins here
######################################################################
print "\n=========================backup start =============\n\n";
system("date");

my $tmp;
%mysql = mysql_connect(abort_on_error => 1);
mysql_query(\%mysql, 'FLUSH TABLES WITH READ LOCK;');

#拷贝数据
my $times = strftime("%y%m%d-%H%M%S", localtime());

my $cpcmd = "cd /home/mysqldatadir/ && ls | xargs -i cp -va {} /home/backup/baktime_".$times."/" ;
system(" mkdir -p /home/backup/baktime_".$times."/");
system($cpcmd);
my $rmoldcmd = "find /home/mysqldatadir -name 'baktime_*' -type d -mmin +86400 | xargs rm -rf" ;
system($rmoldcmd);

mysql_query(\%mysql, 'UNLOCK TABLES;');

system("date");
print "=========================backup end =============\n\n";

#
# mysql_connect subroutine connects to MySQL server
#
sub mysql_connect {
my %con;
my %args = (
# Defaults
abort_on_error => 1,
keepalives => 0,
@_
);

$con{abort_on_error} = $args{abort_on_error};
$con{keepalives} = $args{keepalives};

parse_connection_options(\%con);

$now = current_time();
print STDERR "$now  $prefix Connecting to MySQL server with DSN '$con{dsn}'" .
(defined($con{dsn_user}) ? " as '$con{dsn_user}' " : "") .
" (using password: ";
if (defined($con{dsn_password})) {
print STDERR "YES).\n";
} else {
print STDERR "NO).\n";
}

eval {
$con{dbh}=DBI->connect($con{dsn}, $con{dsn_user},
$con{dsn_password}, { RaiseError => 1 });
};

if ($EVAL_ERROR) {
$con{connect_error}=$EVAL_ERROR;
} else {
$now = current_time();
print STDERR "$now  $prefix Connected to MySQL server\n";
}

if ($args{abort_on_error}) {
if (!$dbd_mysql_installed) {
die "ERROR: Failed to connect to MySQL server as " .
"DBD::mysql module is not installed";
} else {
if (!$con{dbh}) {
die "ERROR: Failed to connect to MySQL server: " .
$con{connect_error};
}
}
}

if ($con{dbh}) {
$con{dbh}->do("SET SESSION wait_timeout=2147483");
if ($con{keepalives}) {
start_keepalives(\%con);
}
}

return %con;
}

#
# parse_connection_options() subroutine parses connection-related command line
# options
#
sub parse_connection_options {
my $con = shift;

$con->{dsn} = 'dbi:mysql:';

# this option has to be first
if ($option_defaults_file) {
$con->{dsn} .= ";mysql_read_default_file=$option_defaults_file";
}

$con->{dsn} .= ";mysql_read_default_group=xtrabackup";

if ($option_mysql_password) {
$con->{dsn_password} = "$option_mysql_password";
}
if ($option_mysql_user) {
$con->{dsn_user} = "$option_mysql_user";
}
if ($option_mysql_host) {
$con->{dsn} .= ";host=$option_mysql_host";
}
if ($option_mysql_port) {
$con->{dsn} .= ";port=$option_mysql_port";
}
}
sub current_time {
return strftime("%y%m%d %H:%M:%S", localtime());
}

#
# mysql_query subroutine send a query to MySQL server child process.
# Parameters:
#    query     query to execute
#
sub mysql_query {
my ($con, $query) = @_;

eval {
if ($con->{keepalives}) {
stop_keepalives($con);
}

if ($query eq 'SHOW VARIABLES') {
$con->{vars} = $con->{dbh}->selectall_hashref('SHOW VARIABLES',
'Variable_name');
} elsif ($query eq 'SHOW STATUS') {
$con->{status} = $con->{dbh}->selectall_hashref('SHOW STATUS',
'Variable_name');
} elsif ($query eq 'SHOW MASTER STATUS') {
$con->{master_status} =
$con->{dbh}->selectrow_hashref("SHOW MASTER STATUS");
} elsif ($query eq 'SHOW SLAVE STATUS') {
$con->{slave_status} =
$con->{dbh}->selectrow_hashref("SHOW SLAVE STATUS");
} else {
$con->{dbh}->do($query);
}

if ($con->{keepalives}) {
start_keepalives($con);
}
};
if ($EVAL_ERROR) {
die "\nError executing '$query': $EVAL_ERROR\n";
}
return $con;
}

数据恢复的时候比较简单，将被分的文件拷贝到线上mysql， 然后启动mysql即可。后面改天记一下用innobackupex的使用体验。