1
---
reset a MySQL root password [ MySQL or MariaDB]
find your server version by issuing the following command:
mysql --version
Copy
If you have MySQL installed in your system the output will look something like this:
mysql Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using EditLine wrapper
Copy
Or output like this for MariaDB:
mysql Ver 15.1 Distrib 10.1.33-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2
Copy
Be sure to make note of which version of MySQL or MariaDB you’re running.
How to Reset MySQL or MariaDB Root Password
Follow these steps to reset your MySQL/MariaDB root password:
1. Stop the MySQL/MariaDB service
To change the root password, first we need to stop the MySQL server. To do so type the following command:
sudo systemctl stop mysql
Copy
2. Start the MySQL/MariaDB server without loading the grant tables
When the --skip-grant-tables option is enabled anyone can to connect to the database server without a password and with all privileges.
To start the database server without loading the grant tables type:
sudo mysqld_safe --skip-grant-tables &
Copy
The ampersand & at the end of the command above will cause the program to run in the background, so we can continue to use the shell.
3. Log into the MySQL shell
Now you can connect to the database server as the root user, without being prompted the password:
mysql -u root
Copy
4. Set a new root password
Run the following commands if you have MySQL 5.7.6 and later or MariaDB 10.1.20 and later:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MY_NEW_PASSWORD';
FLUSH PRIVILEGES;
Copy
If ALTER USER statement doesn’t work for you, try to modify the user table directly:
UPDATE mysql.user SET authentication_string = PASSWORD('MY_NEW_PASSWORD')
WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;
Copy
Run the following commands if you have MySQL 5.7.5 and earlier or MariaDB 10.1.20 and earlier:
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MY_NEW_PASSWORD');
FLUSH PRIVILEGES;
Copy
In both cases if all goes well, you should see the following output:
Query OK, 0 rows affected (0.00 sec)
Copy
Now that the root password is set, we’ll need to stop the database server and start it normally.
Stop the database server using the following command:
mysqladmin -u root -p shutdown
Copy
You will be prompted to enter the new root password:
Start the database server normally:
For MySQL, type:
sudo systemctl start mysql
Copy
For MariaDB, type:
sudo systemctl start mariadb
Copy
6. Verify the password
To verify that the new root password has been applied correctly type:
mysql -u root -p
Copy
You will be prompted to enter the new root password. Enter it, and you should be logged into your database server
--