Category : How-to
If you’ve lost or forgotten the root user password on a MySQL or MariaDB server you’ll want to reset it and leave all the other accounts and data intact. Fortunately it’s possible, but you’ll need access to an SSH account hosting the instance and the ability to stop and start the database service.
Before going any further, make sure your instance of MySQL or MariaDB is shutdown.
service mysql stop
Start the server in safe mode and don’t load the table grants and permissions.
mysqld_safe --skip-grant-tables &
Log into the local instance with the root user.
mysql -u root mysql
Run the below commands SQL, once connected, and reset your password. Be sure to substitute new-password with the new password for your root account.
use mysql; UPDATE mysql.user SET Password=PASSWORD('new-password') WHERE User='root'; FLUSH PRIVILEGES; exit;
Finally, start the SQL server instance and use your new root account password.
service mysql restart mysql -u root -p