Resolve MySQL "Access denied for user ' root ' @ ' localhost '"

# mysql-uroot-p
Enter Password:
ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:no)

Use the Web-based method to modify the root user's password:
# mysqladmin-uroot-p password ' newpassword '
Enter Password:
Mysqladmin:connect to server at ' localhost ' failed
Error: ' Access denied for user ' root ' @ ' localhost ' (using Password:yes) '

Now I finally found the solution, as follows (please test method Three first, thank you!) ）：
Method One:
#/etc/init.d/mysql Stop
# Mysqld_safe--user=mysql--skip-grant-tables--skip-networking &
# mysql-u Root MySQL
mysql> UPDATE user SET Password=password (' NewPassword ') where user= ' root ';
mysql> FLUSH privileges;
Mysql> quit

#/etc/init.d/mysql Restart
# mysql-uroot-p
Enter Password: < Enter the newly set password newpassword>

Mysql>

First, MySQL Change Password method summary

The first thing to say is: In general, to modify the MySQL password is required to have root permissions in MySQL, so that the general user is unable to change the password, unless requested to help the administrator to modify.

Method One

Using phpMyAdmin

(graphical management of MySQL database Tools), this is the simplest, directly with the SQL statement to modify the MySQL Database Library user table, but do not forget to use the password function, insert the user with the Insert command, modify the user with the update command, delete with delete command. A detailed description of the data table User field is later in this section.

Method Two

Use Mysqladmin. Input

Mysqladmin-u root-p OldPassword newpasswd

After executing this command, you need to enter the root's original password so that the root password will be changed to NEWPASSWD. Similarly, you can change your password by changing the root of the command to your username.

Of course, if your mysqladmin isn't connected to MySQL,

Server, or you have no way to execute mysqladmin, then this method is invalid, and mysqladmin cannot erase the password.

The following methods are used at the MySQL prompt and must have root privileges for MySQL:

Method Three

Mysql> INSERT into Mysql.user (Host,user,password) VALUES

('% ', ' system ', PASSWORD (' manager '));

Mysql> FLUSH Privileges

Specifically, this is the addition of a user named system with a password of manager. Note To use the password function, and then use the flush

Privileges to perform the confirmation.

Method Four

As with method Three, just use the Replace statement

Mysql> REPLACE into Mysql.user (Host,user,password)

VALUES ('% ', ' system ', PASSWORD (' manager '));

Mysql> FLUSH Privileges

Method Five

Using the Set Password statement

mysql> SET PASSWORD for [email protected] "%" = PASSWORD (' manager ');

You must also use the password () function, but you do not need to use flush privileges to perform acknowledgments.

Method Six

Use Grant ... The identified by statement to authorize.

Mysql> GRANT USAGE On * * to [e-mail protected] "%" identified by ' manager ';

Here the password () function is unnecessary and does not require the flush privileges to perform the acknowledgment.

Note: PASSWORD () function is for password word encryption, in the program MySQL automatic interpretation.

Ii. How to set access restrictions in MySQL

We use two methods to set up the user.

Go to the MySQL execution directory (usually C:/mysql/bin). Input Mysqld-shareware.exe, enter MySQL

--user=root MySQL, or you cannot add new users. Go to the mysql> prompt to proceed.

Suppose we want to create a superuser, the user name is system and the user password is manager.

Method One

Authorization with the grant command, enter the following code:

Mysql>grant all privileges on * * to [e-mail protected] identified by

' manager ' with GRANT OPTION;

Should be displayed: Query OK, 0 rows affected (0.38 sec)

Method Two

To set each user's permissions:

Mysql>insert into user

VALUES (' localhost ', ' system ', PASSWORD (' manager '),

' Y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ');

For the 3.22.34 version of MySQL, there are 14 "Y", with the corresponding permissions (in order of the fields):

Permission table column name explains usage scope accordingly

Select Select_priv requires a SELECT permission table only if it is actually retrieved from a table

Insert Insert_priv allows you to insert a new row into a table in an existing table

Update Update_priv allows you to refresh the list of rows in an existing table with new values

Delete Delete_priv allows you to delete a row table that satisfies a condition

Create Create_priv allows you to make new database and table databases, tables, or indexes

Drop Drop_priv Discard (delete) existing database and table database or table

Reload Reload_priv allows you to tell the server to read in the Authorization Table server Management

Shutdown Shutdown_priv may be abused (by terminating the server to deny service to other users) server management

Process Process_priv allows you to view the normal text of the currently executing query, including setting or changing the password Query server management

File File_priv permissions can be abused to read any readable files on the server to the database table server for access to files

Grant Grant_priv allows you to delegate those permissions you own to other user databases or tables

References References_priv allows you to open and close a log file database or table

Index INDEX_PRIV allows you to create or discard (delete) an indexed table

Alter ALTER_PRIV allows you to change the table, which can be used to override the permission system table by renaming the table

If you create a user with only SELECT, INSERT, UPDATE, and delete permissions, the user is allowed to implement operations only on the existing tables of one database.

Below we can create the database we want to use, we enter directly. For example: To create a database named Xinxiku, use the following code:

Mysql>create database Xinxiku;

Should be displayed: Query OK, 1 row Affected (0.00 sec)

Resolve MySQL "Access denied for user ' root ' @ ' localhost '"