This post will guide you how to create, change or reset MySQL or MariaDB user password under Linux operating system terminal. For a Linux new user or MySQL new user, how to reset a password for a normal user in the MySQL server.
- Creating New MySQL User
- Granting Privilege for New MySQL User
- Changing or Reset Password for MySQL User
- Recover Your MySQL Password
- Delete Your MySQL User
MySQL is an open-source relational database management system. Its name is a combination of “My”, the name of co-founder Michael Widenius’s daughter, and “SQL”, the abbreviation for Structured Query Language.
Creating New MySQL User
Assuming that you have installed MySQL server package on your current Linux operating system. And then you want to create your first normal MySQL User, just do the following steps:
Step1: login to the MySQL server with the following command in the Linux terminal:
$ mysql -u root -p
Then you need to type the password of root for authentication.
Outputs:
root@devops:~# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.26-0ubuntu0.18.04.1-log (Ubuntu) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
Step2: switch to the MySQL database, type the following SQL statements at the mysql> prompt to create a new user called “Jeffery“.
Mysql> user mysql; Mysql> create user 'jeffery'@'localhost' identified by 'password';
Outputs:
mysql> create user 'jeffery'@'localhost' identified by 'password'; Query OK, 0 rows affected (0.01 sec)
Note:
- Jeffery is the user name that you want to create new MySQL user.
- Localhost: this user can only allow to connect to this MySQL server from localhost, if you want to allow remote host to connect to this MySQL server, just change localhost to a pattern character %.
- Password is the password of your new MySQL user.
Step3: you can run the following select statement to check if “Jeffery” user is created or not.
Mysql> select user from mysql.user
Outputs:
mysql> select user from mysql.user;
+------------------+
| user |
+------------------+
| replica |
| debian-sys-maint |
| jeffery |
| joomlauser |
| mysql.session |
| mysql.sys |
| root |
+------------------+
7 rows in set (0.00 sec)
You would see that “Jeffery” user is created.
Granting Privilege for New MySQL User
If you want to grant more privileges for your newly created MySQL user “Jeffery”, you can use the following SQL statement syntax:
Grant privileges on database.tablename to ‘username’@’host’ indentified by ‘password’;
Note:
- Privileges: containing select, insert, update privileges, if you want to grant all privileges for user, just using “ALL” keyword.
- Databasename: can only operate this specified database.
- Tablename: can only operate this specified table in the above database.
For example, if you want to grant all privileges for all databases, just execute the following SQL statement:
Mysql> grant all on *.* to 'jeffery'@'%' identified by 'password';
Outputs:
mysql> grant all on *.* to 'jeffery'@'%' identified by 'password'; Query OK, 0 rows affected, 1 warning (0.00 sec)
Then you still need to flush privileges with the following command:
Mysql> flush privileges;
Changing or Reset Password for MySQL User
If you want to change or reset password for a MySQL User called “Jeffery”, just use the following SQL statement:
Mysql> set password for 'jeffery'@'localhost' = PASSWORD('newpassword');
And if you want to change the current logging user, you can also use another simple SQL statement:
Mysql> set password=PASSWORD(“newpassword”);
Recover Your MySQL Password
If you forgot your MySQL root user password, how to recover the password, just do the following these steps:
Step1: you need to update mysqld.cnf configuration file located in /etc/mysql/mysql.conf.d/ directory with vim text editor, and then add the following lines under [mysqld] section.
$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
skip-grant-tables
save and close the file.
Step2: restart the MySQL server with the following command :
$ sudo systemctl restart mysql
Step3: login to the MySQL server, press Enter key on your keyboard, it will not prompt for a password. type:
$ mysql -u root -p
Step4: you need to run the following MySQL commands to reset the root password:
Mysql> use mysql;
Mysql> update user set authentication_string=password('NEWPASSWORD') where user='root';
Mysql> flush privileges;
Delete Your MySQL User
If you want to remove a normal user called “Jeffery” from your MySQL server, you can use the following MySQL command:
Mysql> drop user Jeffery;
Outputs:
mysql> drop user jeffery; Query OK, 0 rows affected (0.01 sec)
Then you can run the select command to check if it is deleted.
Mysql> select user from mysql.user;
Outputs:
mysql> select user from mysql.user; +------------------+ | user | +------------------+ | replica | | debian-sys-maint | | joomlauser | | mysql.session | | mysql.sys | | root | +------------------+ 6 rows in set (0.00 sec)
Conclusion
You should know that how to create MySQL user, grant privileges for MySQL user, or recovery password for root user under Linux system.