How to Access a Database in Linux

Back to: Linux

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.

Configuration

1. Defile the database configuration parameters like the config.yaml file

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

This shell scripting code sets variables for the database name, host, user, and password to be used in accessing a MySQL/MariaDB database using Linux shell scripting.

The fourth line sets the variable DB_PASSWD to the base64-encoded password that will be used to authenticate the user.

To generate a base64-encoded password, you can use the echo command to input your password (without the quotes) and pipe it to the base64 command, which will output the encoded string.

📝Note: It’s important to keep the password secure by not storing it in plain text in the script. By using base64 encoding, the password is not readable in plain text and provides some level of security. However, it’s still important to take additional security measures, such as limiting access to the script file and the database itself, to ensure the password is not compromised.

Making Database Connection and Error Handling

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
  
}

Test or Run the Database Queries

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