How to change the password for root@127.0.0.1 and root@example.com

Question

I was looking at the users in my mysql.user table and noticed that there are 3 roots.

root@localhost
root@127.0.0.1
root@example.com

When I first discovered this none of the roots were password protected. I managed to password protect root@localhost but I can't figure out how to password protect the other two. How would I do this via the command line?

Thanks for any help.

Answer 1

SQL Query:

update user set password=PASSWORD("NEWPASSWORD") where User='root' AND Host="127.0.0.1";
update user set password=PASSWORD("NEWPASSWORD") where User='root' AND Host="example.com";

Answer 2

I have three(3) precautions for you. Please remember the following:

Precaution 1) Always run FLUSH PRIVILEGES after updating mysql.user. Otherwise, changes do not take effect until next restart of mysql.

Precaution 2) If you have binary logging turned on, disable binary logging in your session. Otherwise, the mysql password will be visible in the binary logs. Don't worry, binary logging is on for all other DB connections.

SET sql_log_bin=0;
update user set password=PASSWORD("NEWPASSWORD") where User='root' AND Host="127.0.0.1";
update user set password=PASSWORD("NEWPASSWORD") where User='root' AND Host="example.com';
FLUSH PRIVILEGES;

Precaution 3) In Linux version of mysql, the file /root/.mysql_history records your session typing. The password is also still visible. Edit your changes out using vi.

So as to not worry about turning off binary logging to hide password, this is what I usually do:

I have mysql running on my windows desktop. I ran the following in MySQL for Windows:

lwdba@localhost (DB information_schema) :: select password('NEWPASSWORD');
+-------------------------------------------+
| password('NEWPASSWORD') |
+-------------------------------------------+
| *B845F78DCA29B8AE945AB9CFFAC24A9D17EB5063 |
+-------------------------------------------+
1 row in set (0.00 sec)

Now, run this in MySQL for Linux:

SET sql_log_bin=0;
update user set password='*B845F78DCA29B8AE945AB9CFFAC24A9D17EB5063' where User='root' AND Host="127.0.0.1";
update user set password='*B845F78DCA29B8AE945AB9CFFAC24A9D17EB5063' where User='root' AND Host="example.com';
FLUSH PRIVILEGES;

Three reasons why this is more advantageous:

1. This is more secure. Password in clear text is never present anywhere in Linux.
2. You don't have disable binary logging.
3. Though it will be visible in the binary logs, who can understand MD5 encrypted output ???