EverKB

Knowledge Base Ever

Solve the problem of ERROR 1698 (28000): Access denied for user 'root'@'localhost'

2019-12-18 13:21:50

label: MySQL,MariaDB,mysql_native_password,caching_sha2_password, Access denied for user 'root' @ 'localhost'

For a long time, MySQL has supported different authentication plugins. The original password authorization method is to find the hash value of the password with a specific algorithm and store it in the mysql.user table, but this is not the only authentication method.

When you log in to the server with the correct user and password and have been authorized correctly, the MySQL service also prompts that the service is rejected. You can consider that this may be caused by different password authorization methods.

1. Unix Socket authorization allows users to log in to mysql with a local username and password. The advantage of this authorization method is that there is no need to maintain the authorization password separately. This is possible because Unix Socket native mode is used.
2. The PAM authorization plug-in allows installation, such as LDAP and other back-end systems to authorize, this method is suitable for integration into the organization's existing system.
3. The latest version of MySQL8 uses cache_sha2_password to authorize, which is theoretically more secure. This update will of course be better, but at the cost of upgrading the client driver, otherwise you will need to switch back to the old but compatible licensing method.
Here is the solution: log in to mysql from the command line
sudo mysql -u root -p
Various commands can now be entered. Convert the current database to mysql.
USE mysql;
Run the following command to update the authorization plugin. And immediately update the authorization to make it effective.
UPDATE user SET plugin='mysql_native_password' WHERE User='root';
FLUSH PRIVILEGES;
exit;
Restart mysql
service mysql restart
If you log in to mysql from the shell command line, use the --default-auth option to specify the authorization authentication method. Sometimes you cannot log in because the default authorization verification method is incorrect.
shell> mysql --default-auth=mysql_native_password ...

label

CentOS7 CentOS PHP Linux Python Python3 MySQL MariaDB YUM JavaScript mysql_native_password PHP7 CSS Redis MySQL8 caching_sha2_password HTML Failed to build these modules: _ctypes Minimal ISO substr Bluetooth date sub INTERVAL PECL SQL Date Funcitons Wi-Fi CodeIgniter font-size root Tag Cloud dual network interfaces Laravel ss5 apache httpd CURLFille horiz-align node.js ShadowsocksR vertical-align exec mb_substr PyCharm SUBDATE BarManager date add IntelliJIDEA PEAR SOCKS5 Proxy Server WebStorm Chrome find remi Tag du jQuery PHP5.5 src Apache cURL hide nginx Shadowsocks-Python V2Ray eth0 MariaDB10.4 PostgreSQL sub folder availability date Install MongoDB pdo_sqlsrv SOCKS5 Web Server File Upload mssql-tools register_shutdown_function Symfony DevExpress JetBrain php-fpm sqlsrv Zend Framework ADDDATE height network interface Shadowsocks-Libev unixODBC-devel error_get_last Port 80 string Aura data INDENTIFIED BY password policy requirements SOCKS Web Development Fedora Media Writer msodbcsql Redis5 SVN Deepin jemalloc SQLAlchemy absolute convert FuelPHP mysqlclient Shadowsocks toggle Error Control Operator margin PHPUnit Framework ss5.passwd at-symbol daemon process IIS password SOCKET vps Fatal Errors MongoDB4 sudoers center DATE_SUB Phalcon SQL Server 2017 WiFi .htaccess Compiling PHP frequency conflict set_error_handler TestCase Elasticsearch7.3 PHPUnit ss5.conf apr daemon HTTP particles.js Slim Vincent Garreau Failed to build these modules: _uuid MongoDB Sudo DATE_ADD ip command pg_bha.conf SQL Server width Compiling Apache Frameworks MySQL-python scrollbar Tcp BBR Elasticsearch libpython3.7m.so.1.0 PHPStorm ss5-3.8.9 Apache2.4 CURLOPT_POSTFIELDS NodeJs show VIM