Mysql search for a string in all tables

Hello together,

today i want to show you how to search for a string in all tables of a mysql-database.

Requirements:

mysql
bash

Solution:

First the script for the mysql search.:

#!/bin/bash
IFS='
'
DBUSER=dbread
DBPASS=dbread
echo -n "Which database do you want to search in (press 0 to see all databases): "
read DB
echo -n "Which string do you want to search: "
read SEARCHSTRING
for i in `mysql $DB -u$DBUSER -p$DBPASS -e "show tables" | grep -v \`mysql $DB -u$DBUSER -p$DBPASS -e "show tables" | head -1\„
do
for k in `mysql $DB -u$DBUSER -p$DBPASS -e "desc $i" | grep -v \`mysql $DB -u$DBUSER -p$DBPASS -e "desc $i" | head -1\` | grep -v int | awk '{print $1}'`
do
if [ `mysql $DB -u$DBUSER -p$DBPASS -e „Select * from $i where $k=’$SEARCHSTRING'“ | wc -l` -gt 1 ]
then
echo " Your searchstring was found in table $i, column $k"
fi
done
done

 

Explanation:

The first part is to setup the Database you want to connect and the string you want looking for.

echo -n "Which database do you want to search in: "
read DB
echo -n "Which string do you want to search: "
read SEARCHSTRING

For the second part, we have to create a double for-loop.

The first loop select all tables in  the database.

for i in `mysql $DB -u$DBUSER -p$DBPASS -e "show tables" | grep -v \`mysql $DB -u$DBUSER -p$DBPASS -e "show tables" | head -1\„

We have to remove the header with the command head -1

Now we want to search and list every field in this table

for k in `mysql $DB -u$DBUSER -p$DBPASS -e "desc $i" | grep -v \`mysql $DB -u$DBUSER -p$DBPASS -e "desc $i" | head -1\` | grep -v int | awk '{print $1}'`

Again we remove the header, and we only want the name of the field – that's why we use awk '{print $1}' and the fields of the table who haven't a type of integer (long int, short int..,)

 

Then we select the given string on this column. If the result is greater or equal 1, then the string was found in this table / column and you will get an output for the search.

if [ `mysql $DB -u$DBUSER -p$DBPASS -e „Select * from $i where $k=’$SEARCHSTRING'“ | wc -l` -gt 1 ]
then
echo " Your searchstring was found in table $i, column $k"
fi
done
done

That' all

Output:

mysql-search-output

Here you can download the script. After downloading and extracting the zip, set the permissions to executable (chmod +x) and then you can start it with.

./search_my_db

Download shell script to search all tables in a mysql database

Bye
Chris

 

 

Kommentare sind abgeschaltet.