Import output of linux command to MySQL table

Question

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

I would do this as a bash script. Run the command on one line and loop through each line of the result ( stackoverflow.com/questions/35927760/… ), 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
– Marvin WordWeaver Parsons, Commented Sep 16, 2018 at 6:20

KamilCuk · Accepted Answer · 2018-09-16 07:07:36Z

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.

edited Sep 16, 2018 at 7:07

answered Sep 16, 2018 at 6:37

KamilCuk

146k8 gold badges84 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Molenpad Over a year ago

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?

KamilCuk Over a year ago

mysql has great cdocumentation available online: 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

Collectives™ on Stack Overflow

Import output of linux command to MySQL table

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related