Connect to MySQL instance on Google Cloud Compute Engine VM from Cloud Run PHP

Question

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.

Answer 1

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!