How to reset the forgotten MySQL root password?

Question

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

Answer 1

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"

Answer 2

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.