Jenkins MySQL - auto apply database changes from SCM

A database change apply step for Jenkins pipeline?

When designing for the fully automated deployment pipeline, databases tend to become the bottleneck more often than not. Starting with the complications of storing database changes in the SCM to applying the actual changes with availability and rollback in consideration can be challenging.

Given the uniqueness of each deployment coming up with a single solution for this type of requirement is practically impossible. Having this mind, we will try to come up with a simple set of process and a supporting bash script that can help to automate the database change apply process.

This particular solution was mainly proposed as an automation step for development environment where multiple dev teams are changing the same databases backend. This requires some discipline from the developers while committing the code to the SCM as well. The particular implementations is based on Jenkins MySQL and GitLab as SCM.

The Idea

In a step wise timeline,

1. Dev teams commit the database changes along with code changes. Database changes must be kept at a different folder. (e.g when committing code changes of release 1.1.0, the related database changes should be kept inside a folder with agreed name such as "dbScript")
2. When a change is committed (trigger can be changed as per requirements), the script will check for any changes in sql files (i.e changes to files in "dbScript" folder) and sparse checkout only those changed files to Jenkins workspace.
3. SCM commit hash is captured from Jenkins and used as the unique identified for each of the deployments. This can be useful in any reconciliation as well.
4. Script will initiate a database backup identifiable by commit hash and check backup log for any errors before applying the changes.
5. Applied sql files under each commit which triggered the script run will be stored under Jenkins in an identifiable manner (based on commit hash)
6. All the changes made by the script will be logged in file named under the commit hash.

Although simple these steps can provide the foundation to a Jenkins MySQL CI/CD step that can be flexible and safe even in production pipeline.

Scripted Solution - Jenkins MySQL and GitLab

 ############ Vars  
 workDir=/var/jenkins_home/scripts  
 repoDir=$workDir/lrd_"$GIT_COMMIT"  
 varFile=$workDir/var_"$GIT_COMMIT"  
 sqlFile=$workDir/sql_"$GIT_COMMIT"  
 logFile=$workDir/log_"$GIT_COMMIT"  
 chgFiles=`git diff-tree --no-commit-id --name-only -r "$GIT_COMMIT"`  
 strCheck=dbScript  
 dbHost=192.168.56.22  
 dbUser=mydce #Change to Backup only user  
 dbPasswd=abc123 #Change to File read  
 backLoc=/var/jenkins_home/dbBack  
 failBackLoc=/var/jenkins_home/dbBack/Failed  
 ########### Fun  
 # MySQL Backup Function  
 mysql_backup() {  
 mysqldump -u "$dbUser" -p"$dbPasswd" -h "$dbHost" --all-databases --master-data   
 --log-error=$backLoc/My_Back_$1.log --single-transaction > $backLoc/My_Back_$1_$(date +"%Y_%m_%d_%I_%M_%p").sql  
 #echo "Fail Test" > $backLoc/My_Back_$1.log  
 }  
 # MySQL Apply  
 mysql_apply() {  
 #SQL file changed  
 sed -i -e 's#^#'$repoDir/'#' $sqlFile  
 for i in $(cat $sqlFile)  
 do  
  echo "[Note] Applying to DB "$i"" >> $logFile  
  mysql -vv -u "$dbUser" -p"$dbPasswd" -h "$dbHost" < $i >> $logFile 2>&1  
 done   
 }  
 ############ Scr  
 # List changed Files  
 if [ ! -f $varFile ]  
 then  
   echo $chgFiles > $varFile  
 fi  
 # Send Mail If No Changed Files written  
 if [ -s $varFile ]  
 then  
   echo "[Note] Changed Files written to var_"$GIT_COMMIT"" >> $logFile  
 else  
   echo "[Error] No Changed files recorded by push in var_"$GIT_COMMIT"" >> $logFile  
   #Mail ??  
 fi  
 # List of Changed SQL Files  
 for i in $chgFiles  
 do  
  echo $i | grep $strCheck >> $sqlFile  
 done  
 # Check If DB changes exists  
 if [ -s $sqlFile ]  
 then  
   echo "[Note] Downloading Changed SQL files" >> $logFile  
 # Sparse Check Out Files  
   if [ ! -d $repoDir ]  
   then  
    mkdir $repoDir  
       cd $repoDir  
       git init  
    git remote add origin 'http://oauth2:M9aKD46yHBDcz9z974YU@192.168.56.88/root/mydce.git'  
       git config core.sparseCheckout true  
    git sparse-checkout init  
       cp $sqlFile .git/info/sparse-checkout  
       git --work-tree=$repoDir pull origin dev  
       cd $WORKSPACE  
       echo "[Note] Changed SQL Files downloaded to "$repoDir"" >> $logFile  
       mysql_backup $GIT_COMMIT  
       #Check Backup success  
       if [ -s $backLoc/My_Back_$GIT_COMMIT.log ]  
       then  
         echo "[Error] Issues in Backup, Please check Backup log" >> $logFile  
            mv $backLoc/My_Back_"$GIT_COMMIT"_*.sql $failBackLoc/  
       else  
         mysql_apply  
       fi  
   else  
    echo "[Error] Repo Directory "$repoDir" alredy exists" >> $logFile  
   #Mail Send ??  
   fi  
 else  
   echo "[Note] Commit Does not have DB changes, Exiting" >> $logFile  
 fi  

Although above example is not meant for production use, same can be the starting point for any Jenkins MySQL pipeline deployment project with required changes