How to change user password on MySQL

Send to Kindle

Today let’s talk about how to change MySQL user password

We can use 2 ways, 1 – mysqladmin, 2 – linguagem SQL

1. mysqladmin:

The syntax is easy:

 mysqladmin -u USER -p password NEWPASSWORD 

Let’s then change the password of ‘marcelo’ user to ’123′

mysqladmin -u marcelo -p password '123'

For this command, we have 3 problems:

. You can just change your own user

. You need SUPER PRIVILEGES to run this command

. If you share you linux user account with other users, this command will appear on historic, to avoid it we can edit ~/.bash_history and delete this lines

2. SQL (the best on my opinion):

To change the password, we’ll just run an update on user’s table on mysql db, you can do this in 2 ways, both have the same result

SET PASSWORD FOR 'user'@'host' = PASSWORD('newpass');

ou

UPDATE mysql.user SET Password=PASSWORD('newpass') WHERE User='user' AND Host='host'; 

After run the above command, we need to FLUSH PRIVILEGES to tell mysql to reload the user table

Let’s change the password of ‘marcelo’ user to ’123′

UPDATE mysql.user SET Password=PASSWORD('123') WHERE User='marcelo' AND Host='localhost';

FLUSH PRIVILEGES;

If you are using mysql command line, you also will have the same problem as mysqladmin, the historic , to hide it, we can edit the file on ~/.mysql_history

Is this, I hope you enjoy!
Any question, just leave a comment

Send to Kindle
This entry was posted in MySQL, password. Bookmark the permalink.

3 Responses to How to change user password on MySQL

  1. Edmar Roberto Prandini says:

    Eu posso usar esse comando SQL no PHPMyAdmin?

  2. PJ Brunet says:

    Note, option #1 will prompt for the old password.

Leave a Reply

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


5 − four =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>