An important application is how to modify the password when the root user password is forgotten, using the method is to boot the MySQL server to ignore loading authorization table.
The way that MySQL uses usernames and passwords differs a lot from how UNIX or Windows uses them:
· MySQL uses the user name for the authentication purpose, regardless of the UNIX user name (login name) or the Windows user name. By default, most MySQL customers try to log on using the current UNIX username as the MySQL username, but this is only for convenience. The client program allows you to specify a different name with the-U or--user option, which means that you cannot make a database more secure in any case, unless all MySQL user names have a password. Anyone can attempt to connect to the server by any name, and if they specify any name without a password, they will succeed.
· The MySQL username can be up to 16 characters long and typically a UNIX user name is limited to 8 characters.
· The MySQL password does not matter with the UNIX password. There is no need to have a connection between the password you use to log on to a UNIX machine and the passwords that you use to access a database on that machine.
· The MySQL encryption password uses the different algorithms used during a UNIX logon.
This section describes how to modify passwords for users of the MySQL database system.
Using the MyAdmin utility
The command line to modify a password using the Mysqladmin utility is:
shell>mysqladmin-u user-p Password "NewPassword"
Run this command, when prompted to enter a password, the data is the password, the user's password is changed to NewPassword
。 If the original user does not have a password, then the-p option is not specified. For example, after initializing the authorization table, the root user's password is empty, and you can set the password for root:
shell>mysqladmin-u root Password "NewPassword"
Use statement set PASSWORD
Using Mysqladmin to modify the password for the user has an obvious disadvantage, that is, must know the user's original password, if it is to the forgotten password for users to reset the password is powerless. A SQL statement specifically used to modify the password is set PASSWORD:
· SET PASSWORD = PASSWORD (' some PASSWORD ')
Sets the password for the current user. Anyone who is not anonymous can change his own password!
After connecting to the server, you can change your password as follows:
Copy Code code as follows:
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 who have access to the MySQL database can do so. The user should be given in user@hostname format, where user and hostname are exactly the same as the user and host columns they listed in the Mysql.user table entry. For example, if you have an entry whose user and host fields are ' Bob ' and '%.loc.gov ', you will write:
Mysql> SET PASSWORD for bob@ "%.loc.gov" = PASSWORD ("Newpass");
Directly Modify Authorization Form
Another way to modify the password is to directly modify the authorization table user. Only users who have access to the MySQL database can do so.
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 root user's password, it will be a very troublesome thing to do. Unless you have other privileged users, many operations cannot be completed, such as shutting down the database, and so on.
You should use the--without-grant-tables option to start the MYSQLD service, you can change the contents of the authorization form at this time, you can also 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 procedure.
1. Shut down MySQL server
Send Kill command to MYSQLD server to turn off MYSQLD server (not kill-9), and the file that holds the process ID is usually in the same directory as the MySQL database.
Kill ' Cat/mysql-data-directory/hostname.pid '
You must be a UNIX root user or an equivalent user on the server you are running to perform this operation.
If you are on a Windows platform, you can also stop the process. If it is NT, you can also close the database with the net stop MySQL command.
2, use '--skip-grant-tables ' parameter to start mysqld.
UNIX Platform:
$su MySQL
$safe _mysqld--skip-grant-tables &
Windows platform:
C:\mysql\bin>mysqld--skip-grant-tables
The above statements are assumed to be in the correct directory.
3, connect to the server, modify password
Use the ' mysql-h hostname mysql ' command to log on to the MYSQLD server and change the password with the grant command:
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 there is a root user who can log in from any address, initialize the authorization table, generate the user, and you may have deleted the user for security purposes).
In fact, you can also directly modify the authorization table:
mysql> use MySQL;
mysql> Update user Set Password =password (' Yourpass ') where user= ' root ';
You may use tool mysqladmin to modify your password:
shell> mysqladmin-h hostname-u root password ' new password
But it modifies the cipher language server to match the user. If you are connected from a server host, then the server matches the root@localhost, modifies the user's password, or generally modifies the root@% password unless you have other root users.
4. Loading permission table:
Shell> mysqladmin-h hostname flush-privileges
or use the SQL command ' FLUSH privileges '.
Of course, here, you can also restart mysqld.
