0

Using: MySQL 5.7

What I want to achieve:

To save console output of Cloud SQL output as a text.

Error:

Cloud SQL returns this: ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)

Things I tried:

  • Logging in with no password → asks password anyways, and any password including that of the server itself does not work.
  • Creating various users with password → same error result
  • Creating a Cloud SQL instance with skip-grant-tables so that no permission is required to modify the table →　Cloud SQL does not support this flag

I tried manually flagging the database with this option, but Cloud Shell doesn’t even support root login without password.

Possible solution:

If I can: mysql -u root with Cloud SQL with no password, then it should be able to do this just fine. It seems that any user besides root cannot even login to the instance.

Thank you in advance. Any clues / help is appreciated.

Etchee
  • 60
  • 1
  • 8
  • Howdy. I'm not sure I'm 100% following the puzzle ... are you saying you can't login to your cloud sql environment? The recipe I use is "mysql --host=[IP_ADDRESS] --user=root --password" – Kolban Mar 13 '19 at 01:26
  • @Kolban yes. I used that command from Cloud Shell and the above errors (in "things I tried section") come up. – Etchee Mar 13 '19 at 01:34
  • Where are you trying to connect from? Where are you running your mysql command? I'm wondering if you have authorized that source to be able to connect to your Cloud SQL instance? Are you using a Public IP or a Private IP as the target of the client connection? – Kolban Mar 13 '19 at 01:38
  • @Kolban Thanks! Sorry, I actually did not use mysql --host=[IP_ADDRESS] --user=root --password ... (although, I just tried it and it didn't work for both private and public ip. Private -> Error 2003 can't connect, public -> Error 1045 access denied.) I use gcloud sql connect root --password=xyz – Etchee Mar 13 '19 at 02:59
  • Where are you running those commands from? A local Linux/Windows box? Cloud Shell? A Compute Engine instance? – Kolban Mar 13 '19 at 03:28
  • @Kolban I primarily used Cloud Shell. Since it did not work, I tried from my local terminal machine but got the same error. – Etchee Mar 13 '19 at 04:51
  • Can you elaborate on what exactly you want to achieve? What do you mean by “saving console output of Cloud SQL output as a text”? – komarkovich Mar 13 '19 at 16:24
  • @komarkovich thank you for your comment. I have an open-source software deployed on GCP. I need to know all tables' columns and the table name. I have a SQL query that returns all of that. It has a long output and I need to save them all (selecting & copying does not work either.) to a text file. – Etchee Mar 14 '19 at 03:26
  • @komarkovichTo add to my comment above, I have tried SQL managing softwares like MySQL Workbench. No luck for them either.. – Etchee Mar 14 '19 at 03:30

1 Answers1

0

I believe the most trivial solution is to use the Google Cloud SDK with the following command.

You will export the results of the query in CSV format to Google Cloud Storage bucket, and copy them from the bucket to your system. Then you’ll have to parse the CSV file which is a standard procedure.

There’s an how-to guide here and you can take a look at a concrete example below:

Have some variables that will be used in multiple commands

INSTANCE_ID=your_cloud_sql_instance_id
BUCKET=gs://your_bucket here

Create bucket if you don’t have one, choosing the location accordingly

gsutil mb -l EU -p $DEVSHELL_PROJECT_ID $BUCKET

You can read the explanation of the following commands in the documentation 2, but bottom line will have a csv file on your file system at the end. Also make sure to edit the name of the DATABASE variable below as well as the correspondent query.

gsutil acl ch -u `gcloud sql instances describe $INSTANCE_ID --format json | jq -c ".serviceAccountEmailAddress" | cut -d \" -f 2`:W $BUCKET

DATABASE=db_visit

FILENAME=$DATABASE'_'`date +%Y%m%d%H%M%Y`_query.csv

gcloud beta sql export csv $INSTANCE_ID $BUCKET/$FILENAME --database=$DATABASE --query="select * from visit"

gsutil cp $BUCKET/$FILENAME .

To automate the login through mysql client and make subsequent queries and get its output I encourage you to research a solution along the lines of pexpect.

fbraga
  • 711
  • 4
  • 9