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.