MySQL 8 contains new feature and improvement , so does the technic how to reset if you forget you root password for some reason. There are 2 methods on how to reset and here is how.
Using Init file
Shutdown mysql service if the service is running
systemctl stop mysqld.services
Now create init file
nano /opt/init.txt
Copy paste the following command and save it.
ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_preferred_password';
Now run the following command
mysqld --user=mysql --init-file=/home/user/init-file.txt --console
The command above will start MySQL service and will also execute init file you have created to update the root password in the process.
Now stop MySQL service again and start it normally.
systemctl stop mysqld.service
systemctl start mysqld.service
You should be able to connect to MySQL server with root user using the new password initiated by the init file.
Using –skip-grant-tables
shutdown mysql service if the service is running
systemctl stop mysqld.services
Then start the service with the following command
mysqld --skip-grant-tables --user=mysql &
Now, connect to MySQL server using this simple command
mysql
Since account-management is disabled when the service is started with <strong>--skip-grant-tables</strong> option, we need to reload the grants so we will be able to change the password later:
FLUSH PRIVILEGES;
Now we can run the following query in MySQL server to update root password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_preferred_password';
Now stop MySQL service again and start it normally.
systemctl stop mysqld.service
systemctl start mysqld.service
Small Note
If you encounter error like below when granting created user with root account
ERROR 1410 (42000): You are not allowed to create a user with GRANT
Please login to MySQL passoword using your root account and grant the root with GRANT OPTION command
CREATE USER 'root'@'%' IDENTIFIED BY 'your_preferred_password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Don’t forget to flush priviliges;
FLUSH PRIVILEGES;
Now you able to grant created user with grant command in MySQL server.
wpDiscuz