[MySQL] How to modify the MySQL root password

How to modify the MySQL root password
Forgetting the MySQL root password is a common problem in mysq use, but a lot of friends do not reset the root password, that complain Ah, close-up this article to communicate with you:

1. Edit the MySQL configuration file: My.ini
In general, the MySQL installation directory has a my.ini that is the MySQL configuration file.
Add the following line at the end of this configuration file:
Skip-grant-tables
Save to exit Edit.

2. Then restart the MySQL service
Execute at the command line:
net stop MySQL
net start MySQL

3. Set a new root password
And then execute it at the command line:
Mysql-u root-p MySQL or mysql-u root-p
Directly enter the database without a password.
At this point, execute the use MySQL at the command line;
Now we execute the following statement to update the root password to:
Update user Set Password=password ("root") where user= ' root ';
(Note: Do not use mysqladmin-u root-p password ' Your new password ' This command to change the password, because ' skip-grant-tables ' configuration,
If you don't believe it, you can try it and it will certainly report the following error:
F:\Documents and settings\long>mysqladmin-u root-p password ' root '
Enter Password:
Warning:single quotes were not trimmed from the password by your command
Line client, as you might has expected.
Mysqladmin:
You cannot use ' password ' command as Mysqld runs
With Grant tables disabled (is started with--skip-grant-tables).
Use: "Mysqladmin flush-privileges password ' * '" instead)
Exit to quit MySQL.

4. Restore the configuration file and restart the service

Then modify the MySQL config file to delete the line you just added ' skip-grant-tables '.
Restart the MySQL service again and the password has been modified.
Try it with the new password root, and you can log in again and re-enter MySQL?

Some ways to change passwords with MySQL:
1. Use the MySQL GRANT statement, for example
Mysql-h hostname–u root command to log on to MYSQLD server to change the password with the grant command:
Mysql-h 192.168.1.101-u Root
The top of the 192.168.1.101 is even mysqld run the machine, you replace your own, so log on, you can change the password,
In fact, there is no need for such trouble, direct mysql-u root can be.
GRANT all on * * to ' root ' @ ' localhost ' identified by ' root ' with GRANT OPTION

2. Mysqladmin-u username-p Old password password new password
Example 1: Add a password root to root. First enter cmd, and then type
The following command, as to whether the mysqladmin can be used under CMD,
It depends on your Windows environment variable if you add "E:\Program files\mysql\mysql Server 5.1\bin in Path"; (Please change to your own installation path). )
mysqladmin-u Root Password root
Note: Because Root does not have a password at the beginning, the-p old password can be omitted.
Example 2: Then change the root password to admin.
Mysqladmin–u root-proot Password Admin (note-P do not and the following password points
Write, write together, or it will go wrong, the error is as follows:
F:\Documents and settings\long>mysqladmin-u root-p root password admin
Enter Password: * * *
Mysqladmin:unknown command: ' Root ')
Of course you can also write: mysqladmin–u root-p password Admin Enter,
Then enter your old password, so it is also completely possible to see your hobby.
Example 3: Remove the root user's password again.
F:\Documents and settings\long>mysqladmin-u root-p password;
Enter Password:root
At this point, the root user has no password. The following methods can be set:
F:\Documents and Settings\long>mysql-u Root
Mysql>set password for ' root ' @ ' localhost ' =password (' root ');(syntax: Set password for ' username ' @ ' host ' = password (' password '))
Mysql>set password for ' root ' @ '% ' =password (' root ');
This article is optional, which is in the configuration MySQL database, if you choose to allow root via Telnet, you are in the user table under the MySQL database,
Use MySQL;
SELECT * from user, you can see there are two records, if you do not configure this item, only the first record!
Host User Password
' localhost ', ' root ', ' *9c9f4927129ecc3209d8550dc8b67156fdbf9418 ', ...
'% ', ' root ', ' *81f5e21e35407d884a6cd4a731aebfb6af209e1b ', ...
With the above settings, the root password will become root so that the root password is set to work.

3. Use MySQL;
Update user Set Password =password (' Yourpass ') where user= ' root '

(Note: The following methods I have not tried, I do not know right, I just reproduced a bit:)
The following methods are used at the MySQL prompt and must have root privileges for MySQL:
Method 4
Mysql> INSERT into Mysql.user (Host,user,password)
VALUES ('% ', ' Jeffrey ', PASSWORD (' biscuit '));
Mysql> FLUSH Privileges
Specifically, this is the addition of a user named Jeffrey, with a password of biscuit.
There is this example in the MySQL Chinese reference manual, so I wrote it out.
Note that you want to use the password function, and then you also use the flush privileges.

Method 5
As with method Three, just use the Replace statement
Mysql> REPLACE into Mysql.user (Host,user,password)
VALUES ('% ', ' Jeffrey ', PASSWORD (' biscuit '));
Mysql> FLUSH Privileges

Method 6
Using the Set Password statement,
mysql> SET PASSWORD for [email protected] "%" = PASSWORD (' biscuit ');
You must also use the password () function,
However, you do not need to use flush privileges.

Method 7
Use Grant ... Identified by statement
Mysql> GRANT USAGE On * * to [e-mail protected] "%" identified by ' biscuit ';
Here the password () function is unnecessary and does not require the use of flush privileges.

Note: MySQL new setup user or change password after use flush privileges refresh the MySQL system permission related table,
Otherwise there will be denied access, another way is to restart the MySQL server, so that the new settings take effect.