CryptR, a quick and fun bash script to handle AES-256 encryption

If you’ve ever wanted an easy way to encrypt and decrypt files on the command line without having to memorize or look up OpenSSL commands, then here’s a quick and easy script that provides that functionality. This script also functions as a reference to using getopt to process command line arguments. It should be noted that this has been functionally tested on Linux and OSX.

# Date: 2011-07-29, update: 2012-04-23
# Author: Matt Reid
# Function: Decrypts and Encrypts files

function generate_digests() {    
    echo "  Input file: $filein"
    openssl dgst -md5 $1
    echo "  Output file: $fileout"
    openssl dgst -md5 $2

function header() {
    echo " -------------------------------- "
    echo "|CryptR | security for the masses|"
    echo " -------------------------------- "
    echo "m.reid 2012.04.23 ver 2.68        "
    echo ""

function help() {
    echo "Purpose: Encrypts and Decrypts files via AES-256"
    echo "  -e, --encrypt       encrypt the file"
    echo "  -d, --decrypt       decrypt the file" 
    echo "  -i, --input         input file to read"
    echo "  -o, --output        output file to write"
    echo ""

## Start GetOpt stuff
encrypt="no"    #encrypt function state (e,encrypt)
decrypt="no"    #decrypt function state (d,decrypt)
filein="no"   #filename IN flag (i,input)
fileout="no"  #filename OUT flag (o,output)

while [ $# -gt 0 ]; do
    case $1 in
        -e|--encrypt) encrypt="yes" ;;
        -d|--decrypt) decrypt="yes" ;;
        #long opts need additional shift
        -i|--input) filein="$2" ; shift;;
        -o|--output) fileout="$2" ; shift;;
        (--) shift; break;;
        (-*) echo "$0: error - unrecognized option $1" 1>&2; exit 1;;
        (*) break;;
## End GetOpt

## Secure delete process
function sdelete() {
    if [ $(uname -s) == 'Darwin' ]; then
        srm $1
    elif [ $(uname -s) == 'Linux' ]; then
        shred -u $1

## Decrypt Process
function decrypt() {
    if [ "$filein" = "no" ]; then
        echo -n "No encrypted file specified, what file are we decrypting: "
        read filein
    if [ -r "$filein" ]; then
        if [ "$fileout" = "no" ]; then
        openssl enc -d -aes256 -in $filein -out $fileout
        generate_digests $filein $fileout
        exit 0;
        echo "File '$filein' is not readable or does not exist."
        exit 1;

## Encrypt Process
function encrypt() {
    if [ "$filein" = "no" ]; then
        echo -n "No input file specified, what file are we encrypting: "
        read filein
    if [ -r "$filein" ]; then
        if [ "$fileout" = "no" ]; then
        if [ -f "$fileout" ]; then
            echo "Output file exists already, encrypting will overwrite this file."
            echo -n "Do you want to encrypt anyway? [Y/n]: "
            read choice
            if [ "$choice" = "Y" ] || [ "$choice" = "y" ] || [ "$choice" = "" ]; then
                openssl enc -aes256 -in $filein -out $fileout
                generate_digests $filein $fileout
                sdelete $filein
                exit 0;
                exit 2;
            openssl enc -aes256 -in $filein -out $fileout
            generate_digests $filein $fileout
            sdelete $filein
            exit 0;
        echo "Input file does not exist or is not readable. You're attempting to encrypt file: '$filein'"
        exit 1;

if [ "$encrypt" = "yes" ] && [ "$decrypt" = "no" ]; then
    encrypt $filein $fileout
elif [ "$decrypt" = "yes" ] && [ "$encrypt" = "no" ]; then
    decrypt $filein $fileout
Tagged , , , ,

Fun with Bash :: one liners

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

Same as above but replacing # lines with blank lines
sed -e '/^#/g' $1 < $file_name_here

Find all symlinks in the current directory and subdirs
find ./ -type l -exec ls -l {} \;

Find all executable files in current directory and subdirs
find ./ -type f -perm -o+rx -exec ls -ld '{}' \;

Remove all files matching the input string
echo -n "filename match to remove [rm -i]: " && read f; find ./ -name ${f} -exec rm -i {} \;

Display largest ten files in current dir and subdirs 
du -a ./ | sort -n -r | head -n 10

Display all files in current dir and subdirs in order of filesize
du -a ./ | sort -n -r

Generate a MD5 hash for the input string (not file)
Linux: echo -n "str: " && read x && echo -n "$x" | md5sum
OSX: echo -n "str: " && read x && echo -n "$x" | md5

Display a summary of all files in current and subdirs
for t in files links directories; do echo `find . -type ${t:0:1} | wc -l` $t; done 2> /dev/null

Download a website's SSL Certificate to a file for later use
openssl s_client -showcerts -connect $HOST:443 > $FILE-NAME.txt 
Tagged , ,

SAN vs Local-disk :: innodb_flush_method performance benchmarks

If you’ve been tuning your MySQL database and have wondered what effect the innodb_flush_method settings have on write performance, then this information might help. I’ve recently been doing a lot of baseline load tests to show performance differences between localdisk and the new SAN we’re deploying. Since we run InnoDB for everything in production, and writes are very heavy, I decided to run comparison tests between two identical servers to find the best setting for innodb_flush_method. We have the following specs for the hardware:

  • Dell R610
  • 24 core Intel Xeon X5670 @ 2.93ghz
  • 72GB ECC RAM
  • Brocade 825 HBA
  • Local disk: RAID-10 15K SAS Ext3 (ugh)
  • SAN: Oracle 7420 with four Intel Xeon X7550 @ 2.00GHz, 512GB RAM, 2TB read-cache(MLC-SSD), 36GB write cache (SLC-SSD), 3 disk shelves populated with 60x2TB 7200RM SATA drives setup in mirrored format with striped logs, dual 8Gb FC links to redundant fabric, connected to Brocade DCX 8510-4.
  • The my.cnf file being used for the tests: click-click

I’m using the following sysbench command to run the tests. On each server the same commands are used. I ran a 1B row prepare prior to the 1B row test.

sysbench –db-driver=mysql –num-threads=64 –max-requests=1000000000 –max-time=3600 –test=oltp –verbosity=3 –validate=off –oltp-test-mode=complex –oltp-read-only=off –oltp-table-name=sbtest –oltp-table-size=1000000000 –oltp-dist-type=special –mysql-host=localhost –mysql-port=3306  –mysql-table-engine=innodb run

On the server that is utilizing SAN paths there are two LUNS presented for MySQL use. /db/data01 for InnoDB data files, /db/logs01 for InnoDB logs. These filesystems are both formatted as XFS. The server running local-disk tests is running Ext3. I might run some more tests later with the local-disk setup as XFS if time allows.

Here are the results. Clearly a well designed SAN infrastructure is superior to even RAID-10 15K SAS drives. And of course you can see the different performance values from using O_DIRECT for the innodb_flush_method for the different data storage mediums.

1B Row Complex Transactional Test, 64 threads

  • SAN O_DIRECT: read/write requests: 31560140 (8766.61 per sec.)
  • SAN O_DSYNC: read/write requests: 5179457 (1438.52 per sec.)
  • SAN fdatasync: read/write requests: 9445774 (2623.66 per sec.)
  • Local-disk O_DIRECT: read/write requests: 3258595 (905.06 per sec.)
  • Local-disk O_DSYNC: read/write requests: 3494632 (970.65 per sec.)
  • Local-disk fdatasync: read/write requests: 4223757 (1173.04 per sec.)
Tagged , , , ,

Quick How-To for DRBD + MySQL + LVS

I wrote this up a while ago and decided that I didn’t want to lose it in a shuffle of documents during my transition to a new workstation. It’s the basics of setting up Heartbeat (LVS) + DRBD (block replication between active/passive master servers) + MySQL. This should give you the basics of a H/A system without the benefits of SAN but also without the associated cost. The validity of this setup for H/A purposes is highly dependent on your workload and environment. You should know the ins and outs of your H/A solution before deciding to blame the system for not performing as expected. As with all production systems you should test, test, test and test some more before going live.

When I get around to it later I’ll post my How-To for setting up RHCS + SAN + MySQL. You can download the DRBD document PDF here: DRBD_LVS_Install-Configure_HowTo

Tagged , , , ,

Quadrant Framework – rev7 update adds DyGraphs support

Quick update to the framework that was released yesterday; I’ve added automatic graph generation. I chose DyGraphs due to the quick ability to enable support – the HTML is very quick and simply loads the CSV data. It has the same zooming features of Highcharts without the JS overhead.

Now when you run a load test you will get (in the output directory) a mixture of files: the main cumulative CSV and HTML file for the hostname that was tested, and then one CSV and HTML per report variable that was tested. This means you don’t have to drag the main CSV file into an alternate program or spend time parsing out certain variables one at a time to generate specific graphs.  I’ve also added support for limiting output of SNMP variables (LOAD,CPU,MEM). Head over here and download the update:

To enable the graph generation, as it is not on by default, use this flag: –output-graphcode-enable

Tagged , , , , ,

MySQL Load Testing Framework – initial release

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 looping and iteration commands.
    graphing output via Highcharts and Sparklines (coming in the next release in a week)
    CSV test result output for easy import to other data visualization systems or import into a SQL database
    customize your iterations via ability to watch an unlimited number of MySQL global status variables
    customize your iterations via ability to alter MySQL global variables between iterations
    run arbitrary SQL commands between each iteration
    allows you to control the caches between iterations
    set custom default values and create configuration templates for easy repeatability and portability of your tests
    extensible code base: written purely in Python 2.6

Download it now:

MySQL Community – what do you want in a load testing framework?

So I’ve been doing a fair number of automated load tests these past six months. Primarily with Sysbench, which is a fine, fine tool. First I started using some simple bash based loop controls to automate my overnight testing, but as usually happens with shell scripts they grew unwieldy and I rewrote them in python. Now I have some flexible and easily configurable code for sysbench based MySQL benchmarking to offer the community. I’ve always been a fan of giving back to such a helpful group of people – you’ll never hear me complain about “my time isn’t free”. So, let me know what you want in an ideal testing environment (from a load testing framework automation standpoint) and I’ll integrate it into my existing framework and then release it via the BSD license. The main goal here is to have a standardized modular framework, based on sysbench, that allows anyone to compare their server performance via repeatable tests. It’s fun to see other people’s benchmarks but it’s often difficult to repeat and compare since most tests aren’t fully documented in their blog posts – this could be a solution to that.

Currently I have the harness doing iterations based on:

  • incrementing (choose a global dynamic variable, ie: sync_binlog=0-1000) system values
  • storage engine vs storage engine for the same workload
  • thread quantity increments for read-only or read+write
  • N-nodes in a cluster workloads with WRR traffic distribution (need to code WLC and others)
  • QPS testing for connection pool vs open/close connection
  • multi-table vs single-table workloads

Outputs available: CSV, XML, JSON for easy integration into any number of the various graphing frameworks available. I’ll probably code up a light weight python http server preloaded with Highcharts and Sparklines so you can see your benchmarks easily without having to roll your own graphs.

Quick now, tell me what you’d like me to code for you!

MySQL and RAMdisk, or how to make tmpdir-usage queries faster

Have you ever looked at your processlist and seen queries in the state “copying to tmp table” and then run an explain on it and noticed that the tmp table is being created on disk? Happens a lot with some servers and some workloads. Of course disk is much slower than RAM so this becomes a slow process and makes queries execute slower than they could if they were allowed to use RAM. So, one way to get this process to speed up (aside from tuning your queries which should be done first) is to create a tmpfs or ram-disk and let MySQL use that for it’s temp-table-on-disk creations. MySQL on Linux defaults to /tmp for the tmpdir location so this will need to be changed.

Here is how you get MySQL to use a 1G size tmpfs. How you size your tmpfs depends how much ram your system has and how much tmpdir space mysql needs for your workload. If you need more tmpdir space than you can make in a tmpfs mount then mysql allows multiple tmpdir locations to be set (see the manual:

0. mkdir -p /db/tmpfs01 && chown mysql:mysql /db/tmpfs01
1. mount tmpfs /db/tmpfs01 -t tmpfs -o size=1G
3. edit my.cnf file and add: tmpdir = /db/tmpfs01
4. restart MySQL

To have persistent tmpfs with reboot put the following into /etc/fstab. On this system the mysql user is uid/gid of 27, yours might vary so adjust accordingly.
tmpfs /db/tmpfs01 tmpfs rw,uid=27,gid=27,size=1G,nr_inodes=10k,mode=0755 0 0


MySQL Analytics: updated query for table engine data statistics

This is a follow up to my previous post titled “MySQL analytics: information_schema polling for table engine percentages”. Here’s an updated query with more output and quicker execution time. What you get: innodb table space utilization percentage, data+index usage total and per innodb/myisam engine, innodb data/index/percentage, myisam data/index/percentages, and overall percentage values. Rather useful for profiling your table engine usage.

Sample output:
innodb_tablespace_utilization_perc: 100
total_size_gb: 26.275011910126
index_size_gb: 2.994891166687
data_size_gb: 23.280120743439
innodb_total_size_gb: 6.751220703125
innodb_data_size_gb: 5.2576751708984
innodb_index_size_gb: 1.4935455322266
myisam_total_size_gb: 19.523791207001
myisam_data_size_gb: 18.02244557254
myisam_index_size_gb: 1.5013456344604
perc_index: 11.3982
perc_data: 88.6018
innodb_perc_index: 22.1226
innodb_perc_data: 77.8774
myisam_perc_index: 7.6898
myisam_perc_data: 92.3102
innodb_perc_total_index: 49.8698
innodb_perc_total_data: 22.5844
myisam_perc_total_index: 50.1302
myisam_perc_total_data: 77.4156

select round(sum(innodb_data_size + innodb_index_size) / (innodb_data_free + sum(innodb_data_size + innodb_index_size))) * 100  as 'innodb_tablespace_utilization_perc'
, (data_size + index_size) / gb as total_size_gb 
, index_size / gb as index_size_gb 
, data_size / gb as data_size_gb 
, sum(innodb_index_size + innodb_data_size) / pow(1024,3) as innodb_total_size_gb 
, innodb_data_size / pow(1024,3) as innodb_data_size_gb 
, innodb_index_size / pow(1024,3) as innodb_index_size_gb 
, sum(myisam_index_size + myisam_data_size) / pow(1024,3) as myisam_total_size_gb 
, myisam_data_size / pow(1024,3) as myisam_data_size_gb 
, myisam_index_size / pow(1024,3) as myisam_index_size_gb 
, index_size / (data_size + index_size) * 100 as perc_index 
, data_size / (data_size + index_size) * 100 as perc_data 
, innodb_index_size / (innodb_data_size + innodb_index_size) * 100 as innodb_perc_index 
, innodb_data_size / (innodb_data_size + innodb_index_size) * 100 as innodb_perc_data 
, myisam_index_size / (myisam_data_size + myisam_index_size) * 100 as myisam_perc_index 
, myisam_data_size / (myisam_data_size + myisam_index_size) * 100 as myisam_perc_data 
, innodb_index_size / index_size * 100 as innodb_perc_total_index 
, innodb_data_size / data_size * 100 as innodb_perc_total_data 
, myisam_index_size / index_size * 100 as myisam_perc_total_index 
, myisam_data_size / data_size * 100 as myisam_perc_total_data 
from ( select sum(data_length) data_size, 
	sum(index_length) index_size, 
	sum(if(engine = 'innodb', data_length, 0)) as innodb_data_size, 
	sum(if(engine = 'innodb', index_length, 0)) as innodb_index_size, 
	sum(if(engine = 'myisam', data_length, 0)) as myisam_data_size, 
	sum(if(engine = 'myisam', index_length, 0)) as myisam_index_size,
	sum(if(engine = 'innodb', data_free, 0)) as innodb_data_free, 
	pow(1024, 3) gb from information_schema.tables ) 

Fun with Bash: aliases make your live easier… share your favorites

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?

Tagged ,