mysql root password forgotten

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( )

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.

2

7 Answers

Here are the steps to be followed:

1. 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

2. Edit the configuration file using: $ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

3. Add skip-grant-tables under [mysqld] block and save the changes.

4. Restart MySQL service using: sudo service mysql restart

5. Check MySQL service status: sudo service mysql status

6. Login to mysql with: $ mysql -u root

7. And change the root password:

mysql> FLUSH PRIVILEGES;

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

8. Revert back the MySQL configuration file changes by removing skip-grant-tables line or commenting it with a # (hash).

9. Finally restart the MySQL service and you are good to go.

2

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

3

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;

5

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

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 :

#
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:

1. Make sure MySQL is stopped, so you can restart it with skip-grant-tables

2. Make sure your password update SQL syntax is correct for your MySQL version

3. Make sure you append the end of the query with FLUSH PRIVELEGES;

0

Using windows command prompt you can change the password

1. 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.
2. Create text file reset.txt with the text ALTER USER 'root'@'localhost' IDENTIFIED BY 'Your New Password'; and save in C drive.
3. open the command prompt as administrator then change the directory where your MySQL is installed "C:\Program Files\MySQL\MySQL Server 8.0\bin".
4. Type the command mysqld --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" --init-file=C:\\reset.txt

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');**

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy