Ошибка 1698 access denied for user root localhost - ErrorsMaster.ru - большая энциклопедия ошибок и их решений

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.

Jeff Holt

2,9303 gold badges22 silver badges29 bronze badges

asked Sep 1, 2016 at 22:06

On some systems, like Ubuntu, MySQL is using the Unix auth_socket plugin by default.

Basically it means that: db_users using it, will be «authenticated» by the system user credentials. 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 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 |
+------------------+-----------------------+

As you can see in the query, the root user is using the auth_socket plugin.

There are two ways to solve this:

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

Option 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

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

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

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 9 (Stretch)) the ‘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';

From andy’s comment it 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 authentication plugin and you can log in from the Bash shell now with «mysql -u YOUR_SYSTEM_USER -p» and provide the password for this user on the prompt. There isn’t any need for the «UPDATE user SET plugin» step.

For the 8.0.4 default authentication plugin update, see MySQL 8.0.4: New Default Authentication Plugin: caching_sha2_password.

answered Mar 12, 2017 at 1:17

zetacuzetacu

16.9k2 gold badges18 silver badges26 bronze badges

A new version of MySQL does it this way

In the new MySQL client, if the password is left empty while installing then, it is based on the auth_socket plugin.

The correct way is to log in to MySQL with the sudo privilege.

sudo mysql -u root -p

And then updating the password using:

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

Once this is done, stop and start the MySQL server.

sudo service mysql stop
sudo service mysql start

For complete details, you can refer to this link.

answered Sep 14, 2018 at 16:17

NandeshNandesh

4,3872 gold badges20 silver badges26 bronze badges

No need of sudo

The database is initialised with 2 all-privilege accounts: the first one is «root» which is inaccessible and the second one with your user name (check with command whoami).

To enable access to root account, you need to login with your user name

mysql -u $(whoami)

and manually change password for root

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

mysql -u root -p

answered Jan 10, 2020 at 18:31

I was having this issue on an Debian 8 (Jessie) VM that I was interacting with through PuTTY on my Windows 10 desktop.

I tried the various suggestions on here, but nothing quite worked and I am running MariaDB on the Debian host. In the end I found that I couldn’t start the database server in safe mode, but I didn’t need to and the following commands actually worked for me, i.e., allowing a newly created MySQL user to log into the MySQL/MariaDB server:

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

If the above doesn’t quite work for you, follow the steps outlined in zetacu’s post, and then follow my steps.

Now you should be able to use a remote terminal client and securely log into MySQL using the command:

mysql -u your_user_name -p

*Type in the password when prompted

answered Nov 11, 2017 at 14:03

TrevorTrevor

1,5611 gold badge20 silver badges28 bronze badges

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.

answered Dec 1, 2018 at 11:04

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.

answered Jan 29, 2020 at 17:09

I would suggest to remove the MySQL connection —

This is for MySQL version 5.5. If your version is different, please change the first line accordingly.

sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-5.5 mysql-client-core-5.5
sudo rm -rf /etc/mysql /var/lib/mysql
sudo apt-get autoremove
sudo apt-get autoclean

And install again, but this time set a root password yourself.
This will save a lot of effort.

sudo apt-get update
sudo apt-get install mysql-server

answered Mar 10, 2017 at 12:25

Eminem347Eminem347

3774 silver badges11 bronze badges

I found my solution after hours of research here.

Stop MySQL

sudo service mysql stop

Make MySQL service directory.

sudo mkdir /var/run/mysqld

Give MySQL user permission to write to the service directory.

sudo chown mysql: /var/run/mysqld

Start MySQL manually, without permission checks or networking.

sudo mysqld_safe --skip-grant-tables --skip-networking &

Log in without a password.

mysql -uroot mysql

Update password

UPDATE mysql.user SET authentication_string=PASSWORD('YOURNEWPASSWORD'), plugin='mysql_native_password' WHERE User='root' AND Host='%';
EXIT;

Turn off MySQL.

sudo mysqladmin -S /var/run/mysqld/mysqld.sock shutdown

Start the MySQL service normally.

sudo service mysql start

answered Sep 3, 2019 at 6:41

Sanjun DevSanjun Dev

5188 silver badges20 bronze badges

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:

validate_password.length

validate_password.number_count

validate_password.policy

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’;

answered Jan 14, 2021 at 20:16

TaronTaron

1691 silver badge13 bronze badges

I also faced the same issue for the first time.

Now it is fixed:

First, you copy the /etc/mysql/mysql.conf.d/mysqld.cnf file and paste in to /etc/mysql/my.cnf.

You can do it by the command:

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

Now let’s reset the password:

Use the following commands in your terminal:

sudo service mysql stop
sudo service mysql start
sudo mysql -u root

Now you are inside the MySQL console.

Then let’s write some queries to reset our root password:

USE mysql
update mysql.user set authentication_string=password('newpass') where user='root' and Host ='localhost';
update user set plugin="mysql_native_password";
flush privileges;
quit

Now we can clean /etc/mysql/my.cng.

Open the above file in your editor and remove the whole lines inside the file.

After that let’s restart MySQL:

sudo mysql service restart

Now let’s use MySQL with the newly created password:

sudo mysql -u root -p

Finally enter your newly created password.

answered Jul 18, 2019 at 11:42

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.

answered Dec 3, 2019 at 13:16

This worked for me:

mysql --user=root mysql
CREATE USER 'some_user'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'some_user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

answered Dec 30, 2018 at 21:19

DoradDorad

3,4562 gold badges43 silver badges70 bronze badges

This works for me with MySQL version 8.0.26 and Ubuntu 20.04 (Focal Fossa).

sudo mysql -u root

mysql> USE mysql;
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 |
| root             | localhost | auth_socket           |
+------------------+-----------+-----------------------+

mysql> UPDATE user SET

plugin='caching_sha2_password' WHERE User='root';

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'you_mysql_password';
mysql> FLUSH PRIVILEGES;
mysql> exit;

sudo service mysql restart

answered Sep 3, 2021 at 9:48

WhileWhile

611 bronze badge

OS: Ubuntu 18.04 (Bionic Beaver)

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

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.

answered Mar 23, 2019 at 4:36

iamcxliamcxl

6971 gold badge7 silver badges15 bronze badges

First step: go to file /etc/phpmyadmin/config.inc.php, and then uncomment lines where you find «AllowNoPassword».

Second step: log in to your MySQL default account

mysql -u root -p

use mysql;
update user set plugin="" where user='root';
flush privileges;

And that’s all!

answered Apr 17, 2018 at 20:00

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

mysql -u root -p

Credits to Raoul HATTERER

answered Oct 26, 2022 at 9:26

BlackBlack

17.7k39 gold badges154 silver badges268 bronze badges

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!

answered Jan 28, 2022 at 22:25

Geyser14Geyser14

1,3353 gold badges14 silver badges32 bronze badges

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.

answered Jan 13, 2019 at 11:03

sudipsudip

1431 gold badge1 silver badge9 bronze badges

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

CREATE USER ‘jack’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘<>’;
GRANT ALL PRIVILEGES ON . TO ‘jack’@’localhost’;

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

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

ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘<>’;
FLUSH PRIVILEGES;

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

answered Dec 31, 2020 at 5:20

For anyone that tried the solution here and nothing works, make sure you are using the correct command, sudo sudo mysql -u root -p and not mysql mysql -u root -p.

You’ll need to enter two passwords, the one of the current user and the root one.

answered May 21, 2021 at 1:47

You want to access MySQL with root user but you’re not providing root’s correct password.

If you need to set a new password for root, MySQL’s site has great documentation on how to do it: B.3.3.2 How to Reset the Root Password

I’ll not show the process in here, because MySQL’s documentation on the above link is clear and concise.

answered Sep 2, 2016 at 0:36

Источник

I found another way that is much better as we need not to give any password for local system.
It is as followed.

Open terminal and type

sudo mysql -u root -p

It will prompt you in mysql, here you can fire any mysql commands.

Use mysql table for change table type, so we can use empty password. Bellow is command for it

USE mysql;

Now we change type of table by following command

UPDATE user SET plugin='mysql_native_password' WHERE User='root';

now we have to flush the privileges, because we have used UPDATE. If you use INSERT, UPDATE or DELETE on grant tables directly you need use FLUSH PRIVILEGES in order to reload the grant tables.

FLUSH PRIVILEGES;

now exit from mysql by following command

exit;

now restart mysql server by following command

service mysql restart

Hope this may help

Thank you.

Источник

With our easy steps, you can easily solve error 1698 (28000) Access denied for the user.

This error is very common when you try to log in to your MySQL for the first time.

Personally, I too faced this issue multiple times, maybe because we were not following the right way to install MySQL, or we forgot to set a password, or else we didn’t get the options to set a password.

Similarly, there may be many other reasons for the above error, but the solution to this error is pretty simple and easy. In this article, you will find a way to resolve 1698 (28000) Access denied for user ‘root’@’localhost’.

There are two ways to resolve this error.

First, you will see a simple and less time-consuming method. If the first method didn’t work in your case, then go to the second one.

Method 1: The Easiest Way to Fix Error 1698 (28000): Access Denied for User

One of the easiest ways to resolve this error is to invoke “mysql_secure_installation”, which will ask you to set a password for your MySQL root user.

$ sudo mysql_secure_installation

You need to enter a new password and, for confirmation, re-type that password.

If the password is less than 25 characters, you will be prompted to confirm whether you want to continue or not. If you don’t want to set password characters more than 25, simply type “n”.

solve error 1698 (28000) Access denied for user: Fix error using mysq_secure_installation

Fix error using mysql_secure_installation

In my case, it didn’t work and threw me an error on-screen:

“Failed! Error: SET PASSWORD has no significance for user ‘root’@’localhost’ as the authentication method used doesn’t store authentication data in the MySQL server.

Please consider using ALTER USER instead if you want to change authentication parameters.”

If the same thing happens with you too, then close the terminal and follow the second set of steps, which worked in my case to solve error 1698 (28000) Access denied for user.

Method 2: Fix Error 1698 (28000): Access Denied for User using Alter

When you install MySQL for the first time, there is a chance you will not find the option to set a password because of the new method of authenticating users.

If it is like that, then you need to login to MySQL as a root user.

Most of the time, users may try to log in to MySQL as “mysql -u user” or “mysql -u root”, but it won’t work because “[email protected]” has not yet been created on your system, and root cannot be accessed without sudo privileges.

And when you try to log in as the root user with “mysql -u [email protected]”, you will get the below error.

MySQL Error 1698(2800)

Check the Authorization Mechanism

To resolve this, you must log into MySQL as a sudo on the new installation because the account that is linked with MySQL is the “root” account, not the “user” account.

Run the below command in your terminal:

$ sudo mysql -u root

Once you are logged in, you will find the MySQL console looking like the below-shown image.

Login to MySQL with sudo

Now you can access your MySQL account with the above command, but there is a small problem with this: you cannot log in to MySQL without sudo privileges.

To fix that, you can create a new user and grant the necessary privileges, or else change the password authentication mechanism of the root account.

There are different types of password mechanisms supported by MySQL by default you will find “auth_socket” for your root account to make it robust you can choose conventional “mysql_native_password” or the latest “caching_sha2_password“ on later version 8.0.0

If you want to learn more about authentication plugins, then do check out MySQL authentication documentation.

Before moving to the next step, you should check what password mechanism or plugin is attached to your root account.

To find out, you can run the following queries on the MySQL console:

mysql> SELECT User, plugin from mysql.user ;

The behavior of the above command is shown below:

Check password plugin in MySQL

Set a password on the root account

Now you can set or update your password by following the below snippet. Make sure to replace [ENTER-NEW-PASSWORD] with your password and, prior to that, select the MySQL database.

mysql> use mysql;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH 'caching_sha2_password' BY '[ENTER-NEW-PASSWORD]';

Output:

Set Password on MySQL

I’m using “caching_sha2_password”.

If you want to use “mysql_native_password” on > MySQL 5.7 version, type the below queries on your MySQL console.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '[ENTER-NEW-PASSWORD]';

Once the changes have been implemented, restart the MySQL service.

$ sudo systemctl restart mysql.service

And once the service is restarted, type the below code in your terminal and enter the password which you have set above.

$ mysql -u root -p

Output:

Login back to MySQL after resolving the error

Wrapping Up

That’s all to resolve error 1698 (28000) Access denied for user on MySQL.

I’m sure after following the above steps you will be able to resolve the issue, but if you are still having any issues, then do let me know in the comment section.

If you are interested in learning about how to change the MySQL port number, then check out this article.

See you later in the next article.

profile

A man with a tech effusive who has explored some of the amazing technology stuff and is exploring more. While moving towards, I had a chance to work on Android development, Linux, AWS, and DevOps with several open-source tools.

Источник

Are you getting the following error when trying to login to the MySQL root on an Ubuntu system or other Linux distribution? We’ll show you how to clear it up in a matter of seconds.

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

This error is caused by the auth_socket plugin. The simplest solution is to just disable the plugin. Here are the commands to do it:

1. Login to MySQL as root (sudo will bypass password prompt)

$ sudo mysql -u root

2. Change to the mysql database and set the plugin to mysql_native_password for the root user.

mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;

3. You have to change the root password for this to work. Even just specifying the current password is fine too, but this command must be executed:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password_here';
mysql> exit;

4. Lastly, restart MySQL for the changes to take effect.

$ sudo systemctl restart mysql

5. You can now login to MySQL root as normal, and will no longer encounter the error.

$ mysql -u root -p

Источник

I’m working on Xubuntu 15.04. I already installed the MariaDB-Server on various systems and was always asked for a root password during installation. This time, however, I don’t remember being asked for the password. When I try to login without a password (or a blank password), I get the Access denied for user 'root'@'localhost' error.
I tried uninstalling the package completely by

sudo apt-get remove mariadb-server
sudo apt-get purge mariadb-server

When I reinstalled, I still didn’t get asked for the root password.

I tried the mysqld --skip-grant-tables approach from
mysql how to fix Access denied for user ‘root’@’localhost’ .
I can modify the password for the root user in the mysql database — at least the hash value changes — but I still cannot login with the new password after a restart of the mysql-server. I still get the same error.

The user debian-sys-maint does not exist. So, I cannot use it to fix anything.

Any ideas what else I could try?

asked Aug 15, 2015 at 16:12

You need to reset the password.
so for that

sudo mysql -u root
use mysql;
update user set plugin='' where User='root';
flush privileges;
exit;

answered May 15, 2016 at 16:16

The idea with the new set-up is that you shouldn’t be using passwords at all. See UNIX_SOCKET Authentication Plugin for details.

What’s especially relevant is the contents of /usr/share/doc/mariadb-server-10.0/README.Debian.gz on Ubuntu 16.04:

On new installs no root password is set and no debian-sys-maint user is
created anymore. Instead the MariaDB root account is set to be authenticated
using the unix socket, e.g. any mysqld invocation by root or via sudo will
let the user see the mysqld prompt.

You may never ever delete the mysql user «root». Although it has no password
is set, the unix_auth plugin ensure that it can only be run locally as the root
user.

The credentials in /etc/mysql/debian.cnf specify the user which is used by the
init scripts to stop the server and perform logrotation. This used to be the
debian-sys-maint user which is no longer used as root can run directly.

So if you disable that plug-in for root and set a password, the daily cron job will break as it’s assuming it will log in as root without a password, but with the plug-in.

Later it says:

Scripts should run as a user have have the required grants and be
identified via unix_socket.

So it looks like passwords should no longer be used by applications.

answered Jul 21, 2016 at 20:22

colancolan

1,0259 silver badges15 bronze badges

I solved the problem following the answer from this post:

Can’t reset MySQL (MariaDB) root password

One has to change the plugin field of mysql.user for all roots to a blank string.

answered Aug 28, 2015 at 6:42

d72b617cd72b617c

2991 gold badge3 silver badges6 bronze badges

I did it by running this command, right after installation:

$ sudo mysql_secure_installation

At first step, password is blank, so just press Enter.

answered May 30, 2017 at 1:57

Just use sudo mysql -u root — that’s it

Details: Newer versions authenticate to mysql using system authentication. So if you can sudo to the OS, it assumes you’re db root too. You can confirm this by issuing sudo mysql -u root -e "USE mysql; SELECT User, Host, plugin FROM mysql.user;". You should see something like this (maybe with auth_socket in other distros)

+------+-----------+-------------+
| User | Host      | plugin      |
+------+-----------+-------------+
| root | localhost | unix_socket |
+------+-----------+-------------+

answered Oct 12, 2018 at 23:09

DeepSpace101DeepSpace101

8,68712 gold badges44 silver badges47 bronze badges

I had the same issue on a raapberry pi with stretch. My solution was to create a new user with all privileges by doing the following:

sudo mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root

MariaDB [(none)]> use mysql
MariaDB [mysql]> FLUSH PRIVILEGES;
GRANT ALL privileges ON mysql.* TO 'admin'@'localhost' with grant option;

Now I can login and us the user «admin» as superuser.

I hope this is going to help someone.

answered Nov 5, 2017 at 13:44

Источник