我正在设置一个新的服务器,但一直遇到这个问题。
当我尝试登录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 ?是的,我尝试了安装提示,我在配置文件中找不到这些变量。
我在Debian 8 (Jessie)虚拟机上遇到了这个问题,我在Windows 10桌面上通过PuTTY进行交互。
我尝试了这里的各种建议,但都不太管用,我正在Debian主机上运行MariaDB。最后,我发现我无法在安全模式下启动数据库服务器,但我不需要这样做,下面的命令实际上对我有用,即允许新创建的MySQL用户登录到MySQL/MariaDB服务器:
sudo service mysql restart
sudo mysql # Logs in automatically into MariaDB
use mysql;
update user set plugin='' where user='your_user_name';
flush privileges;
exit;
sudo service mysql restart # Restarts the MySQL service
如果上面的方法对你不太管用,那就按照zetacu的帖子中列出的步骤来做,然后再按照我的步骤来做。
现在你应该能够使用远程终端客户端,并安全地登录到MySQL使用命令:
mysql -u your_user_name -p
*在提示时输入密码
操作系统: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吗?
您应该决定重置密码以填写策略或更改策略。
@zetacu和@peter给出的答案非常准确,但只有一部分对我有用。在这里添加这个给正在使用的用户
mysql Ver 8.0.30-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))
我的用户表是这样的:
mysql> SELECT User,Host,plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| User | Host | plugin |
+------------------+-----------+-----------------------+
| debian-sys-maint | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| pk | localhost | auth_socket |
| root | localhost | auth_socket |
+------------------+-----------+-----------------------+
6 rows in set (0.00 sec)
因此,首先我采用了第二个(推荐的)选项,用您拥有的用户名替换YOUR_SYSTEM_USER。所以,我用同样的方法创建了一个新用户,但没有任何效果。
然后我尝试了第一种方法,设置根用户使用my_native_password插件:
sudo mysql -u root
mysql> USE MySQL;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE
User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;
sudo service mysql restart
它成功了!!因此,只需创建一个新用户,并使其使用my_native_password插件。
在一些系统上,比如Ubuntu, MySQL默认使用Unix auth_socket插件。
基本上,这意味着:db_users使用它,将由系统用户凭证“验证”。您可以通过执行以下操作查看您的根用户是否像这样设置:
sudo mysql -u root # I had to use "sudo" since it was a 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 |
+------------------+-----------------------+
正如您在查询中看到的,根用户正在使用auth_socket插件。
有两种方法可以解决这个问题:
您可以设置root用户使用mysql_native_password插件
您可以使用system_user创建一个新的db_user(推荐)
选项1:
sudo mysql -u root # I had to use "sudo" since it was a 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
选项2:(用您拥有的用户名替换YOUR_SYSTEM_USER)
sudo mysql -u root # I had to use "sudo" since it was a 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
记住,如果你使用选项#2,你必须连接到MySQL作为你的系统用户名(MySQL -u YOUR_SYSTEM_USER)。
注意:在某些系统上(例如Debian 9 (Stretch)), 'auth_socket'插件被称为'unix_socket',因此相应的SQL命令应该是:
从andy的评论来看,MySQL 8.x。X更新/替换了caching_sha2_password的auth_socket。我没有MySQL 8.x的系统设置。X来检验这个。但是,上面的步骤应该可以帮助您理解这个问题。下面是回答:
MySQL 8.0.4的一个变化是新的默认认证插件是'caching_sha2_password'。新的“YOUR_SYSTEM_USER”将有这个身份验证插件,你可以从Bash shell登录,现在使用“mysql -u YOUR_SYSTEM_USER -p”,并在提示符上提供该用户的密码。不需要执行“UPDATE user SET plugin”步骤。
关于8.0.4默认认证插件的更新,请参见MySQL 8.0.4: New default authentication plugin: caching_sha2_password。