0

I'm trying to create an inventory of my google cloud SQL instances and keep it in a separate MySQL database, so I can feed it into our existing on-premise inventory.

To get a list of gcloud instances, I can run the following:

$> gcloud sql instances list

Which will return something like:

NAME    DATABASE_VERSION  LOCATION        TIER         ADDRESS         STATUS
inst3   MYSQL_5_7         europe-west4-b  db-f1-micro  xx.xx.xx.xx     RUNNABLE
inst2   MYSQL_5_6         europe-west4-b  db-f1-micro  xx.xx.xx.xx     RUNNABLE
zandra  MYSQL_5_7         europe-west4-c  db-f1-micro  xx.xx.xx.xx     RUNNABLE

In a bash script if I run this like:

$> allinstances=`gcloud sql instances list`

to set a variable called allinstances, how do I then insert this into a mysql table called gcloudinv

I have tried this expecting an error:

$> mysql -uuser -ppassword gcloudinv < allinstances

I know this won't work, but what I would I need to do inbetween to turn the output into a format mysql can work with.

Ideally I will be overwriting this base inventory daily.

Thanks

Maxim
  • 4,075
  • 1
  • 14
  • 23
Molenpad
  • 833
  • 2
  • 14
  • 34
  • 1
    I would do this as a bash script. Run the command on one line and loop through each line of the result ( https://stackoverflow.com/questions/35927760/bash-script-loop-through-shell-output ), ignoring the first, storing each value in a variable. Then use the mysql command to insert the values required for your purposes. – Marvin WordWeaver Parsons Sep 16 '18 at 06:20

1 Answers1

2
# just craete table if does not exists query
query="CREATE TABLE IF NOT EXISTS 'gcloud' ( NAME varchar(255), DATABASE_VERSION  varchar(255), LOCATION varchar(255), TIER varchar(255), ADDRESS varchar(255), STATUS varchar(255) ); "
# convert the lines in format `string[spaces]string[spaces] and so on` into a `insert into 'gcloud' ( string , string, and so on );`
query+="$(  gcloud sql instances list | tail -n +2 | while read -r name database_location location tier address status; do echo "insert into 'gcloud' ( '$name', '$database_location', '$location', '$tier', '$address', '$status' );"; done )"
# execute the query
mysql -uuser -ppassword gcloudinv -e "$query"

Notes:

  • tail -n +2 removes the first line from input
  • Don't use ` ` to get command output, it's been deprecated and can't be nested. Use $( .. )
  • You can pipe the content of a variable into a command using HERE-strings, like: mysql -uuser -ppassword gcloudinv <<<"$allinstances"
  • After removing the first line, it's a metter of transforming the line into a insert mysql statement.
KamilCuk
  • 120,984
  • 8
  • 59
  • 111
  • Thanks for this, looks reasonably simple. One question, I'm getting a syntax error on the insert statemtent as the string values are not coming out with single quotation marks or commas between: `insert into instances ( mydb MYSQL_5_7 europe-west4-c db-f1-micro 00.00.00.00 RUNNABLE );` Which part should I edit to write this into the output? – Molenpad Sep 16 '18 at 07:02
  • 1
    `mysql` has great cdocumentation available online: https://www.w3schools.com/sql/sql_insert.asp . The values in insert have to be comma delimeterd. Also I think I forgot about qoutes. You posted your text with spaces inside, probably there are tabs in `gcloud` output, not spaces , but you posted spaces. Try to use `while` without any `IFS=' '` for example, or use sed with `s/[[:space:]]\+/, /g` – KamilCuk Sep 16 '18 at 07:05