Can not connect to mysql database using php script on AWS instance (LEMP)

Question

I am learning AWS EC2 to host my website made on WordPress, Laravel and custom PHP scripts.

I created a t2.small instance with ubuntu 18.04 as my OS.

Then I read tutorials and Installed the latest version of Ngnix server, MySQL 8, and PHP 7.3. I was able to configure them and everything work fine.

Then I created a new MySQL user and database. granted the user all permission for the database and also checked it using command line via SSH. Everything works fine.

I created a new server block in nginx and configured my domain, migrated the site(WordPress). Changed the database username, password (the one that I created) in wp-config.php. But when I accessed my domain I am getting Error establishing a database connection

I doubled check the database details and setting, everything is fine. Then why PHP script is not able to connect with the database? I am missing something? I think I don't need to open port 3306? Since my database host is localhost, I don't need to open it right?

Answer 1

Ok, problem seems to be only with MySQL 8.

I was able to solve the problem by referring answers from - stackoverflow

Basically, while creating user for your database run the following command

CREATE USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

And not this

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

If user already exists, run following command

ALTER USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Hope this help someone else.