Mysql – Change thesql password interactively

MySQLpassword

Is it possible to change the user password in mysql in an interactive mode like it one can do in PostgreSQL for example using \password command?
My goal is not to provide the new password together with the command like it is described in mysql documentation about how to change the user password.

Best Answer

From the mysqladmin documentation:

In MySQL 5.6, the new password can be omitted following the password command. In this case, mysqladmin prompts for the password value, which enables you to avoid specifying the password on the command line. Omitting the password value should be done only if password is the final command on the mysqladmin command line. Otherwise, the next argument is taken as the password.

In earlier versions you can't do this as those will complain for 'Too few arguments to change password' (thanks Kaarel for reporting this). In theory you can issue a command like

mysqladmin -u kaarel password newpass

and then it does not appear in the database logs - but may still be stored elsewhere, like .bash_history.

Related Solutions

Thesql change password in session

According to MySQL Documentation on SET PASSWORD

The SET PASSWORD statement assigns a password to an existing MySQL user account. If the password is specified using the PASSWORD() or OLD_PASSWORD() function, the literal text of the password should be given. If the password is specified without using either function, the password should be the already-encrypted password value as returned by PASSWORD().

With no FOR clause, this statement sets the password for the current user. Any client that has connected to the server using a nonanonymous account can change the password for that account

However, you just been struck by lightning because the same webpage says

Important

SET PASSWORD may be recorded in server logs or in a history file such as ~/.mysql_history, which means that plaintext passwords may be read by anyone having read access to that information. See Section 6.1.2, “Password Security in MySQL”.

Note

If you are connecting to a MySQL 4.1 or later server using a pre-4.1 client program, do not use the preceding SET PASSWORD or UPDATE statement without reading Section 6.1.2.3, “Password Hashing in MySQL”, first. The password format changed in MySQL 4.1, and under certain circumstances it is possible that if you change your password, you might not be able to connect to the server afterward.

Your password change is forever, so to speak.

HOW TO FIX IT

Suppose your user is myuser@localhost, you can have someone with ALL PRIVILEGES run this

UPDATE mysql.user SET password=OLD_PASSWORD('[your password]')
WHERE user='myuser' AND host='localhost';
FLUSH PRIVILEGES;

If you have no DBA but can restart mysql, then do this

service mysql restart --skip-networking --skip-grant-tables
mysql -e"UPDATE mysql.user SET password=OLD_PASSWORD('[your password]') WHERE user='myuser' AND host='localhost'"
service mysql restart

Mysql – Forgot thesql owner/root username and password

You have a variety of options to reset the password http://code.openark.org/blog/mysql/recovering-a-mysql-root-password-the-fourth-solution

UPD: Following the Max's advice let me elaborate. The easiest way to reset any password is to restart MySQL with skip-grant-tables in my.cnf. Then MySQL won't ask the password. To set new password execute this:

 mysql -e "UPDATE mysql.user SET password = PASSWORD('newpassword') WHERE user = 'root' and host = '<host>'"

where <host> is host where user connects form.

Then restart MySQL again, but without skip-grant-tables option.

Best Answer

Related Solutions

Thesql change password in session

HOW TO FIX IT

Mysql – Forgot thesql owner/root username and password

Related Question