1 #!/usr/bin/perl

     2 # 

     3 # take apart a mysql dump.gz to into compressed table.zstd files

     4 #

     5 # cc-by_sa / GPLv3+ by Peter 'grin' Gervai, 2023

     6 #

     7 

     8 use warnings;

     9 use strict;

    10 use IO::File;

    11 

    12 # the dump file

    13 my $fn = "db_dump.sql.gz";

    14 

    15 # skip these tables

    16 my %skip = ( 'stupid_table' => 1, 'dont_need_either' => 1 );

    17 

    18 # target dir

    19 my $dir = "d_split_tables";

    20 `mkdir -p ${dir}`			# that's the laziest way, highly unportable, yadda-yadda

    21 

    22 # cat a compressed file

    23 my $gzip_cat = "pigz -dcf";		# or 'gz' if you wish single threaded

    24 my $zstd_cat = "zstd -T0 -dcf";		# or 'gz -dcf' or whatever

    25 # store it compressed

    26 my $zstd_store = "zstd -T0 - -o";	# or 'gz - -o' or even 'cat >'

    27 

    28 # globals are okay

    29 my $table='';	# actually processing table name (convenience)

    30 my $skip=0;	# are we skipping a table now?

    31 

    32 # write prelude into this file

    33 my $fout = IO::File->new( "| ${zstd_store} prelude.sql" ) or die "cannot create prelude.sql: $!";

    34 

    35 my $cnt=0; 	# count tables for fun

    36 

    37 # read the compressed dump (usually .gz)

    38 $f = IO::File->new( "${gzip_cat} $fn |" ) or die "cannot read pipe: $!";

    39 while( <$f> ) {

    40     if( $skip ) {			# we are actively skipping this table

    41         if( /^UNLOCK TABLES;/ ) {	# that's the end of a table dump (for me, if it isn't for you... tough luck)

    42             $skip = 0;			# end of skip mode

    43         }

    44         next;				# skip, get next line

    45     }

    46     

    47     if( /^DROP TABLE.+`([^`]+)`;/ ) {	# start of a new table (again, for me)

    48         $table = $1;			# better readability: get table name

    49         if( $skip{$table} ) {		# this could be rewritten as a regex, for example

    50             print "SKIP $table!\n";	# skip stuff in %skip hash

    51             $skip = 1;			# entering skip mode

    52             next;

    53         }

    54         $fout->close;			# let's start a new file for a new table

    55         $fout = IO::File->new( "| ${zstd_store} ${dir}/table_${table}.sql.zst" ) or die "Cannot pipe for table_${table}.sql: $!";

    56         $cnt++;

    57         print "Created table_${table} as #${cnt}...         \r";

    58     }

    59     

    60     print $fout $_;			# no skip, we have a file already open, so write the line there

    61 }

    62 

    63 print "\n\nDone. ($cnt)\n\n";		# sayonara.