Splitting massive MySQL dumps

As I posted yesterday, I have a massive MySQL dump to import. I tried BigDump, but one of the tables kept producing errors and so BigDump would exit. I don't need the whole db imported, so I wrote this to split it by table. It produces a new sql file for every table it finds, numbered sequentially so if you process them in alphabetical order it's the equivalent of the whole dump. USE statements get their own files in the same sequence.

  1. #! /usr/bin/perl
  2.  
  3. use strict;
  4. use warnings;
  5. use 5.010;
  6.  
  7. my $dump_file = $ARGV[0];
  8. &usage() if !$dump_file;
  9.  
  10. say "using ".$dump_file;
  11.  
  12. my ($line, $table,@query, $file_number,$file_name);
  13. my $line_number = 1;
  14. my $find_count = 0;
  15.  
  16. open(DUMP_IN, "< $dump_file");
  17. while(<DUMP_IN>){
  18. my $line = $_;
  19. if (/^USE\s.(\w+)./){
  20. say "changing db: ".$1;
  21. $file_name = &make_file_name("USE_$1", "$find_count");
  22. &write_USE($file_name, $line);
  23. $find_count++;
  24. }elsif (/^-- Table structure for table .(.+)./){
  25. ## If the current line is the beginning of a table definition
  26. ## and @query is defined, then @query must be full of the previous
  27. ## table, so we want to process it now:
  28. if (@query){
  29. $file_name = &make_file_name("$table", "$find_count");
  30. open(OUTPUT, ">$file_name");
  31. foreach(@query){
  32. print OUTPUT $_;
  33. }
  34. close OUTPUT;
  35. undef @query;
  36. }
  37. $table = $1;
  38. $find_count++;
  39. }
  40. next unless $table;
  41. push @query, $line;
  42.  
  43. $line_number++;
  44. }
  45. close DUMP_IN;
  46. say $line_number;
  47.  
  48. ## Subroutines!
  49. sub write_USE() {
  50. my($filename, $line) = @_[0,1];
  51. open (OUTPUT, ">$filename");
  52. print OUTPUT $line;
  53. close OUTPUT;
  54. }
  55.  
  56. sub make_file_name() {
  57. my ($type, $number) = @_[0,1];
  58. $number = sprintf("%05d", $number);
  59. $file_name=$number."_".$type.".sql";
  60. return $file_name;
  61. }
  62.  
  63. sub usage() {
  64. say "Error: missing arguments.";
  65. say "Usage:";
  66. say "$0 [MYSQL_DUMP]";
  67. exit 1;
  68. }
  69.  

A small downside is that this replaces my 2.5Gb file with about 1800 smaller ones. A scripted importer is to follow.