32

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.

Improve this question
3
  • youtu.be/gFo5DV_pSg8 - This video helped me in changing password in one of my production servers. Commented Dec 31, 2018 at 19:26
  • Does this answer your question? How to reset or change the MySQL root password? Commented Sep 3, 2020 at 4: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" Commented May 16, 2023 at 18:25

8 Answers 8

Reset to default
51

Here are the steps to be followed:

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

enter image description here

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

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

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

enter image description here

  1. Restart MySQL service using: sudo service mysql restart

  2. Check MySQL service status: sudo service mysql status

enter image description here

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

  2. And change the root password:

mysql> FLUSH PRIVILEGES;

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

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

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

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

life saving solution :)
For older versions of MySQL, e.g. for MySQL 5.5: the password change is SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');
This should be marked as answer.
2024, over a decade in the industry, an old server that we had no clue about. This saved me. Thanks.
Doesn't work with MySQL 8.0: ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
16

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
Improve this answer

3 Comments

This is applicable for MysQL 5.6.16 on Mac OSX 10.9.1, it works for me ;-)
The start -skip-grant-tables command throws a ERROR! The server quit without updating PID file error..
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
13

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;
Improve this answer

6 Comments

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 .
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';
failed to set datadir to /var/lib/mysql/
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';
"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
|
5

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:

  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;

Improve this answer

Comments

4

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
Improve this answer

Comments

4

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. enter image description here
  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

enter image description here

Improve this answer

Comments

1

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;
Improve this answer

1 Comment

This is the only one that worked for me. I am using MariaDB instead of MySQL;
-1

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

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.