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?

---

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.

---

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!

---

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

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

---

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.

---

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.

---

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

---

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

---

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

---

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

---

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.

---

Following command should do the trick for both the cases,

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

---

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.)

---

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.

---

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

---

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

mysqlshow $DOMAIN %

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

---

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.

---

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

---

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.

---

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."

---

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