Execute Google Cloud SQL query in command line non-interactively [duplicate]

Question

I'm writing a script to orchestrate a Google Cloud SQL instance using the gcloud SDK.

The SDK has a connect command, which takes a username but the password has to be entered at the prompt - ie it cannot be passed as an argument.

Successfully authenticating then allows a query to be executed - the flow looks like this:

$ gcloud sql connect instance-name --user=root
Whitelisting your IP for incoming connection for 5 minutes...done.
Connecting to database with SQL user [root].Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 8054
Server version: 5.7.14-google-log (Google)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]> USE tablename;

I need to do this non-interactively. I've tried:

gcloud sql connect instance-name --user=root && echo "password" && echo "USE tablename;"

But it only executes the first command.

How can I connect, authenticate & run a query in a single command?

Update:

As per the comment from @danlor linking to this question, I've tried both:

yes password | gcloud...

and

printf 'password\n' | gcloud...

but both still prompt for the password.

Answer 1

It doesn't look like the gcloud sql connect command give you the ability to pass in the execute flag to the MySQL command line utility. I would suggest using the Cloud SQL proxy for this instead. Your script will likely look something like the following:

./cloud_sql_proxy -dir=/cloudsql -instances=<INSTANCE_CONNECTION_NAME> &
PID=$!
mysql -u root --password "YOUR-PASSWORD" -S /cloudsql/<INSTANCE_CONNECTION_NAME> --execute "USE tablename;"
kill $PID