How to Access Database(MySQL/MariaDB) in Linux Shell Scripting

In this lesson, we are going to see how to access MySQL or MariaDB database properly with error handling in Linux shell scripting. Let’s start together..

We will have three different files, config.yaml for saving the database configurations, perform_db_operation.sh for having different functions for the validations of the database configuration parameters and running the actual database queries, and finally test.sh file to call the functions available in the perform_db_operation.sh file. Save the following files with the content in the same working directory.

1. Database configurations: config.yaml

DB_NAME='<your-database-name>'
DB_HOST='127.0.0.1' #your database host
DB_USER='root' #Database user

#paste your base64 encripted password 
#To genearte base64 encripted password, run command: echo -n "your-password" | base64
DB_PASSWD='<encripted password>'

2. Validation of database configuration parameters and performing database operations: perform_db_operation.sh

#!/bin/bash

#current working directory
CWD="$(dirname "$0")"

#Check if the config.yaml file exists
if [ ! -f $CWD/config.yaml ]; then
	echo "Error:: No configuration file is found at the '$CWD' directory."
	exit 1
fi

#Load the config.yaml
source $CWD/config.yaml

DB_NAME=$DB_NAME
DB_HOST=$DB_HOST
DB_USER=$DB_USER
DB_PASSWD=$DB_PASSWD

echo "Database name: $DB_NAME"

#validate database connection parameters
function checkConnectionParam {
if [ -z "$DB_HOST" ]; then
	echo "Error:: No database host is supplied."
	exit 1
fi

if [ -z "$DB_NAME" ]; then
	echo "Error:: No database name is supplied."
exit 1
fi

if [ -z "$DB_USER" ]; then
	echo "Error:: No database username is supplied."
exit 1
fi

if [ -z "$DB_PASSWD" ]; then
	echo "Error:: No database password is supplied."
exit 1
else
  #Converting base64 encripted password to the original password
  DB_PASS_ORG=`echo "$DB_PASSWD" | base64 --decode`
  #echo "$DB_PASS_ORG"   
fi

}

# run database queries
function runQuery {
  #checking/validating the database connection param
  checkConnectionParam
  
  QUERY=$1
  if [ -z "$QUERY" ]; then
	echo "Error:: No database query string is supplied!"
  exit 1
  fi

  mysql -u"$DB_USER" -p"$DB_PASS_ORG" --database $DB_NAME -se "$QUERY" | while read -r result;
  do
  #Echo/Return the query results
    echo "$result"
  done
  
}

3. Test or run the database queries by calling the functions from the file perform_db_operation.sh using the test.sh file:

#!/bin/bash

#current working directory
CWD="$(dirname "$0")"

. ./perform_db_operation.sh

#Call the runQuery function from the perform_db_operation.sh file
#show all the available database table
tables=$(runQuery "show tables")

#loop through the tables field and print all database tables
for table in "${tables[@]}"
do
   echo "$table"
done

#Get all the rows from a table, for example account table
all_vals=$(runQuery "select * from account")

for val in "${all_vals[@]}"
do
   echo "$val"
done

Finally run the test.sh file to test/use the database and see the output:

$ bash test.sh

Also see file manipulation and how to Find file in Linux . See complete examples in our GitHub repository.

Follow us on social media

Author