EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL User Password

MySQL User Password

Aanchal Sharma
Article byAanchal Sharma
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated June 6, 2023

MySQL User Password

Definition of MySQL User Password

MySQL User Password command modifies the user password by applying several query statements like SET PASSWORD, ALTER USER, GRANT USAGE, and UPDATE. But we need to consider a few significant factors before we perform the password change of a user account in the MySQL server: For which user account you require to change the password in MySQL?

ADVERTISEMENT
Popular Course in this category
MYSQL Course Bundle - 18 Courses in 1 | 3 Mock Tests

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

What application is the user account using whose password will be changed? When the password is changed without modifying the application’s connection string used for the user account, the application may not be able to establish a connection to the database server. Only after confirming all these factors can you proceed further to change the user account password in MySQL.

How does Password Work in MySQL?

  • MySQL database is open-source server software that is essential for managing data records in the form of tables. Users can easily organize, insert, update, delete, store, and retrieve data later. A user must have specific access and privileges to make changes in the database and its associated tables. To make our server restricted for others, we use passwords during login mode to protect our data records.
  • We use this query whenever we forget or want to change the previous password or even when we have never set the password for the root user in our database. In this case, MySQL will run anywhere else in the data center. We can follow some steps to ensure that we never lose the password for the database root user.
  • Suppose when the user is checking for any security game, and we do not remember the password for root because the original password might be something complex or far than simple.
  • By default, MySQL provides the username “root” without applying any password. The password field is left empty, allowing access to the database server. If in the process of installation, you have added a password unintentionally and cannot find now, then we need to change or reset the password.
  • We can use many commands to modify user passwords based on MariaDB or MySQL server version running on your system. You can log in to the MySQL shell command console as root. If you have not provided a password, then leave it empty by default; if you have a password, then login in with it.

How to Create a Password for the User in MySQL?

Let us illustrate some methodologies to create a password for the user account with some examples demonstrated below:

1. with UPDATE Query

We will discuss here the initial step to change the user password using the UPDATE statement that updates the table of the MySQL database user. But remember that after executing the UPDATE command, we also need to run the FLUSH PRIVILEGES command that will be required to reload privileges from the granted MySQL database table.

Suppose for a user named mysqladmin that is connected from the localhost, you want to change the password to MyAdmin, then we will use the following query:

USE mysql;
UPDATE user
SET PASSWORD = PASSWORD('myadmin')
WHERE user = 'mysqladmin' AND host = 'localhost';
FLUSH PRIVILEGES;

We should note that from MySQL 5.7.6, only the authentication_string column is used by the user table to save the password. Additionally, it has uninvolved the password column then. Hence, if MySQL 5.7.6+ is used, then in the UPDATE statement, we should apply the authentication_stringcolumn instead, shown as follows:

USE mysql;
UPDATE user
SET authentication_string= PASSWORD('myadmin')
WHERE user = 'mysqladmin' AND host = 'localhost';
FLUSH PRIVILEGES;

We must know that PASSWORD() calculates the hash value from a natural text value.

2. with SET PASSWORD Query

Secondly, we can change the user password in MySQL with the SET PASSWORD methodology. To modify a password, we must implement a user account, user@hostlayout. The user account should hold at least an UPDATE privilege if we want to change the password for another account. Using the SET PASSWORD query statement, we do not need to run the FLUSH PRIVILEGES command to reload rights from the grant tables. For this, we have an example below as previous to change the user password:

SET PASSWORD FOR 'mysqladmin'@'localhost' = PASSWORD ('myadmin');

But from MySQL 5.7.6 version, MySQl has devalued this syntax, and it may not include future releases. Thus, it implements the plaintext password rather as follows:

SET PASSWORD FOR 'mysqladmin'@'localhost' = myadmin;

3. Change with ALTER USER Query

This is the third method to modify a user password using the statement ALTER USER in MySQL. Here, we will write the ALTER USER command along with the clause as IDENTIFIED BY. Let us view the following query using ALTER USER that will change the user password from old to new:

ALTER USER mysqladmin@localhostIDENTIFIED BY 'myadmin';

Here the user mysqladmin has changed its password to myadmin on query execution.

How to Delete a Password for the User?

  • If the user needs to reset or delete the password related to the MySQL user root account, then the user must force the database MYSQL server to discontinue. Without applying the grant table authentication, you need to restart localhost.
  • If you log in to your user account using SSH(command prompt), stop MySQL using the proper command and restart the server by skipping the grant table options.
  • Afterward, you can set your user account password for the MySQL database host using the UPDATE command statement.

Conclusion

  • It provides support for implementing various query commands on the server to execute changes to user passwords. These commands include MySQL statements like SET PASSWORD, UPDATE, or GRANT USAGE.
  • This helps in server security implementation and maintenance processes to keep our databases secure, updated, and store the backups. Also, for user privileges safety, it may be necessary to apply this User password command to a user account in an application.

Recommended Articles

We hope that this EDUCBA information on “MySQL User Password” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL Offset
  2. MySQL Average
  3. ALTER Column in MySQL 
  4. MySQL ALL
ADVERTISEMENT
C++ PROGRAMMING Course Bundle - 9 Courses in 1 | 5 Mock Tests
40+ Hour of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
ASP.NET Course Bundle - 28 Courses in 1 | 5 Mock Tests
123+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
SQL Course Bundle - 51 Courses in 1 | 6 Mock Tests
205+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
SOFTWARE TESTING Course Bundle - 13 Courses in 1
53+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW