MySQL Server running on GCP Instance - connection blocked by firewall

Created
Dec 24, 2020 7:39 PM
Tags
Link to Post

Google Cloud Platform's managed CloudSQL is awesome, but you have to pay for a whole instance

So sometimes it's better to run a single instance and host your software and database on it - basically if you're super budget conscious - like I was when setting up some personal projects.

However I found the firewall was blocking my code's connection to localhost. To get around this, I connected directly to MySQL's socket.

First get the socket:

mysql> show variables like 'socket';

Then add it too your environment and read it in to your mysql settings

const settings = {
    'host'                  : process.env.DB_HOST,
    'socketPath'            : process.env.DB_SOCKET_PATH,
    'port'                  : process.env.DB_PORT,
    'user'                  : process.env.DB_USER,
    'password'              : process.env.DB_PASS,
    'database'              : process.env.DB_DATABASE,
    'waitForConnections'    : true,
    'connectionLimit'       : 10,
    'queueLimit'            : 1000
}

This little snippet to print mysql settings without password is also handy:

const chalk = require('chalk')

function printDBsettings(settings){
    let s = Object.assign({}, settings)
    try{
        s.password = "*".repeat(s.password.length)
    }catch(err){
        console.error(chalk.bgRed(`
        ***************                         
        Cannot start database, be sure to run   
        $ source .env                           
        ***************                         `
        ))
        return false
    }

    console.info(chalk.yellow(`
    ***************
    ${chalk.inverse.red("ENV:" + process.env.ENV)}

    Starting database with settings 
${JSON.stringify(s, null, 2)}

    ***************`))
    return true
}

Also available here: