One Liner: Check Cron Jobs for All Users on a *nix Server

If you are ever in a situation where you are asked to find out whether or not a server is being used for anything important, some of the first things you would want to check is whether or not there are active websites, databases and automated tasks. Automated tasks often times are run by the Cron daemon on behalf of a user on the server. If you would like to check the Crontab entries for all users on the server, you can run a one-line command as a super user and get a nice long list of who does and does not have a Crontab entry:

for user in $(cut -f1 -d: /etc/passwd); do crontab -u $user -l; done | grep -v '#'

The “grep -v” after the pipe simply ensures that no lines starting with a hash mark (#) are listed.

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

?>

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).

USER=kris
PASS=myPassword
HOST=database-server.mydomain.com

# Databases to exclude
DBEXCLUDE=db_name_i_dont_want

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/
exit
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 => 192.168.0.100

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

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

multisite.blog.com
*.multisite.blog.com

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

Done

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.

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

REGEX

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 ==

http://www.bees.com/articles/bees/bee-left-in-the-cold
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 ==

http://www.bees.com/honey_bees.html
Regex:     ^       /    (.*) \.html$
Variables:         /     $1   /
http://www.bees.com/honey_bees/
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:

redirection

== 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/

Get the real file or directory size in unix or linux

Size of DirectoriesAll BASH users (Linux, Unix, OSX, etc) use the ls command, but when we want to know how much disk space has been used, the ls command just doesn’t cut it sometimes. While it is a useful command for listing information about files in a directory, or the directory structure, it doesn’t give you the overall space that a directory uses – including the files inside of it. Sometimes you need a lot more information and the commands to do it are not commonly known. Here’s a typical output of the ls command:


user@localhost:~/Pictures$ ls -lh

 total 644M
 -rw------- 1 user user 993K Jul 12 15:08 IMAG0142.jpg
 -rw------- 1 user user 790K Jul 12 15:08 IMAG0143.jpg
 -rw------- 1 user user 1.1M Jul 12 15:08 IMAG0144.jpg
 -rw------- 1 user user 1.3M Jul 12 15:08 IMAG0145.jpg
 -rw------- 1 user user 1.1M Jul 12 15:08 IMAG0146.jpg
 -rw------- 1 user user 1.1M Jul 12 15:08 IMAG0147.jpg

Notice how the output shows the sizes of the files. However, if we cd .. and look at the directory itself, it shows this:

drwxr-xr-x 2 user user 24K Jul 21 12:02 Pictures

Although it is clearly stated above while in the Pictures directory that the content takes up 644 Megabytes of space, listing the directory itself only shows that it is 24 Kilobytes. That’s a little misleading, don’t you think?

In order to get around this issue, there is a different command that will do the trick; the du command.

user@localhost:~/Pictures$ du -sh
 686M .

The command as typed above shows the combined size of all directories in the present working directory. However, if you were to add a directory name to the command, you would have this output:

user@localhost:~/$ du -sh Pictures
 644M Cell

If you were to use a * instead of a directory name, you would retrieve the results of all of the directories in the current directory:

user@localhost:~/$ du -sh *
 644M Pictures
 2.6M Videos
 4.5M Wallpapers

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
:%s/^M//g

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

vi filename
:%s/^M/\r/g