Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

6 Answers

up vote 6 down vote accepted

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

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

share|improve this answer
4  
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. –  ithinkihaveacat Aug 29 '10 at 10:49
    
Thanks, I didn't know the --batch option ! –  Dom Aug 30 '10 at 8:58
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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/ }'
share|improve this answer
add comment

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
share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.