error accessing the raw binlog files

[Amit Chandel]

Hi,

I am trying to access the binary log files of a cloud sql instance using mysqlbinlog. I used the following command from a GCE vm which has access to the cloud sql instance:
$ mysqlbinlog --read-from-remote-server --host=xxx --user=xxx --password=xxx mysql-bin.00pqrs

but getting the following error from the above command :
ERROR: Got error reading packet from server: Binary log is not open

Any idea what might be going wrong? I have confirmed the following cases: 

- Binary logs are already enabled on this server and there is a working read-replica of this instance.

- queries are running fine from this GCE vm using the mysql client.

- the mysql user already have 'REPLICATION SLAVE' privilege

- Accessing any binlog file from the output of 'SHOW BINARY LOGS' gives the same error. 

I am also wondering what are the alternate ways to access the raw binlog files? Is there a simple way to just export these to cloud storage?

Thanks,
Amit

[Katayoon (Cloud Platform Support)]

Could you clarify if this command has ever worked before? If yes, how recently did it stop working?  

For the second question, I am looking into it and come back to you shortly.

[Amit Chandel]

Thanks for the quick revert, Katayoon! 

I am trying this approach for the first time. Due to lack of documentation around the binlog files access, this is what I thought should work (since we can setup the external replica on a GCE vm, binlog files should be accessible by the mysql user with 'REPLICATION SLAVE' privilege).  

Just to add more, I am using 2nd Gen cloud-sql instance. 

Thanks,

Amit

[Katayoon (Cloud Platform Support)]

You could follow the steps in “How can I access transaction logs for my instances?” section of FAQ and write mysqlbinlog command based on the official MySQL documentation.You could try the following command:

mysqlbinlog -h INSTANCE_IP --user=root --password -R --base64-output=decode-rows -t --verbose LOG_NAME

For exporting any file to Cloud Storage, you could use gsutil Tool or even define a Cron job, so that you would be able to schedule making a copy of your binary log.

I hope this works for you.

[Amit Chandel]

Thanks for your help, Katayoon!

I was trying earlier as you have suggested but I was using the wrong IP to connect, and hence were getting the above error. I was able to access binlog using the proper INSTANCE_IP.

Just to add, I was getting "ERROR: Could not construct log event object: Found invalid event in binary log" when used the mysqlbinlog version 3.2 but version 3.4 fixed the issue.