Unable to set password in MySQL installation

Question

I have tried multiple times uninstalling MySQL but every time it skips setting up the password for the host. I need a password as I need it connect it to Python.

This is the output after running sudo mysql_secure_installation:

There are three levels of password validation policy:
LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0

Skipping password set for root as authentication with auth_socket is used by default.
If you would like to use password authentication instead, this can be done with the "ALTER_USER" command.
See https://dev.mysql.com/doc/refman/8.0/en/alter-user.html#alter-user-password-management for more information.

Answer 1

I think it may be a bug. In the earlier, if we change auth_socket to mysql_native_password for root before we use mysql_secure_installation, mysql_secure_installation allows us to change root's password.

However, now, I have to change auth_socket to mysql_native_password and use ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxx' to set a password for root and only then I can use mysql_native_password to change root's password.

To reproduce,

1. After install mysql-server, run

   sudo mysql -u root mysql
   

2. Execute the following SQL statements:

   UPDATE user SET plugin='mysql_native_password' WHERE User='root';
   FLUSH PRIVILEGES;
   

   And (but this should not be necessary)

   ALTER USER root@localhost IDENTIFIED BY 'password';
   

3. Run sudo mysql_secure_installation, and then set root's password.

Answer 2

You can login to mysql on the physical machine (or via ssh). Then inside the database use the ALTER USER command to enable password authentication and set the password.

Example:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password'