MySQL password Settings

An important application is how to change the root user's password when you forget it by ignoring the loading of the authorization table when starting the MySQL server.
The method of using username and password by MySQL differs from that used by Unix or Windows in many ways:
· MySQL user name used for authentication purposes, not related to Unix user name (login name) or Windows user name. By default, most MySQL customers try to log in using the current Unix user name as the MySQL user name, but only for convenience. The client allows you to specify a different name with the -u or --user options, which means you can't make a database more secure in any way, unless all MySQL usernames have a password. Anyone can try to connect to the server under any name, and if they specify any name without a password, they will succeed.
· MySQL username can be up to 16 characters; Typically, Unix usernames are limited to eight characters.
· the MySQL password is unrelated to the Unix password. There is no necessary correlation between the password you use to log into one Unix machine and the password you use to access one database on that machine.
· the MySQL encrypted password USES a different algorithm used during an 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"
Run this command, and when prompted for a password, the data is a password, and the password for 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 form, the password for the root user is empty. You can set the password for the root user by:
shell > mysqladmin -u root password "newpassword"
Use statement SET PASSWORD
One obvious drawback to using mysqladmin to change a password for a user is that you must know the user's original password, and there is nothing you can do to reset a password for someone who has forgotten it. One SQL statement for password change is SET PASSWORD:
· SET PASSWORD = PASSWORD('some password')
Sets the password for the current user. Any non-anonymous user can change his/her password!
After connecting to the server, you can change your password by:

　　mysql> SET PASSWORD = PASSWORD('another pass'); 
 ・ SET PASSWORD FOR user = PASSWORD('some password') 

Sets the password for 1 specific user on the current server host. Only users with access to the mysql database can do this. The user should be given user @hostname, where user and hostname are exactly the same as the User and Host columns in their mysql.user table entries. For example, if you have 1 entry whose User and Host fields are 'bob' and '%. loc.gov ', you will write:
mysql > SET PASSWORD FOR bob@"%.loc.gov" = PASSWORD("newpass");
Modify the authorization table directly
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:
mysql > UPDATE mysql.user SET password=PASSWORD("newpass") where user="bob' AND host="%.loc.gov";
mysql > FLUSH PRIVILEGES;
Reset 1 forgotten root password
If you forget the password for an root user, this can be a real hassle. Unless you have other privileged users, many operations cannot be done, such as shutting down the database, etc.
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, or you can check whether your authorization is in place with mysqlaccess.
For example, if you forget your MYSQL root password, you can recover by following the procedure.
1. Shut down the MySQL server
Send the kill command to mysqld server to close mysqld server(not kill-9). The files for the ID process are usually in the directory where MYSQL's database resides.
kill ` cat/mysql - data - directory/hostname pid `
You must be an root user of UNIX or an equivalent user of SERVER on which you are running to perform this operation.
If you are on the windows platform, you can also stop the process. If it's NT, you can also close the database with the net stop mysql command.
2. Start mysqld with the '-- skip-grant-tables 'parameter.
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.
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 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 an root user who can log in from any address, after initializing the authorization form, generate the user, you may have deleted the user for security reasons.
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 the mysqladmin tool to change your password:
shell > mysqladmin -h hostname -u root password 'new password
But it modifies the password language server to match the user concerned. If, you connect from the server host, the server matches root @localhost, change the user password, otherwise 1 will change the root@% password, unless you have another root user.
4. Load the permission table:
shell > mysqladmin -h hostname flush-privileges
Or use the SQL command 'FLUSH PRIVILEGES'.
Of course, you can also restart mysqld here.