If you have forgotten the root password, the steps below will show you how to reset it easily without deleting and reinstalling the database server. Forgetting your desktop password is one thing. However, forgetting the root password to your MySQL database in production is totally another thing and not fun. And if you did, then this post shows you show you how to recover the root password. Also, for students and new users learning Linux, the easiest place to start learning is Ubuntu Linux. Ubuntu is the modern, open-source Linux operating system for desktops, servers, and other devices. When you’re ready to reset the root password for MySQL or MariaDB, follow the steps below. Depending on the MySQL or MariaDB server version you are running, you will need to use different commands to recover the root password. So, run the commands below to get the version of MySQL or MariaDB. Be sure to take notes of the version of the MySQL or MariaDB database server you’re running. You’ll need them later in the post.
MySQL and MariaDB using root passwords
Both MariaDB and MySQL servers come with the root user set to use the auth_socket authentication method by default. The auth_socket plugin authenticates users that connect from the local host through the Unix socket file. This means that you can’t authenticate as a root by providing a password. However, you can disable the auth_socket plugin using the SQL commands below. If you do, then you’ll be required to type the root password to log on. MySQL: MariaDB: Now you can reset the forgotten password below.
How to reset MySQL or MariaDB root password
To reset the root password for either MySQL or MariaDB, first, stop the database server. You can do that by running the commands below. You can do that for MySQL or MariaDB. After that, run the commands below to start MySQL in safe mode by bypassing the standard authentication process without loading the grant tables: When the –skip-grant-tables option is used, anyone can connect to the database server without a password and with all privileges granted. However, this can only happen if you’re connecting from the local server terminal console. For MySQL servers, you may need to run these commands to get passwords for some weird errors. Then run the commands below to create a new mysqld directory and make mysql user owner. While on the local server, run the commands below to log on as root to the database server. Next, Run the following commands if you run MySQL 5.7.6 and later or MariaDB 10.1.20 and later: If the SQL commands above do not work, try the one below. If you’re running earlier versions of the database server, then run the commands below to reset the password. If all goes well above, you should see the following output without any errors. Now that the root password has been reset, you can stop the server normally by running the commands below. You will be prompted for the new password just created above. Type it to continue with the shutdown. Next, you can start up either server using the commands below. You can now try to log in with the newly created password above. If it works, then you’re all set. Conclusion: This post showed you how to reset the root password for MySQL or MariaDB. If you find any error above or have something to add, please use the comment form below.
