From a shell script, how can I check whether a MySQL database exists?

Question

mysqladmin -uroot create foo returns an exit status of 1 if foo exists, and 0 otherwise, but of course it will also create the database if it doesn't already exist. Is there some easy way to simply check whether a database exists?

Solution 1

I realize this was answered a long time ago, but it seems much cleaner to me to do this:

mysql -u root -e 'use mydbname'

If the database exists, this will produce no output and exit with returncode == 0.

If the database does not exist, this will produce an error message on stderr and exit with returncode == 1. So you'd do something like this:

if ! mysql -u root -e 'use mydbname'; then
  ...do stuff to create database...
fi

This operates nicely with shell scripts, doesn't require any processing of the output, and doesn't rely on having local filesystem access.

Solution 2

mysql -e "SHOW DATABASES LIKE 'foo'" should help you.

http://dev.mysql.com/doc/refman/5.1/en/show-databases.html

Solution 3

From http://www.jbmurphy.com/2011/02/08/quick-check-if-a-mysql-database-exists/ this is more akin to what I wanted:

DBNAME="dblookingfor"
DBEXISTS=$(mysql --batch --skip-column-names -e "SHOW DATABASES LIKE '"$DBNAME"';" | grep "$DBNAME" > /dev/null; echo "$?")
if [ $DBEXISTS -eq 0 ];then
    echo "A database with the name $DBNAME already exists."
else
    echo " database $DBNAME does not exist."
fi

Solution 4

Databases in MySQL are folders in the filesystem. That make it damn easy to find whether a database exists:

test -d "/var/lib/mysql/databasename"

In this case, /var/lib is the datadir of MySQL. The nice thing about this snippet is that it won't need a MySQL daemon running, neither does it need credentials. Of course the user running the command must be allowed to descend into that directory.

Solution 5

I think you can check if your needed database working in simple manner in any shell

mysql -uUSERNAME -pPASSWORD DATABASE -e exit

and then check $? for exit code

This command tries your specific credentials (USERNAME and PASSWORD) to connect to selected DATABASE and exit immediately. So, if connection is ok exitcode will be 0, and non-zero otherwise.

Of, course, you can redirect any output to /dev/null if needed

PS. This method is very useful to check health of ephemeral storages which are so fast and popular our days. If database cannot be connected, you should start restore asap.

View more solutions

Comments

[Lolmister]

Thanks. With a few more command-line options it integrates better into shell scripts: mysql --batch --skip-column-names -e "SHOW DATABASES LIKE 'foo'" | grep foo.

[Mani]

perfect way to check it.

[PeterM]

For anyone who might want to suppress the error message, try this (of course, consider taking your password out for security): if ! mysql -u <user> -p<pw> -e 'use <db name>' 2> /dev/null; then mysql -u <user> -p<pw> -e 'CREATE DATABASE <db name>;'; fi