1

Well, I'm using Ubuntu 20.04 and I have installed MySQL some months ago. Actually MySQL version is 8.0.30.

I'm trying to get in the mysql console, but it gives me that neither root nor my personal user have permissions on MySQL.

What I've already tried:

  • skip-grant-tables => One of the solutions was putting this option in one of the MySQL configuration files, and then alter the root password. but when I try to change the root password, it gives me that I'm using the skip-grant-tables option. After trying this, and of course, using flush privileges and restarting the service after changing it, I get:

    ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'

  • alter user => I tried many commands with alter user like:

    alter user 'root'@'localhost' identified by 'My_password';
    alter user 'root'@'localhost' identified with native_mysql_password by 'My_password';
    set password for 'root'@'localhost' = PASSWORD('My_password');

But all theses commands give the error ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'.

  • Put the right authentication_string => Yes, I had checked this column to the root user, and it still giving me the same thing:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

No success at all. The last one even gives me a syntax error. But all this attempts gave me some information:

  • debian.conf => I did a cat in this file, and I found out that root user is not in there, even if it's in the Users table inside mysql database. But I can access mysql console with debian-sys-maint, using the password given in the file.
  • Permissions => I doesn't have any permissions with root.
  • root password => I have set the authentication_string to the root user inside users table to a very strong password, and even using it, I does not have permissions to enter mysql console.
  • Password validation => I have changed the global variables of password validation so I can change my password to a weak one. But when I restarted the MySQL service, it went back to the default configurations. It bothers me, but I can get along with it.

So what I'm looking for is: how can I give permissions to my root user access MySQL console? Should I recreate root user? Or is there another way to fix this?

EDIT1: PASSWORD function does not work for some reason.

EDIT2: I'm not able to create a root user. It gives me an error saying that I don't have permission to do so. It looks like mysql is not recognizing 'root' as a user. When I try "GRANT ALL" to 'root', it says that I don't have permission to create it, but it is already in the users table.

Gabriel Antonio
  • 11
  • 2
  • There's no special access for the mysql console. If they have access to the database they can use the mysql command line. – Barmar Aug 16 '22 at 15:45
  • https://stackoverflow.com/questions/33991228/what-is-the-default-root-pasword-for-mysql-5-7/50305285#50305285 – Stewart Aug 16 '22 at 15:46
  • Does this answer your question? [What is the default root pasword for MySQL 5.7](https://stackoverflow.com/questions/33991228/what-is-the-default-root-pasword-for-mysql-5-7) – Stewart Aug 16 '22 at 15:47
  • I've already tried to all these things, but when I try to change the password, it says that the "PASSWORD()" function does not exists, and if I try to change it in the other way, it gives me an error: ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost' As I said, I'd tried everything that I saw related to this. – Gabriel Antonio Aug 17 '22 at 02:17

0 Answers0