Thatbytes

Mostly Interesting

Mysql Dump Splitter

After trying to import massive db dumps and it taking for ever i came up with a script to chop up a mysql dump at the table level. This allows me to import the dumps table by table, allowing for a multi threaded import rather than a single threaded import.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
#!/usr/bin/php
<?php
$start=time();
echo "MySQL Dump Split to Tables \r\n";

set_time_limit(600);

$filetype=mime_content_type($argv[1]);

if(!isset($argv[1]))
{
  echo "Please provide dump file as a argument \r\n";
  echo "If the 2nd argument is gzip it will compress the sql dumps of the tables \r\n";
  exit(1);
}


if(isset($argv[2]))
{
  if($argv[2] == "gzip")
  {
      $gzipoutput=true;
  }
}

if($filetype == "text/plain")
{
  $handle = @fopen($argv[1], "r");
}
else if($filetype == "application/x-gzip")
{
  $handle = @gzopen($argv[1], "r");
}
else
{
  echo "Please provide a sql or gzip compressed sql file \r\n";
        exit(1);
}


$header=true;
if ($handle)
{
  while (!feof($handle))
  {
      $line = fgets($handle);
      if($header == true && !strstr($line, 'Table structure for table'))
      {
          $headersstuff=$headersstuff.$line."\n";   
      }
      
      if(strstr($line, 'Table structure for table'))
      {
          $header=false;
          
          if(isset($out))
          {
              if($gzipoutput == true)
              {
                  gzclose($out);
              }
              else
              {
                  fclose($out);
              }
              unset($out);
          }
          
          preg_match('%Table structure for table `(.+?)`%', $line, $matches);
          $table_name = $matches[1];
          if($gzipoutput == true)
          {    
              $out = gzopen($table_name . '.gz', 'w'); 
          }
          else
          {
              $out = fopen($table_name . '.sql', 'w');
          }
          echo "Create dump for $table_name \r\n";
          fwrite($out,$headersstuff);
          fwrite($out, $line . "\n");
      }
      else
      {
          if(isset($out))
          {
              fwrite($out, $line . "\n");
          }
      }
  }
  
  if($gzipoutput == true)
        {
          gzclose($out);
        }
        else
          {
               fclose($out);
        }

  unset($out);

  
  if($filetype == "text/plain")
  {
      fclose($handle);
  }
  else
  {
      gzclose($handle); 
  }
}
else
{
  echo "Unable to open file \r\n";
        exit(1);
}
$end=time();
$total=$end-$start;
echo "\r\n Total time taken in seconds".$total."\r\n";
echo "Done \r\n";
?>

Comments