How to manage a MySQL database

Adding and deleting user accounts

This page gives a description of simple tasks to do with managing user accounts on MySQL.

Create a user

To create a user on MySQL, log in as root and use

mysql> CREATE USER 'name';

Here name is the user name. This creates a user 'name'@'%', where the percent sign indicates the user can log in from any host. You can check the user as follows:

use mysql;
select User, Host from user;

This selects mysql's own database and prints a list of users and the hosts they can log in from.

Set the user password

The newly created user can log in to MySQL without a password, by typing

$ mysql -u name

To create an account with a password, use

mysql> CREATE USER 'name' IDENTIFIED BY 'passwd';

where passwd is the password. Then the user can log in using

mysql -u name -p

MySQL starts and prompts the user for the password.

Change a password

To add or change the password for a user, log in as root and use

mysql> SET PASSWORD FOR 'name' = PASSWORD('passwd');

where name is the user's name, and passwd is the user's password in plain text. This operates on 'name'@'%', the host is the wildcard character %.

mysql> SET PASSWORD = PASSWORD('passwd');

sets the password for the account that you are currently logged in with.

Find the login name

To find out what your current user name is, similar to whoami on Unix, use

mysql> select current_user();

or

mysql> select user();

The second version gives the actual IP whereas the first one gives % in the host column.

Change a user name

To change the user name of an account, log in as root and use

mysql> rename user old_name to new_name;

where old_name is the current user name, and new_name is the name which you want to change to.

Delete a user

To delete a user, use

mysql> DROP USER name;