How to set the mysql password when you use the INSERT or UPDATE statement to store a non-empty PASSWORD, you must use the PASSWORD () function to encrypt it. This is because passwords are stored in encrypted form in the user table, rather than plain text. If you forget this fact, you may try to set a password like this:
Shell> mysql-u root mysql
Mysql> insert into user (Host, User, Password) VALUES ('%', 'Jeffrey ', 'biscuit ');
Mysql> FLUSH PRIVILEGES
The result is that the plain text value 'biscuit' is stored in the user table as the password. When jeffrey tries to use this PASSWORD to connect to the server, mysql uses PASSWORD () to encrypt it and send the result to the server, the server compares the values in the user table (the plain text value is 'biscuit') with the encrypted password (instead of 'biscuit'). The comparison fails and the server rejects the connection:
Shell> mysql-u jeffrey-pbiscuit test
Access denied
Because when they are inserted into the user table, the password must be encrypted. On the contrary, the INSERT statement should be specified as follows:
Mysql> insert into user (Host, User, Password)
VALUES ('%', 'Jeffrey ', PASSWORD ('biscuit '));
When you use the set password statement, you must also use the PASSWORD () function:
Mysql> set password for jeffrey @ "%" = PASSWORD ('biscuit ');
If you use the GRANT... identified by statement or the mysqladmin password command to set a PASSWORD, the password () function is unnecessary. They all consider encrypting your password. you can specify a password like this ':
Mysql> grant usage on *. * TO jeffrey @ "%" identified by 'biscuit ';
Or shell> mysqladmin-u jeffrey password biscuit
Note: PASSWORD () is not different from the Unix PASSWORD encryption method.