mysql myisam表备份_mysql数据热备份脚本-MYISAM表

最近工作中需要用到数据库热备，虽然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的使用体验。