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.


/* 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.

// Database Credentials

// 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!";


How to Exclude Specific Databases from an –all-databases MySQL Dump

There may come a time where you need to make a complete database dump on your MySQL server, yet for whatever reason, you need to exclude databases. In my case, I needed to exclude a certain database so that when I combined multiple databases into one, another database’s tables didn’t get overwritten during the conversion.

If you’re familiar with the grep command and piping, you can exclude as many databases as you want using grep’s -v option.

To keep this simple, here’s a script which you can use (modify to fit your needs) to make this work (by default, I have it skipping the main MySQL, Information Schema, and Test databases).


# Databases to exclude

MYSQL=$(mysql -N -u${USER} -p${PASS} -h${HOST} <<<"SHOW DATABASES" | grep -v ${DBEXCLUDE} | grep -v mysql | grep -v information_schema | grep -v test | tr "\n" " ")

mysqldump -v -u${USER} -p${PASS} -h${HOST} --databases  --skip-lock-tables ${MYSQL} > DB-DUMP.sql

Convert WordPress Multi-DB back to Single DB

If you have used the WPMU Multi-DB plugin to fragment your databases from a single instance to 16 or more databases, there may come a time where you need to merge all of those databases back into one. In my case, I want to be able to use Backup Buddy to backup and migrate WordPress blogs. Here are the steps on how to accomplish this massive change. Keep in mind, this is a tutorial  which is written for informational purposes only. I am not responsible for anything that goes wrong.

Create a new database named myDatabase:

mysql> create database myDatabase;

Make a MySQL dump of your existing multisite database:

mysqldump -v -u wpadm -h myDatabaseName.cygwiniuiold.us-east-1.rds.amazonaws.com -pP@ssw0rd --all-databases --skip-lock-tables > WP_DUMP.sql

Next, strip out the MySQL dump lines in the .sql file which tell it to use different databases:

egrep -v "^USE" WP_DUMP.sql > WP2.sql

Next, strip out the MySQL dump lines in the .sql file which tell it to create databases:

egrep -v "^CREATE DATABASE" WP2.sql > WP3.sql

Change what database is currently “used” when it is being imported:

sed -i 's/-- Current Database: `WP_db_00`/use myDatabase;/g' WP3.sql

Import the MySQL dump into the newly created database from step one:

mysql -v -u root -pMyPassword myDatabase < WP3.sql

Last, but not least, remove all files which came with the Multi-DB plugin (db.php and db-conf.php)

* Keep in mind that I spent a lot of time scratching my head over why the sites wouldn’t show up in the Network Admin > Sites list. After all kinds of looking around, I found out that my main global database had been overwritten by another database in the –all-databases array which had the same tables.

How to Move or Clone a Single Site or Multi Site WordPress Installation

If you ever have the need to move a WordPress site or a network of WordPress sites to a new server, it can be an outright frustrating task due to all of the settings and data that get accumulated over time. This article is written to be a quick reference for this seemingly daunting task. Eventually I may turn this article into a script to automate the process [EDIT: I have done this successfully] – and/or maybe even a WordPress plugin. If you think this would be useful, feel free to let me know.

Follow through these steps and you might have WordPress up and running on a different server in 30 minutes depending on the size of your data.

Back Up Database

In order to move your database to the new server, you’ll want to do a database dump. A database dump is a file where all of the information from a database is stored into multiple queries. When you import the dump file into the new database server, these queries are executed one by one until the database is built.

Dump the database

mysqldump -v -u root -h database.blog.com --database my_wp_db --skip-lock-tables > my_wp_db.sql

Copy the database dump to the new web server

Because this article is not intended to help you actually set up a web server, it is expected that you have already configured the new database server to allow access from the new web server. After the database dump is complete, you should see a file called my_wp_db.sql in your current directory. Copy the .sql file to the new WordPress server.

scp -i ~/.ssh/your_private_key ./my_wp_db.sql root@my_new_server.com:~/

Change the domain name in the MySQL dump file

Using sed, you can execute one command and it will modify a whole entire file without even opening it.

sed -i 's/my_old_server.com/my_new_server.com/g' ./my_wp_db.sql

Import the database dump file into the new database server

SSH into the new web server

ssh -i ~/.ssh/your_private_key root@my_new_server.com

Import the database dump into the new database server

mysql -v -u root -h new.database.blog.com -p < ./my_wp_db.sql

Copy WordPress Files

The easiest way to make a clone is to do a direct copy of the whole WordPress file system (rather than downloading a new WordPress installation and installing it from scratch).

I keep my WordPress installation in /var/www/ on the server, so here’s what I did to move the files to the new server (as the root user):

tar -czvf ~/wordpress.tar.gz /var/www
scp -i ~/.ssh/your_private_key ~/wordpress.tar.gz multisite.blog.com:/var/www/
ssh -i ~/.ssh/your_private_key root@multisite.blog.com
cd /var/www
tar -xzvf ./wordpress.tar.gz
mv ./www/* ./

File Modifications:

Open wp-config.php and change the following lines to match the database credentials for the new database server:

/** MySQL database */
define('DB_NAME', 'my_wp_db');

/** MySQL database username */
define('DB_USER', 'my_database_username');

/** MySQL database password */
define('DB_PASSWORD', 'my_database_password');

/** MySQL hostname */
define('DB_HOST', 'database2.server.url');

Scroll down through the file and find this line a bit further down…

/** Domain Name */
define( 'DOMAIN_CURRENT_SITE', 'multisite.blog.com' );

Plugin Modifications

If you have domain mapping enabled on the production server, but are only testing on the new server, you will probably want to disable any domain mapping. You will know if you have domain mapping if a CNAME’d mywordpress-site.com brings up mywordpress-site.multisite.blog.com.

DNS Modifications

In order for WordPress to serve any sub-domains in the multi site instllation, you will need to add a couple of DNS records so that they point to the new WordPress server. In this case…

domain = blog.com

// create a DNS "A" record called "multisite" and point it to the IP address of the server
[A] multisite =>

// create a wildcard DNS "A" record with an asterisk, a dot, and multisite
[A] *.multisite =>

In the end, you will have two DNS A records:


Apache Wildcard Host

In your Apache host configuration file (usually /etc/apache2/sites-available/000-default (or sitename.conf) or at the bottom of /etc/apache2/httpd.conf or /etc/apache2/apache.conf, you’ll want to make sure that you have the following:

ServerName mulsite.blog.com
ServerAlias *.multisite.blog.com

The above lines tell apache to respond to anything.multisite.blog.com


Now that you have made it through these steps, try out your new WordPress site. If you notice that I missed anything, or if I did something that doesn’t look right, please make a comment below and let me know.

Install Homebrew & Ruby on Mac OSX Lion

The purpose of this article is to help you configure Mac OSX so that it has a package manager (similar to apt-get, YUM, Pacman or Portage on Linux distributions), and to update you to the latest version of Ruby (1.9.3 at the time of this writing). Currently, Mac OSX Lion 10.7.4 has Ruby version 1.8.7 which is slightly out of date.

Installing Homebrew

Homebrew is a very easy to use package management system. To view more information on Homebrew, please visit the Homebrew home page.

Enter the following command to install Homebrew – I took this directly from the Homebrew installation page:

/usr/bin/ruby -e "$(/usr/bin/curl -fsSL https://raw.github.com/mxcl/homebrew/master/Library/Contributions/install_homebrew.rb)"

It will take anywhere from 5 to 10 minutes for Homebrew to install.

After Homebrew is installed, you can install all kinds of packages from the command line. For example, if you would like to install Nmap:

brew install nmap

To update your brew installed packages:

brew update --system

For other brew commands, type this command:

brew help

Installing Ruby

Once Homebrew is installed, you can install Ruby using the brew command:

brew install ruby

Ruby will take another 5 to 10 minutes to install.

In order to use the version of Ruby which was installed by Homebrew (instead of using the default version 1.8.6), you can modify your /etc/paths file by moving /usr/local/bin to the top of the list (above /usr/bin).

Reboot your machine and the changes will be applied.

After you log back in, you can check what version of Ruby you are using by running the following command:

ruby -v

Installing Ruby Gems

Now that Ruby is installed, you can install whatever Ruby Gems that you need. For example, if you wish to install the mechanize gem, run this command:

sudo gem install mechanize

Importing Images Into WordPress from SquareSpace

This article is a continuation of a previous article that I wrote titled Migrating Blog Content and Assets from SquareSpace to WordPress. That article showed you how to actually get the content of the blogs from SquareSpace into WordPress. This article show you how to get the files and images.

Install Firefox and the DownThemAll Extension

There is an extension for the Firefox Browser called DownThemAll. This extension allows you to batch download files which are linked to in a web page. Install the DownThemAll extension – this will be the quickest way to get this job done. You can manually grab all the files and migrate them too, but if you have a lot of files, I don’t recommend it.

Get all images and files

Before you start, keep in mind that DownThemAll does not enter directories and take images from inside them. It only takes files that are in the current page. Not only does it take images, but if you tell it to, it will download every link on the page, including web pages. Because I wanted this job to be done fast and was not worried about taking 10 Megabytes of disk space, I told it to download everything. After that, I entered a sub directory on the page in the list, and downloaded those images one-by-one since there were only a few in each directory.

  • Open Firefox (and make sure the DownThemAll extension is in installed)
  • Sign into the backend of your SquareSpace site at http://yoursite.squarespace.com/display/Login
  • Click on the “Website Management” tab
  • Click on “File Storage” under the Data and Media category
  • Right-click your mouse in an empty part of the page and select DownThemAll

  • Set the path where you want the files to be downloaded (I created a directory on my desktop and named it website.com)
  • Create a directory inside the website.com directory called “storage”
  • Check all of the boxes in the DownThemAll window and click “Start”

  • Inside of your “storage” directory, create a sub-directory with the same name as any sub-directories in your SquareSpace file list (if applicable)
  • Enter each sub-directory in the web page and download those files all to the sub-directory you created in the storage directory

Now that all of your assets are downloaded and the directory structure is the same as it was on the SquareSpace site, you can copy the whole storage directory to the root of your WordPress installation (or the root of the multi-site installation where WordPress treats a directory as the root of an individual site; in my case, each migrated site has it’s own root at /var/www/migrated-content/website.com/)

Here’s the command I used to put the storage directory on the web server:

scp -r -i ~/.ssh/my_private_key ~/Desktop/website.com/storage root@website.com:/var/www/migrated/content/website.com/

Migrating Blog Content and Assets from SquareSpace to WordPress

Rather than explaining why anybody would want to migrate content from SquareSpace to WordPress, I thought it would be more efficient to just dig in. So, in order to migrate content and assets from SquareSpace to WordPress, you will need to follow these steps:

Configure WordPress for importing data from SquareSpace

Before you do anything, you will want to make sure that WordPress is configured to import data from SquareSpace. The import is done using the Movable Type and TypePad Importer plugin.

  • First, you will need to install the Movable Type and TypePad Importer plugin from http://wordpress.org/extend/plugins/movabletype-importer/ (you can also search for it in your plugins dashboard).
  • Next, activate the plugin either network wide (for multi-site WP installations, or for the site in which you are importing the SquareSpace content to)
  • Your WordPress blog is now configured to import from SquareSpace.

Export the data from SquareSpace

Next, navigate to http://yoursite.squarespace.com/display/Login and sign in so that you can perform administrative actions. Once you are in, follow these instructions:

  • Go to the home page of your SquareSpace site at http://yoursite.squarespace.com
  • At the top-right of the page, click on “structure”



  • Next, click on “configure this page” which is just above the title of the post

  •  Under “Journal Page Configuration” (the black slide-in that comes in on the right-half of the page), scroll all the way to the bottom and, under Data Export, click the “Export Blog Data” button.

  •  Confirm the data export when prompted and download the file to your Desktop.
  • The blog posts are now exported from SquareSpace.

Importing the SquareSpace content into WordPress

Now that you have exported the post content from SquareSpace, the Moveable Type and Typepad plugin is enabled, you are ready to import the content into WordPress. Follow these steps:

  •  Back to your WordPress site, navigate to the WordPress Dashboard for that site and go to tools > import.
  • Click on “Moveable Type and Typepad”
  • Browse for the file you exported from SquareSpace and upload it

  • That’s it! Your content should now be imported and, if you’re lucky like I was, all you will need to do now is import your images.

Importing Images

To continue with importing images, see Importing Images Into WordPress from SquareSpace.

RegEx for Redirection: A Reference Guide

Regular Expressions are very handy and save you a lot of time when you have the need to manipulate a lot of data in a little bit of time. When you have


multiplestrings where parts of them are the same, they are considered a pattern. Regular expressions can be built to match those patterns and manipulate them in many ways.

Meta Characters

A metacharacter is a character that has a special meaning (instead of a literal meaning) to a computer program, such as a shell interpreter or a regular expression engine. — http://en.wikipedia.org/wiki/Meta_Character. For a full list of meta characters, visit php.net.

Examples of Meta Characters:

^     The root of the domain - only what is on the right of this will be matched.
.     (period) Match anything
\     First part used with a meta character (see examples below)
\d    Match only digits
\D    Match only non-digits
\w    Match only words
\W    Match any non-words
\s    Match any white space
\S    Match any non-white space
*     Match the character to the left and match it 0 or more times.
+     Match the character to the left one or more times

*Note: If there is no d, w, s (or other meta character) then the character proceeding the \ is treated literally. You can also escape a backslash; \d tells regex to treat it as a digit, however \\d tells regex to actually look for a \d (literally as a backslash and d).

As another example, if you have a directory with spaces in the name such as “directory name,” you would need to escape the space (escape means put \ in front of something) as follows:

directory\ name == “directory name”

Regular Expression Examples

== Strip “articles” out of the URL ==

Regex:        ^    /articles/(.*)/       (.*)
Variables:         /          $1 /        $2
http://www.bees.com/        /bees/bee-left-in-the-cold
Resulting URL: http://www.bees.com/bees-abuse-1/bee-left-in-the-cold

== Strip out the .html from http://www.bees.com/filename.html ==

Regex:     ^       /    (.*) \.html$
Variables:         /     $1   /
Resulting URL: http://www.bees.com/honey_bees/

== The difference between / and not / ==

/title-of-post is not equal to /title-of-post/, however /title-of-post/*$ is equal to both of them.
If you are redirecting http://www.bees.com/honey-bee-shortage to http://www.bees.com/honey-bee-population-comeback, this is how you would do it in the redirection plugin:


== Detecting Digits ==

As you saw on the first page in the meta characters section, Regex can match digits.

http://bees.com/   2012   /     06     /   regex-is-amazingly-powerful   /
Regex:     ^   /   (\d*?) /   (\d*?)   /             (.*)                /    *$
Variables:     /     $1   /     $2     /              $3                 /

The output of the above would be just what the input is because I didn’t change anything – I just wrote down what everything was. However, if I wanted to display the URL above as http://bees.com/regex-is-amazingly-powerful/06/2012, here’s how I would do it:

http://bees.com/     $3   /    $2      /              $1                 /    *$

Here’s what it would look like in the redirection plugin:

== Strip out part of a word ==

Occasionally you may have a WordPress category such as types-of-honey-1. Suppose that the -1 shouldn’t be in the category – the category is actually supposed to be types-of-honey. Now, suppose there are 1,241 articles in the types-of-honey-1 category now, yet all the links point to types-of-honey. This would be a nightmare without Regex.

Stripping out the -1 is very simple:

http://www.bees.com        /    types-of-honey-1    /    clover-honey/
Regex:     ^               /        (.*)-1          /    clover-honey/
Variable:                  /         $1             /    clover-honey/
Result: http://www.bees.com/    types-of-honey      /    clover-honey/

Setting Up the Eclipse IDE With PHP

For Php developers, there are two options for setting up the Eclipse IDE. You can install the Php only version which is made specifically for those who use Php and only Php, or you can install the classic version and add Php into it. Systems Admins will want to install the classic version and add Php to it because of other tools that may be needed such as Ant. Ant is a Java program for building automated scripts for deployments, etc. There is another program which is based on Ant, but built with Php called Phing. If you use Phing, you do not need Ant. However, if you want to be able to launch Phing scripts from the Eclipse IDE, you can do it by using Ant to execute Phing which would execute Phing scripts in Eclipse’s built in console.Eclipse can be obtained from http://www.eclipse.org/downloads/. If you using a Mac, but don’t know if you need the 32-bit or 64-bit version, open up the Terminal.app and run the following command:

uname -a

After running that command, you should see an output similar to the following:

Darwin LB.local 10.8.0 Darwin Kernel Version 10.8.0: Tue Jun 7 16:33:36 PDT 2011; root:xnu-1504.15.3~1/RELEASE_I386 i386

The very last “word” on the block above is i386. i386 means you have a 32-bit computer. This means you need the 32-bit version of Eclipse. If you see something different, such as x64 or x86-64 then that means you need the 64-bit version of Eclipse because your computer is running 64-bit software.

After you have downloaded and installed Eclipse (we are assuming you installed the classic version), you will then need to add Php tools to the IDE. You can do this like so:

  • Open Eclipse (start the program)
  • Click on Help > Install New Software
  • Where it says “Work with:”, select the dropdown menu (next to the “Add” button)
  • Select “All Available Sites”
  • Install the following modules and you should be set up:
    • Eclipse Platform
    • Eclipse RCP Target Components
    • Eclipse SDK
    • Under “Programming Languages,” select Php Development Tools and any other languages that you might need.
    • Under “Web, XML, Java EE…” select Php Development Tools…
Accept agreements as needed if you agree with them, and press next and let it work until it is finished. After it is finished, press the finished button and then restart the Eclipse IDE.

Remove DOS EOL ^M Character from File

endoflineIf you have the ^M character in your file, it is a Dos/Windows end of line character that is tough to get rid of. You could always ignore it, but the possibility of it corrupting a file is very high. Some editors such as Nano don’t even seem to show this character by default – maybe Nano strips it out – I’m not sure at this point.

If you are a vi warrior like I am, then you don’t really care for using Nano anyway. To remove the DOS EOL character using the vi editor, the easiest way is by following these steps:

vi filename

Press “:” to get to command mode.

Search and Replace all Globally is :%s/^M//g (Press control V then control M) which will replace ^M with nothing.

Then to write and quit enter “:wq”

In summary, here’s the command would look in vi:

vi filename

If you need the character to actually turn into line breaks, try this:

vi filename