MySQL 8.0, change root password

When you install MySQL 8.0 in Ubuntu 20.04 the root has enabled the auth_socket plugin by default, you can checking executing:

SELECT User,Host,plugin FROM mysql.user WHERE user='root';
+------+-----------+-------------+
| User | Host      | plugin      |
+------+-----------+-------------+
| root | localhost | auth_socket |
+------+-----------+-------------+

what is mean you can connect only from localhost and using a UNIX user with root privileges, not MySQL root password is required.

$ sudo mysql -u root

The previous configuration ensures a high level of security and changing it carries security risks but if you still want to run the method described below do it in an safest environment like VPN, domestic LAN, deploy a firewall to neutralize any attack to your DB server or set up MySQL SSL connections.

To allow root user connections from any device using password we must change the value of the Host and Plugin columns, first change the value of the Host column with:

UPDATE mysql.user SET Host='%' WHERE User='root' AND Host='localhost';

% means that we can connect from any device as long as the bind-address directive allows remote connections, refresh privileges with:

FLUSH PRIVILEGES;

To change the authentication mechanism, firsts check the authentication plugins available with:

SHOW PLUGINS;

Note the rows that have AUTHENTICATION value in the Type column, second change the root authentication method with:

ALTER USER root IDENTIFIED WITH caching_sha2_password BY 'nueva-contraseña';

Reload privileges:

FLUSH PRIVILEGES;

MySQL 8.0, change root password

Administering MySQL from the command line, 6 (15)

Leave a Comment Cancel Reply

Related links:

Administering MySQL from the command line, 6 (15)

Leave a Comment Cancel Reply