MySQL Script – Alter/Convert All Tables in a Database from MyISAM to InnoDB

Pieced together from various tutorials on the web with a lot of my own additions, here is a snippet of SQL that will alter or convert all tables in a database from “MyISAM” to “InnoDB”. As long as your MySQL user account has privileges to create stored procedures, all you have to do is simply edit the @DATABASE_NAME variable at the top of the script and run it in PhpMyAdmin if you’ve got it. Enjoy! Continue reading “MySQL Script – Alter/Convert All Tables in a Database from MyISAM to InnoDB”

Ubuntu Server – Apache 2.4 Upgrade Changes

As many of you may have noticed, upgrading to Apache 2.4 can either cause the “apache2” service to fail to start altogether or may render your websites more or less inoperable. Luckily, there are only a few minor changes to look for that will hopefully get you back up and running with a minimal period of downtime.

  1. During the upgrade, if you decided to keep your original versions of your config files like I did (always a good idea), you will then end up with some extra files with “.dpkg-dist” on the end of the filename. These are the latest version of the config files. As a best practice with any major config file changes, I would suggest to create a backup directory and get in the habit of copying any files with the date in the filename before you make changes. There have been many instances where this has come back to bite me in the past and I have learned my lesson over the years so save yourself some time, stress, and headaches.
    cd /etc/apache2
    mkdir backups
    mv apache2.conf backups/apache2.conf.09_25_2014
     Continue reading "Ubuntu Server – Apache 2.4 Upgrade Changes"

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"