Full MySQL Search & Replace in All Databases and Tables

If you get into half of the database stuff that I regularly get into, you probably have a need to replace a specific string, such as a URL, very often – especially if you are cloning databases for use with WordPress. Sure, there are a lot of plugins or programs out there which do the same thing, but most of them avoid three-dimensional arrays and work on only one database – excluding that low level option of modifying all databases.

Because I couldn’t find anything worthwhile for what I was doing, I decided to make one myself.

This script is designed to be run by Php in the command line, but will also work in  a web browser if you replace all instances of PHP_EOL with HTML line breaks. To run this script the way that I wrote it, simply run it like this (assuming you saved the code below to a file named query-db.php):

php ./query-db.php

To make this work, change the credentials in the “Database Credentials” below, and change the strings in $search and $replace variables below.

<?php

/* Search & Replace All Databases + Fields + Columns
*
* Written by Kris Law for KASL Network
* October 21, 2012
*
* This script will search and replace specified strings from
* all columns from all tables from all databases.
*
* USE AT YOUR OWN RISK!
*/


// Database Credentials
$test_db='db.host.com';
$db_user='myDb_us3r';
$db_pass='myDb_p@ssw0rd';

// Search & Replace
$search = 'the quick brown fox jumped over the lazy dog'; // term to have replaced
$replace = 'the lazy dog was quicker and blocked the quick brown fox'; // string to put in place

// Open DB Connection
$link = mysql_connect($test_db, $db_user, $db_pass);

### Show list of databases
$query_string = "SHOW databases";
$query = mysql_query($query_string);

echo "Running Search & Replace on Databases. This will take a while..." . PHP_EOL . PHP_EOL;

while($array = mysql_fetch_array($query)){

$database = $array['Database'];
$q2 = mysql_query("show tables in $database");

if($database != 'information_schema' && $database != 'mysql' && $database != 'test'){

while($array2 = mysql_fetch_array($q2)){

$tables = $array2['Tables_in_'. $database];

$q3 = mysql_query("show columns in $database.$tables");

while($array3 = mysql_fetch_array($q3)){

$column = $array3['Field'];

$replace_query = "update $database.$tables set $column = replace($column, '$search','$replace')";
$replace = mysql_query($replace_query) or die(mysql_error());

echo "Searching $database.$tables->$column" . PHP_EOL;

}

}
}

}

echo PHP_EOL . "All instances of $search have been replaced with $replace!";

?>

One Thought on “Full MySQL Search & Replace in All Databases and Tables

  1. thank you . but you must change “$replace = mysql_query($replace_query) or die(mysql_error());” this place.

    $replace_at = mysql_query($replace_query) or die(mysql_error());

Post Navigation