is there any program or script that can search mysql databases?

Portal Home > Knowledgebase > Articles Database > is there any program or script that can search mysql databases?

Posted by CNSERVERS, 08-31-2007, 10:02 PM
Looking for a program or script able to search particular text in all mysql databases under a user. phpmyadmin is able to do that for one database, but I need to be able to search all the databases. Thanks
Posted by david510, 08-31-2007, 10:53 PM
Is the word found in a table that is having same name in all databases?
Posted by CNSERVERS, 08-31-2007, 11:09 PM
no, all the databases structures are different.
Posted by jon-f, 08-31-2007, 11:25 PM
you can just go to the sql directory and do find and grep for example find /var/lib/mysql/usersdir -name *.* | exec grep searchstring Im sure there is probably other ways and better ways but if I had to find something in someones db Id use that.
Posted by CNSERVERS, 08-31-2007, 11:40 PM
no, what I want to do is search text from all the records inside the databases, not the database name.
Posted by jon-f, 09-01-2007, 01:04 AM
yeah that's what it does it finds all files in the directory, since there are different extensions you can put *.* then when it finds them which it will find them all it will grep each file for whatever string you are looking for or you could just grep the entire directory
Posted by dollar, 09-01-2007, 01:07 AM
He doesn't want to search for the actual database names or table names, he wants to search for data within each of those MySQL databases. As far as I know nothing exists that will handle this out of the box. You could either spend the time doing it by hand, or you could automate it. It really depends on how many databases you have to go through as to if you want to automate it or not. If you have a list of the databases you can just create a SQL file with your queries and a shell script to run it on all existing databases (from your text list or however you want to list the names). How many databases are we talking about here?
Posted by foobic, 09-01-2007, 01:50 AM
Well, mysql does store its data as files and although they're binary format they're not encrypted - you would expect simple text strings to be searchable by grep, giving you a list of tables containing the search text. It might not be 100% reliable but SecureServerTech's suggestion should work. Normally though there's no separation of database files into user directories, so you might end up searching all databases belonging to all users unless you're on a control panel using the username_dbname name convention: grep -lr "text" /var/lib/mysql/username_*
Posted by kerplunk, 09-02-2007, 01:36 PM
I think Webmin may do this?