Skip to main content

Root log in by both socket and password

The fix: let root log in by socket or password

First, look at how root is currently set up. Don’t paste the output here, since it contains the password hash:


bash
sudo mariadb -u root -p -e "SHOW CREATE USER 'root'@'localhost'\G"

You’ll see something like IDENTIFIED BY PASSWORD '*ABC...' (the mysql_native_password plugin) or IDENTIFIED VIA .... If it’s the common native-password form, add the socket as an alternative while keeping the same hash, so the password stays unchanged:


bash
sudo mariadb -u root -p -e "ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING '<hash from SHOW CREATE USER>' OR unix_socket;"

Replace <hash from SHOW CREATE USER> with the *... string from the previous command. If it shows a different plugin (for example ed25519), tell me the plugin name only and I’ll adjust the statement.

Keep a second terminal logged in as mariadb -u root -p while you do this, so a typo can’t lock you out.

Verify


bash
sudo mariadb -e "SELECT CURRENT_USER();"        # works without a password now
sudo mariadb -u root -p -e "SELECT 1;"           # password still works
sudo mariadb-admin --local flush-slow-log && echo "flush ok"

Catch up on skipped maintenance

Once the socket login works, run the start-up script once by hand. It runs mariadb-upgrade and the root-account check, both safe to repeat:


bash
sudo /etc/mysql/debian-start

You should see “Upgrading MariaDB tables if necessary” without the FATAL ERROR line. You have a dump from earlier if you ran the backup step, but for the medical instance I’d make a fresh one first.