Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to change the password of MySQL". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to change the password of MySQL".

Summary: 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's password is forgotten, by ignoring the loading of the authorization table when starting the MySQL server.

There are many differences between the way user names and passwords are used by MySQL and those used by Unix or Windows:

MySQL is a user name used for authentication purposes, regardless of the Unix user name (login name) or Windows user name. By default, most MySQL customers try to log in with the current Unix user name as the MySQL user name, but this is only for convenience. The client program allows you to specify a different name with the-u or-- user option, which means you can't make a database more secure anyway 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 long; typically, the Unix user name is limited to 8 characters.

The MySQL password has nothing to do with the Unix password. There is no need to correlate between the password you use to log in to a Unix machine and the password you use to access a database on that machine.

The MySQL encrypted password uses a different algorithm used during Unix login.

This section describes how to change the password for users of the MySQL database system.

Use the myadmin utility

The command line to change the password using the mysqladmin utility is:

Shell > mysqladmin-u user-p password "newpassword"

When you run this command, when prompted for a password, the data is encrypted, and the password of the user user is changed to newpassword.

. If the original user does not have a password, the-p option is not specified. For example, after initializing the authorization table, the password for the root user is empty, and you can set the password for the root user like this:

Shell > mysqladmin-u root password "newpassword"

Use the statement SET PASSWORD

There is an obvious disadvantage of using mysqladmin to change the password for the user, that is, you must know the user's original password, and there is nothing you can do if it is to reset the password for the user who has forgotten it. A SQL statement specifically used to change passwords is SET PASSWORD:

SET PASSWORD = PASSWORD (some password)

Sets the password for the current user. Any non-anonymous user can change his own password!

After connecting to the server, you can change your password like this:

Mysql > SET PASSWORD = PASSWORD (another pass)

SET PASSWORD FOR user = PASSWORD (some password)

Sets the password for a specific user on the current server host. Only users with access to the mysql database can do this. The user should give it in format, where user and hostname are exactly the same as the User and Host columns of their mysql.user table entries. For example, if you have an entry whose User and Host fields are bob and% .loc.gov, you will write:

Mysql > SET PASSWORD FOR "= PASSWORD (" newpass ")

Modify the authorization table directly

Another way to modify the password is to modify the authorization table user directly. 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:

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 a very troublesome thing. Unless you have other privileged users, many operations cannot be done, such as shutting down the database, and so on.

You should use the-without-grant-tables option to start the mysqld service, where you can change the contents of the authorization form, or you can use mysqlaccess to check that your authorization is in place.

For example, if you forget your root password for MYSQL, you can recover it through the following procedure.

1. Shut down the MySQL server

Send a kill command to mysqld server to turn off mysqld server (not kill-9), and the files where the process ID is stored are usually in the same directory as MYSQL's database.

Kill `cat / mysql-data-directory/ hostname.pid`

You must be a root user of UNIX or an equivalent user on the SERVER you are running to perform this operation.

If you are on the platform, you can also stop the process. If it is NT, you can also use the net stop mysql command to close it.

2. Use the-- skip-grant-tables parameter to start d.

Unix platform:

$su mysql

$safe_mysqld-skip-grant-tables &

Windows platform:

C:mysqlin > mysqld-- skip-grant-tables

The above statements are assumed to be in the correct directory.

3. Connect to the server and change the password

Log in to mysqld server using the mysql-h hostname mysql command and change the password with the grant command:

Mysql > GRANT ALL ON *. * TO INDENTIFIED BY new password

-> WITH GRANT OPTION

Mysql > GRANT ALL ON *. * TO INDENTIFIED BY new password

-> WITH GRANT OPTION

If there is a root user who can log in from any address, after initializing the authorization table, generate the user, for security reasons, you may have deleted the user.

In fact, you can also modify the authorization table directly:

Mysql > use mysql

Mysql > update user set password = password (yourpass) where user=root

You may use the tool mysqladmin to change your password:

Shell > mysqladmin-h hostname-u root password new password

But it modifies the password server to match the user. If you connect from the server host, then the server matches by changing the user's password, otherwise the password is generally changed unless you have another root user.

4. Load the permissions table:

Shell > mysqladmin-h hostname flush-privileges

Or use the SQL command `FLUSH PRIVILEGES.

Of course, you can also restart mysqld here.

Thank you for your reading, the above is the content of "how to change the password of MySQL", after the study of this article, I believe you have a deeper understanding of how to change the password of MySQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.