mysql root password forgotten

Question

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.

Answer 1

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.

Answer 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