Fix MySQL Login issue with root user, #1698: Access denied for user ‘root’@’localhost'(MySQL 5.7)
MySQL has been changed its security model from version MySQL 5.7, You can’t log in with root from now by an application such as phpMyAdmin, WordPress, Magento, OpenCart or any others built by PHP which required use function mysqli_real_connect(), because these applications will not be able to use `root` as sudo mode, you must use root sudo mode to login using terminal.
However, I’m describing below the simplest, secure and permanent way. Just create a new user at MySQL and grant required privileges.
Solution 1. Connect to MySQL with terminal
sudo mysql --user=root mysql
//place your MySQL password
Solution 2. Create a user to use in applications including phpMyAdmin, WordPress, Magento, OpenCart, Laravel or your own Application
Run the following commands (replacing your_password by whatever you want as the password):
CREATE USER 'mysql_user'@'localhost' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON *.* TO 'mysql_user'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Now try to connect your application, hopefully, it’ll be fixed.
Solution 3. allow remote connections (Unsafe Method)
Note: this is really a security concern for your server.
It will allow login via remote connections (replace your_password by your password which you used in solution #2 )
CREATE USER 'phpmyadmin'@'%' IDENTIFIED BY 'some_pass';
GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Solution 4. Update your MySQL login credential in your application
user=mysql_user
pass=your_password
Hopefully, it’s enough to fix mysqli_real_connect(): (HY000/1698): Access denied for user ‘root’@’localhost’ for MySQL 5.7
