mysql_secure_installation Authentication Error on Ubuntu 20.04

When installing MySQL on Ubuntu 20.04, you may encounter an error during the mysql_secure_installation process:

Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.

This error occurs because MySQL 8.0+ on Ubuntu uses the auth_socket plugin by default for the root user, which doesn't require a password for authentication when connecting via the local socket.

Root Cause

The authentication plugin auth_socket authenticates users based on their system username rather than a password. While this is secure for local access, it conflicts with mysql_secure_installation which expects password-based authentication.

Solution: Change Authentication Method

To resolve this issue, you need to change the authentication method for the root user to mysql_native_password.

Step 1: Access MySQL as Root

First, connect to MySQL using the socket authentication:

sudo mysql

Step 2: Change Authentication Plugin

Execute the following SQL command to change the authentication method and set a password:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_strong_password';

Replace 'your_strong_password' with a secure password of your choice.

Step 3: Flush Privileges

Apply the changes by flushing the privileges:

FLUSH PRIVILEGES;

Step 4: Exit MySQL

EXIT;

Step 5: Run mysql_secure_installation

Now you can successfully run the security script:

sudo mysql_secure_installation

During the process, choose to use the existing password you just set.

WARNING

After making these changes, you will no longer be able to use sudo mysql to connect without a password. You'll need to use regular MySQL authentication:

mysql -u root -p

Alternative: Keep Socket Authentication

If you prefer to keep the socket authentication but still want to run mysql_secure_installation, you can temporarily switch to password authentication and then revert:

-- Temporarily enable password authentication
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'temporary_password';

-- Run mysql_secure_installation here
-- Then revert to socket authentication
ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;

Verification

To check the current authentication method for your root user:

SELECT user, plugin FROM mysql.user WHERE user = 'root';

This will show whether root is using auth_socket or mysql_native_password.

TIP

For production environments, it's recommended to use password authentication and create additional users with appropriate privileges rather than using the root account for routine operations.

This solution ensures you can successfully complete the MySQL secure installation process while maintaining proper authentication security on your Ubuntu 20.04 system.