Reset mysql root password

This article describes how to reset MySQL's or MariaDB's root password on your workstation. It's meant for local development purposes only, don't do this in production. This article will also help you if you have a fairly recent MariaDB version that uses authentication based on linux users instead of passwords for the root user and you prefer using a password for root.

Solution

Step 1 is getting a root mysql shell that allows us to change user credentials. We need to stop the mysql daemon first and re-start it without authorization checking. This works for both MariaDB and MySQL since they share names of binaries.

Copysudo systemctl stop mysql
sudo mysqld_safe --skip-grant-tables &

This starts the mysql daemon in the background and we can now access the shell without passwords. We can now

Copy$ mysql -uroot
MariaDB [(none)]> use mysql
    Database changed

Enabling Access

Checking the current configuration

CopyMariaDB [mysql]> select host, user, password, plugin from user;
+-----------+--------+-------------------------------------------+-------------+
| host      | user   | password                                  | plugin      |
+-----------+--------+-------------------------------------------+-------------+
| localhost | root   | *FE4F2D624C07AAEBB979DA5C980D0250C37D8F63 |             |
| localhost | peter  | *E74858DB24EBA20BC33D0AECAE8A8108C56B17FA |             |
+-----------+--------+-------------------------------------------+-------------+

Resetting the root account

Still in the databse shell, we can now reset the account:

CopyMariaDB [mysql]> UPDATE user SET password='', plugin = 'unix_socket' WHERE user='root';
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

Re-read permissions

When you're done making the above changes, we need to re-read mysql's permission tables since made settings that did not implicitly do that for us.

CopyMariaDB [mysql]> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.01 sec)
MariaDB [mysql]> quit
    Bye

Restart the service

Copysudo mysqladmin shutdown
sudo systemctl start mysql

# Login as root via
sudo mysql

Optional: Changing root's authentication method to password

It's recommended to use the unix socket authentication as described above. If you still want to use a password for the root account use these steps:

CopyMariaDB [mysql]> UPDATE user SET plugin='', password=PASSWORD('newpassword') WHERE user='root';
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
MariaDB [mysql]> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.01 sec)
MariaDB [mysql]> quit
    Bye

sudo mysqladmin shutdown

Fixing startup and maintenance scripts on Debian and Ubuntu

If you've changed root's authentication method, starting and stopping the mysql and some automatic maintenance scripts on Debian and Ubuntu will fail, since those all used now invalid authentication method. Adding the new root password to /etc/mysql/debian.cnf will fix that, though.

Edit the file /etc/mysql/debian.cnf and supply the new password to both password = lines.

After that start the service with sudo systemctl start mysql.