Reset mysql root password without mysql client access

Question

In short,I have inherited a project... it has a live production site running on a LAMP stack. I have root access to the server nobody knows the mysql root password. In addition the user that the application uses cannot access the mysql client, for some reason the credentials only work from the application.

I am trying to reset the root password using the instructions from mysql https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

The server is running ubuntu 16 lts with mysql 5.7.19.

Following the above guide, they instruct mysql to be shutdown with the following:

kill `cat /mysql-data-directory/host_name.pid`

I run the sql script on startup... but the result is i still cannot login to the mysql shell via root :/

Anyone got a solid way to reset the root pass..?

Trying to get the service running with the skip grant tables and networking:

1. Stop the service: /etc/init.d/mysql stop LOG OUTPUT
2. Start the service with mysqld and options: mysqld --skip-grant-tables --skip-networking --user=root & the output

3. Try to start the client but the client says:

   root@...:/home/john# mysql -u root ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

   1+ Exit 1 mysqld --skip-grant-tables --skip-networking --user=root

4. When starting the mysql server this is the LOG OUTPUT

---

The answer below was correct, just ensure you have the /var/run/mysqld/ else mysqld cannot start.

Answer 1

https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

B.5.3.2.3 Resetting the Root Password: Generic Instructions

1. Stop the MySQL server if necessary, then restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges, and disables account-management statements such as ALTER USER and SET PASSWORD. Because this is insecure, you might want to use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting.

-or-

https://www.codero.com/knowledge-base/content/33/296/en/how-to-reset-your-root-mysql-password.html

$  mysqld --skip-grant-tables --skip-networking &
$  mysql -u root

 ...change your password
 ...reset mysql without --skip-grant-tables and --skip-networking

UPDATE

It likely the mysqld cannot run as root or your system account. See here.

$ mysqld --skip-grant-tables --skip-networking --user=root &

or you can revise the my.cnf file directly after stop the mysql server. I recommend the way.

[mysqld]
...
# add the following two settings to configure variables for reseting password.
skip-grant-tables
skip-networking

After restart mysqld. Using the mysql cli client execute the following sql statement to change your password.

UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPass')
WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

Finally, stop mysqld, and rollback your my.cnf, then restart mysqld.