How to Set MySQL root Password on CentOS 6.2

MySQL is a popular open-source relational database management system. By default, the root user account in MySQL is not password protected, which can pose a security risk.

In this guide, we will show you how to set a root password for MySQL on CentOS 6.2.

Step 1: Log in to MySQL as the Root User

The first step is to log in to MySQL as the root user. Open your terminal and type the following command:

mysql -u root

This command will log you in to MySQL as the root user without a password.

Step 2: Set the Root Password

Once you are logged in, you need to set the root password. Run the following command:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('yourpassword');

Replace ‘yourpassword’ with your desired password. Make sure your password is strong and secure.

See also  How to Fix MySQL Database Error: Can't create database 'newdbname' (errno: 28)

Step 3: Flush the Privileges

After setting the root password, you need to flush the privileges to apply the changes. Run the following command:

FLUSH PRIVILEGES;

Step 4: Test the New Root Password

Finally, you need to test the new root password to make sure it is working. Log out of MySQL by running the following command:

exit;

Now, log in to MySQL again using the new password:

mysql -u root -p

You will be prompted to enter the new password. Enter it and hit Enter. If the password is correct, you will be logged in to MySQL.

[root@centos6 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 150
Server version: 5.1.52 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select user,host,password from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | password                                  |
+---------------+-----------+-------------------------------------------+
| root          | localhost |                                           |
| root          | centos6.2 |                                           |
| root          | 127.0.0.1 |                                           |
|               | localhost |                                           |
|               | centos6.2 |                                           |
+---------------+-----------+-------------------------------------------+
7 rows in set (0.00 sec)

mysql> set password for root@'localhost'=password('password');
Query OK, 0 rows affected (0.00 sec)

mysql> set password for root@'127.0.0.1'=password('password');
Query OK, 0 rows affected (0.00 sec)

mysql> set password for root@'centos6.2'=password('password');
Query OK, 0 rows affected (0.00 sec)


mysql> select user,host,password from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | password                                  |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root          | centos6.2 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root          | 127.0.0.1 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
|               | localhost |                                           |
|               | centos6.2 |                                           |
+---------------+-----------+-------------------------------------------+
7 rows in set (0.00 sec)

mysql> select user,host,password from mysql.user;
+---------------+-----------+-------------------------------------------+
| user          | host      | password                                  |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root          | centos6.2 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
| root          | 127.0.0.1 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
|               | localhost |                                           |
|               | centos6.2 |                                           |
+---------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql> exit
Bye

Commands Mentioned:

  • mysql -u root – Log in to MySQL as the root user
  • SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘yourpassword’); – Set the root password
  • FLUSH PRIVILEGES; – Flush the privileges
  • exit; – Log out of MySQL
  • mysql -u root -p – Log in to MySQL with a password
See also  How to Install MySQL Server on RHEL 6

Conclusion

In this guide, we have shown you how to set a root password for MySQL on CentOS 6.2. By following these steps, you can protect your MySQL installation from unauthorized access. We recommend that you use a strong and secure password and keep it safe.

If you have any comments or suggestions for improvements, please feel free to share them below.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *