way to search entire database for a string in MySQL

Question

I am working on a MySQL database that is huge (about 120 tables). I am trying to make some sense of it and it will help a great deal if I can search all 120 tables + columns for a string I am looking for.

Is that possible to do on a MySQL DB?

Answer 1

There is one solution, which might not be what you want. If you dumped the table into a file (mysqldump) with the data, then you would be able to grep any information you wanted out of it.

It would remove the need for time consuming search queries, and is the most efficient way I can think of.

Answer 2

This will help you to find a string in entire database

DELIMITER ##
 CREATE PROCEDURE sp_search1(IN searchstring INT)
BEGIN
 DECLARE done INT DEFAULT FALSE;
 DECLARE output TEXT;
  DECLARE table_name TEXT;
  DECLARE column_name TEXT;
DECLARE s TEXT;  
 DECLARE searchcursor CURSOR FOR 
SELECT table_name,column_name  FROM information_schema.columns AS column    
ORDER BY table_name,ordinal_position;   
OPEN searchcursor;
PREPARE stmt2 FROM 'select * from ? where ? = ?' ;  
search_loop : LOOP
IF done THEN 
LEAVE search_loop;
END IF;              
FETCH searchcursor INTO table_name,column_name;
IF(     EXECUTE stmt2 USING table_name, column_name,searchstring) THEN
 INSERT INTO `table_names`(`table_name`) VALUES(@table_name);    
    END IF;
END LOOP;   
END;

Answer 3

Just wanted to add on to Omnipresent's answer, which is the de facto way to search a db.

Unfortunately, 99% of the time, my db is huge and an average dump has few newlines, meaning grepping for the string I want returns the vast majority of the sql file.

I now prefer to use the --tab switch which makes a tab delimited txt file per table in a db.

This means not only do I get one record per line, but I can quickly get the table my search term is in.

Try this:

mysqldump -u user_name -p database_name --tab=tmp

Where tmp is an empty directory you've created.

An ls of tmp would give you something like this:

users.sql
users.txt
orders.sql
orders.txt

where the sql files contain the create table syntax, and the txt contain the data.

Note that the tab option utilizes mysql's SELECT INTO OUTFILE which means this trick cannot be done anywhere but localhost.

Answer 4

In unix machines, if the database is not too big:

mysqldump -u <username> -p <password> <database_name> --extended=FALSE | grep <String to search> | less -S

Answer 5

You could just iterate each table:

mysql="mysql -uUSER -pPASS -hHOST --protocol=tcp dbname -e"
for table in `$mysql "show tables;"`
do
  echo $table
  $mysql "select * from $table;" | grep STRING_TO_SEARCH_FOR
done