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

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:

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 4:

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 5:

A bit hacky, but this will print 1 if foo does not exist, 0 otherwise:

mysqlshow -u root foo 2>&1 >/dev/null | awk ' { print /Unknown database/ }'