Please note that the contents of this offline web site may be out of date. To access the most recent documentation visit the online version .
Note that links that point to online resources are green in color and will open in a new window.
We would love it if you could give us feedback about this material by filling this form (You have to be online to fill it)



Backups and Recovery

This page describes how to schedule and restore your instance backups, and enable and perform a point-in-time recovery of an instance.

Contents

Introduction

Google Cloud SQL takes care of securely storing your backed-up data and makes it easy for you to restore from a backup and perform a point-in-time recovery to specific state of an instance. Google Cloud SQL retains up to 7 backups for each instance, which is included in the cost of your instance.

Keep the following in mind when planning how you will use your instance and your restore needs:

  • To perform a restore of your instance to a backup, you must have automated backups enabled.
  • To perform a point-in-time recovery of your instance, you must have automated backups and binary logging enabled for the instance.
  • Restoring from a backup restores to the instance from which the backup was taken. Performing a point-in-time recovery creates a new instance, which is a clone of the source instance.

You can enable or disable automated backups and binary logging for an instance at any time.

Backups

For instances that have backups enabled, Google Cloud SQL retains and stores the last 7 backups of the instance without charge. Google Cloud SQL takes a backup of an instance only if the instance's data has changed since the last backup. Depending on the idle time of an instance between backups, the time spanned by its retained backups can be greater than 7 days. When you restore from a backup, you will replace all data on the instance.

You can enable/disable automated backups at any time for an instance. For example, you might need to want to perform a long-running task, like loading data, where you want to disable automated backups temporarily.

Scheduling backups

Each Google Cloud SQL instance has backups enabled by default, scheduled to run at a time chosen at random.

Developers Console

  1. Go to the Google Developers Console and select a project by clicking on the project name.
  2. In the sidebar on the left, click STORAGE , and then click Cloud SQL to show a list of Cloud SQL instances for the project.
  3. Select the instance for which to configure backups.
  4. Click Edit .

  5. In the Options section, in the Backup Window box, select a value.

    You can choose one of 23 four-hour backup windows, displayed in the time zone used by the browser. For example, if you choose the 6:00 am - 10:00 am window, this means that a backup of the instance could start at 6:00 am at the earliest and 10:00 am at the latest.

    To disable daily backups for the instance, select None .

  6. Click Confirm to save your changes.

Cloud SQL Command Line

  1. Using the Google Cloud SDK , select a project. 
    $ gcloud config set project your-project-id
  2. Edit the instance to specify a backup start time (e.g., hh:mm = "16:00"). 
    $ gcloud sql instances patch your-instance-name --backup-start-time hh:mm
  3. To disable backups for the instance use the no-backup flag. 
    $ gcloud sql instances patch your-instance-name --no-backup

After you set up a schedule for backups, Google Cloud SQL backs up your databases according to that schedule. To back up your instance, Google Cloud SQL logs into it as the cloudsqladmin user. For more information, see User for scheduled backups .

Google Cloud SQL backups are taken by using FLUSH TABLES WITH READ LOCK to create a snapshot. This will prevent writes, typically for a few seconds. Even though the instance remains online, and reads are unaffected, it is recommended to schedule backups during the quietest period for your instance. If there is a pending operation at the time of the backup attempt, Google Cloud SQL retries until the backup window is over. Operations that block backup are long-running operations such as import, export, update (e.g., for an instance metadata change), and restart (e.g., for an instance restart).

Note : If your instance uses the per use billing plan, scheduled backups incur a charge, because the instance needs to be activated to take the backup. A backup will not be taken if your instance has not been used.

Viewing backup history

If backups are enabled for an instance, Google Cloud SQL retains and stores the last 7 backups of the instance without charge. Depending on the idle time of an instance between backups, the time spanned by the retained backups can be greater than 7 days. Note that when you use the Developers Console to display backup history, it does not show skipped or failed backups in the backup history. But, using the Cloud SQL Command Line shows skipped backups.

Developers Console

  1. Go to the Google Developers Console and select a project by clicking on the project name.
  2. In the sidebar on the left, click STORAGE , and then click Cloud SQL to show a list of Cloud SQL instances for the project.
  3. Select the instance for which to view backup history.
  4. Scroll to the bottom of the instance summary to view the Backups pane.

Cloud SQL Command Line

  1. Using the Google Cloud SDK , select a project. 
    $ gcloud config set project your-project-id
  2. List the backups of the instance. 
    $ gcloud sql backups --instance your-instance-name list
  3. To list the details of one backup, use the dueTime in a GET request. You can get the dueTime (e.g., "2014-03-01T00:49:00.678Z") from the list of backups of the instance. 
    $ gcloud sql backups --instance your-instance-name get dueTime

Restoring from backups

Your instance will be unavailable while a backup is being restored. The restore process overwrites all the current data so that any changes made since the selected backup was created, are lost.

Developers Console

  1. Follow the steps for Viewing backup history .
  2. Find the backup from which you want to restore and select Restore .
  3. Select Restore .
  4. In the Restore database from backup dialog box, click OK to start the restore process.
  5. You can check the status of the restore by going to the Operation page of the instance.

Cloud SQL Command Line

  1. Using the Google Cloud SDK , select a project. 
    $ gcloud config set project your-project-id
  2. Restore from a specified backup. 
    $ gcloud sql instances restore-backup --due-time dueTime your-instance-name

    You can get the dueTime by running a gcloud sql backups (see Viewing backup history ). Be sure to choose a backup that is marked as SUCCESSFUL and not SKIPPED.

User for scheduled backups

Google Cloud SQL creates a special user for performing scheduled backups Google Cloud SQL creates the cloudsqladmin user for each instance, and generates a unique instance-specific password for it. Cloud SQL logs in as the cloudsqladmin user to perform scheduled backups.

Point-in-time recovery

Point-in-time recovery allows you to recover an instance to a specific point in time. For example, if an operator error causes a loss of data you can recover a database to the state it was just before the error occurred. A point-in-time recovery differs from restoring from a backup in that it creates a new instance with the recovered data. (Restoring from a backup restores the data to the instance for which the back up was taken.) An instance created from a point-in-time recovery is charged at the standard Cloud SQL rate. Point-in-time recovery requires that backups are enabled (see Scheduling backups ) and that binary logging for an instance is enabled. An instance can then be recovered back to any valid state up to the first successful backup taken after binary logging was enabled.

Also note that enabling binary logging will lead to a slight reduction in write performance. Reads are unaffected. The impact on performance is greater for instances that use synchronous replication, so we recommend that you enable binary logging for instances with asynchronous replication.

Enabling binary logging

Points to consider when enabling binary logging for point-in-time recovery:

  • Binary logging is not enabled by default for a new instance.
  • Enabling binary logging results in every update to your database being written to an independent log (binary log), so it involves a small reduction in write performance.
  • The space occupied by the binary logs would count towards the storage used in an instance.
  • There must be at least one backup before a point-in-time recovery can be performed.
  • You can recover an instance back to any valid state up to the first successful backup taken after binary logging was enabled.
  • If you are recovering to the most recent state of an instance (i.e., you do not specify binary log coordinates), then the newly create (clone) instance is recovered up to the latest binary log coordinates with respect to the time you initiate recovery.

When you perform a point-in-time recovery, you recover to a new instance. The following settings are copied from the source instance to the new instance:

  • Tier, package, preferred location, backup window, region, replication settings, and binary log settings.
  • Authorized networks and authorized App Engine applications.
  • SSL configuration, including certificates.

Developers Console

  1. Go to the Google Developers Console and select a project by clicking on the project name.
  2. In the sidebar on the left, click STORAGE , and then click Cloud SQL to show a list of Cloud SQL instances for the project.
  3. Select the instance for which to enable binary logging.
  4. Click Edit .
  5. In the Binary Log box, select Enabled .
  6. Click Confirm .
  7. In the summary page for the instance, you should see Binary Log Enabled .

Cloud SQL Command Line

  1. Using the Google Cloud SDK , select a project. 
    $ gcloud config set project your-project-id
  2. Enable binary logging. 
    $ gcloud sql instances patch --enable-bin-log your-instance-name

    To disable binary logging use the --no-enable-bin-log flag.

  3. Check the status of binary logging of the instance. 
    $ gcloud sql instances get your-instance-name

    Look for binaryLogEnabled: True in the backupConfiguration setting.

Performing a point-in-time recovery

There must be at least one successful backup after you have enabled binary logging for an instance before you can perform a point-in-time recovery. To check if backups exist for an instance, see Viewing backup history .

Cloud SQL Command Line

  1. Using the Google Cloud SDK , select a project. 
    $ gcloud config set project your-project-id
  2. If you want to restore to the latest state of an instance (up to the latest binary log cordinates), then do the following: 
    $ gcloud sql instances clone source-instance target-instance

    The clone command returns information about the operation, including an operation ID (e.g, "1d68c0d1-e124-4400-80a9-826a501188d4"). You can use this ID to check the status of the clone operation.

  3. If you want to restore from the instance up to specific binary log coordinates (identified by a binary log file and binary log position combined), then do the following:
    1. Get the binary log name by logging into your instance. 
      $ mysql --host=instance-ip --user=root --password
mysql>show binary logs;  #shows all binary log files
mysql>show master status;  #shows name of the current binary log file

      For help on getting the instance-ip , see Configuring access control for IP connections .

    2. Use mysqlbinlog to show the contents of a binary log and find coordinates. 
      $ mysqlbinlog -h instance-ip --user=root --password -R --base64-output=decode-rows -t --verbose log-name
    3. Restore using the binary log name and coordinates. 
      $ gcloud sql instances clone source-instance target-instance \
--bin-log-file-name=file-name --bin-log-position=position

      For example, a restore command might look similar to the following: 

      $ gcloud sql instances clone instance1 instance1-cloned --bin-log-file-name=mysql-bin.0000031 --bin-log-position=107
  4. Check the status of a restore operation by using the name of the target instance and the operation ID returned from the clone command. 
    $ gcloud sql operations --instance target-instance get operation-id

    When the operation is in progress, a state of "RUNNING" is returned. When the operation is complete, a state of "DONE" is returned.

For more information about point-in-time recovery, see the MySQL Reference, Point-in-Time (Incremental) Recovery Using the Binary Log .

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 3.0 License , and code samples are licensed under the Apache 2.0 License . For details, see our Site Policies .

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.