I'm setting up a new server and keep running into this problem.
When I try to login to 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, 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 with Apache, MySQL and PHP, so that it can host websites, and iRedMail 0.9.5-1, so that it can host mail.
Login in to the MySQL database works fine before I install iRedMail. I also tried, just installing iRedMail, but then root, also doesn't work...
If someone could tell me how I fix my MySQL login problem or how I install iRedMail, on top of an existing MySQL install. And yes I tried the Installation Tips and I can't find those variables in the config files.
Check here:
NEW Version of MYSQL does it this way.
In the new my-sql if the password is left empty while installing then it is based on the auth_socket plugin.
The correct way is to login to my-sql with 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.
Do comment for any doubt.
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
Login as 'root'
mysql -u root -p
I have done the following steps to get rid of this issue. Login 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 '<<any password>>';
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 '<<any password>>';
5. FLUSH PRIVILEGES;
Please try it once if you are still getting the error. I hope this code will help you a lot !!
I would suggest to remove the Mysql connection -
UPDATE-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
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
I was having this issue on an Debian 8 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 db 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 above (zetacu) 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
There is a good and regularly updated guide on how to set a new password for the latest MySQL.
https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-ubuntu-20-04
It would be best to read whole topic from the link above but in short, this maybe could help,
Run the security script:
sudo mysql_secure_installation
Detailed Info for "mysql_secure_installation"
After that, you can change password by following the next steps
sudo mysql
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';
Detailed Info for changin root user password
If you have a problem maybe you will need to reinstall MySql.
in my case,
dev@Dev-007:~$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
I am sure my password was correct otherwise error code would be ERROR 1045 (28000): Access denied for user
so i relogin using sudo,
dev@Dev-007:~$ sudo mysql -u root -p
this time it worked for me . see the docs
and then change root password,
mysql> alter user 'root'@'%' identified with mysql_native_password by 'me123';
Query OK, 0 rows affected (0.14 sec)
mysql>
then restart server using sudo /etc/init.d/mysql restart
os:Ubuntu18.04
mysql:5.7
add the skip-grant-tables to the file end of mysqld.cnf
cp the my.cnf
sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/my.cnf
(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
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
(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>
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)!
validate_passwordmysql> 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 decided to reset the your password to fill the policy or change the policy.
After hours of struggle with no solution here, this worked for me then 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 mysql from 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 mysql back to your normal terminal place. You need to restart mysql for this to take effect. for that type the following:
sudo service mysql restart
Refer to this video link for better understanding
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 MySQL
exit;
sudo mysql -u root -p
and now you can write your password 4 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 DB for user 'root';
Let me solve it for ya.
SET GLOBAL validate_password.length = 4;
SET GLOBAL validate_password.mixed_case_count = 0;
SET GLOBAL validate_password.number_count = 0;
SET GLOBAL validate_password.policy = LOW;
SET GLOBAL validate_password.special_char_count = 0;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';
Only for testing purposes.
That 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;
This has happened to me as well. The problem is with the mysql repo that comes already with the linux distro. So when you simply do:
$ sudo apt install mysql-server
it installs mysql from their default repo which gives this problem. So to overcome that you need to uninstall that installed mysql
$ sudo apt remove mysql* --purge --auto-remove
Then download mysql repo from official mysql website MySQL APT Repo Follow their documentation on how to add repo and install it. This gives no issue. Also as answered by @zetacu, you can verify that mysql root now indeed uses mysql_native_password plugin
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, yo can change it.
step 4. exit
Thanks. You are done.
I also faced the same issue at the first time.
Now it is fixed:
First, you copy the /etc/mysql/mysql.conf.d/mysqld.cnf file and past in to /etc/mysql/my.cnf.
You can do it by command:
sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/my.cnf
Now let's Rest 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 newly created password:
sudo mysql -u root -p
Finally enter your newly created password.
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: http://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html
I'll not show the process in here because MySql documentation on the above link it's clear and concise.
First step: go to /etc/phpmyadmin/config.inc.php then uncomment lines where you find AllowNoPassword . Second step: login to your mysql default account
mysql -u root -p
use mysql;
update user set plugin="" where user='root';
flush privilege;
and that's all!
Source: Stackoverflow.com