Remote MySQL Question - access denied

[Darkfox]

We're currently setting up a new cPanel environment for a new project and we're having a slight issue with getting remote mysql working.

Our cPanel is installed on an EC2 instance with domain cpanel2.example.com, it has a private internal IP address of 10.0.0.2
Our database is installed on an EC2 instance in the same vpc/subnet, it does not have internet connectivity, however the cpanel server can access it on it's private IP of 10.0.0.3

# mysql -h 10.0.0.2 -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1900
Server version: 5.6.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
#

I tried using 'root'@'10.0.0.2' as the username and it came back with "...access denied for user '[email protected]'@'10.0.0.2'...", so I have a fairly good feeling that '-u root' equates to 'root'@'10.0.0.2'

Anyway.

I can successfully connect via password authentication from the command line, however when I setup the remote server in cPanel using the following options, I get the access denied error attached;




The user exists in the MySQL User table;




If it matters, mysql is installed but not running on the cpanel2.example.com server. I don't know if this matters or not.

 

[Darkfox]

I tried to edit the topic to put in my questions, but the forum software wouldn't let me.

My questions;

1. Which password is cPanel using to connect to the remote MySQL host? The root shell users password I specified for an SSH connection or the MySQL Root Password specified in WHM?
2. Is it a requirement to have the local MySQL server running in order to get the remote one working?
3. If a customer/user utilizes cPanel for a wordpress blog or similar, can they just use "localhost" as the database server when running through their wp-install script?

 

[Darkfox]

Have found the solution to my particular problem.

For those that might find this from Google Searches, it's quinessential to have the following file with your root password securely stored in plaintext form:

$ sudo cat /root/.my.cnf
[client]
user=root
pass=fail

After adding this file, I was then able to add the remote database.
I was then able to remotely log into the mysql host;
mysql -h 10.0.0.3 -u root -p

then I could do
show databases;

and the new database I created showed up

So this is resolved.

 

[cPanelMichael]

Hello

I am happy to see the issue is now resolved. Note the following document should also help others setting up a remote MySQL server:

Setup Remote MySQL Server - Documentation - cPanel Documentation

Thank you.