What can you do with root access ошибка

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

1st open your terminal then type and enter

pkexec su

it will log you in as a root user

now type (replace username with your PC’s username):

chown -R username /usr/lib/

now open file explorer and goto /usr or type this in another terminal: nautilus /usr

now, right click on the lib folder and goto ‘Permissions’ tab.

From there change the access of ‘Group’ and ‘Others’ to- ‘Access files’

now click on the button ‘Change Permissions for Enclosed Files’
now change the access of ‘Group’ and ‘Others’ to- ‘Access files’ or ‘Read-only’.

now close that window and return to the terminal(the one with root user).

now type

chown -R root /usr/lib/

voila!

now you can use sudo again.

answered Oct 21, 2018 at 6:12

If you can’t login as root, then start Ubuntu in recovery mode, from the menu select «Drop to shell prompt», press Enter, and then type the following:

mount -o remount,rw /
mount --all
chmod 644 /usr/lib/sudo/sudoers.so
chown -R /usr/lib/sudo/

You should be able to run «sudo» commands now, including here, in recovery mode console, for example you can just run sudo to test if it was fixed.

**

on ubuntu 22.04 the proper directory for sudoers.so is /usr/libexec/sudo/sudoers.so

**