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?

Answer 1

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

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

Answer 2

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.

Answer 3

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/ }'

Answer 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. exiting" exit; fi

Answer 5

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.