Out of the blue i was not longer able to log in to my local mysql server. i have no idea what caused it but i found out how to fix it.
MySQL service
First of all make sure the mysql service is stopped.
In Linux distributions using the systemd service manager services can be controlled by the systemctl command.
To stop a service the following command can be used:systemctl stop mysqld
If you do not have sufficient permission add a sudo in front of the command. In order to start a service just use the term start instead of stop.
If you are on Windows you can control services using the services app. To start the app just enter services in the search bar or press Windows+R to open the Run box and enter services.msc
Locate mysql-config file
On Linux it should be located at /etc/my.cnf or /etc/mysql/my.cnf depending on your distribution.
On FreeBSD you will find it at /usr/local/etc/my.cnf or /usr/local/etc/mysql/my.cnf.
On Windows it should be located at %ProgramData%\MySQL\MySQL Server 5.6\my.ini. (not “program files”)
Edit the config-file
Add skip-grant-tables to the block [mysqld] and save the file.
...
[mysqld]
skip-grant-tables
...Set new Password
Start the MySQL service. You should now be able to log into MySQL as root user without being prompted for a password.
mysql> flush privileges;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
# for mysql < 5.7 or mariaDB this command fails. use this command instead:
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('password')Finally
Now stop the MySQL Service, Remove skip-grant-tables and start the MySQL Service. Your server should be back to normal.