Checking MySQL connection status

If you are writing a shell script which requires importing / exporting data to / from MySQL database, sometimes, it is important to check whether the username and password are still valid.

To do this, we can check for MySQL exit status. If connection is successful, exit status is 0. If connection failed, exit status is 1.

[root@localhost ~]# mysql --user=root -e exit
[root@localhost ~]# echo $?
0
[root@localhost ~]# mysql --user=root --password=oldpassword -e exit
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@localhost ~]# echo $?
1

The following is an example how we can implement this in our bash script.

#!/bin/bash

  dbuser="root"
  dbpass="oldpassword"

  dbaccess="denied"
  until [[ $dbaccess = "success" ]]; do
    echo "Checking MySQL connection..."
    mysql --user="${dbuser}" --password="${dbpass}" -e exit 2>/dev/null
    dbstatus=`echo $?`
    if [ $dbstatus -ne 0 ]; then
      echo -e "MySQL Username [$dbuser]: \c "
      read dbuser
      # Check if user field is empty
      test "${dbuser}" == "" && dbuser="root";
      echo -e "MySQL Password: \c "
      read -s dbpass
      echo
    else
      dbaccess="success"
      echo "Success!"
    fi
  done

Output:

[root@localhost ~]# ./check_mysql_login.sh
Checking MySQL connection...
MySQL Username [root]:
MySQL Password: (wrong password)
Checking MySQL connection...
MySQL Username [root]: root
MySQL Password: (correct password)
Checking MySQL connection...
Success!
[root@localhost ~]#