Cloudsql backup validation

[Sharath Kumar]

Hello there, 

We are trying to create an automated cloudsql backups validation process. We have read posts on cloudsql backups being corrupt and not being able to restore it. So we wanted to make sure the backups we take are valid!

This is our current set-up:

cloudsql-master

 - cloudsql-read-replica

 - cloudsql-failover-replica

We plan to do the following to validate our backups:

 1. create a new instance: backup-instance

 2. restore one of the backup files onto backup-instance

 3. make backup-instance a read replica to cloudsql-master

 4. run pt-table-checksum  on cloudsql-master to verify if backups are valid and in sync with master

My question is:

 - is it possible to do step 3 and 4?

 - If not is there a better way to verify the validity of cloudsql-backups?

Thanks

[Thad Megow]

To elaborate, since using pt-table-checksum for backup validation will require us to create a new mysql slave from a backup, can we use either of the following two strategies to accomplish this?

1. Restore a cloudsql backup to a new primary (non-replica) cloudsql instance; setup/start replication between two primary (non-replica) instances. Similar to the technique described in the "Configuring External Replica" here -> https://cloud.google.com/sql/docs/mysql/replication/configure-external-replica However in our case the "external replica" will be another primary (non-replica) cloudsql instance.
2. Restore a cloudsql backup to an existing read replica, overwriting the existing data on the read replica, and restart replication to the newly-restored read replica.

[Jun (Cloud Platform Support)]

Hi Sharath,

Please allow me some time to research on this, and I will update here once I'll have any findings, thanks!

- Jun

On Tuesday, May 23, 2017 at 11:25:34 AM UTC-4, Sharath Kumar wrote:

[Jun (Cloud Platform Support)]

Hi Sharath,

After some research and discussion, I can confirm now that there is no way to accomplish step 3 since step 2 will almost certainly fail if a backup is corrupted. In other words, the backup would not be corrupt if the restore in step 2 succeeds and restore would fail if there is actual corruption. 

- Jun

On Tuesday, May 23, 2017 at 11:25:34 AM UTC-4, Sharath Kumar wrote: