Home / server / Questions / 173978
Accepted
ithinkihaveacat
Asked: 2010-08-25 09:10:19 +0800 CST

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

  • 772

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?

mysql bash database shell

6 Answers

  1. Best Answer

    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.

    • 49

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

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

    • 11

  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.

    • 7

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

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

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

    • 2