Filed under Programming

Simple MySQL: using TRIGGERs to keep datetime columns updated without direct SQL calls

If you’ve ever used non-opensource code, or applications that you don’t have complete control over, then you may have run into situations you need to alter data on a per-row basis but been unable to do so for lack of application SQL access. The solution to this type of problem is to use a MySQL TRIGGER, which allows us to execute arbitrary SQL commands when defined events occur. Why is this useful and how does it work? Well…

For example, I have a freeRADIUS server that uses MySQL as a backend for the user authentication, and one of my server applications (HostBill) provides a freeRADIUS plugin that allows my users to manage their RADIUS accounts; however the default freeRADIUS schema lacks a DATETIME column on the user table. When a user is created (INSERT) or has their password changed (UPDATE) I have no row data that tells me the dates when these operations were issued. Typically this would be a trivial change: issue an ALTER TABLE statement to add two columns and then add some NOW() statements to the application’s SQL calls.

However, the problem is that the application in question is ion-cube encrypted so I cannot make SQL changes to add support for my ‘date_created’ and ‘date_modified’ columns. I could intercept the TCP steams via MySQL Proxy or a custom script but that’s quite silly to have to do for this. Other methods also exist but are rather hack-ish.

MySQL TRIGGERs easily solve this situation. Here are two TRIGGERs, one that runs on INSERT and the other on UPDATE. Now the database can have ‘date_created’ and ‘date_modified’ data without any application changes. Whenever a row is inserted into the table the ‘date_create’ column will be populated via the NOW() function and correspondingly when a row is modified the ‘date_modified’ column will be updated with the NOW() function.

DELIMITER |
CREATE TRIGGER trigger_radcheckDatetimeInsert BEFORE INSERT ON radcheck FOR EACH ROW 
BEGIN
    SET NEW.date_create = NOW();
END; 
|
CREATE TRIGGER trigger_radcheckDatetimeModify BEFORE UPDATE ON radcheck FOR EACH ROW 
BEGIN
    SET NEW.date_modify = NOW();
END; 
|
DELIMITER ;
Tagged , , ,

OpenCode: MySQL procedures + python + shell code repositories now public

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: https://bitbucket.org/themattreid/generic-sql-scripts/src

I’ll add more code to those as time goes on.

Tagged , , ,

Simple MySQL: Converting ANSI SQL to SQLite3

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 update [^,]*//g' |            
perl -e 'local $/;$_=<>;s/,\n\)/\n\)/gs;print "begin;\n";print;print "commit;\n"' |
perl -pe '               
  if (/^(INSERT.+?)\(/) { 
     $a=$1;               
     s/\\'\''/'\'\''/g;   
     s/\\n/\n/g;          
     s/\),\(/\);\n$a\(/g; 
  }                       
  ' > $1.sql              
cat $1.sql | sqlite3 $1.db > $1.err   
ERRORS=`cat $1.err | wc -l`           
if [ $ERRORS == 0 ]; then             
  echo "Conversion completed without error. Output file: $1.db"
  rm $1.sql         
  rm $1.err         
    rm tmp          
else                
   echo "There were errors during conversion.  Please review $1.err and $1.sql for details."
fi
Tagged , ,

MySQL: a convenient stored procedure for memory usage reporting

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

#####################################################################                                                                                                                                                           
## NAME: memory_report_sp.sql                                                                                                                                                                                                   
## AUTHOR: Matt Reid                                                                                                                                                                                                            
## SITE: http://themattreid.com                                                                                                                                                                                                 
## DATE: 2012-09-02                                                                                                                                                                                                             
## LICENSE: GPL v3                                                                                                                                                                                                              
##                                                                                                                                                                                                                              
## INSTALL METHODS:                                                                                                                                                                                                             
##  a) install via linux shell                                                                                                                                                                                                  
##     $> mysql --user=root -p mysql < memory_report_sp.sql                                                                                                                                                                     
##  b) install via mysql command line                                                                                                                                                                                           
##     mysql> use mysql; import memory_report_sp.sql                                                                                                                                                                            
##                                                                                                                                                                                                                              
## USAGE:                                                                                                                                                                                                                       
##  execute the stored procedure to generate the report                                                                                                                                                                         
##     mysql> use mysql;                                                                                                                                                                                                        
##     mysql> call memory_report();                                                                                                                                                                                             
##                                                                                                                                                                                                                              
## EXAMPLE OUTPUT:                                                                                                                                                                                                              
##  [localhost mysql://root@localhost/mysql > call memory_report();                                                                                                                                                             
##  +-----------------------------+----------+                                                                                                                                                                                  
##  | VARIABLE                    | VALUE    |                                                                                                                                                                                  
##  +-----------------------------+----------+                                                                                                                                                                                  
##  | TOTAL_BUFFERS_GLOBAL        | 420.00 M |                                                                                                                                                                                  
##  | TOTAL_BUFFERS_PER_THREAD    | 32.72 M  |                                                                                                                                                                                  
##  | MAX_CONNECTIONS_LIMIT       | 151      |                                                                                                                                                                                  
##  | MAX_CONNECTIONS_USED        | 2        |                                                                                                                                                                                  
##  | MAX_CONNECTION_USED_PERCENT | 1.32 %   |                                                                                                                                                                                  
##  | TOTAL_MEMORY_LIMIT          | 452.53 M |                                                                                                                                                                                  
##  | TOTAL_MEMORY_ACTIVE         | 47.44 M  |                                                                                                                                                                                  
##  | TOTAL_MEMORY_ACTIVE_PERCENT | 10.48 %  |                                                                                                                                                                                  
##  | HEAP_TABLE_LIMIT            | 16.00 M  |                                                                                                                                                                                  
##  | TEMP_TABLE_LIMIT            | 16.00 M  |                                                                                                                                                                                  
##  +-----------------------------+----------+                                                                                                                                                                                  
##  10 rows in set (0.02 sec)                                                                                                                                                                                                   
#####################################################################                                                                                                                                                           
                                                                                                                                                                                                                                
DELIMITER $$                                                                                                                                                                                                                    
DROP PROCEDURE IF EXISTS `memory_report` $$                                                                                                                                                                                     
CREATE PROCEDURE `memory_report` ()                                                                                                                                                                                             
BEGIN                                                                                                                                                                                                                           
                                                                                                                                                                                                                                
                                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
## Declare some variables                                                                                                                                                                                                       
#####################################################################                                                                                                                                                           
DECLARE SUM_SGA BIGINT UNSIGNED;                                                                                                                                                                                                
DECLARE SUM_PGA BIGINT UNSIGNED;                                                                                                                                                                                                
DECLARE MAX_CONNECTIONS_LIMIT INT;                                                                                                                                                                                              
DECLARE MAX_CONNECTIONS_USED INT;                                                                                                                                                                                               
DECLARE CONNECTION_RATIO FLOAT;                                                                                                                                                                                                 
DECLARE TOTAL_HEAP BIGINT UNSIGNED;                                                                                                                                                                                             
DECLARE TOTAL_TEMPTABLE BIGINT UNSIGNED;                                                                                                                                                                                        
DECLARE k VARCHAR(255);                                                                                                                                                                                                         
DECLARE v BIGINT UNSIGNED;                                                                                                                                                                                                      
DECLARE TICK BOOL;                                                                                                                                                                                                              
                                                                                                                                                                                                                                
DECLARE MEM_LIMIT BIGINT UNSIGNED;                                                                                                                                                                                              
DECLARE MEM_USED BIGINT UNSIGNED;                                                                                                                                                                                               
DECLARE MEM_PERC FLOAT;                                                                                                                                                                                                         
DECLARE MAX_CONNECTION_USED_PERCENT FLOAT;                                                                                                                                                                                      
                                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
## Declare the queries                                                                                                                                                                                                          
#####################################################################                                                                                                                                                           
DECLARE GLOBALS CURSOR FOR SELECT                                                                                                                                                                                               
        VARIABLE_NAME, VARIABLE_VALUE                                                                                                                                                                                           
        FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES                                                                                                                                                                                
        WHERE VARIABLE_NAME IN                                                                                                                                                                                                  
                ('query_cache_size',                                                                                                                                                                                            
                'key_buffer_size',                                                                                                                                                                                              
                'innodb_buffer_pool_size',                                                                                                                                                                                      
                'innodb_additional_mem_pool_size',                                                                                                                                                                              
                'innodb_log_buffer_size',                                                                                                                                                                                       
                'read_buffer_size',                                                                                                                                                                                             
                'read_rnd_buffer_size',                                                                                                                                                                                         
                'sort_buffer_size',                                                                                                                                                                                             
                'thread_stack',                                                                                                                                                                                                 
                'join_buffer_size',                                                                                                                                                                                             
                'binlog_cache_size',                                                                                                                                                                                            
                'max_connections',                                                                                                                                                                                              
                'max_heap_table_size',                                                                                                                                                                                          
                'tmp_table_size')                                                                                                                                                                                               
        UNION                                                                                                                                                                                                                   
        SELECT VARIABLE_NAME, VARIABLE_VALUE                                                                                                                                                                                    
        FROM INFORMATION_SCHEMA.GLOBAL_STATUS                                                                                                                                                                                   
        WHERE VARIABLE_NAME IN                                                                                                                                                                                                  
                ('max_used_connections');                                                                                                                                                                                       
                                                                                                                                                                                                                                
                                                                                                                                                                                                                                
DECLARE CONTINUE HANDLER FOR NOT FOUND SET TICK = 1;                                                                                                                                                                            
                                                                                                                                                                                                                                
                                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
## Set default values                                                                                                                                                                                                           
#####################################################################                                                                                                                                                           
SET SUM_SGA = 0;                                                                                                                                                                                                                
SET SUM_PGA = 0;                                                                                                                                                                                                                
SET MAX_CONNECTIONS_LIMIT = 0;                                                                                                                                                                                                  
SET MAX_CONNECTIONS_USED = 0;                                                                                                                                                                                                   
SET CONNECTION_RATIO = 0;                                                                                                                                                                                                       
SET TOTAL_HEAP = 0;                                                                                                                                                                                                             
SET TOTAL_TEMPTABLE = 0;                                                                                                                                                                                                        
                                                                                                                                                                                                                                
                                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
## Query global variables                                                                                                                                                                                                       
#####################################################################                                                                                                                                                           
SET TICK = 0;                                                                                                                                                                                                                   
OPEN GLOBALS;                                                                                                                                                                                                                   
looper:LOOP                                                                                                                                                                                                                     
      FETCH GLOBALS INTO k,v;                                                                                                                                                                                                   
  IF TICK = 1 THEN                                                                                                                                                                                                              
    LEAVE looper;                                                                                                                                                                                                               
  END IF;                                                                                                                                                                                                                       
                                                                                                                                                                                                                                
    IF k in ('query_cache_size',
       'key_buffer_size',
       'innodb_buffer_pool_size',
       'innodb_additional_mem_pool_size',
       'innodb_log_buffer_size')                                                                                         
       THEN SET SUM_SGA = SUM_SGA + v;                                                                                                                                                                                          
    ELSEIF k in ('read_buffer_size',
       'read_rnd_buffer_size',
       'sort_buffer_size',
       'thread_stack',
       'join_buffer_size',
       'binlog_cache_size')                                                                                            
       THEN SET SUM_PGA = SUM_PGA + v;                                                                                                                                                                                          
    ELSEIF k in ('max_connections') THEN SET MAX_CONNECTIONS_LIMIT = v;                                                                                                                                                         
    ELSEIF k in ('max_heap_table_size') THEN SET TOTAL_HEAP = v;                                                                                                                                                                
    ELSEIF k in ('tmp_table_size','max_heap_table_size')                                                                                                                                                                        
     THEN SET TOTAL_TEMPTABLE = IF ((TOTAL_TEMPTABLE > v), TOTAL_TEMPTABLE, v);                                                                                                                                                 
    ELSEIF k in ('max_used_connections') THEN SET MAX_CONNECTIONS_USED = v;                                                                                                                                                     
                                                                                                                                                                                                                                
    END IF;                                                                                                                                                                                                                     
                                                                                                                                                                                                                                
END LOOP;                                                                                                                                                                                                                       
CLOSE GLOBALS;                                                                                                                                                                                                                  
                                                                                                                                                                                                                                
                                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
## Output report                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
SET MEM_LIMIT = ROUND((SUM_SGA + (MAX_CONNECTIONS_LIMIT * SUM_PGA))/POW(1024,2),2);                                                                                                                                             
SET MEM_USED = ROUND((SUM_SGA + (MAX_CONNECTIONS_USED * SUM_PGA))/POW(1024,2),2);                                                                                                                                               
SET MEM_PERC = ROUND((MEM_USED * 100)/MEM_LIMIT,2);                                                                                                                                                                             
SET MAX_CONNECTION_USED_PERCENT = ROUND((MAX_CONNECTIONS_USED * 100)/MAX_CONNECTIONS_LIMIT,2);                                                                                                                                  
                                                                                                                                                                                                                                
SELECT "GLOBAL_BUFFERS_TOTAL" AS VARIABLE, CONCAT(ROUND(SUM_SGA/POW(1024,2),2),' M') AS VALUE UNION                                                                                                                             
SELECT "THREAD_BUFFERS_TOTAL", CONCAT(ROUND((SUM_PGA * MAX_CONNECTIONS_LIMIT)/POW(1024,2),2),' M') UNION                                                                                                                        
SELECT "THREAD_BUFFERS_EACH", CONCAT(ROUND(SUM_PGA/POW(1024,2),2),' M') UNION                                                                                                                                                   
                                                                                                                                                                                                                                
SELECT "MAX_CONNECTIONS_LIMIT", MAX_CONNECTIONS_LIMIT UNION                                                                                                                                                                     
SELECT "MAX_CONNECTIONS_USED", MAX_CONNECTIONS_USED UNION                                                                                                                                                                       
SELECT "MAX_CONNECTION_USED_PERCENT", CONCAT(MAX_CONNECTION_USED_PERCENT, ' %') UNION                                                                                                                                           
                                                                                                                                                                                                                                
SELECT "MEMORY_UTILIZATION_LIMIT", CONCAT(MEM_LIMIT,' M') UNION                                                                                                                                                                 
SELECT "MEMORY_UTILIZATION_ACTIVE", CONCAT(MEM_USED,' M') UNION                                                                                                                                                                 
SELECT "MEMORY_UTILIZATION_RATIO", CONCAT(MEM_PERC,' %') UNION                                                                                                                                                                  
                                                                                                                                                                                                                                
SELECT "HEAP_TABLE_LIMIT", CONCAT(ROUND(TOTAL_HEAP / POW(1024,2),2),' M') UNION                                                                                                                                                 
SELECT "TEMP_TABLE_LIMIT", CONCAT(ROUND(TOTAL_TEMPTABLE / POW(1024,2),2),' M') ;                                                                                                                                                
                                                                                                                                                                                                                                
END $$                                                                                                                                                                                                                          
DELIMITER ; 
Tagged , ,

TunnelMaker, a simple script to generate multi-hop SSH tunnels

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 (arbitrary port number) over an SSH connection to Europe, and then through another SSH tunnel to the Japan server’s port 80 to talk to Apache. Then we simply load http://localhost:8080 in our web browser and we’ll have access to Apache running on the server in Japan. You can think of the chain like this: localhost:8080->Europe:8080->Japan:80. The SSH command to create this chain is as follows: ssh -v -L 8080:localhost:8080 europe ssh -v -L 8080:localhost:80 -N japan

Since you are not limited to chaining between one or two hosts with this method, and you can choose different ports and services to forward, the opportunities are nearly endless. Here’s a script that makes building the tunnel commands easier.

#!/bin/bash
## NAME: TunnelMaker
## PURPOSE: Creates multi-hop SSH tunnels for forwarding data+connections
## AUTHOR: Matt Reid
## DATE: 2012-05-30
## VERSION: 1.0.2-jf
## SAMPLE: 
#   echo "ssh -v -L $localport:localhost:$remotehost1port $remotehost1 \
#   ssh -v -L $remotehost1port:localhost:$remotehost2port -N $remotehost2 ... repeat"

echo "------------------->"
echo "-->Tunnel-->Maker-->"
echo "-->version: 1.0.2-jf"
echo "-->themattreid.com"
echo "------------------->" 

## Get sequence value
echo -n "How many hops are we making [#remote servers]: "
read hops

## Check user, set initial port
user=`whoami`
notice=' [>1024]'
if [ "$user" = "root" ]; then notice=''; fi

## Initialize some vars
c=1             #session value counter
n=''            #session value for notice
p=''            #session value for port
final=''        #end result string for tunnel command
localport=''    #session value holder
let hops=hops+1 #increment hops for iteration in loop

## Build connection strings
while [  $c -lt $hops ]; do
    echo ""
    echo -n "Host[or IP] for hop#[$c]: "; read host
    echo -n "Localhost port for $host$notice: "; read localport
    echo -n "Destination port for $host$notice: "; read destport    

    if [ "$localport" = '' ]; then echo "no port selected. exiting." exit 1; fi
    if [ "$destport" = '' ]; then echo "no port selected. exiting." exit 1; fi
    if [ $c -gt 1 ]; then n="-N "; fi #added to suppress remote login

    string="ssh -v -L $localport:localhost:$destport $n$host "
    final="$final $string"

    let c=c+1
    p=$localport
done

echo "You can initiate your tunnels via command: \"$final\""
echo "Shall I start the tunnel now? [y,N]: "; read choice
if [ "$choice" = "Y" ] || [ "$choice" = "y" ]; then `$final`; else exit 0; fi

Here’s the sample output from our example with Europe and Japan.

> ./tunnelmaker 
------------------->
-->Tunnel-->Maker-->
-->version: 1.0.2-jf
-->themattreid.com
------------------->
How many hops are we making [#remote servers]: 2

Host[or IP] for hop#[1]: europe
Localhost port for europe [>1024]: 8080
Destination port for europe [>1024]: 8080

Host[or IP] for hop#[2]: japan
Localhost port for japan [>1024]: 8080
Destination port for japan [>1024]: 80

You can initiate your tunnels via command: " ssh -v -L 8080:localhost:8080 europe  ssh -v -L 8080:localhost:80 -N japan "
Shall I start the tunnel now? [y,N]: N
Tagged , , ,

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.

#!/bin/sh
# 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;;
    esac
    shift
done
## End GetOpt

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

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

## Encrypt Process
function encrypt() {
    filein="$1"
    fileout="$2"
    if [ "$filein" = "no" ]; then
        echo -n "No input file specified, what file are we encrypting: "
        read filein
    fi
    
    if [ -r "$filein" ]; then
        if [ "$fileout" = "no" ]; then
            fileout="$filein.aes256"
        fi
        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;
            else 
                exit 2;
            fi      
        else
            openssl enc -aes256 -in $filein -out $fileout
            generate_digests $filein $fileout
            sdelete $filein
            exit 0;
        fi
    else
        echo "Input file does not exist or is not readable. You're attempting to encrypt file: '$filein'"
        exit 1;
    fi
}

if [ "$encrypt" = "yes" ] && [ "$decrypt" = "no" ]; then
    encrypt $filein $fileout
elif [ "$decrypt" = "yes" ] && [ "$encrypt" = "no" ]; then
    decrypt $filein $fileout
else 
    #clear
    header
    help
fi
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: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

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: http://code.google.com/p/quadrant-framework/

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

Tagged , , , , ,