OSETC TECH

How to Reset or Change Password for MySQL/MariaDB User

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.

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:

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:

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.