Why Am I Getting \'Access Denied\' for MySQL User \'root\' on Ubuntu 16.04?

Access Denied for MySQL User 'root' Using Ubuntu 16.04

When attempting to connect to a MySQL database from a web server on Ubuntu 16.04, users may encounter the following error:

SQLSTATE[HY000] [1698] Access denied for user 'root'@'localhost'

This issue occurs because, in MySQL 5.7, the default authentication mechanism has changed, and the root user can no longer be accessed without elevated privileges.

Solution:

To resolve this error, users must create a new MySQL user with the necessary privileges instead of using the root user. The following steps outline the process:

1. Create a New MySQL User:

sudo mysql -u root -p

Enter your root password when prompted.

CREATE USER new_user@localhost IDENTIFIED BY 'new_password';

Replace 'new_user' with the desired username and 'new_password' with a strong password.

2. Grant Privileges to the New User:

GRANT ALL PRIVILEGES ON *.* TO new_user@localhost;

This grants the new user full access to all databases and tables.

3. Configure Web Server to Use New User:

Modify the PHP code in the provided question to use the newly created user credentials, replacing 'root' with 'new_user' and 'root' with 'new_password'.

4. Flush Privileges:

FLUSH PRIVILEGES;

This updates the privileges table with the changes made.

5. Exit MySQL:

EXIT;

By following these steps, users can connect to their MySQL database from a web server on Ubuntu 16.04 without encountering the "Access denied" error.

The above is the detailed content of Why Am I Getting \'Access Denied\' for MySQL User \'root\' on Ubuntu 16.04?. For more information, please follow other related articles on the PHP Chinese website!