Point in time recovery mysql

[Fernando Gonzalez]

Hi, 

Sorry if it is very basic, but I don't understand something. For an instance of mysql with HA and Point in time recovery enabled. How can I estimate how much data can be lost (RPO)?

I understand that the backups are stored in multiple regions and that with backup + binary logs I can recover the database. But where are the binary logs stored? 

Thanks

[Olu]

As you have rightly mentioned, the Point in time recovery depends on the Binary logs. The binary log file name and position that correspond to the target point in time you plan the recovery; these are the essential information to be used to carry out the Point in time Recovery. 

Binary Logging can be enabled on your CloudSQL Instance by following the steps here[1], and once it is enabled, the Binary Logs are stored for each Automated Backup of your CloudSQL Instance. The Binary logging use storage space on your CloudSQL Instance. However, the Binary logs are deleted with their associated automatic backup every 7 days[2].

You may be concerned about performance on your CloudSQL Instance with the binary logging enabled, well, as per this doc[3], performance of read operations are unaffected by binary logging, regardless of the size of the binary log files.

About estimating how much Data may be lost, I think that depends mainly on your choice of recovery position[4]. 

I hope this answers your questions. 

[1]https://cloud.google.com/sql/docs/mysql/backup-recovery/pitr#enablingpitr
[2]https://cloud.google.com/sql/docs/mysql/backup-recovery/pitr#disk-usage

[3]https://cloud.google.com/sql/faq#pitr_perf

[4]https://cloud.google.com/sql/docs/mysql/backup-recovery/pitr#coordinates

[Fernando Gonzalez]

Thanks for al the info

Then, if the binary logs are stored for each Automated Backup of my instance, and the backups are generated daily, if something happens to the instance just before the next backup is executed, what happens to the data? Is all the data between the daily backups lost or am I understanding something wrong?

[George (Cloud Platform Support)]

If your point-in-time recovery date stamp is set to a moment during the day before, then all data will get lost since that date stamp yesterday, and the moment something happens to your instance, as detailed in the Identifying the recovery position paragraph [1]. 

[1] https://cloud.google.com/sql/docs/mysql/backup-recovery/pitr#coordinates