Google Cloud SQL for MySQL - Connection Security, High Availability and Failover

Introduction

Designing an application that incorporates Google Cloud SQL requires some thought. There are several factors to consider regarding security, performance, fault tolerance and availability. Incorrectly implementing one of these areas can affect the other areas, usually negatively.

In this article, we will start with a simple example, written in Node.js. Then we will analyze this example, find issues and explore improved solutions.

I created a GitHub repository with the files in this article.

Connection Management

A typical application opens and closes connections as required. Opening connections to a database server is typically considered an expensive operation. Database servers usually impose a limit on the number of simultaneous connections. Failing to close connections can result in a Denial-of-service (DoS) for your own services.

For container-based deployments (App Engine, Cloud Functions, Cloud Run, Containers on GCE, Kubernetes), connection management or the lack of, can affect cold-start times (waiting for a new database connection) and/or limit the number of containers that can be simultaneously deployed (hitting connection limits blocks new containers from getting new connections).

Google Cloud SQL Connection Limits:

By implementing connection management, connections can be shared, reused and quantity limited. Sharing connections improves connection latency and performance. Limiting the number of connections created can detect code bugs that fail to release connections and can help prevent self-inflicted DoS of your database resources. Connection management can detect failed connections and reopen each connection with a database replica server improving high availability.

High Availability

The Google Cloud SQL High Availability configuration, which requires creating an additional instance, provides data redundancy. This configuration consists of a primary instance (master) and a failover replica (slave). The master and slaves can be located in different zones to improve high availability.

Google Cloud SQL for MySQL uses semisynchronous replication for data synchronization from the master to the slave and asynchronous replication between the master and the read-only replicas (also called slaves).

MySQL 5.7 Replication

Load Balancing

Google Cloud SQL supports load balancing for read-type requests. For requests that update (write) the database, load balancing is not supported. Write requests must be sent to the master instance. Load balancing is implemented by adding Read Replicas.

A read replica provides a read-only copy of the master. Read replicas do not provide high availability as a master cannot fail over to a read replica. Read replicas can be a different machine type than the master instance. You can have more than one read replica.

A failover replica can also be used as a read replica. In the examples below, we load balance read requests on the master, failover replica and read replica.

Connection management can distribute read-type requests between the master, failover replica and the read replicas. This can be in a random or round-robin fashion.

Failover

If a Google Cloud SQL High Availability master instance becomes unresponsive, Cloud SQL automatically switches to the failover replica. The failover replica is promoted to the primary instance role (master). The instance name and IP address move to the failover replica. Once this completes a new failover replica is created and the old master is deleted and a new failover replica is created.

During this transition, requests to the Cloud SQL cluster can fail. Implementing connection management with retries and traffic distribution (load balancing) is necessary for true high availability.

Load Balancing plus Failover

In my testing of Google Cloud SQL for MySQL, I have determined that the best configuration is combining a failover replica with a read replica and cluster connection management. If a read-type request fails, it will be retried on another instance. If a write-type request fails, a time delay with retries is necessary while waiting for the failover replica to become the master.

Version #1 – Simple Version

Let’s start with a very simple program that connects to Cloud SQL and displays the name of each database on the Cloud SQL server. The user “sqluser” must have either the MySQL “SHOW DATABASES” privilege or have privileges to each database.

The configuration file which provides the Cloud SQL credentials – myconfig.json:

{

"host": "34.45.12.101",

"user": "sqluser",

"password": "use-a-good-password"

}

Example program:

var mysql = require('mysql');
var myconfig = require('./myconfig.json');

function db_test() {
	var con = mysql.createConnection({
		host: myconfig.host,
		user: myconfig.user,
		password: myconfig.password
	});

con.connect(function(error) {
		if (error) {
			console.log("Error: Cannot connect to server: " + myconfig.host);

if ("sqlMessage" in error) {
				console.log(error.errno + " : " + error.sqlMessage);
			} else {
				console.log(error);
			}

return;
		}

db_listDatabases(con);
	});
}

function db_listDatabases(con) {
	con.query('SHOW DATABASES', function (error, results, fields) {
		if (error) {
			console.log("Error: Cannot query databases");

if ("sqlMessage" in error) {
				console.log(error.errno + " : " + error.sqlMessage);
			} else {
				console.log(error);
			}

con.end();

return;
		}

console.log('DATABASES');
		console.log('--------------------');
		Object.keys(results).forEach(function(key) {
			var row = results[key];
			console.log(row.Database)
		});
		con.end();
	});
}

db_test();

var mysql = require('mysql');

var myconfig = require('./myconfig.json');

function db_test() {

var con = mysql.createConnection({

host: myconfig.host,

user: myconfig.user,

password: myconfig.password

});

con.connect(function(error) {

if (error) {

console.log("Error: Cannot connect to server: " + myconfig.host);

if ("sqlMessage" in error) {

console.log(error.errno + " : " + error.sqlMessage);

} else {

console.log(error);

}

return;

}

db_listDatabases(con);

});

}

function db_listDatabases(con) {

con.query('SHOW DATABASES', function (error, results, fields) {

if (error) {

console.log("Error: Cannot query databases");

if ("sqlMessage" in error) {

console.log(error.errno + " : " + error.sqlMessage);

} else {

console.log(error);

}

con.end();

return;

}

console.log('DATABASES');

console.log('--------------------');

Object.keys(results).forEach(function(key) {

var row = results[key];

console.log(row.Database)

});

con.end();

});

}

db_test();

The above example program works, but has several problems/limitations:

Security. SSL is not being used. This means that the login credentials are being sent in the clear over TCP port 3306.
- Solution: Either implement SSL or use the Google Cloud Proxy to implement TLS connections. I discuss the Cloud SQL Proxy in this article.
- SSL added in code version #2.
- Cloud SQL Proxy added in code version #5 (TCP) and #6 (Unix Sockets).
Security. It is not clear if the server is limiting the number of connections that a user can open. If unlimited, a program bug can consume the maximum number of connections and cause a Denial of Service (DoS) of the Cloud SQL instance. We will explore this issue in another article. Connection management can help detect and mitigate this issue (see code version #3).
Performance: The example code is opening a connection to Cloud SQL and then closing the connection. Creating connections is an expensive operation. No connection management is present.
- Solution: Use connection pooling to manage connections.
- Connection management added in code version #3.
Availability: The example code does not retry failed connection attempts.
- Solution: Implement connection retries and exponential backoff.
- Failover, retries and read replicas added in code version #4.
Availability: The example code does not implement load balancing and failover.
- Solution: Implement Cloud SQL Failover and Read Replicas.
- Failover, retries and read replicas added in code version #4.

Version #2 – Implement SSL Connections

The first improvement that I will make is adding SSL to the example program. This requires three items from the Cloud SQL server:

SSL Server Certificate. This is a PEM formatted file of all available Cloud SQL SSL server certificates. This can be downloaded anytime.
SSL Client Certificate. This is a PEM formatted file of the client’s certificate. This can be downloaded anytime once created.
SSL Client Private Key. This is a PEM formatted file of the client’s private key. This can only be downloaded once. If lost, you must create a new client certificate.

Steps to create SSL certificates:

Go to the Google Cloud Console – SQL
Log in to the console or confirm which login credentials you are using.
Confirm the project that you want to use.
Select the Cloud SQL Instance by clicking on the “Instance ID”.
Click on the “CONNECTIONS” tab.
Under “Configure SSL client certificates,” click “Create a client certificate”.
Enter a unique identifier (name) for your SSL certificates.
A “New SSL certificate created” dialog appears.
1. Download the three certificate files:
  1. Download client-key.pem
  2. Download client-cert.pem
  3. Download server-ca.pem
2. Copy the mysql example (the text in black). We will use this to test SSL connectivity.
3. Click CLOSE.

The dialog looks similar to this:

In the configuration, file myconfig.json add the SSL section to specify the correct path and filename of the three SSL certificate files downloaded from the previous step.

{

"host": "34.56.12.101",

"user": "sqluser",

"password": "use-a-good-password",

"ssl": {

"ca": "/path/server-ca.pem",

"key": "/path/client-key.pem",

"cert": "/path/client-cert.pem"

}

The changes to add SSL are small. Review lines 10 – 14.

The modified program to support SSL:

var fs = require('fs');
var mysql = require('mysql');
var myconfig = require('./myconfig.json');

function db_test() {
	var con = mysql.createConnection({
		host: myconfig.host,
		user: myconfig.user,
		password: myconfig.password,
		ssl : {
			ca: fs.readFileSync(myconfig.ssl.ca),
			key: fs.readFileSync(myconfig.ssl.key),
			cert: fs.readFileSync(myconfig.ssl.cert)
		}
	});

con.connect(function(error) {
		if (error) {
			console.log("Error: Cannot connect to server: " + myconfig.host);

if ("sqlMessage" in error) {
				console.log(error.errno + " : " + error.sqlMessage);
			} else {
				console.log(error);
			}

return;
		}

db_listDatabases(con);
	});
}

function db_listDatabases(con) {
	con.query('SHOW DATABASES', function (error, results, fields) {
		if (error) {
			console.log("Error: Cannot query databases");

if ("sqlMessage" in error) {
				console.log(error.errno + " : " + error.sqlMessage);
			} else {
				console.log(error);
			}

con.end();

return;
		}

console.log('DATABASES');
		console.log('--------------------');
		Object.keys(results).forEach(function(key) {
			var row = results[key];
			console.log(row.Database)
		});
		con.end();
	});
}

db_test();

var fs = require('fs');

var mysql = require('mysql');

var myconfig = require('./myconfig.json');

function db_test() {

var con = mysql.createConnection({

host: myconfig.host,

user: myconfig.user,

password: myconfig.password,

ssl : {

ca: fs.readFileSync(myconfig.ssl.ca),

key: fs.readFileSync(myconfig.ssl.key),

cert: fs.readFileSync(myconfig.ssl.cert)

}

});

con.connect(function(error) {

if (error) {

console.log("Error: Cannot connect to server: " + myconfig.host);

if ("sqlMessage" in error) {

console.log(error.errno + " : " + error.sqlMessage);

} else {

console.log(error);

}

return;

}

db_listDatabases(con);

});

}

function db_listDatabases(con) {

con.query('SHOW DATABASES', function (error, results, fields) {

if (error) {

console.log("Error: Cannot query databases");

if ("sqlMessage" in error) {

console.log(error.errno + " : " + error.sqlMessage);

} else {

console.log(error);

}

con.end();

return;

}

console.log('DATABASES');

console.log('--------------------');

Object.keys(results).forEach(function(key) {

var row = results[key];

console.log(row.Database)

});

con.end();

});

}

db_test();

Version #3 – Implement Connection Pooling

The changes to add connection pooling are:

Line 6: Switch from mysql.createConnection() to mysql.createPool().
Line 15: Limit the number of connections to 8 (change for your requirements).
Line 16: Specify what to do if there are no more connections.
Line 23: Use pool.query() instead of connecting and then using con.query().
- The benefit is the automatic release of the connection when the query completes.
- Connection handles can be reused improving performance.

The modified program to support SSL:

var fs = require('fs');
var mysql = require('mysql');
var myconfig = require('./myconfig.json');

function db_test() {
	var pool = mysql.createPool({
		host: myconfig.host,
		user: myconfig.user,
		password: myconfig.password,
		ssl : {
			ca: fs.readFileSync(myconfig.ssl.ca),
			key: fs.readFileSync(myconfig.ssl.key),
			cert: fs.readFileSync(myconfig.ssl.cert)
		}
		connectionLimit: 8,
		waitForConnections: false
	});

db_listDatabases(pool);
}

function db_listDatabases(pool) {
	pool.query('SHOW DATABASES', function (error, results, fields) {
		if (error) {
			console.log("Error: Cannot query databases");

if ("sqlMessage" in error) {
				console.log(error.errno + " : " + error.sqlMessage);
			} else {
				console.log(error);
			}

return;
		}

console.log('DATABASES');
		console.log('--------------------');
		Object.keys(results).forEach(function(key) {
			var row = results[key];
			console.log(row.Database)
		});

// This program is complete. Close all connections in the pool
		pool.end(function (err) {
			//
		});
	});
}

db_test();

var fs = require('fs');

var mysql = require('mysql');

var myconfig = require('./myconfig.json');

function db_test() {

var pool = mysql.createPool({

host: myconfig.host,

user: myconfig.user,

password: myconfig.password,

ssl : {

ca: fs.readFileSync(myconfig.ssl.ca),

key: fs.readFileSync(myconfig.ssl.key),

cert: fs.readFileSync(myconfig.ssl.cert)

}

connectionLimit: 8,

waitForConnections: false

});

db_listDatabases(pool);

}

function db_listDatabases(pool) {

pool.query('SHOW DATABASES', function (error, results, fields) {

if (error) {

console.log("Error: Cannot query databases");

if ("sqlMessage" in error) {

console.log(error.errno + " : " + error.sqlMessage);

} else {

console.log(error);

}

return;

}

console.log('DATABASES');

console.log('--------------------');

Object.keys(results).forEach(function(key) {

var row = results[key];

console.log(row.Database)

});

// This program is complete. Close all connections in the pool

pool.end(function (err) {

});

}

db_test();

Version #4 – Implement Connection Clustering

The following code implements connection management, high availability, and failover with retries.

The configuration file myconfig_pool_ssl.json is more complicated as we have three instances (master, failover replica and read replica).

{

"masterConfig": {

"host": "34.45.12.101",

"port": "3306",

"user": "sqluser",

"password": "use-a-good-password",

"ssl": {

"ca": "master/server-ca.pem",

"key": "master/client-key.pem",

"cert": "master/client-cert.pem"

}

"slaveConfig_1": {

"host": "34.45.12.102",

"port": "3306",

"user": "sqluser",

"password": "use-a-good-password",

"ssl": {

"ca": "failover/server-ca.pem",

"key": "failover/client-key.pem",

"cert": "failover/client-cert.pem"

}

"slaveConfig_2": {

"host": "34.45.12.103",

"port": "3306",

"user": "sqluser",

"password": "use-a-good-password",

"ssl": {

"ca": "slave/server-ca.pem",

"key": "slave/client-key.pem",

"cert": "slave/client-cert.pem"

}

var fs = require('fs');
var fs = require('fs');
var mysql = require('mysql');
var myconfig = require('./myconfig_pool_ssl.json');

var count = 0;

var poolconfig = myconfig

// Read in the SSL certificates
if ('ssl' in poolconfig.masterConfig) {
	poolconfig.masterConfig.ssl.ca = fs.readFileSync(poolconfig.masterConfig.ssl.ca);
	poolconfig.masterConfig.ssl.key = fs.readFileSync(poolconfig.masterConfig.ssl.key);
	poolconfig.masterConfig.ssl.cert = fs.readFileSync(poolconfig.masterConfig.ssl.cert);
}

if ('ssl' in poolconfig.slaveConfig_1) {
	poolconfig.slaveConfig_1.ssl.ca = fs.readFileSync(poolconfig.slaveConfig_1.ssl.ca);
	poolconfig.slaveConfig_1.ssl.key = fs.readFileSync(poolconfig.slaveConfig_1.ssl.key);
	poolconfig.slaveConfig_1.ssl.cert = fs.readFileSync(poolconfig.slaveConfig_1.ssl.cert);
}

if ('ssl' in poolconfig.slaveConfig_2) {
	poolconfig.slaveConfig_2.ssl.ca = fs.readFileSync(poolconfig.slaveConfig_2.ssl.ca);
	poolconfig.slaveConfig_2.ssl.key = fs.readFileSync(poolconfig.slaveConfig_2.ssl.key);
	poolconfig.slaveConfig_2.ssl.cert = fs.readFileSync(poolconfig.slaveConfig_2.ssl.cert);
}

// https://github.com/mysqljs/mysql#poolcluster-options
var clusterConfig = {
	canRetry: true,
	removeNodeErrorCount: 5,
	// restoreNodeTimeout: 60,		// Wait one minute
	restoreNodeTimeout: 5,			// Wait five seconds
	// RR, RANDOM, ORDER
	defaultSelector: 'RR',
	connectionLimit: 8,
	waitForConnections: false
}

var pool = mysql.createPoolCluster(clusterConfig);

pool.add('MASTER', poolconfig.masterConfig);
pool.add('SLAVE1', poolconfig.slaveConfig_1);
pool.add('SLAVE2', poolconfig.slaveConfig_2);

process.on('SIGINT', function() {
	console.log("");
	console.log("Caught interrupt signal");

// This program is complete. Close all connections in the pool
	pool.end(function (err) {
		//
	});

process.exit(1);
});

function debugMessage(count, host, port) {
	return count + ": " + new Date().toISOString() + ": " + getHost(host, port);
}

function getHost(ipaddr, port) {
	if (ipaddr == poolconfig.masterConfig.host && port == poolconfig.masterConfig.port) {
		return "MASTER";
	}

if (ipaddr == poolconfig.slaveConfig_1.host && port == poolconfig.slaveConfig_1.port) {
		return "FAILOVER";
	}

if (ipaddr == poolconfig.slaveConfig_2.host && port == poolconfig.slaveConfig_2.port) {
		return "SLAVE #2";
	}

return ipaddr + ":" + port;
}

function db_test() {
	db_listDatabases(0);
}

function db_listDatabases(retry) {
	// pool.getConnection('MASTER', function(error, connection) {
	// pool.getConnection('SLAVE*', function(error, connection) {

pool.getConnection(function(error, connection) {
		if (error) {
			console.log("---------------------------------------------");
			console.log("Error: Cannot connect to server")
			console.log(new Date().toISOString());
			console.log(error.code);

if (error.code == "ETIMEDOUT") {
				console.log("Retrying ...");
				db_listDatabases(retry + 1);
				return;
			}

if (error.code == "ECONNREFUSED") {
				console.log("Retrying ...");
				db_listDatabases(retry + 1);
				return;
			}

// PROTOCOL_CONNECTION_LOST probably means the wrong SSL certificate

if (retry == 0) {
				console.log("Retrying ...");
				db_listDatabases(retry + 1);
				return;
			}

console.log("Retry failed");

return;
		}

count += 1;

host = connection.config.host
		port = connection.config.port

msg = debugMessage(count, host, port);

msg += "        \r";

process.stdout.write(msg);

connection.query('SHOW DATABASES', function (error, results, fields) {
			if (error) {
				console.log("---------------------------------------------");
				console.log(debugMessage(count, host, port));

console.log("Error: Cannot query databases: " + error.code + " retrying");

if (retry == 0) {
					db_listDatabases(retry + 1);
					return;
				}

console.log("Retry failed");

connection.release();

return;
			}

console.log("DATABASES on " + getHost(host, port) + "                    ");
			console.log('--------------------');
			Object.keys(results).forEach(function(key) {
				var row = results[key];
				console.log(row.Database)
			});

connection.release();

// This program is complete. Close all connections in the pool
			pool.end(function (err) {
				//
			});
		});
	});
}

db_test()

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

var fs = require('fs');

var mysql = require('mysql');

var myconfig = require('./myconfig_pool_ssl.json');

var count = 0;

var poolconfig = myconfig

// Read in the SSL certificates

if ('ssl' in poolconfig.masterConfig) {

poolconfig.masterConfig.ssl.ca = fs.readFileSync(poolconfig.masterConfig.ssl.ca);

poolconfig.masterConfig.ssl.key = fs.readFileSync(poolconfig.masterConfig.ssl.key);

poolconfig.masterConfig.ssl.cert = fs.readFileSync(poolconfig.masterConfig.ssl.cert);

}

if ('ssl' in poolconfig.slaveConfig_1) {

poolconfig.slaveConfig_1.ssl.ca = fs.readFileSync(poolconfig.slaveConfig_1.ssl.ca);

poolconfig.slaveConfig_1.ssl.key = fs.readFileSync(poolconfig.slaveConfig_1.ssl.key);

poolconfig.slaveConfig_1.ssl.cert = fs.readFileSync(poolconfig.slaveConfig_1.ssl.cert);

}

if ('ssl' in poolconfig.slaveConfig_2) {

poolconfig.slaveConfig_2.ssl.ca = fs.readFileSync(poolconfig.slaveConfig_2.ssl.ca);

poolconfig.slaveConfig_2.ssl.key = fs.readFileSync(poolconfig.slaveConfig_2.ssl.key);

poolconfig.slaveConfig_2.ssl.cert = fs.readFileSync(poolconfig.slaveConfig_2.ssl.cert);

}

// https://github.com/mysqljs/mysql#poolcluster-options

var clusterConfig = {

canRetry: true,

removeNodeErrorCount: 5,

// restoreNodeTimeout: 60, // Wait one minute

restoreNodeTimeout: 5, // Wait five seconds

// RR, RANDOM, ORDER

defaultSelector: 'RR',

connectionLimit: 8,

waitForConnections: false

}

var pool = mysql.createPoolCluster(clusterConfig);

pool.add('MASTER', poolconfig.masterConfig);

pool.add('SLAVE1', poolconfig.slaveConfig_1);

pool.add('SLAVE2', poolconfig.slaveConfig_2);

process.on('SIGINT', function() {

console.log("");

console.log("Caught interrupt signal");

// This program is complete. Close all connections in the pool

pool.end(function (err) {

});

process.exit(1);

});

function debugMessage(count, host, port) {

return count + ": " + new Date().toISOString() + ": " + getHost(host, port);

}

function getHost(ipaddr, port) {

if (ipaddr == poolconfig.masterConfig.host && port == poolconfig.masterConfig.port) {

return "MASTER";

}

if (ipaddr == poolconfig.slaveConfig_1.host && port == poolconfig.slaveConfig_1.port) {

return "FAILOVER";

}

if (ipaddr == poolconfig.slaveConfig_2.host && port == poolconfig.slaveConfig_2.port) {

return "SLAVE #2";

}

return ipaddr + ":" + port;

}

function db_test() {

db_listDatabases(0);

}

function db_listDatabases(retry) {

// pool.getConnection('MASTER', function(error, connection) {

// pool.getConnection('SLAVE*', function(error, connection) {

pool.getConnection(function(error, connection) {

if (error) {

console.log("---------------------------------------------");

console.log("Error: Cannot connect to server")

console.log(new Date().toISOString());

console.log(error.code);

if (error.code == "ETIMEDOUT") {

console.log("Retrying ...");

db_listDatabases(retry + 1);

return;

}

if (error.code == "ECONNREFUSED") {

console.log("Retrying ...");

db_listDatabases(retry + 1);

return;

}

// PROTOCOL_CONNECTION_LOST probably means the wrong SSL certificate

if (retry == 0) {

console.log("Retrying ...");

db_listDatabases(retry + 1);

return;

}

console.log("Retry failed");

return;

}

count += 1;

host = connection.config.host

port = connection.config.port

msg = debugMessage(count, host, port);

msg += " \r";

process.stdout.write(msg);

connection.query('SHOW DATABASES', function (error, results, fields) {

if (error) {

console.log("---------------------------------------------");

console.log(debugMessage(count, host, port));

console.log("Error: Cannot query databases: " + error.code + " retrying");

if (retry == 0) {

db_listDatabases(retry + 1);

return;

}

console.log("Retry failed");

connection.release();

return;

}

console.log("DATABASES on " + getHost(host, port) + " ");

console.log('--------------------');

Object.keys(results).forEach(function(key) {

var row = results[key];

console.log(row.Database)

});

connection.release();

// This program is complete. Close all connections in the pool

pool.end(function (err) {

});

}

db_test()

Version #5 – Add Google Cloud SQL Proxy (TCP) instead of SSL

The only change to support the Google Cloud SQL Proxy is to use a new configuration file. Notice that I have added port numbers as the Cloud SQL instances are accessed via their proxy port number.

{

"masterConfig": {

"host": "127.0.0.1",

"port": "3306",

"user": "sqluser",

"password": "use-a-good-password"

"slaveConfig_1": {

"host": "127.0.0.1",

"port": "3307",

"user": "sqluser",

"password": "use-a-good-password"

"slaveConfig_2": {

"host": "127.0.0.1",

"port": "3308",

"user": "sqluser",

"password": "use-a-good-password"

}

This is the command to start the Cloud SQL Proxy using TCP:

1	cloud_sql_proxy --instances=myproject:us-central1:myinstance=tcp:3306,myproject:us-central1:myinstance-failover=tcp:3307,myproject:us-central1:myinstance-replica=tcp:3308

To understand how this command works:

myproject:us-central1:myinstance is the Google Cloud SQL Instance connection name.

tcp:3306 translates to the following lines in the above configuration file.

“host”: “127.0.0.1”
“port”: “3306”

This means that the Google Cloud SQL Proxy is listening on 127.0.0.1 (localhost) on port 3306 for connections from the local machine. Once a connection is received on port 3306, the proxy forwards that traffic to the Cloud SQL instance named myproject:us-central1:myinstance.

A local connection to port 3307 will forward traffic to myproject:us-central1:myinstance-failover.

A local connection to port 3308 will forward traffic to myproject:us-central1:myinstance-replica.

Version #6 – Add Google Cloud SQL Proxy (Unix Sockets) instead of SSL

The only change to support the Google Cloud SQL Proxy is to use a new configuration file. Notice that this version uses Unix Sockets instead of TCP address plus Port number.

{

"masterConfig": {

"host": "/cloudsql/myproject:us-central1:myinstance",

"user": "sqluser",

"password": "use-a-good-password"

"slaveConfig_1": {

"host": "/cloudsql/myproject:us-central1:myinstance-failover",

"user": "sqluser",

"password": "use-a-good-password"

"slaveConfig_2": {

"host": "/cloudsql/myproject:us-central1:myinstance-replica",

"user": "sqluser",

"password": "use-a-good-password"

}

This is the command to start the Cloud SQL Proxy using Unix Sockets on Linux:

1	cloud_sql_proxy -dir=/cloudsql --instances=myproject:us-central1:myinstance,myproject:us-central1:myinstance-failover,myproject:us-central1:myinstance-replica

Summary

It is fairly easy to improve a SQL database client’s security, availability and fault tolerance by implementing the correct configurations and features.

More Information

Credits

I write free articles about technology. Recently, I learned about Pexels.com which provides free images. The image in this article is courtesy of Piet Bakker at Pexels.

John Hanley

I design software for enterprise-class systems and data centers. My background is 30+ years in storage (SCSI, FC, iSCSI, disk arrays, imaging) virtualization. 20+ years in identity, security, and forensics.

For the past 14+ years, I have been working in the cloud (AWS, Azure, Google, Alibaba, IBM, Oracle) designing hybrid and multi-cloud software solutions. I am an MVP/GDE with several.

Security, software development and devops in a cloud world

Google Cloud SQL for MySQL – Connection Security, High Availability and Failover

Introduction

Connection Management

High Availability

Load Balancing

Failover

Load Balancing plus Failover

Version #1 – Simple Version

Version #2 – Implement SSL Connections

Version #3 – Implement Connection Pooling

Version #4 – Implement Connection Clustering

Version #5 – Add Google Cloud SQL Proxy (TCP) instead of SSL

Version #6 – Add Google Cloud SQL Proxy (Unix Sockets) instead of SSL

Summary

More Information

Credits

Related Posts

John Hanley

Leave a ReplyCancel reply

Subscribe to Blog via Email

Terraform and Cost Controls

Repeatable GCP Environments

Categories

Search

Tags

Recent Posts

Find Us