This article describes how to change a user's password. You can use three methods: GRANT statement, SETPASSWORD statement, directly modify the authorization table, and use the management tool mysqladmin.
This article describes how to change a user's PASSWORD. You can use three methods: GRANT statement, set password statement, directly modify the authorization table, and use the management tool mysqladmin.
An important application is how to change the password when the root user password is forgotten. You can ignore loading the authorization table when starting the MySQL server.
MySQL uses a user name and password in many ways different from Unix or Windows:
· The username used by MySQL for authentication is irrelevant to the Unix username (login name) or Windows username. By default, most MySQL users attempt to log on using the current Unix user name as the MySQL user name, but this is only for convenience. The client program allows you to use the-u or -- user option to specify a different name, which means that you cannot make a database safer in any case, unless all MySQL usernames have passwords. Anyone can try to connect to the server with any name, and if they specify any name without a password, they will succeed.
· The MySQL user name can be up to 16 characters. Typically, the Unix user name must be 8 characters long.
· The MySQL password has nothing to do with the Unix Password. There is no need to associate the password that you log on to a Unix machine with the password that you use to access a database on that machine.
· The MySQL encryption password uses different algorithms used during Unix logon.
This section describes how to change the password of a MySQL database.
Use the myadmin Utility
The command line for changing the password using the mysqladmin utility is:
Shell> mysqladmin-u user-p password "newpassword"
Run this command. When prompted to enter the password, the data is the password, and the password of the user is changed to newpassword.
. If the original user does not have a password, the-p option is not specified. For example, after the authorization table is initialized, the password of the root user is blank. You can set a password for the root user as follows:
Shell> mysqladmin-u root password "newpassword"
Use the set password statement
One obvious drawback of using mysqladmin to change the password is that you must know the user's original password. You can reset the password for users who forget the password. The SQL statement used to change the PASSWORD is SET PASSWORD:
· Set password = PASSWORD ('some password ')
Set the password of the current user. Any non-Anonymous user can change his or her own password!
After connecting to the server, you can change your password as follows:
The Code is as follows:
Mysql> set password = PASSWORD ('another pass ');
· Set password for user = PASSWORD ('some password ')
Set the password of a specific user on the current server host. Only users with access to the mysql database can do this. The user should be given in the format of user @ hostname. Here, the user and hostname are exactly the same as the User and Host columns listed in the mysql. user table entries. For example, if you have an entry whose User and Host fields are 'bob' and '% .loc.gov', you will write them:
Mysql> set password for bob @ "% .loc.gov" = PASSWORD ("newpass ");
Directly modify the authorization table
Another way to change the password is to directly modify the authorization table user. Only users with access to the mysql database can do this.
For example, if you have an entry whose User and Host fields are 'bob' and '% .loc.gov', you will write them:
Mysql> UPDATE mysql. user SET password = PASSWORD ("newpass") where user = "bob" AND host = "% .loc.gov ";
Mysql> flush privileges;
Reset a forgotten root password
If you forget the password of the root user, it will be very troublesome. Many operations cannot be completed unless you have other privileged users, such as shutting down the database.
You should use the -- without-grant-tables option to start the mysqld service. You can change the content of the authorization table at this time, or use mysqlaccess to check whether your authorization is in place.
For example, if you forget your MYSQL root Password, you can recover it through the following process.
1. Shut down the MySQL server
Send the kill command to the mysqld server to turn off the mysqld server (not kill-9). files that store process IDs are usually located in the directory of the MYSQL database.
Kill 'cat/mysql-data-directory/hostname. Pi'
You must be a UNIX root user or an equivalent user on the SERVER you run to perform this operation.
If you are on windows, you can also stop the process. If it is NT, you can also use the net stop mysql command to close the database.
2. Use the '-- skip-grant-tables' parameter to start mysqld.
Unix platform:
$ Su mysql
$ Safe_mysqld -- skip-grant-tables &
Windows:
C: \ mysql \ bin> mysqld -- skip-grant-tables
The preceding statements are all in the correct directory.
3. Connect to the server and change the password
Use the 'mysql-h hostname mysql' command to log on to the mysqld server and use the grant command to change the password:
Mysql> grant all on *. * TO root @ localhost indentified by 'new password'
-> With grant option;
Mysql> grant all on *. * TO root @ % indentified by 'new password'
-> With grant option;
(If a root user can log on from any address, the user is generated after the authorization table is initialized. To ensure security, you may have deleted the user ).
You can also directly modify the authorization table:
Mysql> use mysql;
Mysql> update user set password = password ('yourpass') where user = 'root ';
You may use mysqladmin to change the password:
Shell> mysqladmin-h hostname-u root password 'new password
However, the password it modified is related to the user that the server matches. If you connect from the server host, the server matches root @ localhost and changes the user password. Otherwise, the root @ % password is changed unless you have other root users.
4. Load the permission table:
Shell> mysqladmin-h hostname flush-privileges
Or run the SQL command 'flush privileges '.
Of course, you can restart mysqld here.
