I believe that many users who just installed MySQL on Linux will encounter this problem, how to solve it?I found the answer on StackOverflow (I used method 1 and it worked), carried it over and translated it into Chinese.
The original address: https://stackoverflow.com/questions/39281594/error-1698-28000-access-denied-for-user-rootlocalhost
Problem description: When I log in to the MySQL database via root, I get an ERROR “ERROR 1698 (28000): Access deniedfor user ‘root’ @’ localhost ‘”.
Answer:
This is because MySQL USES the UNIX Auth_Socket Plugin by default in recent Ubuntu installations (and possibly others).
In simple terms, this means that when DB_users use the database, they will be authenticated through the system user authentication table. You can see if your root user is set to this by using the following command:
$ sudo mysql -u root # I had to use "sudo" since is new installation
mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;
+------------------+-----------------------+
| User | plugin |
+------------------+-----------------------+
| root | auth_socket |
| mysql.sys | mysql_native_password |
| debian-sys-maint | mysql_native_password |
+------------------+-----------------------+
As you can see from the query, root is using the AUTH_socket plug-in. There are two ways to solve this problem:
1. You can set your root user to use mysql_native_password plug-in 2. You can create a new database user that is consistent with your system user (recommended)
(Note: Method 2 meets the requirements of auth_Socket plug-in)
Option 1:
$ sudo mysql -u root # I had to use "sudo" since is new installation
mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;
$ service mysql restart
Option 2 (substitute your operating system username for YOUR_SYSTEM_USER) :
$ sudo mysql -u root # I had to use "sudo" since is new installation
mysql> USE mysql;
mysql> CREATE USER 'YOUR_SYSTEM_USER'@'localhost' IDENTIFIED BY '';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'YOUR_SYSTEM_USER'@'localhost';
mysql> UPDATE user SET plugin='auth_socket' WHERE User='YOUR_SYSTEM_USER';
mysql> FLUSH PRIVILEGES;
mysql> exit;
$ service mysql restart
Remember that if you choose to use Method 2, you should connect to MySQL by using your operating system user name (mysql-u YOUR_SYSTEM_USER).
Note: In some operating systems (such as Debian), the ‘auth_Socket’ plugin is called ‘unix_socket’, so the corresponding SQL command statement should be UPDATE User SET plugin= ‘unix_socket’ WHERE user = ‘YOUR_SYSTEM_USER’.
Read More:
- Error 1045 (28000) access denied for user ‘root’ @’localhost ‘appears in MySQL under Windows system
- MySQL connection error access denied for user ‘root’ @ ‘localhost‘
- ER_ACCESS_DENIED_ERROR: Access denied for user ‘root‘@‘localhost‘ (using password: NO)
- Error 1045 (28000): access denied for user ‘root’ @’localhost ‘(using password: no)
- Error 1045 (28000): solution to access denied for user ‘root’ @’localhost ‘(using password: no)
- MySQL startup problem (ERROR 1045 (28000): Access denied for user’ODBC’@’localhost’ (using password: NO))
- Error code 1045, state 28000, Java sql.SQLException :Access denied for user ‘root’@’localhost’
- ERROR 1044 (42000): Access denied for user ”@’localhost’ to database ‘mysql’
- Idea access denied for user ‘xxx’ @ ‘localhost’ solution
- ERROR 1045 (28000): Access denied for user’ODBC’@localhost (using password: NO)209150;’211503;’
- Linux-mysql8.0 password reset problem – error 1396 (HY000): Operation alter user failed for ‘root’ @’localhost ‘
- How to Fix failed to Connect to MySQL at localhost:3306 with user root
- [MySQL] error 1396 (HY000): Operation create user failed for ‘MySQL’ @’localhost ‘
- Mysql Error:The user specified as a definer (‘mysql.infoschema‘@‘localhost‘) does not exist
- Solve net start mysql, start MySQL service, prompt system error 5. Access denied.
- MySQL Access denied
- Make the root user login through SSH and report the error of “permission denied, please try again”
- MySQL Workbench Failed to Connect to MySQL at 127.0.0.1:3306 with user root Bad handshake
- Springboot uses druid to log in MySQL. An error occurred: access denied: errorcode 1045, state 28000
- System error 5 occurred in net start mysql. Access denied)