A guide to show how to manage MySQL user accounts: create, rename or delete a MySQL user; grant privileges; change user password.
1 Login
Login MySQL in command line.
~$ 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 |
+--------------------+
Login and switch to a specified database using -D
(—database
) option.
~$ 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
Or
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
Before MySQL 5.7.6:
mysql> UPDATE mysql.user SET Password=PASSWORD('your_new_password') WHERE user='root';
mysql> FLUSH PRIVILEGES;
For MySQL 5.7.6+:
mysql> UPDATE mysql.user SET authentication_string=PASSWORD('your_new_password') WHERE user='root';
mysql> FLUSH PRIVILEGES;
8.4 Remove —skip-grant-tables and restart MySQL
If you changed MySQL configuration file my.cnf
in step one, please change it back by removing skip-grant-tables
line.
Then restart MySQL and you can login as root using your new root password now.
Comments