Update root password in MariaDB 10.4 on MacOS

There is some changes in authentication procedures in MariaDB 10.4, hence, you can not use previous techniques to change root password. I will show you how to update root password in MariaDB 10.4 on MacOS.

Update root password in MariaDB 10.4

Steps for MariaDB prior to 10.4

In any MariaDB version prior to 10.4, you can easily change the root password by following steps:

1. Stop currently running MariaDB server.
2. Start new instance with mysqld_safe --skip-grant-tables option.
3. Access to MariaDB from CLI, often being mysql.
4. Execute SQL query to change password.

The SQL query varies depending on versions.

UPDATE user SET password=PASSWORD("secret") WHERE User='root';

-- Or

UPDATE user SET authentication_string=PASSWORD("secret") WHERE User='root';

However, if it won’t work under MariaDB 10.4+.

Steps for MariaDB 10.4+

To handle this in latest version at the time of this writing, we do this:

1. Stop currently running MariaDB server.

2. Start MariaDB as usual, no need to use mysqld_safe.

$ brew services start mariadb

3. Access MySQL shell from CLI with sudo.

$ sudo mysql -u root

Without sudo it will throw error message:

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

4. Update root password. As of MariaDB 10.4, password and authentication_string are not updatable, so you cannot use two previously mentioned SQL queries. Instead, use ALTER USER.

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

and that just works.

or you can use the SET PASSWORD query.

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('secret');

Make sure to FLUSH PRIVILEGES; after that.

Conclusion

Congratulation! you’ve got a new root password as I have shown you how to update root password in MariaDB 10.4 on MacOS and it also works for Windows and Linux.