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?

score 50 · Accepted Answer · answered Apr 20 '12 at 18:56

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.

For anyone who might want to suppress the error message, try this (of course, consider taking your password out for security): if ! mysql -u -p -e 'use ' 2> /dev/null; then mysql -u -p -e 'CREATE DATABASE ;'; fi — PeterM, Aug 26 '16 at 17:32

score 11 · Answer 2 · answered Aug 24 '10 at 17:16

11

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

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

answered Aug 24 '10 at 17:16

Dom

6,743
1
20
24

5

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

Lekensteyn · Answer 3 · 2012-04-21T09:49:22.560

7

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.

edited Apr 21 '12 at 09:49

answered Aug 24 '10 at 17:49

Lekensteyn

6,241
6
39
55

score 7 · Answer 4 · edited Jun 10 '14 at 20:12

7

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

edited Jun 10 '14 at 20:12

Slyx

103
3

answered Mar 21 '12 at 03:32

Rob Barreca

171
1
1

perfect way to check it. – Mani Feb 27 '16 at 07:15

score 2 · Answer 5 · answered Aug 24 '10 at 17:31

2

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

answered Aug 24 '10 at 17:31

Donald

341
2
4

score 2 · Answer 6 · edited Jan 25 '16 at 23:52

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.

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

6 Answers6