Mysql Essential Create User and Password Commands

mysql --version
mysql  Ver 14.14 Distrib 5.6.43, for Linux (x86_64) using  EditLine wrapper

If the user doesn't exist, You can use grant command to create the user and password as well.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

Here test is the user to which we have granted all the privileges. If the user already exists using ALTER command as shown below...

ALTER USER 'test'@'localhost' IDENTIFIED BY 'password1';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'password1'' at line 1

On my system above command didn't work because ALTER command works only on version MySQL 5.7.6 and later.

However you can use the command UPDATE

mysql> UPDATE mysql.user SET authentication_string = PASSWORD('password1') where user='test';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
FLUSH PRIVILEGES;

We can also use a SET command...

mysql> SET PASSWORD FOR 'test'@'localhost' = PASSWORD('password2');
Query OK, 0 rows affected (0.00 sec)
FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)