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