Database Update Script

2017-01-01

This is a script for upgrading schema versions on a database.

Requirements

• a folder of sql scripts with filenames in order
• table in database called _system_ with column called schema-version

#!/bin/bash
export MYSQL_PWD=mypassword
DATABASE=mydatabase
SERVER=myhost
UPDATE_FOLDER=myfolder

cat <<EOF
**************************
*   $DATABASE  UPDATER   
**************************
EOF

for f in $(ls -1 $UPDATE_FOLDER | sort)
do
    CURRENT_VERSION=$(mysql -sN $DATABASE -h $SERVER -u root -e "SELECT \`schema-version\` FROM _system_" 2> /dev/null)
    NEXT=$(basename $f)

    if [ "$CURRENT_VERSION" -ge "$NEXT" ] ; then
        continue;
    fi

    echo "[$CURRENT_VERSION] -> [$NEXT]"

    echo "Creating Backup..."

    echo "DROP DATABASE $DATABASE;" > $CURRENT_VERSION.bak.sql
    echo "CREATE DATABASE $DATABASE;" >> $CURRENT_VERSION.bak.sql
    echo "USE $DATABASE;" >> $CURRENT_VERSION.bak.sql
    mysqldump -q --routines --skip-triggers -h $SERVER -u root $DATABASE  >> $CURRENT_VERSION.bak.sql
    EXITCODE=$?

    if [ $EXITCODE -ne 0 ] ; then
        echo "Could not backup database: review backup error and try again";
        exit;
    fi

    echo "Running Update..."
    mysql -sN $DATABASE -h $SERVER -u root -e "source /root/updates/$NEXT"
    EXITCODE=$?

    if [ $EXITCODE -ne 0 ] ; then

        echo "Attempting to restore last backup";

        mysql -sN $DATABASE -h $SERVER -u root -e "source $CURRENT_VERSION.bak.sql"
        EXITCODE=$?

        if [ $EXITCODE -ne 0 ] ; then
            echo "Restore failed: database in possible inconsistent state, review database and restore manually"
            exit;
        else
            echo "Restore Successful: review error and try again"
            exit;
        fi

    else
        echo "Update Complete"
        mysql -sN $DATABASE -h $SERVER -u root -e "UPDATE _system_ SET \`schema-version\` = $NEXT;"
        rm $CURRENT_VERSION.bak.sql
    fi

done

---

mysql   database   version   upgrade   script