How to Backup Google Cloud SQL for MySQL and Restore on Your Own Server

Google Cloud SQL is a managed database service for MySQL, PostgreSQL, and SQL Server. Even if you are new to managed database services, it just takes a few minutes to set up Google Cloud SQL services and scale the instance capabilities as you go. Google Cloud SQL ensures both in transit and at rest data are fully secured and complies with many industry standards like SSAE 16, ISO 27001, PCI DSS, and HIPAA.

This blog discusses Google Cloud SQL for MySQL features and using Backup Ninja as an alternative backup tool for your Google Cloud SQL MySQL databases, files, and directories on Google cloud.

What You Need to Know about Google Cloud SQL for MySQL

Google Cloud SQL for MySQL allows you to create and manage your MySQL Community Edition on Google Cloud without the need to worry about infrastructure setup and maintenance cost. You can setup the instances easily using the Google Cloud Console. Along with the standard features, Google has also ensured enhanced security with support for external connections with the Cloud SQL Proxy or with the SSL/TLS protocol for private IP (private services access). Also, the Google Cloud SQL for MySQL has an automated backup and point in time recovery feature to prevent any data loss and speed up the recovery process. The platform also offers data replication and failover features to minimise any downtime. Google Cloud SQL for MySQL supports App Engine applications that are written in Java, Python, PHP, Node.js, Go, and Ruby. There are many ways to connect to MySQL instances on Google Cloud SQL. You can use third-party tools like SQL Workbench or Toad for MySQL, or even applications running on Kubernetes and Compute engine without any performance issues.

What are the Methods to Backup Google Cloud SQL for MySQL Databases?

Cloud SQL backups are incremental; you have one full backup, which is the oldest version of the database, and the next contains the changes made to the previous backup and are usually smaller in size. Cloud SQL allows you to take the backups in an automated or on-demand way. You can use an on-demand backup method to backup the entire instance without waiting for a backup window. Automated backups are taken in a 4-hours backup window and when the instance is low in activity. Automated backups are deleted according to the retention policy; however, the on-demand backups must be deleted manually.

Google Cloud SQL for MySQL, by default, stores backups in two regions for redundancy. You can also store the backups in a custom location to comply with local regulations or easy recovery.

Google Cloud SQL for MySQL Backups Using Backup Ninja

Google Cloud SQL services allow you to take backups using the automated or on-demand method. Both this method allows you to take backup and store them either on the instance location or on your preferred location, but it limits additional configuration settings like backup frequency, compression, and encryption. Alternatively, you can use backup services like Backup Ninja, Solarwind, Veeam to create and manage backups efficiently in your preferred location with additional settings.

Backup Ninja is a simple to use automated backup service for you to take backups for open source databases, files, and directories and store them locally or in the cloud. You can schedule incremental, partial, or full backups using the same backup system and have them stored on your preferred cloud location. All you need to do is, install the Backup Ninja agent on your database server or virtual instances like Google Compute Engine instance and the Backup Ninja agent will manage your MySQL backups effortlessly. Besides this, you also get to have a full view on the details of your backup schedule, giving you information on the backup copies and storage location in a single view. Backup Ninja provides you with an interactive monitoring dashboard giving you key performance indicators to monitor all your backups and events centrally.

Installing the Backup Ninja Agent on Your Server

To schedule backups on Backup Ninja, you will need to install the agent on your backup server. This agent will then communicate with your database and take backups according to the backup schedule you have created. For security purposes, Backup Ninja does not store any security keys and uses advanced encryption algorithms to protect your backup. Following are the simple steps to install the Backup Ninja agent on your server.

Step 1: Set your backup target

In the first step, you need to decide the type of backup you would like to have. You can either choose to backup and restore or only the backup option where you can always edit and add in the restore option. Next, select the database technology and the version. The version selection is enabled when you choose the database server type here.

Step 2: Creating a Database User

Backup Ninja requires a database user with the privilege to backup and restore the database. For a managed database, click on the checkbox below to use the existing user to manage the database backups. Fill in the hostname, which could be the endpoint or an IP address, and the password, and proceed to the next step.

Step 3: Install the Backup Ninja Agent

To install the agent, you will need to copy and execute the installation script on the backup server.

Step 4: Server Discovery

Once the installation is complete, the server will be discovered automatically and displayed on the screen.

You will see the discovered server added to the list of servers on the Servers listing page.

The next sections show you how to schedule backups for a Google Cloud SQL database instance.

Scheduling a Google SQL Database Backup Using Backup Ninja

Step 1: Complete the schedule details

Give a name for the backup schedule and select the server you want to backup. The backup method will list the backup tools installed in the server automatically. Backup type allows you to choose either a full or partial backup type. Select the mysqldump and partial backup type to backup the selected Google SQL database from the list.

Step 2: Configure the backup storage

Next select where you would like to store the backup. You can have a combination of local and cloud storage configured on the same screen. If you would like to add more storage locations, simply click on the Add New Storage button and fill in the next storage location. Retention here is the duration of how long you would want to keep the backup.

Step 3: Backup name, compression, and encryption setting

Give a name to your backup, or you can choose to leave it at the default setting. If you are concerned about the backup size, enable the compression toggle and the backup will be compressed using the gzip compression. You can also enable the encryption to have encrypted backups.

Step 4: Set the backup frequency

The last step is to set the frequency of the backup task, and the backups will be scheduled to run according to the database server settings.

Check the created schedule on the scheduled listing. Click on the play button to start or pause the schedule.

You can view the details of the schedule, including the backup information when you click on the View link.

Conclusion

Google Cloud SQL for MySQL allows you to spin up MySQL instances with an appropriate configuration, which is scalable when required. As a new customer, you will enjoy $300 in free credits to spend on Google Cloud for the first 90 days, and this gives you the timeframe to test its services widely. With Google Cloud SQL for MySQL, you can reduce time consuming tasks like database provisioning or storage capacity management. If you are a startup, Google Cloud SQL for MySQL is a go-to option because it allows you to have automatic database provisioning and connect your applications on virtual machines running on the Google Cloud Platform. However, when it comes to backup and recovery strategy, it is good to have an independent backup management service or a tool like Backup Ninja to manage the backups effectively. Tools like Backup Ninja will allow you to schedule databases, files, and folders backup using a simple web interface in a reliable, secured, and automated way.