我正在设置一个新的服务器，但一直遇到这个问题。
当我尝试登录MySQL数据库与根用户，我得到的错误:
错误1698(28000):用户“root”@“localhost”被拒绝访问
不管我是通过终端(SSH)连接，还是通过phpMyAdmin或MySQL客户端(例如Navicat)连接。他们都失败了。
我看了mysql。用户表，得到如下:
+------------------+-------------------+
| user | host |
+------------------+-------------------+
| root | % |
| root | 127.0.0.1 |
| amavisd | localhost |
| debian-sys-maint | localhost |
| iredadmin | localhost |
| iredapd | localhost |
| mysql.sys | localhost |
| phpmyadmin | localhost |
| root | localhost |
| roundcube | localhost |
| vmail | localhost |
| vmailadmin | localhost |
| amavisd | test4.folkmann.it |
| iredadmin | test4.folkmann.it |
| iredapd | test4.folkmann.it |
| roundcube | test4.folkmann.it |
| vmail | test4.folkmann.it |
| vmailadmin | test4.folkmann.it |
+------------------+-------------------+
如您所见，root用户应该具有访问权限。
服务器非常简单，因为我已经尝试了一段时间来解决这个问题。
它运行Ubuntu 16.04.1 LTS (Xenial Xerus)和Apache, MySQL和PHP，这样它就可以托管网站，iRedMail 0.9.5-1，这样它就可以托管邮件。
在安装iRedMail之前，登录MySQL数据库工作正常。我也试过只安装iRedMail，但根也不能用。
如何解决MySQL登录问题，或者如何在现有的MySQL安装上安装iRedMail ?是的，我尝试了安装提示，我在配置文件中找不到这些变量。
经过几个小时的挣扎，没有任何解决方案，这对我来说很有效。我找到了一个YouTube视频，上面说密码列现在被称为authentication_string。
所以我可以更改我的密码如下:
首先从终端进入MySQL客户端:
sudo mysql
然后在mysql里面，在mysql>后面输入whatever:
mysql> use mysql
mysql> update user set authentication_string=PASSWORD("mypass") where user='root';
mysql> flush privileges;
mysql> quit;
此时，您已经离开了MySQL客户机，回到了正常的终端位置。您需要重新启动MySQL客户端才能生效。对于这种类型，如下所示:
sudo service mysql restart
请参考这个视频链接来更好地理解。
操作系统:Ubuntu 18.04(仿生海狸)
MySQL: 5.7
Add the skip-grant-tables to the end of file mysqld.cnf
Copy the my.cnf file
sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/my.cnf
Reset the password
(base) ➜ ~ sudo service mysql stop
(base) ➜ ~ sudo service mysql start
(base) ➜ ~ mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25-0ubuntu0.18.04.2 (Ubuntu)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed, 3 warnings
mysql> update mysql.user set authentication_string=password('newpass') where user='root' and Host ='localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> update user set plugin="mysql_native_password";
Query OK, 0 rows affected (0.00 sec)
Rows matched: 4 Changed: 0 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
Remove the skip-grant-tables from my.cnf
(base) ➜ ~ sudo emacs /etc/mysql/mysql.conf.d/mysqld.cnf
(base) ➜ ~ sudo emacs /etc/mysql/my.cnf
(base) ➜ ~ sudo service mysql restart
Open the MySQL client
(base) ➜ ~ mysql -uroot -ppassword
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.25-0ubuntu0.18.04.2 (Ubuntu)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
Check the password policy
mysql> select @@validate_password_policy;
+----------------------------+
| @@validate_password_policy |
+----------------------------+
| MEDIUM |
+----------------------------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password_dictionary_file | |
| validate_password_length | 8 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | MEDIUM |
| validate_password_special_char_count | 1 |
+--------------------------------------+--------+
6 rows in set (0.08 sec)！
Change the configuration of the validate_password
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.05 sec)
mysql> set global validate_password_mixed_case_count=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_number_count=3;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_special_char_count=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=3;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| validate_password_dictionary_file | |
| validate_password_length | 3 |
| validate_password_mixed_case_count | 0 |
| validate_password_number_count | 3 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 0 |
+--------------------------------------+-------+
6 rows in set (0.00 sec)
Note
你应该知道你的错误是由什么引起的?validate_password_policy吗?
您应该决定重置密码以填写策略或更改策略。
经过几个小时的研究，我找到了解决方案。
停止MySQL
sudo service mysql stop
制作MySQL服务目录。
sudo mkdir /var/run/mysqld
赋予MySQL用户写入服务目录的权限。
sudo chown mysql: /var/run/mysqld
手动启动MySQL，不需要权限检查或联网。
sudo mysqld_safe --skip-grant-tables --skip-networking &
无需密码即可登录。
mysql -uroot mysql
更新密码
UPDATE mysql.user SET authentication_string=PASSWORD('YOURNEWPASSWORD'), plugin='mysql_native_password' WHERE User='root' AND Host='%';
EXIT;
关闭MySQL。
sudo mysqladmin -S /var/run/mysqld/mysqld.sock shutdown
正常启动MySQL服务。
sudo service mysql start
