I write a fair number of scripts on this site and have posted a lot of code over the years. Generally if I am not pasting the code to be viewed on the webpage then I link to a file that a user can download; which leads to a lot of mish-mash code that doesn’t have a home. I’ve always kept the code files in a private SVN repo over the years but have recently moved them all to BitBucket Git repositories. So here they are: lots of code samples and useful bits of programming to save time.

Generic Shell Scripts: https://bitbucket.org/themattreid/generic-bash-scripts/src
Generic Python Scripts: https://bitbucket.org/themattreid/generic-python-scripts/src
Generic MySQL Stored Procs: …

I was digging through some old project code and found this script. Sometimes one finds oneself in an odd situation and needs to convert regular SQL, say from a MySQL database dump, into SQLite3 format. There’s not too much else to say, but here is a script that helps with the process. It can likely be improved but this handles the items that came up during conversion on initial runs.

#!/bin/sh
####
# NAME: convert-mysql-to-sqlite3.sh
# AUTHOR: Matt Reid
# DATE: 2011-03-22
# LICENSE: BSD
####
if [ "x$1" == "x" ]; then
   echo "Usage: $0 "
   exit 
fi 
cat $1 |
grep -v ' KEY "' |   
grep -v ' UNIQUE KEY "' |
grep -v ' PRIMARY KEY ' |
sed '/^SET/d' |          
sed 's/ unsigned / /g' | 
sed 's/ auto_increment/ primary key autoincrement/g' |
sed 's/ smallint([0-9]*) / integer /g' | 
sed 's/ tinyint([0-9]*) / integer /g' |  
sed 's/ int([0-9]*) / integer /g' |      
sed 's/ character set [^ ]* / /g' |      
sed 's/ enum([^)]*) / varchar(255) /g' | 
sed 's/ on …

If you’ve ever been troubleshooting on the MySQL command line and needed to quickly see how much memory is being used then you’ve probably noticed that there are no built in commands to give you this data. Unlike other enterprise databases MySQL doesn’t have a very robust management system built in to help make the DBA’s life easier. It doesn’t come with built in Stored Procedures to report on usage statistics or generate handy reports; so we have to code them and import them to MySQL — no relying on Oracle to help us out here.

So, here’s a stored procedure that can be imported to MySQL and run whenever you need to see the memory usage statistics. Installation and usage info is built into the SP below. The SP can also be downloaded from the repo: https://bitbucket.org/themattreid/generic-sql-scripts/src/15c75632f1af/mysql-memory-report-storedproc.sql

##################################################################### …

I read somewhere that you should do extra watering of lawn (after sunset), if the temperature exceeded 80° F during the day.

So, a really quick hack works like this:

A bash script
#!/bin/bash

TEMP="`/usr/bin/mysql -ss open2300 -e \"SELECT FLOOR(MAX(temp_out)) FROM weather GROUP BY (rec_date) ORDER BY rec_date DESC LIMIT 1;\"`"
if [ "$TEMP" -ge 80 ]; then
/root/water-on.exp
/root/water2.exp 120 1
/root/water3.exp 120 1
fi

read more

SSH tunnels provide a very effective means to access remote services and applications. Not only does it provide encryption of data between hosts, but it allows you to route connections between a sequence of servers, thus chaining connections. A common use of this method is to provide encrypted connections to MySQL servers so that user accounts can be limited to only “localhost” privileges, yet accessed from remote workstations without having to run MySQL+SSL.

The concept is simple, for example let’s say you have three servers: localhost (your workstation in America), a server in Europe, and a server in Japan. You want to access Apache running on port 80 on the Japan server but because of firewall restrictions you cannot access port 80 remotely, and to make things more difficult the Japan server only allows SSH connections from the Europe server’s IP. We can solve this by creating a SSH tunnel that forwards localhost port 8080 …

Here are some quick and easy bash commands to solve every day problems I run into. Comment and leave some of your own if you like. I might update this post with new ones over time. These are just some common ones.

Iterate through directory listing and remove the file extension from each file
ls -1 | while read each; do new=`echo $each |sed 's/\(.*\)\..*/\1/'` && echo $new && mv "$each" "$new"; done

Output relevant process info, and nothing else
ps axo "user,pid,ppid,%cpu,%mem,tty,stime,state,command"| grep -v "grep" | grep $your-string-here

Setup a SOCKS5 proxy on localhost port 5050, to tunnel all traffic through a destination server
ssh -N -D 5050 username@destination_server'

Setup a SOCKS5 proxy via a remote TOR connection, using local port 5050 and remote TOR port 9050
ssh -L 5050:127.0.0.1:9050 username@destination_server'

Display text or code file contents to screen but don't display any # comment lines
sed -e '/^#/d' $1 < $file_name_here …

Why This Post
While testing Yoshinori Matsunobo's MHA agent I found that although the wiki has a very complete documentation, it was missing a some details. This article intends to close that gap and bring up some issues to keep in mind when you do your own installation. At the end of the article I added a Conclusions section, if you're not interested in the implementation details, but to read my take on the project, feel free to jump straight to the end from here.

My Test Case
Most of our production environments can be simplified to match the MHA's agent most simple use case: 1 master w/ 2 or more slaves and at least one more slave in an additional tier:

Master A --> …

It seems that everyone loves load testing these days. Problem is that everyone is using their own quick scripts, simple or complex, to drive their tests without the ability for other DBAs to duplicate those tests. Let’s say I write a great test and share my results and graphs on the blog – you want to run the same tests to see how your new DB servers compare in performance: this framework allows you to do that without duplicating any work or writing code. This is a basic release that will get the ball rolling. I’ve included some sample tests in the README file, so give them a try.

This codebase offers a user friendly framework for creating and visualizing MySQL database load test jobs. It is based around Sysbench, which is generally considered the industry standard load test application. The framework allows you to do the following:

• standardize your tests without requiring you to write one-off bash scripts to handle …

I’ve always been a big fan of having a customized .bashrc file. The one I distribute to all of my servers has aliases for quick commands to save me time on the command line, functions that get work done when aliases are too simplistic, reporting for the server for each cli login, and of course a formatted and colored prompt (for terms that support colors). I also change certain aspects and commands based on the operating system since I’m not always on a redhat box or linux at all. Here’s my bashrc file – maybe you have some fun additions that you’d like to share. What saves you time on the command line?

I’ve been playing around with some quick system automation scripts that are handy to use when you don’t want / need to setup a chef or puppet action. I like to keep all of my hostnames and login details in a MySQL database (a cmdb actually) but for this example we’ll just use a couple of nested lists. This script executes commands in parallel across the hosts you choose in the menu system via the “pdsh” command, so make sure you have that installed before running. Alternately you can change the command call to use ssh instead of pdsh for a serialized execution, but that’s not as fun or fast. With some customizations here and there you can expand this to operate parallelized jobs for simplifying daily work in database administration, usage reporting, log file parsing, or other system automation as you see fit. Here’s the code. Comments welcome as always!

#!/usr/bin/env python
## NAME: menu_parallel_execution.py
## DATE: …