I have a little problem with my phpmyadmin, in fact I accidentally delete multiple user accounts. Since it is impossible to connect without the error:
# 1045 - Access denied for user 'root' @ 'localhost' (using password: NO)
I have search a little on the net before, and even the technic:
UPDATE mysql.user SET Password = PASSWORD ('') WHERE User = 'root';
FLUSH PRIVILEGES;
does not work, or I didn't understood how it worked.
I'm on FreeBSD 8.1, my version of PhpMyadmin is 2.11.
Thank you in advance for your answers.
This one worked for me, no side effects. https://help.ubuntu.com/community/MysqlPasswordReset
Answer for XAMPP on Windows:
DONE!
Be awared that mysql root user password does not have to be the same as password for phpmyadmin.
I summarised my solution here: http://snippets.dzone.com/posts/show/13267
For mysql 5.7.16 or later I found this (from mysql.com) to be the working solution. Below are some points which are different compared to previous older versions
1) I used the below command to run mysql in safe mode as per some other references which actually worked fine for me (mysql 5.7.16 ubuntu 16.04).
2) The below update stmt is NOT working for later version (ie. 5.7 and above)
INSTEAD use below for 5.7.6 and later
OR user below for 5.7.5 or earlier versions.
here is what I did and it worked:
After you install (rpm installation), do a
vi /etc/my.cnf
.This will give you a path where your
datadir
is set. For me it wasdatadir=/var/lib/mysql
.Add a line there
user=root
, and remove all the content inside thedatadir
path:rm -rf /var/lib/mysql/*
.Now hit the command:
mysqld --initialize
. A temporary password is generated at a location. For this:grep "A temporary password" /var/log/*
.You will get a line that says:
/var/log/mysqld.log:2018-05-01T15:13:47.937449Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: &uosjoGfi9:K
. So for me&uosjoGfi9:K
was my temporary password.Now do a
mysql -u root -p
. Paste your temporary password from what you got from above.You will be in your mysql cli mode. Now do:
mysql> use mysql;
You will be asked to reset your password:
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
Run your ALTER command:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPswd';
And you are done.
Please follow the instruction from the below link to reset your root password.You have to do it from outside mysql.
Resetting MySql Password
Forgetting your MySQL password can be a real headache for you.Here are some easy steps that you can follow to recover MySql password under Windows Stop MySql
You have to stop MySql service before you proceed.In Windows environment, go to 'Task Manager' and Under 'Service' tab find MySQL and stop it. Write Sql to change your password
All you need to do is to create a text file and put the below two lines into that. UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root'; FLUSH PRIVILEGES; Save it under C:\ drive and give it a name 'mysql-init.txt' Time to restart MySQL by your own.
Now it's time to restart your MySQl which you stopped in before but this time from command line. C:> C:\mysql\bin\mysqld-nt --init-file=C:\mysql-init.txt Finishing up!
Now you can log in with your new password. When you finish remove the file that you created in the previous stage.
Also there is a link (http://kaziprogrammingblog.osinweb.com/article/showarticle/Resetting-MySql-Password) where I have explained the same thing.
Hope this will help..:)