ERROR 1698 (28000) 1045（28000）Access denied for user ‘root‘@‘localhost‘，设置简单mysql密码

问题1：ERROR 1698 (28000) ：Access denied for user ‘root‘@‘localhost‘

转自：https://stackoverflow.com/questions/39281594/error-1698-28000-access-denied-for-user-rootlocalhost

Some systems like Ubuntu, mysql is using by default the UNIX auth_socket plugin.

Basically means that: db_users using it, will be "auth" by the system user credentias. You can see if your root user is set up like this by doing the following:

$ 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 in the query, the root user is using the auth_socket plugin

There are 2 ways to solve this:

1. You can set the root user to use the mysql_native_password plugin
2. You can create a new db_user with you system_user (recommended)

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;

$ sudo service mysql restart

Option 2: (replace YOUR_SYSTEM_USER with the username you have)

$ 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 'YOUR_PASSWD';
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;

$ sudo service mysql restart

Remember that if you use option #2 you'll have to connect to mysql as your system username (mysql -u YOUR_SYSTEM_USER)

Note: On some systems (e.g., Debian stretch) 'auth_socket' plugin is called 'unix_socket', so the corresponding SQL command should be: UPDATE user SET plugin='unix_socket' WHERE User='YOUR_SYSTEM_USER';

Update: from @andy's comment seems that mysql 8.x.x updated/replaced the auth_socket for caching_sha2_password I don't have a system setup with mysql 8.x.x to test this, however the steps above should help you to understand the issue. Here's the reply:

One change as of MySQL 8.0.4 is that the new default authentication plugin is 'caching_sha2_password'. The new 'YOUR_SYSTEM_USER' will have this auth plugin and you can login from the bash shell now with "mysql -u YOUR_SYSTEM_USER -p" and provide the password for this user on the prompt. No need for the "UPDATE user SET plugin" step. For the 8.0.4 default auth plugin update see, https://mysqlserverteam.com/mysql-8-0-4-new-default-authentication-plugin-caching_sha2_password/

 

问题2：ERROR 1045 (28000) ：Access denied for user ‘root‘@‘localhost‘

转自：https://stackoverflow.com/questions/21944936/error-1045-28000-access-denied-for-user-rootlocalhost-using-password-y

Note: For MySQL 5.7+ please see answer from @Lahiru to this question. That contains more current information.

For MySQL < 5.7:

The default root password is blank (i.e. empty string) not root. So you can just login as:

mysql -u root

You should obviously change your root password after installation

mysqladmin -u root password [newpassword]

In most cases you should also set up individual user accounts before working extensively with the DB as well.

 

问题3：mysql8.0 设置简单密码报错ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

转自：https://www.jianshu.com/p/b437566ccf98

【起因】

    有时候只是因为内网访问使用测试服务器，不想设置复杂的密码，比如root：123456
但是新版 mysql 加入密码安全度检测机制，导致报错
解决方法如下

1.查看当前安全变量值

mysql> SHOW VARIABLES LIKE 'validate_password%';

2.修改变量

注意到8.0 比5.7多了带“.”的变量导致只设置一半是不够的，

set global validate_password.policy=0;
set global validate_password.length=4;

然后退出后再执行

mysql_secure_installation

继续重置root密码就可以设置为123456了