In a series of blog posts about Google Cloud and related products, we have already written about BigQuery and App Engine. This week we present Google Cloud SQL.
What is Google Cloud SQL?
Using Google Cloud SQL, you can manage and store data using this relational database. Google ensures performance and availability and takes care of database management, patch management and replication. Google Cloud offers a powerful database in the form of Cloud SQL, which provides your app the reliable and redundant storage it requires, along with sufficient space and speed of operation.
A Cloud SQL instance is quite similar to a server. Several databases can be part of one Cloud SQL instance. It is a MySQL database which resides in Google Cloud. The database has all the functionality and capabilities of MySQL, with a number of additional features and a few unsupported features as well. Google Cloud SQL is ideal for small to midsize apps, doesn’t require any maintenance or software installation and is not difficult to use.
To connect to a Google Cloud SQL instance, you can use:
- Google Apps Script – lets you develop, configure and utilize relational databases on Google Cloud. Connect to the databases in Apps Script through the special method getCloudSqlConnection (For more details, please refer to https://developers.google.com/apps-script/guides/jdbc#accessing_google_cloud_sql_databases.).
- MySQL Client – Before you begin to use MySQL Client, you have to install MySQL, request an IP address and authorize your network. Once that is done, you can connect using the MySQL command-line tool or using the MySQL command-line tool with SSL. You can use various MySQL client programs and common SQL commands to add users, check instance uptime and other status variables and show the number of threads and connections (For more details, please refer to https://developers.google.com/cloud-sql/docs/mysql-client.).
- App Engine Python apps – To create a Google Cloud SQL instance, you have to sign into the Google Developers console; open an existing project or create a new one; select Cloud SQL from inside a cloud console project to access the control panel for that project and click on ‘new instance’ to create an instance in the project. You can configure replication, billing and size options thereafter. (For more details, please refer to https://developers.google.com/appengine/docs/python/cloud-sql/.).
- Toad for MySQL or SQL Workbench, among other third-party tools – You can manage your database through reporting and administration tools that offer different degrees of support. The visual-based tools can help you manage several databases at the same time. (For more details, please refer to https://developers.google.com/cloud-sql/docs/admin-tools.).
- App Engine Java apps – To utilize Google Cloud SQL instances with the Java SDK for Google App Engine, you have to initiate a Cloud SQL instance; develop a starter database and app; access your database; use a local MySQL instance for the duration of development and adhere to the access and size limits while making use of persistence APIs with Cloud SQL (For more details, please refer to https://developers.google.com/appengine/docs/java/cloud-sql/.).
- External apps that use standard MySQL database drivers – The standard MySQL protocol can be used to connect to Google Cloud SQL for apps that don’t run on Google App Engine. You have to specify the IP range from which you intend to connect, before you establish contact, regardless of the method you use for connection (For more details, please refer to https://developers.google.com/cloud-sql/docs/external.).
What Features and Restrictions Does Cloud SQL have?
- As Google Cloud SQL is fully managed by Google, you don’t have to be concerned about database management, patch management or replication as Google takes care of it.
- Since it uses open source databases that are among the most popular ones worldwide, i.e., MySQL databases, you can develop and deploy quickly. Instances are obtainable up to 500GB storage and 16GB RAM.
- You can access and manage instances through a command-line interface or a web console without difficulty. So, Cloud SQL provides you with a fair degree of control.
- The pay-per-use option is quite inexpensive. By paying only for the time for which you access data, you save money, especially if you are operating a database used sporadically or lightly. For instances loaded more heavily, you can control costs through the package option.
- You can avoid lock-in of data while migrating data on to or off the Google Cloud platform through JDBC, MySQL Wire Protocol and mysqldump, among other tools, besides standard connections.
- Failover among the many physical locations where data is replicated is automatic. So, even if there is a major failure, your data is secure and your database is available. Google manages your backups and ensures that it’s not difficult to restore data when required, even if it implies point-in-time delivery.
A few additional features are as follows:
- Point-in-time recovery and automated backups
- Hosting your MySQL databases in the cloud
- Connecting with the secure sockets layer (SSL) protocol
- Greater durability and availability of data which is replicated in various geographic locations
- Support for MySQL connectors and wire protocol
- Availability of instances in Asia, EU and the US
- Asynchronous or synchronous replication among various physical locations
- Google Cloud SQL doesn’t support user defined functions
- It doesn’t support a few MySQL statements
- It doesn’t support the SUPER privilege
- It doesn’t support the SHA2 () function from MySQL
How Do You Get Started With Cloud SQL?
To get started with Google Cloud SQL, you can:
- Create a Google Cloud SQL instance and configure it with an IP address to be able to connect to it.
- Use the MySQL client to connect a Google Cloud SQL – install the MySQL client; use the IP address you created to connect to the instance; create a table and a database, key in some data and query it.
- Configure a Google Cloud SQL instance – configure access; set a root password; create, delete, restart and edit instances; configure SSL for instances; view information about instances; export or import data; configure point-in-time recovery and schedule backups.
- Connect to a Google Cloud SQL instance – from App Engine Go apps; using MySQL client; from App Engine PHP apps; from reporting and admin tools; from App Engine Python apps; from external apps; from App Engine Java apps; from Google Apps Scripts
You can use apps running on Compute Engine and App Engine, in addition to standard tools, to connect to a Google Cloud SQL instance that you have created and configured (For more details, please refer to https://developers.google.com/cloud-sql/docs/getting-started?_ga=1.56177901.77362772.1397802638.).
How do you manage your Cloud SQL instances?
Management of Cloud SQL instances involves:
- Creating an instance – While creating a Google Cloud SQL instance, the name of the instance is the only configuration setting needed. You can modify or accept the default values for all other settings for the instance.
- Editing an instance – You can edit an instance and change its settings at any time after you have created it. The change is applied immediately for all instance settings except for size of the instance. To make changes in the size of an instance, Google Cloud SQL starts and stops the instance automatically for a short period of time. You can’t connect any apps to the instance during that time period. For apps built to handle temporary disruptions to database connectivity, this is generally not a problem.
- Restarting an instance – An instance is stopped as all connections from it are drained. Google Cloud SQL restarts the instance when there is a fresh connection request for the instance, until which the instance stays shut. The requirement to restart any instance arises rarely, however.
- Deleting an instance – When deleted, the data in an instance is lost permanently and it is a good practice to delete an instance following a scheduled backup or export of the instance.
- Configuring SSL for instances – You can use the SSL protocol to connect to a Google Cloud SQL instance. You can configure the instance to be able to connect to it through SSL, once you have created it. All the key files and certificates required for a secure connection are available through the Google Developers Console.
- Adding project members – If you are the owner of a project, you may have to add project members who are allowed to manage instances. Use the Google Cloud SDK or the Google Developers Console to add editors and project owners. You have to validate the machine from where you establish a connection, if you choose Google Cloud SDK for connecting. In any given project, the project rights are the same for all the instances associated with that particular project. You should consider the task to be accomplished and provide the minimum level of access required for that. After a member is added, you can alter the permissions assigned to a project any time you wish to.
How do you use your Cloud SQL instances?
To use Google Cloud SQL instances, you have to:
- Install a MySQL client – Install MySQL, request an IP address and authorize your network. Once you have done that, you can connect to an instance.
- Connect using a MySQL client – Use the mysql command to start the interactive shell and identify the IP address of the instance and a user; enter SQL commands at the MySQL prompt; use the \s command at the MySQL prompt to make sure that you have a secure connection over SSL.
- Use other MySQL client programs – Once you install the MySQL client, there are many client programs that you can use besides the mysql program. Regardless of the MySQL client you use, you send the IP for a Cloud SQL instance, a password and a user name, just like you would for mysql.
- Use common SQL commands – to add users, check status variables such as checking uptime for instances and show the number of threads and connections. You can run some basic SQL commands using any reporting and admin tool that is compatible with MySQL or the MySQL client.
For more details, please refer to https://developers.google.com/cloud-sql/docs/mysql-client?_ga=1.127784011.77362772.1397802638#connect.
What is Cloud SQL Application Programming Interface (API)?
To programmatically administer your instances, Google Cloud SQL offers a REST API. The Google Cloud SQL API offers means for:
- Getting information
- Listing all the SSL certificates of an instance
- Listing instances in a project
- Listing available levels of service
- Gathering information about instances
- Gathering information regarding backup runs
- Handling instances
- Creating and deleting SSL certificates
- Creating and deleting instances
- Exporting and importing databases from and to Google Cloud storage
- Restoring instances from backups
- Restarting instances
An authenticated user has to authorize all the requests sent to Google Cloud SQL API.
At present, anyone can access the API and activate it through the Google Developers console. You just have to turn on the status of Google Cloud SQL API in the sidebar of the console, after choosing the project you want to activate the API for.
An authorization token has to be a part of each request sent by your application to the Google Cloud SQL API. Your application is identified to Google through the token.
For more details, please refer to https://developers.google.com/cloud-sql/docs/admin-api/.
An Overview of Support provided for Cloud SQL
In case you have any queries regarding usage or management of Cloud SQL, you can search Stack Overflow, look at the frequently asked questions or search through Google’s documents. You can also post your queries on Stack Overflow, while using the google-cloud-sql tag.
If you need more far-reaching support, including telephone access to Google’s support team around the clock, you can use the appropriate Google Cloud platform support package. You could also sign up for the google-cloud-sql-announce group at Google Groups to receive periodic announcements regarding Google Cloud SQL.
This is about how CodeFutures utilize their dbShards/Migrate software with Cloud SQL and MySQL wire protocol connection to make migration of data quick and smooth. This is to solve the problem of migrating important data from one environment to another not being smooth and at risk of loss of precious data. This covers migrating databases from a data center to a cloud or from another environment to Google Cloud.
For more details, please refer to https://cloud.google.com/files/codefutures.pdf.
India-based OrangeScape developed the KiSSFLOW app making use of Google Cloud SQL and App Engine. KiSSFLOW is a management and workflow-creation app for the Google Apps Marketplace. The pay-per-use model of Cloud SQL is a significant reason for OrangeScape’s choice and it makes Cloud SQL suitable for other software as a service (SaaS) providers as well. Customers of Cloud SQL can choose to pay only when they access a database and for low-cost storage otherwise. So, OrangeScape can requisition a database instance for every one of its customers at economical cost.
For more details, please refer to https://cloud.google.com/files/kissflow.pdf.
The Daffodil team saves development time up to nearly 80 hours every month because of simplified database management, no active management for scaling and easier deployment with the help of Google App Engine and Google Cloud SQL. They have also been able to reduce the time to market for new products. Not only is Daffodil’s data safe, but they also benefit from Cloud SQL’s dynamic filtering and database indexing abilities.
Daffodil is based in India and is an information technology services company.
For more details, please refer to https://cloud.google.com/files/daffodil.pdf.
Google Cloud SQL makes it easier for you to manage your databases as it supports SQL-like queries, even as it doesn’t support all SQL commands. It offers a pay-per-use service for accessing your data stored on the inexpensive Google Cloud platform.