{"id":406,"date":"2024-07-31T13:35:02","date_gmt":"2024-07-31T13:35:02","guid":{"rendered":"https:\/\/cpanelwebhosting.co.in\/articles\/?p=406"},"modified":"2024-07-31T13:50:13","modified_gmt":"2024-07-31T13:50:13","slug":"reset-password-of-mysql-user","status":"publish","type":"post","link":"https:\/\/cpanelwebhosting.co.in\/articles\/reset-password-of-mysql-user\/","title":{"rendered":"Change or Reset Password of MySQL User from Terminal: A Comprehensive Guide"},"content":{"rendered":"\n<p>Managing MySQL databases is a critical task for any database administrator or developer. One common task is changing or resetting a MySQL user\u2019s password. This can be necessary for various reasons, such as security updates, forgotten passwords, or periodic password changes. In this guide, we&#8217;ll walk you through the process of changing or resetting a MySQL user&#8217;s password from the terminal, covering both MySQL and MariaDB, which is a popular fork of MySQL.<\/p>\n\n\n\n<div class=\"wp-block-rank-math-toc-block\" id=\"rank-math-toc\"><h2>Table of Contents<\/h2><nav><ul><li><a href=\"#prerequisites\">Prerequisites<\/a><ul><li><a href=\"#section-1-changing-a-my-sql-user-password\">Section 1: Changing a MySQL User Password<\/a><ul><li><a href=\"#step-1-log-in-to-my-sql\">Step 1: Log in to MySQL<\/a><\/li><li><a href=\"#step-2-select-the-my-sql-database\">Step 2: Select the MySQL Database<\/a><\/li><li><a href=\"#step-3-change-the-password\">Step 3: Change the Password<\/a><ul><li><a href=\"#method-1-using-the-set-password-statement\">Method 1: Using the SET PASSWORD Statement<\/a><\/li><li><a href=\"#method-2-using-the-update-statement\">Method 2: Using the UPDATE Statement<\/a><\/li><li><a href=\"#method-3-using-the-alter-user-statement\">Method 3: Using the ALTER USER Statement<\/a><\/li><\/ul><\/li><li><a href=\"#step-4-verify-the-change\">Step 4: Verify the Change<\/a><\/li><\/ul><\/li><li><a href=\"#section-2-resetting-a-forgotten-my-sql-user-password\">Section 2: Resetting a Forgotten MySQL User Password<\/a><ul><li><a href=\"#step-1-stop-the-my-sql-service\">Step 1: Stop the MySQL Service<\/a><\/li><li><a href=\"#step-2-start-my-sql-in-safe-mode\">Step 2: Start MySQL in Safe Mode<\/a><\/li><li><a href=\"#step-3-log-in-to-my-sql\">Step 3: Log in to MySQL<\/a><\/li><li><a href=\"#step-4-reset-the-password\">Step 4: Reset the Password<\/a><\/li><li><a href=\"#step-5-restart-my-sql\">Step 5: Restart MySQL<\/a><\/li><li><a href=\"#step-6-verify-the-change\">Step 6: Verify the Change<\/a><\/li><\/ul><\/li><li><a href=\"#section-3-security-best-practices\">Section 3: Security Best Practices<\/a><ul><li><a href=\"#1-use-strong-passwords\">1. Use Strong Passwords<\/a><\/li><li><a href=\"#2-regularly-update-passwords\">2. Regularly Update Passwords<\/a><\/li><li><a href=\"#3-restrict-user-privileges\">3. Restrict User Privileges<\/a><\/li><li><a href=\"#4-monitor-login-attempts\">4. Monitor Login Attempts<\/a><\/li><li><a href=\"#5-use-ssl-tls-for-connections\">5. Use SSL\/TLS for Connections<\/a><\/li><\/ul><\/li><li><a href=\"#conclusion\">Conclusion<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"prerequisites\">Prerequisites<\/h2>\n\n\n\n<p>Before we dive into the steps, ensure that you have the following prerequisites:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Access to the Terminal<\/strong>: You need <a href=\"https:\/\/cpanelwebhosting.co.in\/cpanel-vps-hosting\/\">terminal<\/a> access to the server where MySQL is running.<\/li>\n\n\n\n<li><strong>MySQL Administrative Privileges<\/strong>: You should have root or administrative privileges to change or reset passwords.<\/li>\n\n\n\n<li><strong>MySQL or MariaDB Installed<\/strong>: This guide assumes that you have either MySQL or MariaDB installed on your system.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"section-1-changing-a-my-sql-user-password\">Section 1: Changing a MySQL User Password<\/h3>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"478\" height=\"416\" src=\"https:\/\/cpanelwebhosting.co.in\/articles\/wp-content\/uploads\/2024\/07\/Changing-a-MySQL-User-Password.png\" alt=\"Changing a MySQL User Password\" class=\"wp-image-408\" style=\"width:476px;height:auto\" srcset=\"https:\/\/cpanelwebhosting.co.in\/articles\/wp-content\/uploads\/2024\/07\/Changing-a-MySQL-User-Password.png 478w, https:\/\/cpanelwebhosting.co.in\/articles\/wp-content\/uploads\/2024\/07\/Changing-a-MySQL-User-Password-300x261.png 300w\" sizes=\"(max-width: 478px) 100vw, 478px\" \/><\/figure><\/div>\n\n\n<h4 class=\"wp-block-heading\" id=\"step-1-log-in-to-my-sql\">Step 1: Log in to MySQL<\/h4>\n\n\n\n<p>To start, you need to log in to the MySQL server using the terminal. Use the following command:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>mysql -u root -p<br><\/code><\/pre>\n\n\n\n<p>You will be prompted to enter the root password. Once authenticated, you will be in the MySQL shell.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"step-2-select-the-my-sql-database\">Step 2: Select the MySQL Database<\/h4>\n\n\n\n<p>In MySQL, user information is stored in the mysql user database. Switch to this database using the following command:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>USE mysql;<br><\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"step-3-change-the-password\">Step 3: Change the Password<\/h4>\n\n\n\n<p>There are multiple ways to change the password. Here, we\u2019ll cover the most common methods.<\/p>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"method-1-using-the-set-password-statement\">Method 1: Using the SET PASSWORD Statement<\/h5>\n\n\n\n<p>The SET PASSWORD statement is a straightforward way to change a user\u2019s password. Replace yourusername with the actual username and newpassword with the new password:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>SET PASSWORD FOR 'yourusername'@'localhost' = PASSWORD('newpassword');<br><\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"method-2-using-the-update-statement\">Method 2: Using the UPDATE Statement<\/h5>\n\n\n\n<p>You can also change the password by directly updating the user table. This method involves updating the authentication_string column (or password column in older versions) in the user table. Here\u2019s how:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>UPDATE user SET authentication_string = PASSWORD('newpassword') WHERE User = 'yourusername' AND Host = 'localhost';<br><\/code><\/pre>\n\n\n\n<p>After running the update command, make sure to flush the privileges to apply the changes:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>FLUSH PRIVILEGES;<br><\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\" id=\"method-3-using-the-alter-user-statement\">Method 3: Using the ALTER USER Statement<\/h5>\n\n\n\n<p>The ALTER USER statement is the recommended method for MySQL 5.7.6 and newer:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>ALTER USER 'yourusername'@'localhost' IDENTIFIED BY 'newpassword';<br><\/code><\/pre>\n\n\n\n<p>This method is preferred for its clarity and simplicity.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"step-4-verify-the-change\">Step 4: Verify the Change<\/h4>\n\n\n\n<p>To ensure that the password has been successfully changed, try logging in with the new password:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>mysql -u yourusername -p<br><\/code><\/pre>\n\n\n\n<p>Enter the new password when prompted. If you can log in, the password change was successful.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"section-2-resetting-a-forgotten-my-sql-user-password\">Section 2: Resetting a Forgotten MySQL User Password<\/h3>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"461\" height=\"399\" src=\"https:\/\/cpanelwebhosting.co.in\/articles\/wp-content\/uploads\/2024\/07\/Resetting-a-Forgotten-MySQL-User-Password.png\" alt=\"Resetting a Forgotten MySQL User Password\" class=\"wp-image-411\" style=\"width:412px;height:auto\" srcset=\"https:\/\/cpanelwebhosting.co.in\/articles\/wp-content\/uploads\/2024\/07\/Resetting-a-Forgotten-MySQL-User-Password.png 461w, https:\/\/cpanelwebhosting.co.in\/articles\/wp-content\/uploads\/2024\/07\/Resetting-a-Forgotten-MySQL-User-Password-300x260.png 300w\" sizes=\"(max-width: 461px) 100vw, 461px\" \/><\/figure><\/div>\n\n\n<p>In some cases, you may need to reset a password without knowing the current one. This typically requires restarting the <a href=\"https:\/\/cpanelwebhosting.co.in\/cpanel-vps-hosting\/\">MySQL server<\/a> in a special mode.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"step-1-stop-the-my-sql-service\">Step 1: Stop the MySQL Service<\/h4>\n\n\n\n<p>First, stop the MySQL service using the following command. The exact command may vary depending on your Linux distribution:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>sudo systemctl stop mysql<br><\/code><\/pre>\n\n\n\n<p>For systems using init.d:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>sudo \/etc\/init.d\/mysql stop<br><\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"step-2-start-my-sql-in-safe-mode\">Step 2: Start MySQL in Safe Mode<\/h4>\n\n\n\n<p>Next, start the MySQL server in safe mode with the &#8211;skip-grant-tables option. This allows you to connect to the <a href=\"https:\/\/cpanelwebhosting.co.in\/cpanel-dedicated-server\/\">server<\/a> without a password and with all privileges:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>sudo mysqld_safe --skip-grant-tables &amp;<br><\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"step-3-log-in-to-my-sql\">Step 3: Log in to MySQL<\/h4>\n\n\n\n<p>Log in to the MySQL server as root without a password:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>mysql -u root<br><\/code><\/pre>\n\n\n\n<p>You will now have full access without needing a password.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"step-4-reset-the-password\">Step 4: Reset the Password<\/h4>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"406\" src=\"https:\/\/cpanelwebhosting.co.in\/articles\/wp-content\/uploads\/2024\/07\/reset-password-for-mysql-user--1024x406.png\" alt=\"reset password for mysql user\" class=\"wp-image-409\" style=\"width:648px;height:auto\" srcset=\"https:\/\/cpanelwebhosting.co.in\/articles\/wp-content\/uploads\/2024\/07\/reset-password-for-mysql-user--1024x406.png 1024w, https:\/\/cpanelwebhosting.co.in\/articles\/wp-content\/uploads\/2024\/07\/reset-password-for-mysql-user--300x119.png 300w, https:\/\/cpanelwebhosting.co.in\/articles\/wp-content\/uploads\/2024\/07\/reset-password-for-mysql-user--768x305.png 768w, https:\/\/cpanelwebhosting.co.in\/articles\/wp-content\/uploads\/2024\/07\/reset-password-for-mysql-user-.png 1482w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure><\/div>\n\n\n<p>Use the UPDATE statement to reset the password. For example, to reset the root password:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>UPDATE mysql.user SET authentication_string = PASSWORD('newpassword') WHERE User = 'root';<br><\/code><\/pre>\n\n\n\n<p>For older versions of MySQL, you may need to use the password column instead:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>UPDATE mysql.user SET password = PASSWORD('newpassword') WHERE User = 'root';<br><\/code><\/pre>\n\n\n\n<p>Flush the privileges to apply the change:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>FLUSH PRIVILEGES;<br><\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"step-5-restart-my-sql\">Step 5: Restart MySQL<\/h4>\n\n\n\n<p>Stop the MySQL server again:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>sudo systemctl stop mysql<br><\/code><\/pre>\n\n\n\n<p>Or for systems using init.d:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>sudo \/etc\/init.d\/mysql stop<br><\/code><\/pre>\n\n\n\n<p>Then, restart the MySQL service normally:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>sudo systemctl start mysql<br><\/code><\/pre>\n\n\n\n<p>Or:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>sudo \/etc\/init.d\/mysql start<br><\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"step-6-verify-the-change\">Step 6: Verify the Change<\/h4>\n\n\n\n<p>Try logging in with the new password to ensure that it works:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>mysql -u root -p<br><\/code><\/pre>\n\n\n\n<p>Enter the new password when prompted.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"section-3-security-best-practices\">Section 3: Security Best Practices<\/h3>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"470\" height=\"379\" src=\"https:\/\/cpanelwebhosting.co.in\/articles\/wp-content\/uploads\/2024\/07\/Security-Best-Practices.png\" alt=\"Security Best Practices\" class=\"wp-image-412\" srcset=\"https:\/\/cpanelwebhosting.co.in\/articles\/wp-content\/uploads\/2024\/07\/Security-Best-Practices.png 470w, https:\/\/cpanelwebhosting.co.in\/articles\/wp-content\/uploads\/2024\/07\/Security-Best-Practices-300x242.png 300w\" sizes=\"(max-width: 470px) 100vw, 470px\" \/><\/figure><\/div>\n\n\n<p>Changing or resetting MySQL passwords is crucial, but it&#8217;s also important to follow security best practices to protect your database.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"1-use-strong-passwords\">1. Use Strong Passwords<\/h4>\n\n\n\n<p>Ensure that all MySQL user passwords are strong and complex. Avoid using simple or easily guessable passwords. Use a combination of upper and lower case letters, numbers, and special characters.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"2-regularly-update-passwords\">2. Regularly Update Passwords<\/h4>\n\n\n\n<p>Regularly update MySQL passwords to minimize the risk of unauthorized access. Implement a password rotation policy that requires users to change their passwords periodically.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"3-restrict-user-privileges\">3. Restrict User Privileges<\/h4>\n\n\n\n<p>Grant users the minimum privileges necessary to perform their tasks. Avoid using the root account for routine operations and create specific users with limited permissions.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"4-monitor-login-attempts\">4. Monitor Login Attempts<\/h4>\n\n\n\n<p>Monitor and log all login attempts to your MySQL server. Use tools and scripts to detect and alert on suspicious activities, such as multiple failed login attempts.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\" id=\"5-use-ssl-tls-for-connections\">5. Use SSL\/TLS for Connections<\/h4>\n\n\n\n<p>Enable SSL\/TLS to encrypt connections between MySQL clients and the server. This ensures that sensitive information, including passwords, is transmitted securely.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"conclusion\">Conclusion<\/h3>\n\n\n\n<p>Changing or resetting a MySQL user&#8217;s password from the terminal is a fundamental skill for database administrators. By following the steps outlined in this guide, you can effectively manage user passwords, ensuring the security and integrity of your MySQL databases. Always remember to adhere to security best practices to safeguard your data from unauthorized access and potential threats.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Managing MySQL databases is a critical task for any database administrator or developer. One common task is changing or resetting a MySQL user\u2019s password. This can be necessary for various&hellip;<\/p>\n","protected":false},"author":5,"featured_media":410,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-406","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/cpanelwebhosting.co.in\/articles\/wp-json\/wp\/v2\/posts\/406"}],"collection":[{"href":"https:\/\/cpanelwebhosting.co.in\/articles\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cpanelwebhosting.co.in\/articles\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cpanelwebhosting.co.in\/articles\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/cpanelwebhosting.co.in\/articles\/wp-json\/wp\/v2\/comments?post=406"}],"version-history":[{"count":4,"href":"https:\/\/cpanelwebhosting.co.in\/articles\/wp-json\/wp\/v2\/posts\/406\/revisions"}],"predecessor-version":[{"id":419,"href":"https:\/\/cpanelwebhosting.co.in\/articles\/wp-json\/wp\/v2\/posts\/406\/revisions\/419"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/cpanelwebhosting.co.in\/articles\/wp-json\/wp\/v2\/media\/410"}],"wp:attachment":[{"href":"https:\/\/cpanelwebhosting.co.in\/articles\/wp-json\/wp\/v2\/media?parent=406"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cpanelwebhosting.co.in\/articles\/wp-json\/wp\/v2\/categories?post=406"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cpanelwebhosting.co.in\/articles\/wp-json\/wp\/v2\/tags?post=406"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}