How to set/reset mysql password after Installation?

Question

I am runnig Ubuntu 16.04 and followed this guide to set/reset root's password. But after all if I am using $ mysql -u root -p, or sudo mysql -u root on my commandline and enter the new password, I got this error message:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

If I am using user und password from /etc/mysql/debian.cnf there are no problems to log in with mysql -udebian-sys-maint -p.

And if I do so inside of mysql and query for some topic's

mysql> select User, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked  from mysql.user; 
+------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| User             | authentication_string                     | password_expired | password_last_changed | password_lifetime | account_locked |
+------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| root             | *obfuscated_authentication_string======== | N                | 2018-11-21 17:02:55   |              NULL | N              |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N                | 2018-11-21 17:02:48   |              NULL | Y              |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N                | 2018-11-21 17:02:48   |              NULL | Y              |
| debian-sys-maint | *obfuscated_authentication_string======== | N                | 2018-11-21 17:02:55   |              NULL | N              |
+------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+

I see that root's password never has been changed since installation time.

If I follow these instructions I receive a pid of the background job after step 3 and the shell is prompting

$ 2019-07-11T14:21:55.218651Z mysqld_safe Logging to syslog.
2019-07-11T14:21:55.226406Z mysqld_safe Logging to '/var/log/mysql/error.log'.
/usr/bin/mysqld_safe: 152: /usr/bin/mysqld_safe: cannot create /var/log/mysql/error.log: Permission denied
2019-07-11T14:21:55.232708Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.
/usr/bin/mysqld_safe: 152: /usr/bin/mysqld_safe: cannot create /var/log/mysql/error.log: Permission denied
mysql -u root mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
[1]+  Exit 1                  mysqld_safe --skip-grant-tables

while I have tried to execute mysql -u root mysql.

What need I to do for purging, setting or resetting root's password?

Or is it possible to achieve this from within user debian-sys-maint?

Any hint is welcome.

Answer 1

At least I found following solution with user and password from /etc/mysql/debian.cnf without start-/stopping any service nor sudo:

Log in from standard shell:

$ mysql -udebian-sys-maint -p
Enter password:

Then inside mysql:

mysql> use mysql;
mysql> set password for 'root'@'localhost' = password("xXnew_pwXx");
mysql> flush privileges;
mysql> quit;

Now, without restarting any service I am able to log in with just

$ mysql -u root -p
Enter password:

and the new password xXnew_pwXx.

Now it's clearly to see root's password has been changed.

mysql> select User, password_last_changed, password_lifetime, account_locked  from mysql.user; 
+------------------+-----------------------+-------------------+----------------+
| User             | password_last_changed | password_lifetime | account_locked |
+------------------+-----------------------+-------------------+----------------+
| root             | 2019-07-11 16:49:52   |              NULL | N              |
| mysql.session    | 2018-11-21 17:02:48   |              NULL | Y              |
| mysql.sys        | 2018-11-21 17:02:48   |              NULL | Y              |
| debian-sys-maint | 2018-11-21 17:02:55   |              NULL | N              |
+------------------+-----------------------+-------------------+----------------+
4 rows in set (0,00 sec)

That's it ;)