Home / ubuntu / Questions / 1514709
Accepted
Borea Deitz
Asked: 2024-05-21 02:08:45 +0800 CST

How do I run mysql_secure_installation on 22.04?

  • 772

I've just installed MySQL 8.0.36 on Ubuntu 22.04 and now I want to run mysql_secure_installation.

The guide for this precise thing at Digital Ocean indicates that on Ubuntu, running mysql_secure_installation produces a logic loop if you don't configure the MySQL 'root' user's password first. Following its instructions, I did so:

$ sudo mysql
[sudo] password for user: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.36-0ubuntu0.22.04.1 (Ubuntu)

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'crocodile69210';
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye

After doing this, I am unable to run mysql_secure_installation at all. When I try, I get a password error:

$ sudo mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 
Error: Access denied for user 'root'@'localhost' (using password: YES)

This happens when I enter both the MySQL 'root' user password that I established earlier (crocodile69210) and my OS sudo password. If I enter no password at all, the result is the same except the last line is (using password: NO).

What do I do to be able to run mysql_secure_installation?

Edit: It appears that the ALTER USER statement above does not actually set a password. If I run $ mysql -u root -p and literally copy and paste the password that I set, which is still visible in the terminal history, I cannot login to mysql:

$ mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I'm not sure if or how this illuminates the problem.

Edit 2: Working on the theory that the Digital Ocean guide is wrong, I uninstalled MySQL ($ sudo apt remove mysql-server) and then re-installed it. I did not run the ALTER USER command given in the DO article. I still cannot run mysql_secure_installation and get the same "Access denied" password errors I got during the first attempt.

Edit 3: More information

When I first installed mysql-server from a fully blank slate, I was able to use $ sudo mysql immediately to access the MySQL interactive shell. I find that after 1) uninstalling mysql-server using $ sudo apt remove mysql-server AND 2) fully purging mysql-server using $ sudo apt purge mysql-server, after I reinstall I still can't use $ sudo mysql. I get the "Access denied" password error. So, something about the first installation is obviously still hanging around. Figuring out what this is will likely help solve the whole problem.

Final Edit:

  1. On Ubuntu, apt purge (or apt remove --purge) simply don't work for MySQL. You must follow this procedure to fully uninstall MySQL from your system.

  2. I generated the password for the MySQL 'root' user using a password manager, and I did not inspect the password. It had a backslash in it (\). When setting the password using the ALTER USER command, the shell interprets this as an escape character and removes it before passing it to MySQL. Thus, my 'root' password was not actually what I thought I was setting it to.

Do not use backslashes in MySQL passwords

22.04

1 Answers

  1. Best Answer

    The reason why you're not able to login as root using sudo mysql anymore is that

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

    has set the authentication method for root to mysql_native_password, replacing the default auth_socket, which is the method that allows a system user named the same as the MySQL user (in this case root) to login to the server without providing a password.

    Luckily, MySQL installations on Debian-based distros come with a fairly high-privileged user named debian-sys-maint, which can get you out of troubles in these cases.

    First, login to MySQL as debian-sys-maint (run sudo less /etc/mysql/debian.cnf to find out debian-sys-maint's password):

    mysql -u debian-sys-maint -p<password>

    Then, as a first attempt, just try and change root's password again:

    ALTER USER 'root'@'localhost' IDENTIFIED BY 'crocodile69210';

    (no need to FLUSH PRIVILEGES afterwards.)

    Why?

    Because I tried changing root's password using the same exact method as yours and I'm perfectly able to login to the server running mysql -u root -pcrocodile69210.

    The procedure you followed appears to be correct: there's a chance that, simply, something has gone wrong while going through it.

    But regrdless, for the purpose of running mysql_secure_installation, if setting the password again doesn't avail, most likely you can set the authentication method for root back to auth_socket and just run sudo mysql_secure_installation; the guide you're following is pretty old and chances are the bug it's warning you about has already long been fixed:

    ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;
    • 2