how to recover mysql password in ubuntu 16.04

Question

Mysql version - mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper.

I had forgotten my password and tried many commands online.Also the problem is that grant tables command not working in my terminal.

Answer 1

~$ cat /etc/issue
Ubuntu 16.04.3 LTS \n \l

~$ aptitude show mysql-server |grep Version
Version: 5.7.20-0ubuntu0.16.04.1

In file: /etc/mysql/debian.cnf are two important lines:

user     = debian-sys-maint
password = <unique password>

Use that user and password to login to mysql: Once logged in as debian-sys-maint you can:

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

Answer 2

The following adjustments works for my setup:

$ sudo mkdir /var/run/mysqld
$ sudo chown mysql: /var/run/mysqld
$ sudo kill -9 $(sudo cat /var/run/mysqld/mysqld.pid)

Answer 3

1) Stop the Database Server. sudo systemctl stop mysql

2) Restarting the Database Server Without Permission Checking. For this following command sudo mysqld_safe --skip-grant-tables --skip-networking &

3) Connect to the database as the root user mysql -u root

4) Change the Root Password

FLUSH PRIVILEGES;

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

5) Restart the database server narmally

sudo kill `cat /var/run/mysqld/mysqld.pid`

sudo systemctl start mysql

Now login with new password in database: mysql -u root -p

Or you can follow the following link:

https://www.digitalocean.com/community/tutorials/how-to-reset-your-mysql-or-mariadb-root-password