Search for all occurrences of a string in a mysql database

Question

I'm trying to figure out how to locate all occurrences of a url in a database. I want to search all tables and all fields. But I have no idea where to start or if it's even possible.

Answer 1

A simple solution would be doing something like this:

mysqldump -u myuser --no-create-info --extended-insert=FALSE databasename | grep -i "<search string>"

Answer 2

In phpMyAdmin a 'Search' feature is available:

1. Select particular database not table.
2. Click 'Search' tab
3. Enter the search term you want
4. Select the tables you want to search in

phpMyAdmin screen shot:

The 'Search' feature is also available in MySQL Workbench:

1. Database Menu > Search Table Data
2. Select database and tables you want to search (it will search in selected tables only)
3. In search you can use wildChars.

MySQL Workbench screen shot:

Answer 3

Old post I know, but for others that find this via Google like I did, if you have phpmyadmin installed, it has a global search feature.

Answer 4

Using the MySQL Workbench, you can search for a string from the "Database" -> "Search Table Data" menu option.

Specify LIKE %URL_TO_SEARCH% and on the left side select all the tables you want to search through. You can use "Cntrl + A" to select the whole tree on the left, and then deselect the objects you don't care about.

Answer 5

You can do this by using HeidiSQL without generating Db dumps

Steps:

1) Select the database you need to search in from the left panel of GUI.

2) Export > Export Database as SQL

3) In Table Tools window select "FIND TEXT" tab.

4) Provide your string to search and click "FIND".

5) It will list all the tables contains our string.

6) Select the row with higher relevance %.

7) Click "SEE RESULTS"

Answer 6

I was looking for this myself when we changed domain on our Wordpress website. It can't be done without some programming so this is what I did.

<?php  
  header("Content-Type: text/plain");

  $host = "localhost";
  $username = "root";
  $password = "";
  $database = "mydatabase";
  $string_to_replace  = 'old.example.com';
  $new_string = 'new.example.com';

  // Connect to database server
  mysql_connect($host, $username, $password);

  // Select database
  mysql_select_db($database);

  // List all tables in database
  $sql = "SHOW TABLES FROM ".$database;
  $tables_result = mysql_query($sql);

  if (!$tables_result) {
    echo "Database error, could not list tables\nMySQL error: " . mysql_error();
    exit;
  }

  echo "In these fields '$string_to_replace' have been replaced with '$new_string'\n\n";
  while ($table = mysql_fetch_row($tables_result)) {
    echo "Table: {$table[0]}\n";
    $fields_result = mysql_query("SHOW COLUMNS FROM ".$table[0]);
    if (!$fields_result) {
      echo 'Could not run query: ' . mysql_error();
      exit;
    }
    if (mysql_num_rows($fields_result) > 0) {
      while ($field = mysql_fetch_assoc($fields_result)) {
        if (stripos($field['Type'], "VARCHAR") !== false || stripos($field['Type'], "TEXT") !== false) {
          echo "  ".$field['Field']."\n";
          $sql = "UPDATE ".$table[0]." SET ".$field['Field']." = replace(".$field['Field'].", '$string_to_replace', '$new_string')";
          mysql_query($sql);
        }
      }
      echo "\n";
    }
  }

  mysql_free_result($tables_result);  
?>

Hope it helps anyone who's stumbling into this problem in the future :)

Answer 7

Found a way with two (2) easy codes here. First do a mysqldump:

mysqldump -uUSERNAME -p DATABASE_NAME > database-dump.sql

then grep the sqldump file:

grep -i "Search string" database-dump.sql

It possible also to find/replace and re-import back to the database.

Answer 8

SQLyog is GUI based solution to the problem of data search across all columns, tables and databases. One can customize search restricting it to field, table and databases.

In its Data Search feature one can search for strings just like one uses Google.

Answer 9

MikeW presented an interesting solution, but as mentioned in comments, it's a SQL Server solution not a MySQL solution. Here is a MySQL solution:

use information_schema;
set @q = 'Boston';
set @t = 'my_db';

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

Answer 10

If you can use a bash - here is a script: It needs a user dbread with pass dbread on the database.

#!/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

If anyone wants an explanation: http://infofreund.de/?p=1670

Answer 11

I can't remember where I came across this script, but I've been using it with XCloner to move my WP multisites.

<?php

    // Setup the associative array for replacing the old string with new string
    $replace_array = array( 'FIND' => 'REPLACE', 'FIND' => 'REPLACE');

    $mysql_link = mysql_connect( 'localhost', 'USERNAME', 'PASSWORD' );
    if( ! $mysql_link) {
        die( 'Could not connect: ' . mysql_error() );
    }

    $mysql_db = mysql_select_db( 'DATABASE', $mysql_link );
    if(! $mysql_db ) {
        die( 'Can\'t select database: ' . mysql_error() );
    }

    // Traverse all tables
    $tables_query = 'SHOW TABLES';
    $tables_result = mysql_query( $tables_query );
    while( $tables_rows = mysql_fetch_row( $tables_result ) ) {
        foreach( $tables_rows as $table ) {

            // Traverse all columns
            $columns_query = 'SHOW COLUMNS FROM ' . $table;
            $columns_result = mysql_query( $columns_query );
            while( $columns_row = mysql_fetch_assoc( $columns_result ) ) {

                $column = $columns_row['Field'];
                $type = $columns_row['Type'];

                // Process only text-based columns
                if( strpos( $type, 'char' ) !== false || strpos( $type, 'text' ) !== false ) {
                    // Process all replacements for the specific column                    
                    foreach( $replace_array as $old_string => $new_string ) {
                        $replace_query = 'UPDATE ' . $table . 
                            ' SET ' .  $column . ' = REPLACE(' . $column . 
                            ', \'' . $old_string . '\', \'' . $new_string . '\')';
                        mysql_query( $replace_query );
                    }
                }
            }
        }
    }

    mysql_free_result( $columns_result );
    mysql_free_result( $tables_result );
    mysql_close( $mysql_link );

    echo 'Done!';

?>

Answer 12

The first 30 seconds of this video shows how to use the global search feature of Phpmyadmin and it works. it will search every table for a string.

http://www.vodahost.com/vodatalk/phpmyadmin-setup/62422-search-database-phpmyadmin.html

Answer 13

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 14

I was looking for the same but couldn't find it, so I make a small script in PHP, you can find it at: http://tequilaphp.wordpress.com/2010/07/05/searching-strings-in-a-database-and-files/

Good luck! (I remove some private code, let me know if I didn't break it in the process :D)

Answer 15

Scott gives a good example of how to do it, but the question is why would you want to? If you need to do a find-and-replace on a specific string, you could also try doing a mysqldump of your database, do a find-and-replace in an editor, then re-load the database.

Maybe if you gave some background on what you are trying to achieve, others might be able to provide better answers.

Answer 16

Not an elegant solution, but you could achieve it with a nested looping structure

// select tables from database and store in an array

// loop through the array
foreach table in database
{
    // select columns in the table and store in an array

    // loop through the array
    foreach column in table
    {
        // select * from table where column = url
    }
}

You could probably speed this up by checking which columns contain strings while building your column array, and also by combining all the columns per table in one giant, comma-separated WHERE clause.