How to remove empty tables from a MySQL backup file.

Question

I have multiple large MySQL backup files all from different DBs and having different schemas. I want to load the backups into our EDW but I don't want to load the empty tables.

Right now I'm cutting out the empty tables using AWK on the backup files, but I'm wondering if there's a better way to do this.

If anyone is interested, this is my AWK script:

EDIT: I noticed today that this script has some problems, please beware if you want to actually try to use it. Your output may be WRONG... I will post my changes as I make them.

# File: remove_empty_tables.awk
# Copyright (c) Northwestern University, 2010
# http://edw.northwestern.edu

/^--$/ {
  i = 0;
  line[++i] = $0; getline

  if ($0 ~ /-- Definition/) {
    inserts = 0;
    while ($0 !~ / ALTER TABLE .* ENABLE KEYS /) {
      # If we already have an insert:
      if (inserts > 0)
        print
      else {
        # If we found an INSERT statement, the table is NOT empty:
        if ($0 ~ /^INSERT /) {
          ++inserts
          # Dump the lines before the INSERT and then the INSERT:
          for (j = 1; j <= i; ++j) print line[j]
          i = 0
          print $0
        }
        # Otherwise we may yet find an insert, so save the line:
        else line[++i] = $0
      }
      getline # go to the next line
    }
    line[++i] = $0; getline
    line[++i] = $0; getline
    if (inserts > 0) {
      for (j = 1; j <= i; ++j) print line[j]
      print $0
    }
    next
  } else {
    print "--"
  }
}

{
  print
}

Answer 1

I can't think of any option in mysqldump that would skip the empty tables in your backup. Maybe the -where option but not sure you can do sth generic. IMHO a post-treatment in a second script is not that bad.