MySQL Tutorial: MySQL User Management

A guide to show how to manage MySQL user accounts: create, rename or delete a MySQL user; grant privileges; change user password.

1 Login

~$ mysql -h 127.0.0.1 -P 3306 -u root -p

Omit the default 3306 port:

~$ mysql -h 127.0.0.1 -u root -p

Also can ignore -h host option if your MySQL was installed on the same server.

~$ mysql -u root -p
~$ mysql -uroot -p

You can input login password directly together with the command. Note that please omit the spaces between the -p option and your password string.

~$ mysql -h localhost -u root -p123

The -e option allow user to login and execute a MySQL command at the same time.

~$ mysql -h localhost -u root -p -e 'SHOW DATABASES'
Enter password: 

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

~$ mysql -u root -p -D mysql
Enter password: 

mysql> 
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

2 Create User

There are two common ways to create a new user account in MySQL, using CREATE USER syntax or GRANT syntax.

2.1 CREATE USER

mysql> CREATE USER 'tom'@'localhost' IDENTIFIED BY 'password_of_tom';
mysql> CREATE USER 'jerry'@'192.168.1.101' IDENTIFIED BY 'password_of_jerry';
mysql> FLUSH PRIVILEGES;

Wildcard is supported in host IP address or domain name using % character.

mysql> CREATE USER 'bob'@'%' IDENTIFIED BY 'password_of_bob';
mysql> CREATE USER 'alice'@'192.168.1.%' IDENTIFIED BY 'password_of_alice';
mysql> FLUSH PRIVILEGES;

Tips: You can inspect detailed statement that creates the named user by SHOW CREATE USER clause.

mysql> SHOW CREATE USER 'root'@'localhost';
mysql> SHOW CREATE USER 'tom'@'localhost';

2.2 GRANT

You can create a new user and grant privileges to the new user at the same time.

mysql> GRANT SELECT,INSERT,UPDATE ON sampledb.* TO 'jack'@'localhost' IDENTIFIED BY 'password_of_jack';
mysql> FLUSH PRIVILEGES;

Tips: Don't forget to flush privileges after executing CREATE USER or GRANT command.

3 Delete user

mysql> DROP USER 'tom'@'localhost';
mysql> FLUSH PRIVILEGES;

4 Current user

Show the current MySQL user account.

mysql> SELECT USER();
+----------------+
| USER()         |
+----------------+
| [email protected] |
+----------------+
1 row in set (0.00 sec)

5 Rename user

Rename a MySQL user.

mysql> RENAME USER 'jerry'@'localhost' TO 'jim'@'127.0.0.1';
mysql> FLUSH PRIVILEGES;

6 GRANT

6.1 Grant privileges

For example, grant select, insert and update privileges of all tables in sampledb database to the user [email protected].

mysql> GRANT SELECT,INSERT,UPDATE ON sampledb.* TO 'tom'@'localhost' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

With the WITH GRANT OPTION clause above, [email protected] will get the ability to give other users any privileges owned by himself at the specified privilege level. In simple words, [email protected] can grant his owned privileges to other users.

Grant all privileges:

mysql> GRANT ALL PRIVILEGES sampledb.* TO 'jack'@'localhost';
mysql> FLUSH PRIVILEGES;

Wildcard of all tables in all databases can be accomplished using *.*:

mysql> GRANT SELECT,INSERT,UPDATE ON *.* TO 'tom'@'localhost' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

6.2 Show grants

To inspect privileges of the current user:

mysql> SHOW GRANTS FOR CURRENT_USER;

or for simplicity:

mysql> SHOW GRANTS;

+---------------------------------------------------------------------+
| Grants for [email protected]                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

To view privileges of a specified user:

mysql> SHOW GRANTS FOR 'root'@'localhost';
mysql> SHOW GRANTS FOR 'tom'@'localhost';

6.3 Revoke privileges

Revoke privileges from a user.

mysql> REVOKE INSERT,UPDATE,DELETE ON sampledb.* FROM 'tom'@'localhost';
mysql> FLUSH PRIVILEGES;

Revoke all privileges from a user.

mysql> REVOKE ALL PRIVILEGES FROM 'tom'@'localhost';
mysql> FLUSH PRIVILEGES;

Revoke all privileges and GRANT OPTION from a user.

mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'tom'@'localhost';
mysql> FLUSH PRIVILEGES;

7 Change password

7.1 Change root password using mysqladmin command

If you have a fresh installation of MySQL and the root accout has no password yet, you can set password for the root user by mysqladmin command.

~$ mysqladmin -u root password "new_password"

You can also change the old password of the root user.

~$ mysqladmin -u root -p password "new_password"

Or pass the old password directly (just for example, the old passworld is 123456).

~$ mysqladmin -u root -p123456 password "new_password"

7.2 Change general user password

7.2.1 Current user

Before MySQL 5.7.6, you can change password of the current user using SET PASSWORD clause.

mysql> SET PASSWORD=PASSWORD("your_new_password");
mysql> FLUSH PRIVILEGES;

For MySQL 5.7.6+, you should use ALTER USER clause.

Firstly, get the current user.

mysql> SELECT USER();

Then change the password of the current user.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY "your_new_password"
mysql> FLUSH PRIVILEGES;

7.2.2 A specified user

Before MySQL 5.7.6:

mysql> SET PASSWORD FOR 'jerry'@'localhost'=PASSWORD("your_new_password");
mysql> FLUSH PRIVILEGES;

For MySQL 5.7.6+:

mysql> ALTER USER 'jerry'@'localhost' IDENTIFIED BY "your_new_password"
mysql> FLUSH PRIVILEGES;

8 Forget MySQL root password

If you forget MySQL root password, it's easy to reset the root password using following four steps.

8.1 Restart MySQL with —skip-grant-tables

You have two optional ways to start MySQL with —skip-grant-tables option.

The first way is to specify —skip-grant-tables directly in command line.

For Windows,

mysqld —skip-grant-tables

net start mysql —skip-grant-tables

For Linux,

mysqld_safe —skip-grant-tables

The second way is to edit MySQL configuration file my.cnf and add skip-grant-tables to its mysqld section.

# ...
[mysqld]
# ...
skip-grant-tables
# ...

Then restart MySQL.

8.2 Login as root without password

~$ mysql -uroot

8.3 Reset root password