The Dummit

  • Tutorial
  • Tips & Tricks
  • About Me
  • Reference

MySQL 8: How to reset root password on Centos 7

Posted on 22 May 2019 in Tips & Tricks

 loading


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 --skip-grant-tables 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.


 loading

PowerDNS Authoritative & MySQL: How to Setup for local DNS on Centos 7

 loading

Mikrotik: How to Setup OpenVPN for remote user

Recent Post

  • openldap
    How to compile, install and configure openLDAP in Centos 8
    5 September 2020
  • zimbra desktop
    Zimbra Desktop : How to recover your local folder after crash (Win 10)
    2 September 2020
  • doh
    Mikrotik : How to use DoH with Cloudflare
    7 July 2020
  •  loading
    How to install Openfire and configure server to server on Centos 7
    12 August 2019
  •  loading
    MySQL 5.7 : How to configure Replication (Master/Slave) Database on Centos 7
    25 June 2019