2

I have been trying to reset the root password of MySQL.

I have used to two methods to reset. But I didnt get it.

Following steps are done, But it doesn't work for me:

METHOD-1:

1) Stopped running of MySQL services and created a mysql-init.txt file. mysql-init.txt contains:

USE mysql;

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

FLUSH PRIVILEGES;

2) I have opened the command prompt and i typed the following:

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqld.exe --defaults-file="my.ini" --init-file="C:\\Users\\user-name\\Desktop\\mysql-init.txt" --console

The ERROR which i got is as follows:

Could not open required defaults file: C:\Program Files\MySQL\MySQL Server 5.6\b in\my.ini

Fatal error in defaults handling. Program aborted

METHOD-2:

I simply opened the command prompt and typed as follows:

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqladmin -uroot -psample password
vikash

I got the error as:

Warning: Using a password on the command line interface can be insecure.
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: YES)'

Please help me to reset the password...

ffflabs
  • 17,166
  • 5
  • 51
  • 77
Vikash
  • 39
  • 1
  • 2
  • 8
  • [Here](http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html) you find a documentation from mysql.org – Jens Jun 30 '14 at 12:22
  • well for one, your password probably needs to be in quotes. Also, try just the flag, not putting your password on the commandline: `mysqladmin -uroot -p`. mysql should then ask for your password (at this point, you would NOT use quotes) – Russell Uhl Jun 30 '14 at 12:36

2 Answers2

6

Below is the process to reset the root user password, when we forgot the root user password or missed to recollect the password provided during installation.

OS - Ubuntu 16.04

MySQL - 5.7

  1. Stop Mysql Server sudo /etc/init.d/mysql stop
  2. To avoid the error, mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists , run below commands: sudo mkdir -p /var/run/mysqld sudo chown mysql:mysql /var/run/mysqld
  3. Start mysql in safe mode: sudo mysqld_safe --skip-grant-tables &
  4. Login to Mysql and change the password to say 'root123': In 5.7 version the password column is renamed as authentication_string. mysql -uroot mysql>use mysql; mysql>update user set authentication_string=password('root123') where user='root';
  5. If you get the error :: MySQL fails on: mysql “ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded” then run below commands and then run 4th Step. mysql>update user set plugin="mysql_native_password" where User='root'; mysql>flush privileges; quit;
  6. Stop and Start mysql server sudo /etc/init.d/mysql stop sudo /etc/init.d/mysql start
  7. Login with the new password mysql -uroot -proot123

PFB, the URLs for reference.

https://support.rackspace.com/how-to/mysql-resetting-a-lost-mysql-root-password/ mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists MySQL user DB does not have password columns - Installing MySQL on OSX MySQL fails on: mysql "ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded"

Dorababu G
  • 403
  • 5
  • 9
  • THANKS, especially for step **5**. I'm on (X)ubuntu18.04. I had some trouble killing the `mysql_safe` instance however, but `sudo killall mysqld` seemed to work at some point. And then I reloaded mysql-server with `sudo service mysql restart`. – PlasmaBinturong Sep 24 '18 at 20:49
1

Try stopping mysql service

/etc/init.d/mysqld stop

Run the safe executable skipping grants

mysqld_safe --skip-grant-tables &

(the trailing ampersand gets you back to the command line)

Then connect as root without password

mysql -uroot

You'll be in the mysql prompt where you can update root's password.

EDIT: I just saw you're using windows. Ok, then your first method should almost work. Instead of "UPDATE mysql.user" the file should read

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('vikash');

Then run 

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqld.exe --init-file=C:\Users\user-name\Desktop\mysql-init.txt

I'm curious about the executable though... all those blank spaces can probably get windows confused. Perhaps it would be easier to create your init file in the same folder as the executable so you just have to run

mysqld.exe --init-file=mysql-init.txt

There might be other executables in there. mysqld-nt, mysqld-safe, etc. If mysqld doesn't work, try the others too.

ffflabs
  • 17,166
  • 5
  • 51
  • 77
  • This should probably work. Having said that: I went through this myself just a few weeks ago. There's a bug with mysqld such that it sometimes can't find the file, even if the file is RIGHT THERE. It's actually a permissions issue, but one at the mysql level and not the os level. You may need to put your file in a dir that mysql can read from already (on linux it's something in the `/usr` dir i think). – Russell Uhl Jun 30 '14 at 12:33
  • Happy dance \o\ ~o~ /o/ – ffflabs Jun 30 '14 at 12:41
  • if you get the following error: "mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.", Create the directory its looking for using `mkdir -p /var/run/mysqld` and set correct permissions using `chown mysql:mysql /var/run/mysqld` – NFern Jun 29 '20 at 03:29