4

I am struggling to connect from my PHP-based website hosted in a Google Cloud Run container to a MySQL database on a Google Cloud Compute Engine VM.

Recently, I had successfully set up a PHP-based website within a container in a Google Cloud Run environment. I also set up a Cloud SQL instance and was comfortably connecting from the PHP website to the Cloud SQL instance.

I have discovered that the Cloud SQL service is a lot more expensive than I had planned, so have set up an instance of MySQL running on a Compute Engine VM.

The MySQL instance is running and I can access the database via SSH on the VM instance. The configuration file is:

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
bind-address    = 0.0.0.0

I'm using the bind-address of 0.0.0.0 to allow connections from my development machine as building the container with an updated configuration string is rather tedious.

The connection string I was using to connect to the Cloud SQL instance was:

define('HOSTSPEC', NULL);
define('USERNAME', '<username>');
define('PASSWORD', '<password>');
define('DATABASE_INSTANCE_NAME', NULL); // Or the name of a database instance within your Cloud SQL instance.
define('PORT', NULL);
define('SOCKET', '/cloudsql/<project-name>:<instance-region>:<instance-name>');

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $mysqli = new mysqli(HOSTSPEC, USERNAME, PASSWORD, DATABASE_INSTANCE_NAME, PORT, SOCKET);
...

I can't find any resources to suggest what the connection string should be for MySQL hosted in a Compute Engine VM!

I did try the connection string I use for my development machine, substituting the IP address from localhost to the external IP address of the VM but to no avail:

$server = 'XXX.XXX.XXX.XXX';
$username   = '<username>';
$password   = '<password>';
$database   = '<database>';

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $mysqli = new mysqli($server, $username,  $password, $database);
...

I have monitored the /var/log/mysql/error.log, but am not seeing any connection failures, suggesting that I'm not reaching the server.

So far I have explored the following links, but haven't found the answers I'm seeking:

Add a new firewall rule

Remote connections mysql ubuntu find address failed

Set-up MySQL (note: I was unable to complete these steps as I couldn't install MySQL as outlined, so I followed MySQL package MySQL server has no installation candidate

I was not able to execute the following gcloud command: gcloud config set project <project-name> as I receive the error: You do not appear to have access to project [<project name>] or it does not exist. Though this project does exist and I can access it (and execute that command successfully) in the Cloud Run environment where my PHP-based website is hosted.

Any suggestions for what to try next?

I could, of course, give up and build my PHP-based website inside the VM and then access the MySQL database 'locally', but having figured out how to deploy the service in a container in Cloud Run, I'd like to keep the site there.

MuppetDance
  • 144
  • 10
  • 1
    I would try connecting from the command line first, i.e. first see if you can ping the IP of the MySQL server from your container, then try connecting via mysql cli. You should at least get some useful errors that may help. – Alan Jan 27 '22 at 20:31
  • I can ping the server by its IPv4 address from my development machine (ping is not enabled / installed on the Cloud Run server) and get a response. If I attempt to connect via the command line (again from my development machine, using `mysql -h XXX.XXX.XXX.XXX -u -p` I receive `ERROR 2003 (HY000): Can't connect to MySQL server on 'XXX.XXX.XXX.XXX:3306' (60)` – MuppetDance Jan 27 '22 at 20:57
  • Connecting via the development machine-hosted website gives `ERROR 2002: Operation timed out` – MuppetDance Jan 27 '22 at 20:59
  • Aha! I followed this link to update the firewall to allow connections via port 3306: https://stackoverflow.com/questions/21065922/how-to-open-a-specific-port-such-as-9090-in-google-compute-engine. I now get `ERROR 1130 (HY000): Host '' is not allowed to connect to this MySQL server` – MuppetDance Jan 27 '22 at 21:19
  • And I believe this is caused by my user GRANT statements specifying '@localhost' – MuppetDance Jan 27 '22 at 21:21

1 Answers1

3

Following @alan's suggestion, I attempted to connect to the MySQL instance from the terminal on my development machine.

This suggested that the port 3306 wasn't open on the Virtual Machine.

I followed the instructions in this link to open port 3306 on the VM.

Then, I received ERROR 1130 (HY000): Host '<development machine IP address>' is not allowed to connect to this MySQL server.

I then added my development machine IP address to the CREATE USER and GRANT USER statements, e.g.:

CREATE USER IF NOT EXISTS '<username>'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';
CREATE USER IF NOT EXISTS '<username>'@'XXX.XXX.XXX.XXX' IDENTIFIED WITH mysql_native_password BY '<password>';
GRANT SELECT, INSERT, UPDATE ON <database>.<table> TO '<username>'@'localhost';
GRANT SELECT, INSERT, UPDATE ON <database>.<table> TO '<username>'@'XXX.XXX.XXX.XXX';

The connection string to connect to the VM from the Cloud Run service is:

$server = 'XXX.XXX.XXX.XXX';
$username   = '<username>';
$password   = '<password>';
$database   = '<database>';

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $mysqli = new mysqli($server, $username,  $password, $database);
...

And I am up and running!

MuppetDance
  • 144
  • 10