OXT blog
“People say nothing is impossible, but I do nothing every day.” – Whinnie The Pooh

Recently I faced a problem – I had large mysql dump on remote machine and I needed just some tables from it. Dump file was about 900 Mb, bzip’ed – around 60 Mb. I didn’t want to download it just for about 1 Mb of required data. First idea that came into my head – load this dump to the database on remote machine and then dump again only required tables. The problem that I had no possibility to do that – I had just ftp/ssh access. So, I opened this ~1Gb sql file with vi. It opened it even with syntax highlighting! But when I tried to remove 2 millions of unnecessary lines it hanged forever… Emacs wasn’t better.

I had no wish to learn something like sed, so I wrote small perl script for this task. Usage is simple:

perl strip-dump.pl your-dump-file.sql table1 table2 table3

Script source:

#!/usr/bin/perl -w
#Get only required tables from the whole dump
use strict;
 
my $dump_file = shift @ARGV;
my $out_dump_file = 'stripped_dump.sql';
 
open DUMP, "<$dump_file" or die $!;
open OUT, ">$out_dump_file" or die $!;
 
my @required_tables = @ARGV;
 
my $table_dump_started = 0;
while (<DUMP>)
{
	if (index($_, 'CREATE TABLE') >= 0)
	{
		$table_dump_started = 0;
		foreach my $table (@required_tables)
		{
			if (index($_, "`$table`") >= 0)
			{
				$table_dump_started = 1;
				last;
			}
		}
	}
	print OUT
		if $table_dump_started;
}
 
close OUT;
close DUMP;

Tags: mysql, perl

This entry was posted on Saturday, July 7th, 2007 at 4:22 pm and is filed under Programming. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.