Wed Feb 29 09:40:07 EST 2012

Some things I learned these days

Playing with MySQL logfiles for work.  Note that I'm an amateur at
this and some approaches might be convoluted.  Basic idea: in bash
it's simplest to work with TSV (tab separated value) files, iterate
over them with a "while read RECORD; do ... ; done" and use the
sed/awk/xargs duct tape tools.

* Adding data to the database.

  Basically, just generate SQL from any record stream and pipe it into
  mysql command line client.

  Also useful for custom dumps (write a script that does TSV -> SQL
  conversion using "while read RECORD; do ... ; done".

* Mirroring a database + regexp conversion.

  I wanted a sandbox to create more tables so didn't work in the
  read-only production DB.  Copying databases can usually be done
  using "mysqldump".  However I did not find a way to do a selective
  dump, so I use the TSV->SQL approach.

* Put individual SQL queries in a .sql files and load them in the
  mysql command line client using the "source" command.

* Use views to combine different tables.  I.e. I could find 3
  different kinds of data:

   - log files

   - log file annotation from constant data (i.e. ELF symbol tables)

   - manual annotation

  These are fundamentally different.  The first is the original raw
  data, second is data derved from original raw data in a different
  form (ELF binaries in this case).  

  The latter is data updated during the manual log processing, which
  is dynamic in nature and reflects the state of the current analysis.