mysql root password forgotten

Question

I did not use PHP MySQL for quite a while and now I need to use it again. But the problem is I forget the password for the MySQL console. and getting error #1045 when trying to login in to PHPMyAdmin.

In the MySQL site I saw an article how to reset root password( http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting-permissions-windows)

Steps are

create a mysql-init.txt file containing UPDATE mysql.user SET Password=PASSWORD('newpass') WHERE User='root';
FLUSH PRIVILEGES;

I saved it as C:\me\mysql-init

and in command prompt I wrote--

C:\wamp\bin\mysql\mysql5.5.8\bin\mysqld --init-file=C:\me\mysql-init.txt

I tried with double backslashes also..but it is not working. MySQL console is asking for a password and it's not taking the new-one. What am I doing wrong? I have several tables there.what to do?

Thanks in advance.

https://youtu.be/gFo5DV_pSg8 - This video helped me in changing password in one of my production servers. — Prem, Dec 31 '18 at 19:26
Does this answer your question? [How to reset or change the MySQL root password?](https://stackoverflow.com/questions/16556497/how-to-reset-or-change-the-mysql-root-password) — Jee Mok, Sep 03 '20 at 04:00
Make sure you're admin in the command prompt. I had to enclose my txt file with "". .\mysqld.exe --init-file="C:\MySQLDEV\my_init.txt" — Hein du Plessis, May 16 '23 at 18:25

Rajkaran Mishra · Answer 1 · 2019-11-21T11:35:23.843

35

Here are the steps to be followed:

Locate the MySQL configuration file using: $ mysql --help | grep -A 1 "Default options"

On Ubuntu 16, the file location is typically /etc/mysql/mysql.conf.d/mysqld.cnf

Edit the configuration file using: $ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Add skip-grant-tables under [mysqld] block and save the changes.

Restart MySQL service using: sudo service mysql restart
Check MySQL service status: sudo service mysql status

Login to mysql with: $ mysql -u root
And change the root password:

mysql> FLUSH PRIVILEGES;

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'MyNewPass';

Revert back the MySQL configuration file changes by removing skip-grant-tables line or commenting it with a # (hash).
Finally restart the MySQL service and you are good to go.

edited Nov 21 '19 at 11:35

answered Nov 20 '19 at 22:09

Rajkaran Mishra

4,532
2
36
61

1

life saving solution :) – Sandeep Pandey Jan 12 '22 at 07:03
For older versions of MySQL, e.g. for MySQL 5.5: the password change is `SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');` – Paul Libbrecht Apr 15 '22 at 10:54
This should be marked as answer. – Fletchy Mar 02 '23 at 14:50

score 15 · Answer 2 · answered Mar 22 '13 at 06:22

15

I couldn't get mysqld in Adelave's answer to work. But this worked for me instead

stop and start mysql with --skip-grant-tables

service mysql.server stop
service mysql.server start --skip-grant-tables

then connect to your mysqld without username/password

mysql

then update the password on mysql command line

 mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root'; 
 mysql> FLUSH PRIVILEGES;
 mysql> \q

then restart mysql normally

 service mysql.server restart

answered Mar 22 '13 at 06:22

Thunder Rabbit

5,405
8
44
82

1

This is applicable for MysQL 5.6.16 on Mac OSX 10.9.1, it works for me ;-) – AndaluZ Feb 24 '14 at 11:48
2

The `start -skip-grant-tables` command throws a **ERROR! The server quit without updating PID file** error.. – SexyBeast Apr 10 '15 at 19:32
`ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to` on Mysql 8 – AaA Oct 06 '21 at 03:25

score 12 · Answer 3 · answered Apr 09 '12 at 07:52

12

try to start mysql with --skip-grant-tables

mysqld --skip-grant-tables

then connect to your mysqld without username/password using mysql command line

shell> mysql

then issue command

> mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass')
>        WHERE User='root'; mysql> FLUSH PRIVILEGES;

answered Apr 09 '12 at 07:52

NoodleX

709
1
7
21

1

Thank you very much for reply.It is working for the time being.Now I am able log in to phpmyadmin to copy all the tables that I want to save.THANK YOU.But it is not taking the password when I reopen the wamp server later.I have to go through the same process like mysqld --skip-grant-tables . – user1187405 Apr 09 '12 at 11:42
The version of mysql I have installed doesn't have a Password column instead this worked: update mysql.user set authentication_string=password('MyNewPass') where User = 'root'; – Joshua Jun 07 '19 at 23:32
failed to set datadir to /var/lib/mysql/ – Salim Djerbouh Dec 29 '19 at 13:38
1

If your sql version tells you it isn't "the right syntax to use near ('MyNewPass')" : `ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'MyNewPass';` – François Breton Sep 30 '20 at 09:12
"`Thank you very much for reply.It is working for the time being.`" - in which case, please accept the answer, which will help others who read this question in future – Mawg says reinstate Monica Dec 09 '20 at 14:52

score 5 · Answer 4 · answered Oct 11 '16 at 16:17

If other answer could not help, you can try to uninstall/re-install mysql. It works on my ubuntu server:

$sudo apt-get purge mysql*
$sudo apt-get autoremove
$sudo apt-get autoclean

Update distribution

$sudo apt-get dist-upgrade

And re-install

$sudo apt-get install mysql-server

score 3 · Answer 5 · answered Jun 02 '20 at 23:14

Mac OS Mojave

$ brew services stop mysql

$ pkill mysqld

// warning: deletes all tables
$ rm -rf /usr/local/var/mysql/

$ brew postinstall mysql

$ brew services restart mysql

$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'p4ssword';
Query OK, 0 rows affected (0.04 sec)

mysql> exit
Bye

Then you are back to normal for dev.

$ sudo mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.

I don't like to see the word root in my .env files, so I usually do this after, if making a site such as www.hockeysticks.net:

#
CREATE DATABASE hockeysticks;

CREATE USER 'hockeysticks'@'localhost' IDENTIFIED BY 'hockeysticks';
GRANT ALL PRIVILEGES ON hockeysticks.* TO 'hockeysticks'@'localhost';

Then your localdev .env file is simple:

DB_DATABASE=hockeysticks
DB_USERNAME=hockeysticks
DB_PASSWORD=hockeysticks

Note: If you need to retain your databases, use the skip-grant-tables method. That has 3 hard parts:

Make sure MySQL is stopped, so you can restart it with skip-grant-tables
Make sure your password update SQL syntax is correct for your MySQL version
Make sure you append the end of the query with FLUSH PRIVELEGES;

score 3 · Answer 6 · answered Jan 17 '22 at 17:00

Using windows command prompt you can change the password

Go to windows+R and run services.msc and then stop the MySQL services and see the properties of MySQL by right click and copy the path --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" as it required later.
Create text file reset.txt with the text ALTER USER 'root'@'localhost' IDENTIFIED BY 'Your New Password'; and save in C drive.
open the command prompt as administrator then change the directory where your MySQL is installed "C:\Program Files\MySQL\MySQL Server 8.0\bin".
Type the command mysqld --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" --init-file=C:\\reset.txt

score 0 · Answer 7 · answered Apr 04 '23 at 08:06

The following worked for me in MariaDB 10.6.11

To do next mysql root login without password in shell

mysqld_safe --skip-grant-tables &

Mysql root login

mysql -u root

Use mysql database

use mysql;

Create Authentication string for new password

SELECT PASSWORD('asdlfjasldfkjklasdkflasdjfla');

Output: *B1D04474F8D522B69410FC0E731B35ED22E94A3C

Now check priv for user root

select priv from global_priv where user='root';

Output: {"access":549755813887,"plugin":"mysql_native_password","authentication_string":"*LEICHEI2THEIYUOCH3PEE5OOVU8UQU4B","auth_or":[{}],"version_id":100611,"password_last_changed":1677497088}

Update priv for user root with newly generated authentication_string

update global_priv set Priv='{"access":549755813887,"plugin":"mysql_native_password","authentication_string":"*B1D04474F8D522B69410FC0E731B35ED22E94A3C","auth_or":[{}],"version_id":100611,"password_last_changed":1677497088}' where user='root';

Flush Privileges

flush privileges;

score -1 · Answer 8 · edited May 08 '19 at 12:02

Using SQLYog you can execute commands

User Creation

CREATE USER 'tester'@'localhost' IDENTIFIED BY 'Pass123#d'

Authorization

GRANT ALL PRIVILEGES ON sakila.* TO 'tester'@'localhost'

Changing Password in MySQL 8.0

ALTER USER 'tester'@'localhost' IDENTIFIED BY 'Pass123#d'

(or if u know the authentication_string directly set it to update)

UPDATE mysql.user 
    SET authentication_string='*F9B62579F38BE95639ACB009D79427F2D617158F'  
WHERE USER='root'***

Changing password in lower versions of mysql

GRANT USAGE ON *.\* TO 'tester'@'localhost' IDENTIFIED BY 'Pass123#d'
SET PASSWORD FOR 'tester'@'localhost' = PASSWORD('Pass123#d');**

mysql root password forgotten

8 Answers8

Using SQLYog you can execute commands

Linked

Related