Mysql, backing up

Jimbo (Talk | contribs)
(Created page with 'Sometimes, mysqldump just isn't quite what you want, by itself, for backing up large servers with many databases. In one case, I found myself needing a single dump of ALL da…')

---

Latest revision as of 19:57, 23 January 2011

Sometimes, mysqldump just isn't quite what you want, by itself, for backing up large servers with many databases.

In one case, I found myself needing a single dump of ALL databases on a given server, in a consistent state with one another (ie, no updates done to one db after another db had been dumped). The only way to do that with mysqldump is to use the --single-transaction flag and dump all databases in a single command... but if you do that, you end up with one big monolithic dump file, which can be seriously inconvenient if you do need to restore (or just examine the contents of) a single db at a time.

The following script was my solution to this problem. It reads host, username, and password as single lines from a config file, like so:

127.0.0.1
root
mypassword

Which you specify as the single argument given to the perl script, like so:

me@box:~$ perl /usr/local/bin/mysqlbak.pl /etc/mysqlbak.conf

Like it says in the comments, the script will output a single dump file for each database, in the current directory at the time the script is run.

#!/usr/bin/perl

#
# reads $host, $user, and $password as one single line in that order 
# from config file specified as the argument passed to the program.
# backs up all databases at $host to individual gzipped dumpfiles
# in the current directory.
#

open CONF, $ARGV[0];
$host = <CONF>;
$user = <CONF>;
$password = <CONF>;
close CONF;
chomp $host; chomp $user; chomp $password;

$mysql = '/usr/bin/mysql';
$mysqldump = '/usr/bin/mysqldump';
$grep = '/bin/grep';
$gzip = '/bin/gzip';

@databases = `echo show databases | $mysql -u $user -h $host -p'$password' | $grep -v information_schema`;
delete $databases[0];
$dblist = join (' ', @databases);
$dblist =~ s/\n//g;

open DUMP, "$mysqldump -C --flush-logs --single-transaction -u $user -h $host -p'$password' --databases$dblist |";

do {
        $line =	<DUMP>;
        push @header, $line;
} until ($line =~ /^-- Current Database/); 

pop @header;

open OUT, "> /dev/null";
do {
        if ($line =~ /^-- Current Database/) {       
                close OUT;
                (my $db) = ($line =~ /.*\`(\w*)\`/);
                open OUT, "| $gzip --rsyncable > $db.sql.gz";
		print OUT @header;
		print OUT $line;
        } else {
		print OUT $line;
	}
 } while ($line = <DUMP>);

close OUT;
close DUMP;