0

I am trying to install mysql after i replaced my disk with MySQL on it with a bigger and faster one. After installing mysql-server on ubuntu, when running select user,host,authentication_string from user where user='root';, authentication_string is blank.

I set my password but the only way to login to root is to run the MySQL command with sudo and if I try to put the password in if I run mysql -u root -p it doesn't work. I have also tried to just leave the password blank when logging in and it has had no effect. Any help is greatly appreciated.

System info: pop!_os amd64 20.04 (ubuntu like)

Mikael H
  • 5,031
  • 2
  • 9
  • 18
juls07
  • 3
  • 3
  • How did you set your password? – Alex Aug 24 '21 at 07:54
  • 1
    The root account may be configured to use socket authentication rather than password authentication: https://dev.mysql.com/doc/refman/8.0/en/socket-pluggable-authentication.html and https://serverfault.com/a/795304/794142 – bob Aug 24 '21 at 09:42
  • @bob thanks, this works! would you mind adding an answer to the post so i can accept it? – juls07 Aug 25 '21 at 01:46

1 Answers1

1

Some installations default to enabling the MySQL (and MariaDB) socket authentication plugin.

Rather than using password authentication it is sufficient to be logged in as root on the operating system (either by logging in directly as root or by using sudo mysql to elevate your privileges to root) to log in as root to the MySQL server. You can use the same mechanism for a regular users too, i.e. when I log in as bob to the Linux server and a bob@llocalhost account exists in MySQL I can log on directly without additional authentication.

To remove socket authentication log in as root

$ sudo mysql -u root

mysql> use mysql;

Set up a root with a password instead of the socket authentication

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'MyNewPass';

Activate these changes by flushing all cached credentials

mysql> flush privileges;

Then you can log in with mysql -u root -p and the new password you assigned.

bob
  • 191
  • 1