Checking if mysql user exists

How can I check if a user exists?

Im doing an installer for a mysql database, and I need to check if a user ex开发者_如何学Cits, if not create user, if yes delete user and create it again.

this so i can execute the script without worries.

thanks.

---

MySQL stores user data in a table called user in a database named mysql (by default). The following query will return 1 if a user with the specified username exists, 0 otherwise.

SELECT EXISTS(SELECT 1 FROM mysql.user WHERE user = 'username')

---

If you're deleting the MySQL user anyways, then there's really no need to check if it exists first. MySQL won't throw any errors if there's nothing to delete:

DELETE FROM mysql.user WHERE User = 'username';

---

As newer versions of MySQL allow this option:

DROP USER IF EXISTS 'username'@'host';

---

When in need to check if a user exists without deleting it (for instance when just skipping some instructions instead of executing them in any case), one can use this (where $USER is the user to check):

if [ $(echo "SELECT COUNT(*) FROM mysql.user WHERE user = '$USER'" | mysql | tail -n1) -gt 0 ]
then
  echo "User exists"
else
  echo "User doesn't exist"
fi

NB:

• mysql command requires extra args and/or configuration for authentication)
• tail -n1 is used for removing the query result header

---

MySQL lacks DROP USER IF EXISTS construct.

A good workaround is to grant a harmless privilege to the user before dropping it. This will create the user if it doesn't exist, so that it can be dropped safely, like so:

GRANT USAGE ON *.* TO 'username'@'localhost';
DROP USER 'username'@'localhost';
FLUSH PRIVILEGES;

USAGE actually means no privilege.

Source: http://bugs.mysql.com/bug.php?id=19166

---

This is how I was able to do it:

#!/usr/bin/env bash

set -o errexit
set -o nounset

# Create credentials file
echo -e "[client]\nuser=root\npassword=${MYSQL_ROOT_PASSWORD}" > ~/.my.cnf

# Create standard user and grant permissions
if ! echo "SELECT COUNT(*) FROM mysql.user WHERE user = 'myuser';" | mysql | grep 1 &> /dev/null; then
    echo "Creating database user ..."
    echo "CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
          GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost';
          FLUSH PRIVILEGES;" | mysql
else
    echo "Database user already created. Continue ..."
fi

Change myuser, mydatabase and mypassword accordingly.

---

MySQL 5.7 already includes DROP USER IF EXISTS, but for older versions I use pt-show-grants --drop from percona-toolkit and feed back the DROP USER part to mysql:

pt-show-grants --drop --only=$username | grep '^DROP USER' | mysql -v

If there are multiple username-hostname pairs this removes all of them.