Unable to login to my mySql server as root, and unable to reset root password

Question

I'm trying to access and backup a mySql Database but without success. I know the root password, but if I try to login to the DB using it I get a

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

error.

I tried to follow different tutorials on how to change mySql root password but without success:

• tried to restart mySql in safe mode and to ALTERing the sys table
• tried to use mysqladmin to change password (as explained here)
• tried to use a config file, with mysqld --init-file=./mysql-init &

They all fail with the same error. My plan was to backup the DB before dismissing the server, but without being able to login I cannot backup anything.

I found the problem with the root password because I noticed that I was not able to login to the database using phpMyAdmin nor Webmin. All the other DB users (the ones created for the different apps) look to be working, since I don't see anything broken in my frontends.

Is there any other test that I can do to restore the password? Or, is there any easy way to backup my data before trashing everything?

System: Ubuntu 18.04.6 LTS (GNU/Linux 4.15.0-193-generic x86_64)
Mysql Version: mysql Ver 14.14 Distrib 5.7.39, for Linux (x86_64) using EditLine wrapper

Thank you in advance, S.

UPDATE

Trying what @user9517 suggested in his comment resulted in:

# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 
Error: Access denied for user 'root'@'localhost' (using password: YES)

# mysql --protocol=tcp -uroot -pPASSWORD
mysql: [Warning] Using a password on the command line interface can be insecure.

(and terminal freezed)

ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (110)

UPDATE 2

I have been able to login using the --skip-grant-tables option, after manually creating the /var/run/mysqld file that was missing in my system. I've been able to update the password, and restart the system. But I am still unable to login into my database as root. I guess the problem isn't the password, but some config. If I check the content of the user table, I can see this output:

SELECT * FROM user WHERE User = 'root'

+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host      | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                     | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| *         | root | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX | N                | 2018-01-10 10:58:05   |              NULL | N              |
| 127.0.0.1 | root | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX | N                | 2018-01-10 10:58:05   |              NULL | N              |
| ::1       | root | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 | mysql_native_password | XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX | N                | 2018-01-10 10:58:05   |              NULL | N              |
+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+

Any idea about what I can check to solve the problem?

UPDATE 3

Strangely, if I try to connect to the server using Sequel Pro via ssh I can connect, view the tables, and export them...

Answer 1

I'm on CentOS Linux release 7.9.2009 (Core) - MySQL Ver 14.14 Distrib 5.7.36. MYSQLD_OPTS is the key.

systemctl stop mysqld
systemctl set-environment MYSQLD_OPTS=--skip-grant-tables
systemctl start mysqld
mysql
    mysql> UPDATE mysql.user SET authentication_string = PASSWORD ('ENTER_NEW_PASSWORD') WHERE User = 'root' AND Host = 'localhost'; 
    mysql> FLUSH PRIVILEGES; 
    mysql> quit
systemctl stop mysqld
systemctl unset-environment MYSQLD_OPTS
systemctl start mysqld
# test #
mysql -u root@localhost -p

Hopefully this works in your enviornment.

Answer 2

FOR AWS Linux systemctl stop mysqld systemctl set-environment MYSQLD_OPTS=--skip-grant-tables systemctl start mysqld mysql mysql> UPDATE mysql.user SET authentication_string = PASSWORD ('ENTER_NEW_PASSWORD') WHERE User = 'root' AND Host = 'localhost'; mysql> FLUSH PRIVILEGES; mysql> quit systemctl stop mysqld systemctl unset-environment MYSQLD_OPTS systemctl start mysqld

mysql -u root@localhost -p