Mysql ошибка 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.

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,4462 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.6k39 gold badges154 silver badges266 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

Источник

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

Источник

When I try to login to MySQL database, I get some error message like “error 1698 (28000)”. Please help!

That was a recent support ticket received at our Server Support department where we resolve support queries for web hosts.

Website owners often face this error when they access the MySQL database as root user.

So, what’s the reason behind this? And, how to resolve this?

Today, we’ll discuss the reason for this error and how our Server Supported Engineers fix it.

‘error 1698’ MySQL error – What this means?

Before we move on to the reasons, let’s first get an idea of MySQL error ‘error 1698′.

Website owners face this error when MySQL disallows the root user to access the database.

For instance, users see the complete error message like this:

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

In addition to that, website owners will not be able to connect to the database via a terminal, PHPmyadmin or MySQL client.

In our experience managing websites, majority of such complaints are raised by customers on a Ubuntu machine.

‘error 1698’ Mysql error – Causes and solutions

First, let’s see the main cause of this error identified by our Support Engineers during our debugging process.

Some systems like Ubuntu don’t use root password, instead Mysql root account is configured to use the auth_socket or unix_socket plugin for authentication.

mysql> USE mysql;
mysql> SELECT User, Host, plugin FROM mysql.user;

+------------------+-----------------------+
| User | plugin |
+------------------+-----------------------+
| root | auth_socket |
+------------------+-----------------------+

This plugin allows the user to use Operating System credentials when connecting to the database.

In other words, this plugin doesn’t care for password. It checks if the user is connecting via UNIX socket and then compares the username.

For example, a user authenticate to the Ubuntu system as test, so MySQL won’t trust the user when he login as root user, because this user is different from the system user test.

Result is ‘error 1698‘ Mysql error.

How we fix?

Now, let’s see how our Support Engineers fix this error.

1) Set root user to use ‘mysql_native_password‘ plugin

‘mysql_native_password’ implements authentication against mysql.user table using the native password authentication method.

So, the first solution our Hosting Engineers use, is to update the root user password and change the root user’s authentication plugin to ‘mysql_native_password’.

For example, we use the following command to change the root user’s authentication plugin to mysql_native_password.

user mysql;
update user set plugin='mysql_native_password' where User = 'root';

And, in order for the changes to reflect, and the privileges to be saved, we use the below command.

FLUSH PRIVILEGES;

[Need assistance in fixing MySQL errors in your website? Our MySQL experts can assist you. We are available 24/7.]

2) Add the system user to the MySQL ‘user’ table

Basically, mysql_native_password is the traditional method of authentication, but it isn’t secure, because it uses just a hash of the password.

So, the safe option that our Security Engineers always recommend is to add the system user to the MySQL user table with auth_socket plugin enabled.

For example, we use the following command to add the system user testing to the MySQL user table with the auth_socket plugin.

USE mysql;
CREATE USER 'testing'@'localhost' IDENTIFIED BY '';+
UPDATE user SET plugin='auth_socket' WHERE User='testing';

After that, we grant all privileges to this user, and flush the privileges for the changes to take effect.

Now, the user can login to MySQL with this system user.

3) Disable UNIX_socket authentication

Another simple solution to this problem is to disable UNIX socket authentication.

Our Hosting Engineers disable unix_socket authentication for root user using the following command.

use mysql;
update user set plugin='' where User='root';

Now, the root user can login with standard password authentication.

We’ve discussed 3 different ways to resolve this error.

Considering the security aspect, our MySQL experts always suggest server owners to follow the second step.

Because, it’s always good practice to create a new user and leave the root user there.

[Need a Mysql expert to look at this error? Our Dedicated Support Engineers can fix this for you within minutes.]

Conclusion

In short, ‘error 1698‘ Mysql error occurs due to the incorrect authentication plugin enabled for the root user. Today, we’ve discussed the 3 easy methods to solve this error and the most reliable solution suggested by our Server Support Engineers.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

SEE SERVER ADMIN PLANS

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Источник

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

Источник

11.06.2019

В сегодняшней статье разберемся как устранить ошибку:

#1698 – Access denied for user ‘root'@'localhost'

при попытке подключиться к БД из под root.

MySQL 5.7 изменила модель безопасности: теперь вход в MySQL под рутом (root) требует sudo (при этом пароль всё равно может быть пустым). Т.е. phpMyAdmin невозможно использовать под пользователем root.

Самым простым (и самым безопасным) решением будет создать нового пользователя и предоставить ему требуемые привилегии.

Но начнём мы с другого решения:

Разрешить пользователю root подключаться к MySQL без sudo

Для получения доступа к базе данных MySQL/MariaDB обычному пользователю без использования sudo привилегий, зайдите в приглашение командной строки MySQL

sudo mysql

и запустите следующие команды:

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

Или так:

use mysql;
UPDATE mysql.user SET authentication_string = PASSWORD('12345') WHERE User = 'root' AND Host = 'localhost';
update user set plugin='mysql_native_password' where User='root';
flush privileges;
exit

Затем перезапустите службу MySQL и попробуйте войти в базу данных без sudo, как показано ниже.

sudo systemctl restart mysql.service
mysql -u root -p

Создание нового пользователя

Подключитесь к mysql

sudo mysql --user=root mysql

Создайте нового пользователя с правами root

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'myuser'@'localhost';
FLUSH PRIVILEGES;
exit

После этого можете войти под новым пользователем (myuser) в вашу базу данных с правами как у root.

Если есть вопросы, то пишем в комментариях.

Также можете вступить в Телеграм канал, ВКонтакте или подписаться на Twitter. Ссылки в шапке страницы.
Заранее всем спасибо!!!

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

3.7
3
голоса

Рейтинг статьи

Источник

Building a local webserver involves installing all your dependencies either manually or using a preset stack like LAMP or XAMPP. The problem with these stacks is that they’re meant to be used in development environments and while they make the whole process easier, you can’t quite use them in a production environment.

The reason being these stacks aren’t exactly great from a security perspective. For that, you’re going to have to install individual web server components and set them up on your own.

However, when setting up a database such as MySQL or MariaDB, you might run into the “Error 1698 28000 access denied for user root localhost” issue. In this article, we’re talking about this error, what causes it and how you can fix this.

Also read: Debian vs Ubuntu: Which Linux distro should you pick?

What causes this error?

Usually, when you install a database management system otherwise known as DBMS such as MySQL or MariaDB, you’re asked to set a root password. This password is then used to log in to the database in order to make changes or review entries.

However, at times you might not be asked for a password in which case you’ll run into this issue. This is caused because, on some systems such as Ubuntu, MySQL uses the UNIX auth_socket plugin by default.

What this means is that any database user trying to access the database will be authenticated by the system user credentials.

Also read: RM command in Linux explained with examples

How to resolve the situation?

There are two ways you can tackle this error.

You can set the root user to use the native MySQL password.
You can create a new database user with your system user credentials. This is the recommended way.

Setting the root user to use the native MySQL password

Follow these steps if you’re willing to follow through on the first option.

Step 1: Type the following command in the terminal to launch MySQL as root.

sudo mysql -u root

Step 2: Once the MySQL console opens up, type the following commands one after the other pressing enter after each command.

USE mysql;
UPDATE user SET plugin='mysql_native_password' WHERE user='root';
FLUSH PRIVIILEGES;
exit:

Step 3: Now restart the MySQL service using this command.

sudo service mysql restart

Try accessing your database again it should work just fine.

Creating a new database user

In the commands below, replace your_user with your username.

Step 1: Type the following command in the terminal to launch MySQL as root.

sudo mysql -u root

Step 2: Once the MySQL console opens up, type the following commands one after the other pressing enter after each command.

CREATE USER 'your_user'@'localhost' IDENTIFIED BY 'password_here';
GRANT ALL PRIVILEGES ON *.* TO 'your_user'@'localhost';
UPDATE user SET plugin='auth_socket' WHERE User='your_user';
FLUSH PRIVILEGES;
exit;

Step 3: Now restart the MySQL service using this command.

sudo service mysql restart

Try accessing your database again it should work just fine.

Also read: How to add a user to a group in Linux?

Источник