Question
MySQL DB Connection Access Denied
Environments:
*IP address shown are fictitious
App Server (Droplet) | MySQL Server (Droplet) |
---|---|
Public IP: 153.1.1.1 | Public IP: 153.1.1.1 |
Private IP: 10.1.1.1 | Private IP: 10.2.2.2 |
Ubuntu 18.04 | Ubuntu 18.04 |
NGINX 1.14.0 | MySQL 14.14 Distribution 5.7.27 |
Lets Encrypt | UFW Firewall |
PHP 7.2.19 | my.cnf: bind-address = 0.0.0.0 |
MySQL 15.1 Distribution 10.1.41-MariaDB | requiresecuretransport = ON |
UFW Firewall | Client Connections via SSL Enabled (REQUIRE X509) |
UFW Rules: OpenSSH, NGINX Full, MySQL | OpenSSH, 443, MySQL, 3306 ALLOW IN 10.1.1.1 |
Non-Root User Created | Non-Root User Created |
0 Package Updates | 0 Package Updates |
Cloudflare Cache Disabled | - |
Hi DO Community,
Issue:
Access denied for user 'non_root_user'@'10.1.1.1' (using password: YES)
Scenario:
Multiple Server Block Web Apps Server > MySQL DB Connection (non-root & SSL) > Private IP > MySQL Server > Access Denied
I have a 2 droplet setup currently:
- 1 x App Server
- 1 x MySQL (DB) Server
App server is configured to host multiple websites using the NGINX server blocks.
MySQL (DB) Server will host the multiple databases required and ONLY be available via the Private IP (in this example: 10.2.2.2).
My full configuration was going swimingly until the very last hurdle, I setup 2 sites and configured a singel static HTML file to test and allow SSL setup.
I then also run a php file to check phpinfo(); output works and this also worked fine.
I then deployed one of my sites WordPress filesystems via FTP to the new vhost directory. this all went online fine.
I recreated the Database (I did change the name on import and in the wp_config.php file also)
To this point everything appeared to be going smoothly.
I fun terminal level ping tests
App Server: ping 10.2.2.2
MySQL Server: ping 10.1.1.1
Each returning packet responses as expected.
From the APP Server I then then executed a DB connection request using my non-root user and this connected fine and showed me the database and I could run sql queries as you would expect.
So at this point everything works.
Bearing in mind I have enabled DB user SSL based connections using ALTER USER 'non_root_user'@'10.1.1.1' REQUIRE X509;
This works at the terminal level perfectly as I mentioned above.
Its only when i then navigate to my Wordpress domain to check the DB connection is all working and should see my already developerd website, but instead i get:
Access denied for user 'non_root_user'@'10.1.1.1' (using password: YES)
.
I proceeded to restart the MySQL DB, NGINX, and Flushed all privileges also.
So at this point i am pretty much out of ideas.
Trouble shooting steps taken:
- Fully disable UFW on both environments.
- Changed nonrootuser host to be wildcarded (%) instead of IP specific.
- Changed nonrootusers password to not included possible problematic symbols.
- Created a Database Connection test PHP file, output:
Unable to Connect to '10.2.2.2'
- Enabled WP DEBUG to see the
Access Denied
message. - reviews all log files which all point to the same
Access Denied Message
Solution (Pending):
8 Hours on and I am as confused as to why this i am getting access denied.
Any pointers or possible idea as to why this may be getting denied would be greatly appreciated?
These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.
×