Posted about 9 years ago. Visible to the public.

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.

Copy
sudo 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

Copy
MariaDB [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:

Copy
MariaDB [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.

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

Restart the service

Copy
sudo 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:

Copy
MariaDB [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.

Growing Rails Applications in Practice
Check out our new e-book:
Learn to structure large Ruby on Rails codebases with the tools you already know and love.

Owner of this card:

Avatar
Florian Heinle
Last edit:
4 months ago
by Andreas Vöst
Keywords:
mysql, mariadb
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Florian Heinle to makandra dev
This website uses short-lived cookies to improve usability.
Accept or learn more