EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Root Password

MySQL Root Password

By Payal UdhaniPayal Udhani

MySQL Root Password

Introduction to MySQL Root Password

Multiple users can have different privileges assigned to each of them in the MySQL database. But, when you want to perform database operations that require many higher privileges, you can use the account created by default in MySQL, which has almost all the privileges assigned to it that is the root user. During the installation process, MySQL automatically creates a default user named “root” on the machine and prompts the user to set a password for this account. If this does not happen and you want to set the password for the default root user, you can do so later. Further, there might be a situation in which you have forgotten your root password and wish to set the new one, or sometimes you will want to set the root user’s password stronger than the previous one. It is essential to set strong passwords for your root user from a security standpoint to ensure that the sensitive data stored in your MySQL database remains protected. In this article, we will learn how to change or set the password to the root user.

Login using Root User

We can use the following command to enter the MySQL database using the root user –

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

sudo mysql -u root -p

that will further prompt for the password set by you while installation of MySQL on your machine and gives the following output –

MySQL Root Password 1

Now, you will see the MySQL shell and work on your database. Let us see the list of users in my MySQL database server. In MySQL, the table named “user” in the default-created database stores all the information related to users. Let us first check all the databases using the following command –

show databases;

that gives the following output –

MySQL Root Password 2

Now, we will specify that we have to use the mysql database using the following command –

use mysql;

that gives the following output after execution –

MySQL Root Password 3

Now, we will fire the select query on the user table in the mysql database using the following query statement –

select user, authentication_string, plugin, password_lifetime, password_last_changed, password_expired from user;

The execution of the above query gives the following output.

MySQL Root Password 4

We have retrieved all the password-related fields of the user in the above select query statement. The user table stores various properties and information fields related to all the users in MySQL. You can describe the user table and see the list of the columns stored in the user table using the following command –

desc user;

That gives the following output.

MySQL Root Password 5

After selecting these columns for the root user, you will observe that the root user has almost all the privileges assigned to it and have a ‘Y’ value stored in the privilege column that stands for yes.

Alternatively, one more command can be used to login as the root user into a MySQL database that is as follows –

sudo -i mysql

That gives the following output.

sudo mysql

The reason why MySQL does not prompt for a username or password in certain configurations is that this information is already stored in the configuration files “my.cnf” or “mysqld.cnf”. These files are typically located at the path “/etc/mysql/mysql.conf.d/mysqld.cnf” on the machine.

Setting up the Password for Root User

Now, let us see how we can set the password for the root user or change the existing password. There are multiple ways in which we can change the root user’s password in case you have forgotten the current password or wish to set up a stronger password. They are listed below. We will see all the methods one by one in detail.

1. Using mysqladmin command

By default, a password is asked during the installation of MySQL for the root user. But suppose that for some reason it was not asked, and now you wish to set the password, then you can make use of the mysqladmin command in the following way to assign the new password to the root user –

sudo mysqladmin -u root password myPassWhichIWant

that gives the following output-

sudo mysql 1

2. Using mysql_secure_connection command

We can use the alternative mysql_secure_connection command instead of mysqladmin. Setting the root password during installation is a more secure and advanced method as it not only allows you to set a strong password for the root user but also provides options to configure additional security features in your MySQL database. For instance, you can restrict the root user’s login from remote machines, remove anonymous users, and delete the test database. Simply type in the following command to use it:

mysql_secure_connection

that asks you multiple questions whose answers will set all the properties you wish to assign.

3. Using ALTER command to change the password of the user

You can change the password of any user using the ALTER command. You can also use this command to set up the password for the root user in the following way:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'a';

a is the new password that I wish to assign. Executing the above command gives the following output –

sudo mysql 2

Save that file for further reference as ~/mysql-password.

The next step lies in stopping the MySQL service by using the following command –

sudo systemctl stop mysql

For this, you will have to exit the MySQL command prompt by clicking ctrl+z and then fire the command that gives the following output –

Stop mysql

Now, fire the command for initializing the process of setting the password

sudo mysqld -init-file=~/mysql-password

Then you will again have to start your MySQL service using the following command –

sudo systemctl start mysql

That results in the following output.

start mysql

Further, you can use mysql -u root -p command to log in with the changed password.

Conclusion

The root is the default user created when MySQL is installed. It has many privileges assigned to it. The user can change the password for the root user account during the installation of MySQL or by utilizing any of the methods mentioned below.

Recommended Articles

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

  1. Unique Key in MySQL
  2. MySQL Subquery
  3. MySQL Constraints
  4. ROLLUP in MySQL
C++ PROGRAMMING Certification Course
38+ Hours of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ASP.NET Certification Course
149+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SQL - Everything in SQL
253+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SOFTWARE TESTING Certification Course
74+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Certification Course
 115+ Hours of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • 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

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
Let’s Get Started

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

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