CODEWITHSUNDEEP
mysqllinuxbash
David
David

Reputation: 1805

Check if mysql database exists, perform action based on result

Is it possible from within a bash script to check if a mysql database exists. Depending on the result then perform another action or terminate the script?

Upvotes: 40

Views: 58644

Answers (20)

Cfreak
Cfreak

Reputation: 19309

Use the -e option to the mysql command. It will let you execute any query (assuming the right credentials).

This may be an example:

if mysql "DATABASE_NAME" -e exit > /dev/null 2>&1; then
    echo "Exists"
else
    echo "Not exists"
fi

Upvotes: 6

Theo Balkwill
Theo Balkwill

Reputation: 81

Here's how i did it inside a bash script:

#!/bin/sh

DATABASE_USER=*****
DATABASE_PWD=*****
DATABASE_NAME=my_database

if mysql -u$DATABASE_USER -p$DATABASE_PWD -e "use $DATABASE_NAME";
then
echo "Database $DATABASE_NAME already exists. Exiting."
exit
else
echo Create database
mysql -u$DATABASE_USER -p$DATABASE_PWD -e "CREATE DATABASE $DATABASE_NAME"
fi

Upvotes: 0

Jean-Christophe Meillaud
Jean-Christophe Meillaud

Reputation: 2071

mysqlshow is a good tool for this, here is test to check the presence of the database database_name

if mysqlshow -p${MYSQL_ROOT} 2>/dev/null| grep -q "database_name"
then
    echo "Database exist."
else
    echo "Database does not exist."
fi

Or a simple oneliner

echo "Database "`mysqlshow -p${MYSQL_ROOT} 2>/dev/null| grep -q "database_name"  || echo "does not "`"exist."

Upvotes: 0

user2234372
user2234372

Reputation: 54

Also you can ask to use the database and then handle the exit code.

$ if mysql -uroot -pxxx -e "USE mysql"; then echo "exists"; fi
exists

$ if mysql -uroot -pxxx -e "USE doesnotexist"; then echo "exists"; fi
ERROR 1049 (42000) at line 1: Unknown database 'doesnotexist'

Or inspect $? after the call.

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562563

I give +1 to answer by @chown, but here's another alternative: If the bash script is running locally with the MySQL instance, and you know the path to the datadir, you can test:

if [ -d /var/lib/mysql/databasename ] ; then 
    # Do Stuff ...
fi

This also assumes your shell user running the script has filesystem-level privileges to read the contents of the MySQL datadir. This is often the case, but it is not certain.

Upvotes: 45

Jared Brandt
Jared Brandt

Reputation: 213

FWIW, the auth_socket plugin makes this much easier. The question may be super old, but there are still people like me coming here for inspiration.

If your script is running as root, you can do this:

DBNAME="what_you_are_looking_for"
DBEXISTS="$(mysql -u root -e "show databases like '$DBNAME'" --batch --skip-column-names)"

If the database exists, then $DBNAME = $DBEXISTS.

If the database does not exist, then $DBEXISTS = "".

Both should have an exit status of 0, so you can still use non-zero statuses to report errors, rather than letting a non-existent database appear as an error.

Upvotes: 0

Adambean
Adambean

Reputation: 1161

If it helps, I did this for MariaDB on Debian Stretch:

DB_CHECK=$(mysqlshow "${DB_NAME}" | grep "Unknown database") 1> /dev/null
if [ ! -z "${DB_CHECK}" ]; then
    echo "Database found."
else
    echo "Database not found."
fi

Short explanation: The result of mysqlshow for database name in variable $DB_NAME is checked for "Unknown database". If that string is found it's put into variable $DB_CHECK. Then finally the -z comparison checks if the $DB_CHECK variable is empty.

If $DB_CHECK is empty then "Unknown database" did not appear in the mysqlshow response. Probably not 100% reliable, like if the connection failed or whatever. (I've not tested that.)

Upvotes: 1

gurel_kaynak
gurel_kaynak

Reputation: 554

Here is an alternate version: 

 RESULT=`mysql -u$USER -p$PASSWORD -e "SHOW DATABASES" | grep $DATABASE`
 if [ "$RESULT" == "$DATABASE" ]; then
    echo "Database exist"
 else
    echo "Database does not exist"
 fi

IF there is a DB named abcd and we use -Fo after grep then for the search result of DB a/ab/abc the script will show the result Database exist.

Upvotes: 7

arz.freezy
arz.freezy

Reputation: 688

Another solution without grep:

FOUND_DATABASES=`MYSQL_PWD="${DB_PASSWORD}" mysql \
 -u "${DB_PASSWORD}" \
 --skip-column-names \
 --batch \
 -e "SHOW DATABASES LIKE '${DB_NAME}'" | wc -l`

FOUND_DATABASES:

  • 0 - there is no such database
  • 1 - the database was found

Notes:

  • MYSQL_PWD to disable the warning:

    mysql: [Warning] Using a password on the command line interface can be insecure.

  • --skip-column-names to hide columns

  • --batch to disable borders like +-----------+

Upvotes: 4

gtd
gtd

Reputation: 17246

The mysqlshow path requires parsing the output (at least for the version of mysql I have) because it always returns success. Dale makes a very good point about differentiating between failures.

However, if you know that everything is running and you have correct credentials, etc, and you want to tell only whether the DB exists are not you can do it in one line with a blank sql command:

> mysql -uroot -ppassword good_db -e ''
> echo $?
0
> mysql -uroot -ppassword bad_db -e ''
ERROR 1049 (42000): Unknown database 'busker_core_locala'
> echo $?
1

Upvotes: 0

Dale C. Anderson
Dale C. Anderson

Reputation: 2460

It's easy enough to reliably tell if the database exists with mysqlshow. The trick is being able to reliably tell the difference between a database not existing, or some other failure. The version of mysqlshow I have exits with a '1' in either case, so it can't tell.

Here's what I came up with to handle it. Adjust your mysqlshow command accordingly, or put your credentials in to a chmod 600'd ~/.my.cnf file.

This works on Ubuntu 12 + 14. I haven't tested it in other environments yet:

#!/bin/bash -u

# Takes 1 argument. Aborts the script if there's a false negative.
function mysql_db_exists () {
  local DBNAME="$1"
  # Underscores are treated as wildcards by mysqlshow.
  # Replace them with '\\_'. One of the underscores is consumed by the shell to keep the one mysqlshow needs in tact.
  ESCAPED_DB_NAME="${DBNAME//_/\\\_}"
  RESULT="$(mysqlshow "$ESCAPED_DB_NAME" 2>&1)"; EXITCODE=$?
  if [ "$EXITCODE" -eq 0 ]; then
    # This is never a false positive.
    true
  else
    if echo "$RESULT" | grep -iq "Unknown database"; then
      # True negative.
      false
    else
      # False negative: Spit out the error and abort the script.
      >&2 echo "ERR (mysql_db_exists): $RESULT"
      exit 1
    fi
  fi
}

if mysql_db_exists "$1"; then
  echo "It definitely exists."
else
  echo "The only time you see this is when it positively does not."
fi

Upvotes: 2

isyutaro
isyutaro

Reputation: 29

Following command should do the trick for both the cases,

mysqlshow "DB_NAME" &> /dev/null && echo "YES" || echo "NO"

Upvotes: 1

Michael Litvin
Michael Litvin

Reputation: 4126

mysql_user=<you_db_username>
mysql_pass=<you_db_passwrod>
target_db=<your_db_name>
if [ "`mysql -u${mysql_user} -p${mysql_pass} -e 'show databases;' | grep ${target_db}`" == "${target_db}" ]; then
  echo "Database exist"
else
  echo "Database does not exist"
fi

This executes a MySQL query to get all DB names, then greps to check that the required database exists.

Upvotes: 0

Pubudu
Pubudu

Reputation: 994

I also used a slightly different version from chown's.

result=$(mysqlshow --user=root --password=12345 dbname | grep -v Wildcard | grep -ow dbname)

The above executes the given command and assigns the returned value to result. And the w option matches dbname exactly.

Upvotes: 1

busy
busy

Reputation: 369

mysqlshow will not show underscore characters '_' in the database name.

mysqlshow $DOMAIN %

https://dev.mysql.com/doc/refman/5.1/en/mysqlshow.html

Upvotes: 0

Fedir RYKHTIK
Fedir RYKHTIK

Reputation: 9994

if [ $(mysqlshow DB 1>/dev/null 2>/dev/null) -eq 0 ]; then
    echo "DB found"
fi

Upvotes: 0

mattes
mattes

Reputation: 9429

mysqlshow "test" > /dev/null 2>&1 && echo "Database exists."

Depending on the exit status of the mysqlshow command, it will execute the following echo.

Upvotes: 20

Matthieu Napoli
Matthieu Napoli

Reputation: 49623

I couldn't get the accepted answer work for me (the grep in the quotes didn't work), so here is my version:

RESULT=`mysql -u $USER -p$PASSWORD --skip-column-names -e "SHOW DATABASES LIKE 'myDatabase'"`
if [ "$RESULT" == "myDatabase" ]; then
    echo "Database exist"
else
    echo "Database does not exist"
fi

I used the option --skip-column-names to remove the column names from the result.

Upvotes: 13

chown
chown

Reputation: 52778

Example script (Thanks to Bill Karwin for the --user and --password comment!):

#!/bin/bash
## --user=XXXXXX --password=XXXXXX *may* not be necessary if run as root or you have unsecured DBs but
##   using them makes this script a lot more portable.  Thanks @billkarwin
RESULT=`mysqlshow --user=XXXXXX --password=XXXXXX myDatabase| grep -v Wildcard | grep -o myDatabase`
if [ "$RESULT" == "myDatabase" ]; then
    echo YES
fi

These are what the commands look like when run at a prompt:

[root@host ~]# mysqlshow myDatabase
Wildcard: myDatabase
+------------------+
|    Databases     |
+------------------+
| myDatabase       |
+------------------+

If no DB exists, the output will look like this:

[root@host ~]# mysqlshow myDatabase
Wildcard: myDatabase
+-----------+
| Databases |
+-----------+
+-----------+

Then, parse the output and do what you need to based on if it exists or not!

Upvotes: 39

ajreal
ajreal

Reputation: 47321

YES

for db in $(mysql -u -p -N <<<"show databases like '%something%'")
do
  case $db in 
    "something")
      // do something
    ;;
    "something else")
      // do something else
    ;;
  esac
done

Upvotes: 4

Related Questions