split_mysqldump.pl

#!/usr/bin/perl

# First written by orczhou.com orchzou@gmail.com
# This program is free software; you can redistribute it and/or modify it under
# the terms of the GNU General Public License as published by the Free Software
# Foundation; version 2 of the License.

#  How faster it is:
#    $ls -lh backup.sql.gz 
#     -rw-r--r-- 1 mysql dba 14G Nov 21 04:49 backup.sql.gz
#    $date && gunzip -c backup.sql.gz|./tbdba-restore-mysqldump.pl -s monitor_general -t monitor_host_info && date
#    Fri Nov 25 14:35:06 CST 2011
#    Fri Nov 25 14:46:49 CST 2011
#    (the unzip of backup.sql.gz is 88G)
#  
#  About it :
#    Restore one single table from a Huge mysqldump file VERY QUICKLY!
#    The backup file of mysqldump is sometimes very huge, if you wanna restore one 
#    or two table from the file, there is no easy way to do this. There some way we 
#    try:
#      1. split/csplit the file
#      2. restore some tables.
#    This script will get a tiny improvement, all you need do is :
#      tbdba-restore-mysqldump.pl -t process,user -s monitor -f backup.sql
#
#  Feature:
#    1. When all the table has been found and -s is specified, exit immediately.
#       So it's quicker; If the table you wanna is at the header of the sql file,
#       It will be very quick. That's why i use this a lot.
#    2. Every result sql file will hold the dump header, something like this:
#         /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
#         /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
#         /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
#         /*!40101 SET NAMES utf8 */;
#         /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
#         /*!40103 SET TIME_ZONE='+00:00' */;
#    3. With -a(--all-tables),you can get all the sql file. So this script also can 
#       split the dump file(It will be very useful for parallel restore).
#  
#  Tips:
#    1. If you only wanna dump ONE table: with -s will be much quicker.
#    2. At the end of every dump file, There will be a string like this:
#         -- Table Finished
#       This can be help you to tell whether job on this table finished(If 
#       finished,maybe you can restore it).
#
#  To Do:
#    1. Add parameter --target-dir to specify the target dir where dump file put
#    2. With -d(--debug),script will output some infomation of processing
#    3. Write the documentation with POD
#    4. add a parameter -i|ignore-use to igone the 'use db', in case you wanna retore table to 
#       another database.
#

use strict;
use File::stat;     # To get the file stat
use Time::localtime;
use Socket;
use Getopt::Long;

sub print_usage () {
  my $text = <<EOF;
 NAME:
    tbdba-restore-mysqldump.pl

 SYNTAX:
    Sample:
       1. Get table "process" from backup.sql
          tbdba-restore-mysqldump.pl -t process -f backup.sql
       2. Get table "process" of database "monitor" from backup.sql
          tbdba-restore-mysqldump.pl -t process -s monitor -f backup.sql
       3. Get table "process","users" of database "monitor" from backup.sql
          tbdba-restore-mysqldump.pl -t process,user -s monitor -f backup.sql
       4. Get the table sql file from a STDIN 
          gunzip -c backup.sql.gz|tbdba-restore-mysqldump.pl -t process,user -s monitor
       5. Get all the table sql files in schema 'monitor'
          gunzip -c backup.sql.gz|tbdba-restore-mysqldump.pl -s monitor 
       6. Get all the table sql files in the dump file 
          gunzip -c backup.sql.gz|tbdba-restore-mysqldump.pl --all-tables
       7. With -d, more infomation of processing will be output
          date && gunzip -c /backdir/backup.sql.gz|tbdba-restore-mysqldump.pl -d -a && date

 FUNCTION:
    Restore some tables from the while mysqldump backup

 PARAMETER:
    -t|--table=s
        which table you wanna recovery
    -s|schema=s
        in which schema the table your wanna recovery
    -a|--all-tables
        get all the sql file
        With --schema, will get all the sql file just in the schema
        Without --schema, will get all the sql file in the dump file
        If this paramter is specified, -t will be ignore
    -f|sql-file=s
        from which mysqldump backup file 
    -d|--debug
        debug mode; more output will be there
    -h|--help
        You already know
EOF
 print STDERR $text;
 exit 0;
}

my %opt = (
);

GetOptions(\%opt,
    's|database=s',          # write result to database
    'f|sql-file=s',          # write result to database
    't|table=s',             # debug mode  
    'a|all-tables+',       # debug mode  
    'd|debug+',              # debug mode  
    'h|help+',               # debug mode  
    # order-by: 
    #   execs|Query_time:cnt
    #   ela_time|Query_time:sum 
) or print_usage();

print_usage() if $opt{h};
my $file = "";
my $db = "";
my @tabs ;
my $inTableFlag = 0;
my $inDBFlag = 0;
my $outputdir = "./";
push(@tabs, $opt{t}) if $opt{t};
@tabs = split(/,/,join(',',@tabs));
my $tabcount = scalar(@tabs);
my $alltable = 0;
$alltable = 1 if $opt{a};
$db = $opt{s} if $opt{s};
$file = $opt{f} if $opt{f};

# if no db speicefied, all db is allowed
if($db eq ""){
  $inDBFlag = 1;
}

my $curtab = "";        # is dealing with this table
my $curdb = "";         # is dealing with this db
my $curCreatedbSQL="";  # the sql of create current database
my $headerFlag = 1;     # Whether is in the dump header
my $dumpHeader = "";
open (TABFILE, ">>STDERR"); 
my $ifh;
if($file eq ""){
  $ifh = *STDIN;
}else{
  open $ifh,"<", $file or die $!;
}
while(<$ifh>){
  if ($_ =~ /^-- Current Database\: `(.*)`/){
    print "$_" if $opt{d};
    $headerFlag = 0;
    $curdb = $1;
    if($db ne ""){
      if($inDBFlag == 1){
        # if $db ne "" and $inDBFlag == 1, A new database coming, now we quit
        exit 0;
      }
      $inDBFlag = 0;
      $inDBFlag=1  if $1 eq $db;
    }
  }elsif ($_ =~ /^-- Table structure for table `(.*)`/){
    print "$_" if $opt{d};
    $headerFlag = 0;
    if($db ne ""  && $tabcount == 0 && $alltable ==0){exit 0;}
    $curtab = $1;
    $inTableFlag = 0;
    print TABFILE "-- Table Finished";
    close (TABFILE);
    if($alltable == 1){
      $inTableFlag=1;
    }else{
      for(my $i=0;$i <= scalar(@tabs);$i++){
        if($tabs[$i] eq $1) {
          $inTableFlag=1;
          if($inTableFlag == 1 && $inDBFlag == 1){
            $tabcount = $tabcount - 1;
          }
        }
      }
    }
    if($inTableFlag == 1){
      open (TABFILE, ">>$outputdir"."$curdb."."$curtab".".sql");
      print TABFILE "$dumpHeader";
      print TABFILE "\n\n";
      print TABFILE $curCreatedbSQL;
      print TABFILE "\n\n";
      print TABFILE "USE `$curdb`;\n\n";
    }
  }elsif($_ =~ /^CREATE DATABASE.*;$/){
    print "$_" if $opt{d};
    $headerFlag = 0;
    $curCreatedbSQL = $_;
  }elsif($_ =~ /^USE .*;$/){
    # do nothing;
  }else{
    if($headerFlag == 1) {$dumpHeader .= $_};
    if($inTableFlag == 1 && $inDBFlag == 1) {print TABFILE $_;}
  }
}