MySQL Search Entire DB for String

search_db_for_string.sql

-- Be careful using this on large or on servers with production databases, it could take a long time, and is very invasive to performance.
-- Example, searching a 6GB Magento database on an idle dedicated server took 2 minutes.

set @search = 'my_search_text';
set @database = 'database_name';

select CONCAT('select \'', tbl.`TABLE_NAME`,'\' as TableName, \'', col.`COLUMN_NAME`,'\' as Col, `',col.`COLUMN_NAME`,'` as value  from ' ,@database,  '.`' , tbl.`TABLE_NAME`,'` where `' ,
        col.`COLUMN_NAME` , '` like \'%' ,@search,  '%\' UNION') AS q
from information_schema.`tables` tbl
inner join information_schema.`columns` col on tbl.TABLE_SCHEMA = col.TABLE_SCHEMA and tbl.`TABLE_NAME` = col.`TABLE_NAME`and (col.DATA_TYPE='varchar' or col.DATA_TYPE='text')
where tbl.TABLE_SCHEMA  =  @database;

-- If you can dump the database, you may want to try this as a more performant option (courtesy of David Alger):
-- time mysqldump --single-transaction --skip-extended-insert database_name | grep -i '\\"login\\"' | grep -i username | awk '{print $3}' | sort | uniq -c