Mikrotik Scripting – Array Push Function

Since the scripting language at the moment on Router OS version 6.13 is lacking in the array editing department, here is a simple array push function I created.

# Usage: [$arrayPush <$array name> <value> <key position to place value (0-n or -1)>]
# Input an array name, value, and the key position to push the value to. To push value to the end of the array, enter -1.
# If array doesn't already exist, you must declare the variable and set it to "" before calling the function.
 Continue reading "Mikrotik Scripting – Array Push Function"

Mikrotik Scripting – Function to Split an IP Address into an Array

If you are unfamiliar with Mikrotik networking equipment, do yourself a favor and check them out at mikrotik.com or routerboard.com. Mikrotik uses Router OS, a Linux based operating system. In my eyes, they are every bit as comparable to Cisco at a fraction of the price with an impressive and robust GUI. I’ve been using them in production environments for six years with outstanding results. What other networking equipment has it’s own scripting language? None that I know of. The CCR series of routers with up to 36 processor cores are unparalleled in performance and flexibility.
Continue reading “Mikrotik Scripting – Function to Split an IP Address into an Array”

Bash Script – Sync a File to an Array of Hosts

#!/bin/bash
# Sync a file to a remote set of servers using scp and check using diff
# NOTE: This requires the use of 'sshpass' and IS insecure in nature
# It also assumes the user credentials are identical on all hosts
# and that they have the necessary permissions on the remote directory
# Author: Nathan Thomas
LOGFILE='/var/log/sync_files.log'
SSH_USER='non-root-user'
SSH_PASS='password'
CONF_FILE='/etc/appdir/myconfig.conf'
SERVERS=('server1.fqdn.com' 'server2.fqdn.com' 'server3.fqdn.com')
for HOST in "${SERVERS[@]}" ; do
        # NOTE: This won't work if the host keys are not already in the ssh cache
        # Flush ssh hosts - Either uncomment these two lines on first run or you could leave it uncommented for hosts that change addresses a lot
 Continue reading "Bash Script – Sync a File to an Array of Hosts"

Bash Script – Script Based MySQL Table Replication on an Array of Slaves

This bash script only uses row count and MySQL checksum to determine table consistency. I created this script because I wanted a different option rather than using MySQL replication. I initially had replication set up and working between six sites but it would tend to break from time to time so I needed a different solution. In my situation, my data was only being updated once or twice a week. This script should only be used in cases where you’re not overly concerned about your data consistency and row count and checksum are sufficient enough to suit your needs. If you need a more accurate consistency check of your table data, look into the Percona Toolkit.

#!/bin/bash
#
# Sync DB Tables w/o Replication
# Author: Nathan Thomas 01/16/2014

#------#
# VARS #
#------#
# Server to sync DB from
MASTER='master01.fqdn.com'
MUSER='username'
MPASS='password'
DBNAME='mydbname'
TBLNAME='mytablename'
DUMP='/tmp/mydumpfile.sql'
LOGFILE='/var/log/mysql/mysql_repl_check.log'
MAILTO='my_email_addr@fqdn.com'

# Array of slave hostnames separated by a space
declare -a SLAVE=('slave01.fqdn.com' 'slave02.fqdn.com' 'slave03.fqdn.com')
# Array of slave usernames in same order
declare -a SUSER=('username1' 'username2' 'username3')
# Array of slave passwords in same order
declare -a SPASS=('password1' 'password2' 'password3')

#-----------#
# FUNCTIONS #
#-----------#
# Check table exists
# params: $1=username $2=password $3=hostname
function tableExists() {
        echo "`date "+%a %b%e %T"` - Called function 'tableExists' on $3." >> ${LOGFILE}
        echo "`date "+%a %b%e %T"` - Running query to verify table '${TBLNAME}' exists in database '${DBNAME}' on $3." >> ${LOGFILE}
        local QUERY="SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='${DBNAME}' AND TABLE_NAME='${TBLNAME}'"
        local RESULT=`mysql -u $1 -p$2 -h $3 -Bse "$QUERY" 2>&1`
        local RETVAL="$?"
        echo "`date "+%a %b%e %T"` - Query on $3 retured the following value: '${RESULT}'" >> ${LOGFILE}
        echo "`date "+%a %b%e %T"` - Exit status of last command on $3 returned: '${RETVAL}'" >> ${LOGFILE}
        if [ "${RETVAL}" -eq "0" -a "${RESULT}" -eq "0" 2>/dev/null ] ; then  # Retval 0, query returned 0, hide stderr, Table missing
                echo "`date "+%a %b%e %T"` - Table '${TBLNAME}' does not exist in database '${DBNAME}' on $3." >> ${LOGFILE}
                echo "`date "+%a %b%e %T"` - Function 'tableExists' completed successfully on $3." >> ${LOGFILE}
                return 1  # No dice
        elif [ "${RETVAL}" -eq "0" -a "${RESULT}" -eq "1" 2>/dev/null ] ; then  # Retval 0, query returned 1, hide stderr, Table exists
                echo "`date "+%a %b%e %T"` - Table '${TBLNAME}' exists in database '${DBNAME}' on $3." >> ${LOGFILE}
                echo "`date "+%a %b%e %T"` - Function 'tableExists' completed successfully on $3." >> ${LOGFILE}
                return 0  # All good
        else  # Some other error
                echo "`date "+%a %b%e %T"` - Unable to determine table '${TBLNAME}' exists in database '${DBNAME}' on $3." >> ${LOGFILE}
                echo "`date "+%a %b%e %T"` - We either encountered an error code in the SQL data, had a problem connecting to MySQL, or the last command exited with a nonzero status." >> ${LOGFILE}
                echo "`date "+%a %b%e %T"` - Function 'tableExists' did not finish successfully on $3." >> ${LOGFILE}
                return 2  # No dice
        fi
}

# Get row count
# params: $1=username $2=password $3=hostname
function getRowCount() {
        echo "`date "+%a %b%e %T"` - Called function 'getRowCount' on $3." >> ${LOGFILE}
        echo "`date "+%a %b%e %T"` - Verifying table '${TBLNAME}' exists in database '${DBNAME}' on $3." >> ${LOGFILE}
        tableExists $1 $2 $3
        if [ "$?" -eq "0" ] ; then  # Retval 0, table exists
                echo "`date "+%a %b%e %T"` - Running query to obtain row count on table '${TBLNAME}' in database '${DBNAME}' on $3." >> ${LOGFILE}
                local QUERY="SELECT COUNT(id) FROM ${TBLNAME}"
                local RESULT=`mysql -u $1 -p$2 -h $3 ${DBNAME} -Bse "${QUERY}" 2>&1`
                local RETVAL="$?"
                echo "`date "+%a %b%e %T"` - Query on $3 retured the following value: '${RESULT}'" >> ${LOGFILE}
                echo "`date "+%a %b%e %T"` - Exit status of last command on $3 returned: '${RETVAL}'" >> ${LOGFILE}
                if [ "${RETVAL}" -eq "0" -a -n "${RESULT}" -a "${RESULT}" -eq "${RESULT}" 2>/dev/null ] ; then  # Retval 0, check for nonzero string, is numeric, hide stderr
                        echo "${RESULT}"
                        echo "`date "+%a %b%e %T"` - The row count on $3 is: '${RESULT}'" >> ${LOGFILE}
                        echo "`date "+%a %b%e %T"` - Function 'getRowCount' completed successfully on $3." >> ${LOGFILE}
                        return 0  # All good
                else
                        echo "`date "+%a %b%e %T"` - Unable to retrieve row count on $3." >> ${LOGFILE}
                        echo "`date "+%a %b%e %T"` - We either encountered an error code in the SQL data, had a problem connecting to MySQL, or the last command exited with a nonzero status." >> ${LOGFILE}
                        echo "`date "+%a %b%e %T"` - Function 'tableExists' did not finish successfully on $3." >> ${LOGFILE}
                        return 1  # No dice
                fi
        fi
}

# Get table checksum
# params: $1=username $2=password $3=hostname
function getTableChecksum() {
        echo "`date "+%a %b%e %T"` - Called function 'getTableChecksum' on $3." >> ${LOGFILE}
 Continue reading "Bash Script – Script Based MySQL Table Replication on an Array of Slaves"