How to Reset MySQL Root User Permissions and Password in Linux
Sometime ago, I tampered with my MySQL root permission; I was unable to grant permission to new users, which kept me wondering why I am the root user or is there any other person? :/
In this post, I will show you have to restore your MySQL root permission which might have been tampered, and also resetting your forgotten password. Lets start with resetting the password.
Recovery Steps
- Stop all instance of
mysqlon the system$ sudo systemctl stop mysql
- Start MySQL safe instance with this command
$ sudo mysqld_safe --skip-grant-tables
and then execute
$ sudo mysql
You should have something like this:
123456789Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 2Server version: 10.1.21-MariaDB-5+b1 Debian 9.0Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]>
Resetting MySQL Root Password
Now now reset the root password by executing this query
USE mysql;
UPDATE user SET password=PASSWORD( 'new_password' ) WHERE User='root';
Then flush privileges by executing
FLUSH PRIVILEGES;
Restore Permissions
If you also lost your root permission, this step is for restoring it back
- Still in same the
mysqlconsole, use same starting step of resetting password and make sure you execute this queryFLUSH PRIVILEGES;
- Then execute this to restore permission for
rootGRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
- Exit the console by typing
EXIT
Now kill all instance of MySQL safe and start the real MySQL
$ sudo killall mysqld $ sudo systemctl start mysql
Login into your MySQL and all fixed 🙂
James John
Software Engineer