MySQL is an open-source relational database management system (RDBMS). It is open-source and free software, under GNU General Public License as well as available under various proprietary licenses. MySQL is an integral part of many popular software stacks that allow you to build and maintain everything, from web-facing applications to data-driven B2B services to complex customer-facing web apps. It now supports distributed applications and is included in many cloud data platforms. MySQL backends are used by internet-critical organizations like Facebook, Flickr and Twitter.
MariaDB, a community-developed fork MySQL, is supported by commercial support. It is based on SQL and supports ACID-style data processing with guaranteed atomicity, consistency, isolation and durability for transactions. The database supports JSON APIs and parallel data replication. It also supports multiple storage engines including InnoDB and MyRocks, Spider and Cassandra. MariaDB is used by RedHat, Ubuntu and Tumblr as well as Amazon Web Services, SUSE Linux and many other companies.
In MySQL, root is the default user that was created during installation of the ecosystem. It is the user with highest privilege If you are installing MySQL or MariaDB in Linux for the first time, chances are you will be executing mysql_secure_installation script to secure your MySQL installation with basic settings. Now the root access is protected by default with a password. It is one thing to forget your desktop password. It’s one thing to forget your desktop password but forgetting the root password to your MySQL database in production is very dangerous.
So, if you forget your root password to your MySQL database in production, the steps in this article will help you reset it without having to delete and reinstall the server.
Prerequisites
These are the prerequisites for following the steps in this tutorial:
- A properly configured Ubuntu 20.04 server with MySQL or MariaDB.
- You should have the latest version of mysql/mariadb installed on Ubuntu system.
- You must have sudo user access on MySQL or MariaDB running Linux machine.
You will need different commands depending on which version of MariaDB or
MySQL you are using to recover your root password.
Check MySQL Version
To determine the version of MySQL and MariaDB, run the following commands.
ubuntu@ubunu2004:~$ mysql –version
The default behavior for the MySQL or MariaDB server is to start at system startup. We need to modify the service in order to complete the first step. To do this, we must stop the service. Stopping the MySQL server first is necessary to reset the root password.
Stop MySQL and MariaDB Services
Run the following commands to stop mysql and mariadbd services.
ubuntu@ubunu2004:~$ sudo systemctl stop mariadb
ubuntu@ubunu2004:~$ sudo systemctl stop mysql
Restart the Database Server Without Permission Checking
Then, use the following commands to start MySQL in safe mode. This bypasses the standard authentication process and doesn’t load the grant tables
If the –skip–grant-tables option has been used, anyone can connect to the database server without the need for a password. All privileges are granted. This can only occur if you connect from the local terminal console.
The environment variable has been set. It is time to start MySQL/MariaDB. There will be no output. Check the service status to confirm that the server was started successfully.
ubuntu@ubunu2004:~$ sudo systemctl set-environment MYSQLD_OPTS=”–skip-grant-tables –skip-networking”
ubuntu@ubunu2004:~$ sudo systemctl start mariadb
ubuntu@ubunu2004:~$ sudo systemctl status mariadb
You can now connect to the database with the root user. This should not require you to enter a password. Instead, you will see the database shell prompt.
ubuntu@ubunu2004:~$ sudo mysql -u root
Next, run the command below:
ubuntu@ubunu2004:~$ sudo systemctl edit mysql
It will open a file with the nano editor. It will also include the MySQL service overloads. By default, It will be empty. Add the following lines to the file and save it:
[Service]
ExecStart=
ExecStart=/usr/sbin/mysqld –skip-grant-tables –skip-networking
Restart MySQL and MariaDB
We have twice declared ExecStart. The default value is cleared by the first instance. The second sets the startup command and parameters to disable networking and grant tables. To apply these changes, restart mysql by reloading system and then verify the status.
ubuntu@ubunu2004:~$ sudo systemctl daemon-reload
ubuntu@ubunu2004:~$ sudo systemctl start mysql
ubuntu@ubunu2004:~$ sudo systemctl status mysql
Reset the Root Password
Now, it’s time to change the root password. The ALTER USER command is a simple way to modify the root password in modern MySQL versions. The FLUSH PRIVILEGES command will instruct the database server to reload grant tables.
Run the commands below one by one.
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.000 sec)
Now run alter command and change the root password.
MariaDB [(none)]> ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘test@1234’;
Query OK, 0 rows affected (0.001 sec)
MariaDB supports custom authentication mechanisms. These two statements will reset MariaDB’s default authentication mechanism:
MariaDB [(none)]> UPDATE mysql.user SET authentication_string = ” WHERE user = ‘root’;
Query OK, 0 rows affected (0.001 sec)
Rows matched: 1 Changed: 0 Warnings: 0
MariaDB [(none)]> UPDATE mysql.user SET plugin = ” WHERE user = ‘root’;
Query OK, 0 rows affected (0.000 sec)
Rows matched: 1 Changed: 0 Warnings: 0
Reverting Database Changes
Now the root password has been successfully reset.
In MariaDB, we only need to reset the value of MYSQLD_OPTS to the previous value. Run the following command:
ubuntu@ubunu2004:~$ sudo systemctl unset-environment MYSQLD_OPTS
MariaDB must be restarted in order to take effect the changes.
ubuntu@ubunu2004:~$ sudo systemctl restart mariadb
Reverting MySQL
In the case of MySQL, we had to modify the systemd configuration. One way is to remove the modified systemd configuration:
ubuntu@ubunu2004:~$ sudo systemctl revert mysql
Finally, restart MySQL:
ubuntu@ubunu2004:~$ sudo systemctl restart mysql
Now, verify if the database server is accessible as the root user with the new password.
ubuntu@ubunu2004:~$ mysql -u root -p
Conclusion
So that was all about how to reset your root password for MySQL and MariaDB. All the instruction in this article will work for Ubuntu 12.04. Now, administrative access to MySQL and MariaDB has been restored. To prevent unauthorized access to administrative power, the new password must be strong. To further secure MySQL, go through our article on MySQL performance and security optimization.
