用awk分割 mysqldump结果

cenalulu

Awk, which has been existing for long time on UNIX like operating
system, may sound legacy for people who live in modern web-based
programming world. However, awk is really sophisticated and useful tool
for various purposes. Today, I'll show you how to use it with mysqldump
;)
One feature which is missing on the current mysqldump command
is to separate dump files per DB or table. It can store all data into a
large single file only, whether we run the command with --result-file
option or redirect the output to some file. Of course it is possible to
run mysqldump command several times per DB or table, but it cannot
produce a consistent snapshot at a certain time, each dump may be
time-shifted in other words, unless all involved tables are locked
during dump. So, we need to backup database using a single mysqldump
instance if we need a consistent data.
Now, you can make use of
awk! It is a really powerful text processor. As an output of mysqldump
is text, you can process the output using awk. See the following awk
script:
#!/usr/bin/awk -f
function is_new_db(db_name) {
  for (i = 1; i  current_file;
      for (i = 1; i > current_file;
    }
  } else if (num_db == 0) {
    num_prelines++;
    prelines[num_prelines] = $0;
  } else if ($2 == "Dump" && $3 == "completed") {
    num_postlines++;
    postlines[num_postlines] = "";
    num_postlines++;
    postlines[num_postlines] = $0;
  } else {
    print $0 >> current_file
  }
  next;
}
/^\/\*.+\*\/;/ {
  if (match($0, "character|collation")) {
    print $0 >> current_file;
  } else if (match($0, "SET")) {
    if (num_db == 0) {
      if (match(prelines[num_prelines], "^\-\-")) {
        num_prelines++;
        prelines[num_prelines] = "";
      }
      num_prelines++;
      prelines[num_prelines] = $0;
    } else {
      num_postlines++;
      postlines[num_postlines] = $0;
    }
  } else {
    print $0 >> current_file;
  }
  next;
}
{ print $0 >> current_file }
END {
  for (i = 1; i > current_file
    for (j = 1; j > current_file;
    }
  }
}
Save
the script into a file named separate-dump.awk etc, and make it
exectable (chmod u+rx). You can separate dump files per DB by using the
script like below:
shell> mysqldump -A --single-transaction --master-data=2 --flush-logs | ./separate-dump.awk
Then,
dump files named like "database_name.sql" are created under your
current directory! Of course, you can also process an existing dump
file like below:
shell> ./separate-dump.awk Making use of "good old fashioned" unixy command tools will make us happy ;)
Enjoy!!
               
               
               

本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u3/90603/showart_2135671.html