How can I Search for a string in all fields across all tables of a MySQL database?

I want to find a string and I don't know exactly which column and which table.

How can I find it?

Thanks.

This is a grueling problem and I have grueling suggestions
SUGGESTION #1: Use information_schema

See my old posts on how to go about doing this

• Jul 19, 2012 : Query to find and replace text in all tables and fields of a mysql db

• Mar 19, 2013 : How to search whole MySQL database for a particular string

SUGGESTION #2: Use mysqldump

You should be able to hunt down the string using mysqldump

Let's say the string you are looking for is 'Hello, World'

You can mysqldump all user-defined databases into a text file and grep the text file:

STRTOFIND="Hello, World"
DUMPFILE=mysqldump_to_search.sql
SRCHFILE=Search_Resutls.txt
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SET group_concat_max_len = 1048576;"
SQL="${SQL} SELECT GROUP_CONCAT(schema_name SEPARATOR ' ')"
SQL="${SQL} FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('information_schema','performance_schema','mysql')"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
MYSQLDUMP_OPTIONS="--single-transaction --routines --triggers -B ${DBLIST}"
mysqldump ${MYSQL_CONN} -A ${MYSQLDUMP_OPTIONS} > ${DUMPFILE}
grep -n "${STRTOFIND}" ${DUMPFILE} > ${SRCHFILE}

The search results file will not only have the line the string is located, but also the linenumber within the dump file.
GIVE IT A TRY !!!

StackExchange Database Administrators Q#105022, answer score: 3