mysql: search all tables for a specific substring

Question

Short of SQL-dumping my entire database to text and searching that, is there a way to search for a specific substring in all tables of a MySQL database if you are not sure which table/record/field that substring actually occurs?

Answer 1

Dumping to a text file and searching will be the quickest & least complicated means.

The alternative is that you have to use Dynamic SQL (MySQL's Prepared Statements) because you can't iterate over a list of tables to query against in standard SQL. Additionally, not all columns will be VARCHAR/string based - so these need to be filtered out before attempting LIKE %your_substring%/etc.

Answer 2

Some ideas:

You could use some external full-text search capability, such as http://www.sphinxsearch.com/

Search your binary logs. If you use statement-based logging, that should be pretty straightforward.

It would be nasty and dirty to just search the data files themselves, but that would probably work for the most part. YMMV to an extreme degree!

You could probably come up with algorithm that identifies all the text columns in all the tables, and then, as OMG points out, write some queries for those columns.

I would probably search the binary logs (assuming you have them, of course). When I am researching some database weirdness, I search our logs for some substring that is likely to be unique to the circumstances, then I can zero in on the exact statements in question.