0

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.

6
  • Did you try logging back in with a blank password? It's possible the command to set the password wasn't executed properly. By any chance is this a remote system you are trying to log into or is this running locally?
    – Gordster
    Jul 9 '19 at 16:57
  • @Gordster Indeed mysql is running locally. ps -e | grep sql is displaying a pid for mysqld and if I try to log in without password from terminal with mysql, or mysql -uroot, or mysql -u root the answer is ERROR 1045 (28000): Access denied for user 'userx'@'localhost' (using password: NO) or ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) like if I would follow the mentioned guide and restart sql.
    – John Goofy
    Jul 10 '19 at 13:13
  • Did you try sudo mysql -u root? Additionally, I think this might help: askubuntu.com/questions/766334/… Jul 11 '19 at 12:50
  • 1. stop the mysql sudo /etc/init.d/mysql stop 2. sudo mysqld_safe --skip-grant-tables & 3. mysql -uroot 5. use mysql; 6. update user set authentication_string=PASSWORD("mynewpassword") where User='root'; 7. flush privileges; 8. quit 9. start mysql sudo /etc/init.d/mysql start
    – yusuf hari
    Jul 11 '19 at 12:51
  • @CluelessNoob Yes, I did, but it's resulting in the same error message ERROR 1045 (28000).
    – John Goofy
    Jul 11 '19 at 13:28
0

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 ;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.