Подключение к базе данных не удалось неизвестная ошибка код ошибки 1698

I’m setting up a new server and keep running into this problem.

When I try to log into the MySQL database with the root user, I get the error:

ERROR 1698 (28000): Access denied for user ‘root’@’localhost’

It doesn’t matter if I connect through the terminal (SSH), through phpMyAdmin or a MySQL client, e.g., Navicat. They all fail.

I looked in the mysql.user table and get the following:

+------------------+-------------------+
| 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 |
+------------------+-------------------+

As you can see, user root should have access.

The Server is quite simple, as I have tried to troubleshoot this for a while now.

It’s running Ubuntu 16.04.1 LTS (Xenial Xerus) with Apache, MySQL and PHP, so that it can host websites, and iRedMail 0.9.5-1, so that it can host mail.

Log into the MySQL database works fine before I installed iRedMail. I also tried just installing iRedMail, but then root also doesn’t work.

How can I fix my MySQL login problem or how can I install iRedMail over an existing MySQL install? And yes, I tried the Installation Tips and I can’t find those variables in the configuration files.

Step 1. sudo mysql -u root -p

Step 2. USE mysql;

Step 3. ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'admin';

Here ‘admin’ is your new password, but you can change it.

Step 4. exit

You are done.

After hours of struggling without any solution here, this worked for me. I found a YouTube video where it says the password column is now called authentication_string.

So I was able to change my password as follows:

First get into the MySQL client from the terminal:

sudo mysql

Then inside mysql, type whatever after mysql>:

mysql> use mysql
mysql> update user set authentication_string=PASSWORD("mypass") where user='root';
mysql> flush privileges;
mysql> quit;

At this point you are out of the MySQL client, back to your normal terminal place. You need to restart the MySQL client for this to take effect. For that type, the following:

sudo service mysql restart

Refer to this video link for a better understanding.

For the first

sudo mysql -u root -p

SHOW VARIABLES LIKE 'validate_password%';

we will see something like this:

+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| 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      |
+--------------------------------------+--------+

We need to change these rows:

1. validate_password.length
2. validate_password.number_count
3. validate_password.policy
4. validate_password.special_char_count

SET GLOBAL validate_password.policy=LOW;
SET GLOBAL validate_password.length=4;
SET GLOBAL validate_password.number_count=0;
SET GLOBAL validate_password.special_char_count=0;

SHOW VARIABLES LIKE 'validate_password%';

We will see:

+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | ON    |
| validate_password.dictionary_file    |       |
| validate_password.length             | 4     |
| validate_password.mixed_case_count   | 1     |
| validate_password.number_count       | 0     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 0     |
+--------------------------------------+-------+

Now exit from the MySQL client:

exit;
sudo mysql -u root -p

And now you can write your password, four or more only letters.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new-password';

exit;

sudo mysql -u root -p

Your new password is in the database for user ‘root’;

In my case,

mysql -u root -p

Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I am sure my password was correct. Otherwise, the error code would be ERROR 1045 (28000): Access denied for user

So I relogin using sudo,

sudo mysql -u root -p

This time it worked for me. See the documentation.

And then change the root password,

mysql> alter user 'root'@'%' identified with mysql_native_password by 'me123';
Query OK, 0 rows affected (0.14 sec)

mysql>

Then restart the server using sudo /etc/init.d/mysql restart.

OS: Ubuntu 18.04 (Bionic Beaver)

MySQL: 5.7

1. Add the skip-grant-tables to the end of file mysqld.cnf

2. Copy the my.cnf file

   sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/my.cnf
   

3. 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
   

4. 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
   

5. 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>
   

6. 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)！
   

7. 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

You should know that you error caused by what? validate_password_policy?

You should have decided to reset the your password to fill the policy or change the policy.

This worked for me on mysql Ver 15.1:

$ sudo mysql

MariaDB [mysql]> use mysql;
MariaDB [mysql]> set password for 'root'@'localhost' = password('YOUR_ROOT_PASSWORD_HERE');
MariaDB [mysql]> flush privileges;
MariaDB [mysql]> quit

Login as ‘root’

mysql -u root -p

Credits to Raoul HATTERER

answered Oct 26, 2022 at 9:26

the answer given by @zetacu and @peter is very accurate but only part of it worked for me. Adding this here for users who are using

mysql  Ver 8.0.30-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

So, my user table looked like this:

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)

So, first I followed the second(recommended) option of replacing YOUR_SYSTEM_USER with the username you have. So, I created a new user with same approach but nothing worked.

Then I tried the first approach to set root user to use my_native_password plugin:

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

AND IT WORKED!!! SO, just create a new user and make it use my_native_password plugin.

answered Oct 27, 2022 at 12:37

For those installing the latest MariaDB on macOS and following this tutorial in MariaDB’s documentation, run:

sudo mariadb-secure-installation

instead of just the mariadb-secure-installation command given. Otherwise, no luck, despite the erroneous prompt:

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!

This has happened to me as well. The problem is with the MySQL repository that comes already with the Linux distribution. So when you simply do:

sudo apt install mysql-server

It installs MySQL from their default repository which gives this problem. So to overcome that you need to uninstall that installed MySQL:

sudo apt remove mysql* --purge --auto-remove

Then download the MySQL repository from official MySQL website MySQL APT repository.

Follow their documentation on how to add a repository and
install it. This gives no issue.

Also as answered by zetacu, you can verify that the MySQL root user now indeed uses the mysql_native_password plugin.

I have done the following steps to get rid of this issue.

Log in into the MySQL in your machine using (sudo mysql -p -u root) and hit the following queries.

1. CREATE USER ‘jack’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘<>’;

2. GRANT ALL PRIVILEGES ON . TO ‘jack’@’localhost’;

3. SELECT user,plugin,host FROM mysql.user WHERE user = ‘root’;

   +------+-------------+-----------+
   | user | plugin      | host      |
   +------+-------------+-----------+
   | root | auth_socket | localhost |
   +------+-------------+-----------+
   

4. ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘<>’;

5. FLUSH PRIVILEGES;

Please try it once if you are still getting the error. I hope this code will help you a lot!!

Пользователи для mysql и для сервера — это две разные вещи, посмотрите, как добавить пользователя в базу данных и войти с этими учетными данными

Ещё вопросы

• 1Группировка массивов и получение последнего и первого массива для каждой группы
• 0Наложение на window.history.go (-1)
• 0как сохранить данные в базе данных с помощью jquery
• 0Как выровнять текст внутри <span> после <button>
• 1Android — сохранение состояния активности при запуске новой активности
• 1Обнаружение исключений в родительском классе
• 1Java статическая переменная экземпляра перезаписана в другом экземпляре?
• 0Высота CSS 100% в автоматическом режиме Brother DIV не работает в Chrome
• 0Почему двусторонняя привязка не работает в некоторых ситуациях?
• 1преобразовать массив numpy из объекта dtype в float
• 1Будет ли HBase сканировать строки, которые не содержат семейств, которые я хочу вернуть
• 0JQuery UI — DatePicker — установка второго на основе выбора из первого
• 1Используйте функцию связывания дважды [дубликата]
• 1Простой JavaScript — ScrollIntoView внутри Div
• 1Полноэкранный предварительный просмотр после захвата изображения на камеру в Android
• 1Ошибка атрибута: у объекта ‘Ball’ нет атрибута ‘x’
• 1AndroidRuntime порка
• 0вызвать функцию через jquery и вывести результаты в div в бритве html
• 0Knockout.js для переключения языков на двуязычном сайте
• 1Автозаполнение имени файла в скрипте Python
• 0SVG элемент из массива
• 0Интервал между <li> в [дубликате]
• 1Magento 2 вызвать пользовательскую библиотеку в select.html
• 0HTML5 — заголовок и схема алгоритма — путаница
• 1Селен: не могу найти элемент
• 1снимок экрана для деятельности
• 1Невозможно запустить очень простой COM-клиент
• 1Использование функции map () в классе
• 0получить число между двумя строками, используя подстроку
• 1Можно ли написать скрипт unittest, который не закрывает браузер при каждом тесте?
• 1Как переопределить свиток в просмотре списка
• 0IE условные комментарии ‘и’?
• 1При подключении в PyQt4 QMessageBox не может вызвать метод слота
• 0Как смоделировать команду curl с Angular
• 0Попытка изменить переменную сеанса CodeIgniter перед загрузкой, работает только на перезагрузку?
• 1Ограничения веб-служб Exchange (EWS)
• 1Создание ярлыка на андроид
• 0Загрузка списка выбора из массива с пробелами
• 1Керас: как реализовать целевую репликацию для LSTM?
• 0mariaDB — не могу связаться с php / страница просто пуста [дубликаты]
• 1javascript ответ загрузки XMLHttpRequest после ответа сервера
• 1Построить высокоточную диаграмму для нескольких серий с использованием данных JSON
• 0Как мне показать div, но сделать его полностью неинтерактивным, в Chrome
• 1Добавить несколько изображений на холст с помощью C #
• 1Изменить логическое значение по умолчанию с помощью OrmLite в БД
• 0Почему обработчики событий не работают после того, как я изменил src изображения?
• 0Как создать уникальную область видимости каждый раз, когда вызывается директива (AngularJS)
• 1Некорректное округление двойных значений валюты
• 0Наверное, очень просто — получить три контейнера рядом друг с другом?
• 1[Vue warn]: Ошибка в функции рендеринга: «TypeError: Невозможно прочитать свойство« совпало »с неопределенным» [duplicate]