Score:0

mysql root password is empty

pl flag

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)

Alex avatar
in flag
How did you set your password?
ar flag
bob
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
juls07 avatar
pl flag
@bob thanks, this works! would you mind adding an answer to the post so i can accept it?
Score:0
ar flag
bob

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.

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.