Introduction to MySQL Root
There can be multiple users having different privileges assigned to each of them in the MySQL database. But, when you want to perform the database operations that require many higher privileges, you can use the account created by default in MySQL and has almost all the privileges assigned to it that is the root user. By default, whenever you install MySQL on your machine, the user named root is created, and the password for that user is prompted and asked. 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 where you have forgotten your root password and wish to set the new password, or sometimes you will wish to set the root user’s password stronger than the previous. Setting the strong passwords for your root user is very important from the security view so that the sensitive data stored in your MySQL database remains protected. In this article, we will learn about how we can change the password 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 –
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 –
Now, you will see the MySQL shell, and you work on your database. Let us see the list of the users present in my MySQL database server. All the user-related information is stored in the table named user inside the default created database named MySQL. Let us first check all the databases using the following command –
Show Databases;
that gives the following output –
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 –
Now, we will fire the select query on the user table in the MySQL database using the following query statement –
select user, host from a user;
The execution of the above query gives the following output –
We can see that the user root is present in the database with the localhost as the host. Unlike the newUserForRep user, the root user is not created by me. It already existed in the database when it was installed. There are many properties, and informatical fields stored n the user table for all the user-related information. 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 –
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 –
12 Online Courses | 10 Hands-on Projects| 92+ Hours| Verifiable Certificate of Completion
4.5
View Course
sudo -i mysql
that gives the following output –
We are not asked for the username or password here because that information is stored in my.cnf or mysqld.cnf configuration file located on the path /etc/MySQL/mysql.conf.d/mysqld.cnf
Setting up the password for the root user
Now, let us see how to set the root user’s password or change the existing password. There are multiple ways in which we can change the password of the root user in case if you have forgotten the existing password or wish to set up a new stronger password. They are listed below –
- Using mysqladmin command
- Using mysql_secure_connection command
- Using ALTER command to change the password of the user
We will see all the methods one by one in detail.
Using mysqladmin command
By default, a password is asked while 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 the 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-
Using mysql_secure_connection command
We can use the alternative mysql_secure_connection command instead of mysqladmin that is way more secure and advanced as it not only allows to set the password for the root user but also gives the facility to add and set many secure features to your MySQL database, such as restricting the login of root user from remote machines, anonymous users can be removed, and the test database can also be removed. This command can be used in the following way by simply typing
mysql_secure_connection
that asks you for multiple questions whose answers will set all the properties you wish to assign.
Using ALTER command to change the password of the user
You can change the password of any user using the ALTER command. This command can also be used for setting up the password of the root user in the following way –
ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘a’;
a is the new password that we wish to assign. Executing the above command gives the following output –
Save that file for further reference as ~/MySQL-password.
The next step lies in stoping 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 –
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.
Further, you can use MySQL -u root -p command to log in with the changed password.
Conclusion – MySQL Root
The root is the default user created when MySQL is installed. It has many privileges assigned to it. The password is prompted and asked while installation itself. However, it can be changed by using any of the methods mentioned above.
Recommended Articles
This is a guide to MySQL Root. Here we discuss the Introduction to MySQL Root along with the commands and respective examples. You may also have a look at the following articles to learn more –
