我正在设置一个新的服务器，但一直遇到这个问题。
当我尝试登录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 ?是的，我尝试了安装提示，我在配置文件中找不到这些变量。
操作系统: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吗?
您应该决定重置密码以填写策略或更改策略。
对于那些在macOS上安装最新MariaDB并遵循MariaDB文档中的本教程的用户，请运行:
sudo mariadb-secure-installation
而不是只给出mariadb-secure-installation命令。否则，运气不好，尽管出现错误提示:
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.
Enter current password for root (enter for none):
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
Enter current password for root (enter for none):
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
Enter current password for root (enter for none):
Aborting!
