php - SQLSTATE[HY000] [1698] Access denied for user 'root'@'localhost'
Get the solution ↓↓↓I just installed Ubuntu 16.04 (Xenial Xerus) and installed web server on it. Everything works well, but I cannot access database. Even if I create new user and grant all privileges, I can't create database
In PHP I'm getting this error:
SQLSTATE[HY000] [1698] Access denied for user 'root'@'localhost'
When I try to login in terminal, it works, but in PHP and phpMyAdmin don't.
PHP Code:
protected $host = '127.0.0.1';
protected $db = 'dbname';
protected $name = 'root';
protected $pass = 'root';
protected $conn;
private static $settings = array(
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
);
public function __construct() {
try {
$this->conn = new PDO("mysql:host=$this->host;dbname=$this->db", $this->name, $this->pass, self::$settings);
} catch (PDOException $e) {
echo $e->getMessage();
}
}
Answer
Solution:
It turns out you can't use theroot
user in 5.7 anymore without becoming a sudo'er. That means you can't just runmysql -u root
anymore and have to dosudo mysql -u root
instead.
That also means that it will no longer work if you're using theroot
user in a GUI (or supposedly any non-command line application). To make it work you'll have to create a new user with the required privileges and use that instead.
See this answer for more details.
Answer
Solution:
These steps worked for me on several systems using Ubuntu 16.04 (Xenial Xerus), Apache 2.4, MariaDB, and PDO:
Log into MYSQL as root
mysql -u root
Grant privileges. For a new user, execute:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost'; FLUSH PRIVILEGES;
UPDATE for Google Cloud Instances
MySQL on Google Cloud seem to require an alternate command (mind the backticks).
GRANT ALL PRIVILEGES ON `%`.* TO 'newuser'@'localhost';
NOTE: Depending on wether your new user should be able to grant all privileges to other users as well you could extend the command by the
GRANT WITH
option. Please be aware that this exposes your user to be sudoer and hence become a higher security risk.GRANT ALL PRIVILEGES ON `%`.* TO 'newuser'@'localhost' GRANT WITH OPTION;
Bind to all addresses:
The easiest way is to comment out the line in your /etc/mysql/mariadb.conf.d/50-server.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf file, depending on what system you are running:
#bind-address = 127.0.0.1
Exit MySQL and restart MySQL
exit service mysql restart
By default it binds only to localhost, but if you comment the line it binds to all interfaces it finds. Commenting out the line is equivalent to bind-address=*.
To check the binding of the MySQL service, execute as root:
netstat -tupan | grep mysql
Answer
Solution:
Use:
sudo mysql -u root
And now in the MySQL client:
use mysql;
update user set plugin='' where User='root';
flush privileges;
\q
Now you should be able to log in as root in phpMyAdmin.
(It was found here.)
Answer
Solution:
To create a user for phpMyAdmin:
sudo mysql -p -u root
Now you can add a new MySQL user with the username of your choice.
CREATE USER 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD';
And finally grant superuser privileges to the user you just created.
GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' WITH GRANT OPTION;
Answer
Solution:
In short, in MariaDB:
sudo mysql -u root;
use mysql;
UPDATE mysql.user SET plugin = 'mysql_native_password',
Password = PASSWORD('pass1234') WHERE User = 'root';
FLUSH PRIVILEGES;
exit;
Answer
Solution:
MySQL makes a difference between "localhost" and "127.0.0.1".
It might be possible that 'root'@'localhost' is not allowed because there is an entry in the user table that will only allow root login from 127.0.0.1.
This could also explain why some application on your server can connect to the database and some not because there are different ways of connecting to the database. And you currently do not allow it through "localhost".
Answer
Solution:
Just create a new user for MySQL; do not use root. There is a problem with its security issues:
sudo mysql -p -u root
Log in into MySQL or MariaDB with root privileges
CREATE USER 'troy121'@'%' IDENTIFIED BY 'mypassword123';
Log in and create a new user:
GRANT ALL PRIVILEGES ON *.* TO 'magento121121'@'%' WITH GRANT OPTION;
And grant privileges to access "." and "@" "%" any location, not just only 'localhost'.
exit;
If you want to see your privilege table,SHOW GRANTS;
and enjoy.
Answer
Solution:
With MySQL client version 14.14 and Distrib 5.7.22, the update statement is now:
update user set authentication_string=password('1111') where user='root';
Answer
Solution:
If you are receiving that error even after creating a new user and assigning them the database privileges, then the one last thing to look at is to check if the users have been assigned the privileges in the database.
To do this, log into to your MySQL client (this is presumably the application that has restricted access to the database, but you as a root can be able to access your database table viamysql -u user -p
).
Commands to apply
mysql -u root -p
password: (provide your database credentials)
On successful login, type
use mysql;
from this point, check each user's privileges if it is enabled from the database table as follows:
select User,Grant_priv,Host from db;
If the values of the Grant_priv col for the created user is N, update that value to Y with the following command:
UPDATE db SET Grant_priv = "Y" WHERE User= "your user";
With that, now try accessing the application and making a transaction with the database.
Answer
Solution:
Users for MySQL and for server are two different things. Look how to add a user to the database and log in with these credentials.
Answer
Solution:
I had the same problem in my Ubuntu 20.04 (Focal Fossa) and MySQL 8.0 and I do these steps:
log in to MySQL
sudo mysql -p -u root
Show the users added to MySQL
SELECT user,plugin,host FROM mysql.user
Change the root user plugin from
auth_socket
tomysql_native_password
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
Flush the privileges
FLUSH PRIVILEGES;
Ctrl + z to exit from MySQL
Restart your MySQL service
sudo service MySQL restart
Check your phpMyAdmin page and try to log in.
Answer
Solution:
Use:
sudo mysql -u root
mysql> CREATE USER 'sample'@'localhost' IDENTIFIED BY 'Secure1pass!';
mysql> CREATE DATABASE testdb;
mysql> GRANT ALL PRIVILEGES ON testdb . * TO 'sample'@'localhost';
In case you just want to use your MySQL server on Ubuntu locally and want to connect with your application to a database.
Answer
Solution:
sudo mysql -u root
mysql> USE mysql;
mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> exit;
service mysql restart
After restarting mysql server reload the app please.
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: foreach() argument must be of type array|object, null given
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.