Ben Lobaugh Online

I think, therefore I am. I am, therefore I sail

Category: MySQL Page 2 of 4

MySQL who: See who is connected to your MySQL server with this script

Need to determine who is connected to your MySQL server and how many connections they have open? I tossed together this little script to display the information.

#!/usr/bin/perl

use DBI;
use 5.010;
use Getopt::Long;

# Set defaults
my $host = 'localhost';
my $db = 'information_schema';
my $user = ''; 
my $pass = ''; 

# Get commandline options
GetOptions (
    "host=s"    => \$host,
    "db=s"      => \$db,
    "user=s"    => \$user,
    "pass=s"    => \$pass,
) or die ( "Valid options\n--host\n--user\n--password\n--db database" );

# Create database connection
my $dbh = DBI->connect( "DBI:mysql:$db:$host", $user, $pass, {'PrintError'=>0} )
        or die "** Connection error!\nTry different options:\n--host\n--user\n--password\n--db database\n\n $DBI::         +++errstr\n";

# Get the current processes to check user
my $sql = "show processlist";
my $sth = $dbh->prepare($sql);
$sth->execute() or die "SQL error: $DBI::errstr\n";

# Create an array of all the users
my %user;
while (my @row = $sth->fetchrow_array()) {
    $user{$row[1]}++;
}

# Display User details
print "MySQL - Logged in users\n";
print "=" x 15, "\n";

for my $x (keys %user) {
    print "$x - $user{$x} connections\n";
}


print "\n";

Script to convert MySQL collation from utf8mb4 to utf8

I ran into a strange situation with WordPress where the MySQL server did not support utf8mb4. I needed to quickly convert the tables back to utf8 to get the site running again.

Though this script does work I highly recommend if you are running WordPress that you upgrade your MySQL server to support utf8mb4 for security. WordPress made this change to fix a security vulnerability introduced by The Trojan Emoji.

DB="your_database_name"
USER="your_db_user"
PASS="your_db_pass"
(
    echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;'
    mysql -p$PASS -u $USER "$DB" -e "SHOW TABLES" --batch --skip-column-names \
    | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;'
) \
| mysql -p$PASS -u $USER "$DB"

Script to convert MySQL from InnoDB to MyISAM

If you need to convert all the MySQL tables in a database from InnoDB to MyISAM here is a handy script to automate the process for you.

#!/bin/bash

DB='your_database_name'
USER='your_db_user'
PASS='your_db_pass'

TABLES=$(mysql -p$PASS -u $USER --skip-column-names -B -D $DB -e 'show tables')
for T in $TABLES
do
    mysql -p$PASS -u $USER -D $DB -e "ALTER TABLE $T ENGINE=MYISAM"
done

List column names in a MySQL table

This will allow you to quickly and easily select a list of column names from a MySQL table.

SELECT distinct column_name
FROM information_schema.columns
WHERE table_name='{YOUR TABLE NAME}'

First Experiences with WebMatrix 2 and Windows Azure Website

WebMatrix is a free web development tool produced by Microsoft. WebMatrix is a complete development environment that not only provides a code editor, it also creates a new private instance of IIS Express to debug your project in, and downloads any dependencies your project may need, I.E. SQL Server Express, MySQL, PHP, etc.

I was asked what I thought of WebMatrix, however I have not spent a lot of time with it. I was working on a project that utilizes Windows Azure Web Sites and the App Gallery install of phpBB with another fellow. He was handling all the coding while I was administering the site through the web interface. I am now also coding for the site so I figured this is the perfect time to take WebMatrix for a spin..

Right off the bat I can tell you that if you are using Windows Azure Web Sites WebMatrix is a nice go to tool. WebMatrix is integrated with the Windows Azure Portal. Select your site from the Portal and there is a “WEBMATRIX” button on the toolbar. I did not have WebMatrix installed prior to starting this process. One-click of the button and WebMatrix was installed for me via Web Platform Installer.

Windows Azure Portal toolbar

Clicking the “WEBMATRIX” button automatically downloads the publish settings and sets up the WebMatrix environment for your project. Because WebMatrix understands phpBB it even pulled the PHP and SQL Server dependencies! The files and database were copied to my local machine. I am now able to interact with the phpBB web site through WebMatrix as an exact replica of the production website.

WebMatrix 2

WebMatrix even has a nifty tool that alerts you if you attempt to make changes to project core files.

WebMatrix 2 Alert

I made a few changes and clicked Run. My default browser opened and I was able to poke around the website as if it was running live. Satisfied that all seemed to be in working order, I clicked the Publish button a few minutes later. WebMatrix did a comparison of the files on the server to the local files to let me know which had changed. I could selectively choose which files to publish and which to retain. Even cooler, WebMatrix has an option to push back the entire database.

WebMatrix also supports code insight, or “Intellisense”, which is handy for finding class and function documentation throughout a project.

What is my verdict on WebMatrix? It seems to have matured into a great tool that is highly useful for web developers. Using Windows Azure Web Sites, or the WebMatrix Gallery you can, with a few clicks, have one of many popular open source content management systems (such as WordPress, Drupal, Joomla!) setup and running. The Publish feature eliminates the need for any additional applications to send files to the server. Additionally the automatic check of the server for changed files is very handy at ensuring only the proper files get uploaded or changed. Drawbacks? It currently only supports the Windows operating system.

Moving forwards I have a feeling WebMatrix will become my web code editor of choice, especially when working on Windows Azure related projects.

Page 2 of 4