Changing password for MySQL's account

gistfile1.txt

A root account password can be set several ways. The following discussion demonstrates three methods:

Use the SET PASSWORD statement

Use the UPDATE statement

Use the mysqladmin command-line client program

To assign passwords using SET PASSWORD, connect to the server as root and issue a SET PASSWORD statement for each root account listed in the mysql.user table.

For Windows, do this:

shell> mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'::1' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'%' = PASSWORD('new_password');
The last statement is unnecessary if the mysql.user table has no root account with a host value of %.

For Unix, do this:

shell> mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'::1' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('new_password');
You can also use a single statement that assigns a password to all root accounts by using UPDATE to modify the mysql.user table directly. This method works on any platform:

shell> mysql -u root
mysql> UPDATE mysql.user SET Password = PASSWORD('new_password')
    ->     WHERE User = 'root';
mysql> FLUSH PRIVILEGES;
The FLUSH statement causes the server to reread the grant tables. Without it, the password change remains unnoticed by the server until you restart it.

To assign passwords to the root accounts using mysqladmin, execute the following commands:

shell> mysqladmin -u root password "new_password"
shell> mysqladmin -u root -h host_name password "new_password"
Those commands apply both to Windows and to Unix. The double quotation marks around the password are not always necessary, but you should use them if the password contains spaces or other characters that are special to your command interpreter.

The mysqladmin method of setting the root account passwords does not work for the 'root'@'127.0.0.1' or 'root'@'::1' account. Use the SET PASSWORD method shown earlier.

Ref: Assigning root Account Passwords [https://dev.mysql.com/doc/refman/5.6/en/default-privileges.html]

TODO:: reasons for Do(s) and Don't(s)

Do(s)
====
UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='root';
SELECT User, Host, Password FROM mysql.user;

Don't(s)
=======
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
SET PASSWORD FOR 'joomla_user'@'localhost' = PASSWORD('new_password');
SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('new_password');
grant all on *.* to 'joomla_user'@'localhost';
mysqladmin -u root password new_password