Updating the Password for the MySQL Root User using SQL

To stop the service on a Mac, follow these steps:
0) Stop the service.
1) Skip the grant table.
Once the service is running, avoid closing it and instead, open a new terminal window.
2) Go into the MySQL terminal.
3) Update the password.
For newer versions like 5.7, use the following steps:
4) Run a specific command.
5) Run another specific command.
To quit, use the appropriate command.
6) Start the MySQL server.
Alternatively, you can stop the MySQL service by navigating to Administrative tools > Services. Then, open Start > Run > cmd (Run as administrator) and use a specific command. Be sure not to update the MySQL password data directly, as different authentication plugins may hash the password differently. Keep this in mind.


Solution 1:

For the Windows operating system:

0) shut down service

mysql56

If you plan to access

C:ProgramDataMySQLMySQL Server 5.6


, keep in mind that it is a concealed directory known as

ProgramData

.

Search for the desired file identified by

my.ini

, insert an additional line of code

skip-grant-tables

right after

[mysqld]

, and then save the file.

[mysqld]
skip-grant-tables

3) start service

mysql56

You have the authority to enter the database and execute the command labeled as

mysql

.

Subsequently, execute the following query for password modification.

update mysql.user set password=PASSWORD('NEW PASSWORD') where user='root';

Please use

authentication_string

instead of

password

for the newer version. Kindly take note of this change.

Deactivate the service, eliminate the line marked as

skip-grant-tables

, save the changes, and then reactivate the service. Afterward, attempt to log in using the newly set password.



On Mac

:

0) stop the service

sudo /usr/local/mysql/support-files/mysql.server stop

1)
skip grant table

sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables

After launching it, refrain from closing it and instead, open a fresh terminal window.

2) go into mysql terminal

/usr/local/mysql/bin/mysql -u root

3) update the password

UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='root';

for newer version like 5.7, use

UPDATE mysql.user SET authentication_string=PASSWORD('password') WHERE User='root';

4) run

FLUSH PRIVILEGES;

5) run

q


to quit

6) start the mysql server

sudo /usr/local/mysql/support-files/mysql.server start


Solution 2:

  • To halt the Mysql service, navigate to the Services section in Administrative tools.
  • To access the command prompt with administrative privileges, open the Start menu, type “cmd” in the Run dialog box, and then right-click on it and select “Run as administrator.
  • Initiate the server by executing this command manually.

    mysqld -P3306 --skip-grant-tables
    
  • Execute the following command as an administrator in a new command prompt.

    mysql -P3306 mysql
    
  • Run the subsequent command using the mysql client.

    update mysql.user set authentication_string=password('new_password') where user='root';
    

That’s it!!


Solution 3:


The matter has been sorted out.

As mentioned in my query, I adhered to the guidelines provided by the MySQL guidebook.

My original post detailed the process, but it didn’t happen exactly as described. However, it still worked and I have provided an update in the relevant section.

Frequently Asked Questions