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.

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

mysql> CREATE USER name;

Here name is the user name.

To delete a user, use

mysql> DROP USER name;

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.

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

mysql> select current_user();

mysql> select user();

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

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.

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.

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

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