Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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";
share|improve this answer
    
Thanks! Just what I needed. –  jasonaburton Feb 22 '11 at 18:36
add comment

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 ???
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.