Fastest way to search a string in a mysql database

Question

When searching a string in an mysql database without knowing in what table or column I might find it, I usually resort to

 mysqldump --extended-insert=FALSE --complete-insert=TRUE dbname|grep SomeString

That dumps a lot of unneeded data, ignores indeces invented for that exact purpose, and makes it rather hard to localize the results. Is there a more convenient and more performant way? (Except installing 3rd party software.)

About duplicate questions: As a comment pointed out, I am not restricted to SQL queries, but will accept any solution - may it be a bash script, some CLI function I might have not seen, or grepping the DB file as suggested.

May be a duplicate of https://stackoverflow.com/questions/639531/search-in-all-fields-from-every-table-of-a-mysql-database — Cataklysim, Nov 10 '17 at 14:11
Possible duplicate of [Search in all fields from every table of a MySQL database](https://stackoverflow.com/questions/639531/search-in-all-fields-from-every-table-of-a-mysql-database) — Cataklysim, Nov 10 '17 at 14:11
That question may contain answers to this question but this question asks and encourages also for non-SQL ways too. So not a dupe. — Taha Paksu, Nov 10 '17 at 14:12

Taha Paksu · Answer 1 · 2017-11-10T14:54:14.330

0

I found a find command that lists the file which contains a specific string:

find [path of data folder of database]/*.ibd -type f -exec grep -i '[search string]' {} +

Outputs when found:

Binary file [path]/[table].ibd matches

Doesn't outputs anything when not found.

Means:

find     : linux find command
path     : the path pointing to the database folder under mysql/data 
           directory which contains the table's ibd files
-type f  : only search for files
-exec    : execute this while listing

    grep -i  : case insensitive text search
    string   : string to search for

For others, read this:

https://serverfault.com/a/343177

Same usage here:

http://www.valleyprogramming.com/blog/linux-find-grep-commands-exec-combine-search

edited Nov 10 '17 at 14:54

answered Nov 10 '17 at 14:39

Taha Paksu

15,371
2
44
78

1

Don't know if this is unbelievable brilliant or incredibly dangerous.. what if find read locks the .ibd files? – Raymond Nijland Nov 10 '17 at 14:47
Better than mysqldump for sure. I read that grep doesn't lock the file, and find is used for listing only. So I guess it's perfectly safe. – Taha Paksu Nov 10 '17 at 14:53
Well then it should be safe well still doubt it will work because ibd files are stored in binary format. – Raymond Nijland Nov 10 '17 at 15:20

Fastest way to search a string in a mysql database

1 Answers1

Linked