Programming Google App Engine with Python (2015)
Chapter 11. Using Google Cloud SQL with App Engine
A sizable portion of this book is dedicated to Google Cloud Datastore, a schemaless persistent object store designed for applications that scale to arbitrary sizes. Those automatic scaling capabilities come at the expense of features common to relational databases such as MySQL, PostgreSQL, and Oracle Database. While many common tasks of web application programming are well suited, or even better suited, for a scalable datastore, some cases call for a real relational database, with normalized entries, real-time join queries, and enforced and migratable data schemas. And sometimes you just want to run third-party software that expects you to have a SQL database on hand.
For those cases, there’s Google Cloud SQL. A feature of Google Cloud Platform, Cloud SQL gives you a straight-up no-nonsense MySQL relational database, designed for ease of use from Cloud Platform runtime environments like App Engine. Your database lives on a Cloud SQL instance,a virtual machine of a particular size that runs for as long as you need the database to be available. Your app code connects to the instance to execute SQL statements and queries using a standard database interface. You can also configure an IP address for the instance and connect to it with any MySQL client.
Naturally, Cloud SQL is the opposite of Cloud Datastore when it comes to scaling. Each SQL instance has a large but limited capacity for concurrent connections, CPU, and data. If you need more capacity than a single instance can provide, it’s up to you to start new instances, divide traffic, and shard or replicate data. Cloud SQL includes special support (currently in a beta release) for read replicas, special instances in a read-only mode that copy all of the updates from a master instance. You manage Cloud SQL instances just like you would a fleet of MySQL machines, with all of the flexibility and overhead that comes with it. Cloud SQL offers many instance types, and for many applications you can upgrade a single instance quite far before you have to worry about scaling.
In this chapter, we’ll give a brief overview of how to get started with Cloud SQL, and how to develop App Engine applications that use it. We won’t cover every feature of MySQL, as there is plenty of documentation online and many excellent books on the subject. For information on advanced subjects like read replicas, see the official documentation.
Choosing a Cloud SQL Instance
With Cloud SQL, as with all of Google Cloud Platform, you only pay for the resources you use. Cloud SQL’s billable resources include instance hours, storage, I/O operations to storage, and outbound network bandwidth. Network traffic between App Engine and Cloud SQL is free. Every instance gets a free externally visible IPv6 address, and inbound network traffic is also free of charge. You can pay a little more for an IPv4 address for the instance if you have a client on a network that doesn’t issue global IPv6 addresses.
Like App Engine instances, Cloud SQL instances are available in multiple sizes: D0, D1, D2, D4, D8, D16, and D32. The instance tiers mainly differ in RAM, ranging from 0.125 gigabytes for a D0 to 16 gigabytes for a D32, and the maximum number of concurrent connections, from 250 to 4,000.
Storage is billed per gigabyte used per month, and you only pay for the disk space used by MySQL over time. This includes storage for system tables and logs, as well as your app’s table schemas and rows. You don’t need to specify (or pay for) a maximum size. An instance of any tier can grow its data up to 250 gigabytes by default, and you can raise this limit to 500 gigabytes by purchasing a Google Cloud support package at the “silver” level.
There are two billing plans for Cloud SQL: per use billing, and package billing. With per use billing, you are billed for each hour an instance runs, with a minimum of one hour each time you enable an instance, rounded to the nearest hour. You can start and stop the instance as needed, and the database persists when the instance is stopped, with storage billed at the usual storage rate. This is useful for single user sessions or applications that only need the database during a fixed window of time, where the instance does not need to run continuously.
If you intend to keep the instance running over a sustained period of multiple days, such as to keep it available for a web application, you can claim a substantial discount by selecting package billing. With package billing, you are billed for each day that the instance exists. You can keep the instance running continuously for no additional charge. The package price also includes an amount of storage and I/O, and you aren’t charged for these resources until usage exceeds the bundled amount.
As with App Engine instances, the rate differs by instance tier, with D0 being the least expensive. You can change an instance’s tier at any time. Doing so results in only a few seconds of downtime.
As usual, prices change frequently enough that we won’t bother to list specific numbers here. See the official documentation for the latest pricing information.
TIP
When you first sign up for Cloud SQL, or other Cloud Platform services such as Compute Engine, you may be eligible for a free trial of the Platform. The free trial gives you a starting budget of free resources so you can try out Cloud SQL without charge. This is a one-time trial budget for you to spend on these services. (This is different from App Engine’s free quotas, which refresh daily.) Go to the Cloud Platform website and look for the Free Trial button to get started.
Installing MySQL Locally
While developing and maintaining an app that uses Cloud SQL, you will use a MySQL administrative client to connect to the Cloud SQL instance from your local machine. In addition, you will probably want to run a MySQL server locally to test your application without connecting to Cloud SQL. You can download and install MySQL Community Edition from Oracle’s website to get both the client and the server tools for Windows, Mac OS X, or Linux. MySQL Community Edition is free.
To download MySQL, visit the MySQL Community Downloads page. Be sure to get the version of MySQL that matches Cloud SQL. Currently, this is version 5.5.
Linux users can install MySQL using apt-get like so:
sudo apt-get install mysql-server mysql-client
Once you have MySQL server installed, the server is running locally and can accept local connections. If the installation process did not prompt you to set a password for the root account, set one using the mysqladmin command, where new-password is the password you wish to set:
mysqladmin -u root password "new-password"
Try connecting to the server using the mysql command:
mysql -u root -p
Enter the password when prompted. When successful, you see a mysql> prompt. You can enter SQL statements at this prompt to create and modify databases. The root account has full access, and can also create new accounts and grant privileges.
See the MySQL documentation for more information about setting up the local server.
TIP
While you’re at the MySQL website, you might also want to get MySQL Workbench, a visual client for designing and managing databases. You can download MySQL Workbench Community Edition for free.
Installing the MySQLdb Library
App Engine’s Python runtime environment supports the MySQLdb library, an implementation of the Python DB API for MySQL, for connecting to Cloud SQL instances. You can request this library from the runtime environment using the libraries: section in your app.yaml file:
libraries:
- name: MySQLdb
version: latest
This library is not distributed with the App Engine SDK, so you must install it locally for development and testing. This can a bit of a challenge: MySQLdb uses extensions written in C to interface with the official MySQL libraries, and so building MySQLdb from its original sources requires that you have a C compiler and appropriate libraries installed. You can download prebuilt versions of MySQLdb from unofficial repositories, as long as you’re careful to get the correct version.
For Windows, visit this website to get an unofficial build of MySQLdb for Python 2.7, available in 32-bit and 64-bit varieties.
For Linux, you can install the prebuilt package for your distribution. For example, on Ubuntu and others, this command installs the correct library and its dependencies:
sudo apt-get install python-mysqldb
For Mac OS X, one way to install MySQLdb is with pip. You must have Apple’s Xcode tools installed so that pip can use the C compiler. Xcode is a free download from the Mac App Store. The pip command is as follows:
sudo pip install MySQL-python
With the library installed via pip, you must also add the MySQL client library to the dynamic library load path. Put this in your .bashrc file (if you use bash):
export DYLD_LIBRARY_PATH=/usr/local/mysql/lib:$DYLD_LIBRARY_PATH
As an alternative on Mac OS X, you can use a package manager such as MacPorts. MacPorts builds the software from sources, and manages dependencies automatically. You can install MacPorts using the instructions on the MacPorts website.
With MacPorts installed, use the port install command to install the py-mysql package:
sudo port install py-mysql
Using MacPorts requires that you use the MacPorts version of the Python 2.7 interpreter. You can find this at /opt/local/bin/python2.7. Be sure to use this interpreter when running dev_appserver.py:
/opt/local/bin/python2.7 ~/google-cloud-sdk/bin/dev_appserver.py myproject
On all platforms, you can verify that MySQLdb is installed by importing it from the Python prompt:
% python
Python 2.7.9 (default, Dec 13 2014, 15:13:49)
[GCC 4.2.1 Compatible Apple LLVM 6.0 (clang-600.0.56)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>>
For more information, see the MySQLdb v1 Github repository and an article about installation by the author.
Creating a Cloud SQL Instance
Open a browser and visit the Cloud Console. Create a new project, or select an existing project, in the usual way. In the sidebar navigation, expand Storage, then select Cloud SQL. If this is your first Cloud SQL instance, you see a “Create an instance” button. Click it.
The form for creating a new Cloud SQL instance appears short and simple, asking for an instance ID, region, and tier. Click “Show advanced options…” to see the full list of capabilities. A portion of the full screen is shown in Figure 11-1.
The instance ID is a unique name for the Cloud SQL instance. It always begins with the project ID that “owns” the instance, followed by a colon and the name that you specify.
The region is the major geographical region where the instance lives. When using Cloud SQL with App Engine, this must be set to United States. In the future, when App Engine is available in other regions, the SQL instance and the App Engine app must be hosted in the same region.
The tier is the tier for this instance. The database version selects a version of MySQL to use; version 5.5 is fine. Below that, you can select the billing plan for this instance, either the per use plan or the package plan. Adjusting the tier changes the pricing information displayed in this section.
Figure 11-1. The “Create Cloud SQL instance” form with advanced options shown (excerpt)
The “Preferred location” is more specific than the region. For an App Engine app, you want to set this to Follow App Engine App to minimize the latency between the app and the database. Set the App Engine app ID (the project ID) to tell it to follow this app. You can adjust this if you have more than one distinct app using the same database instance, and you want the database to follow a specific app that isn’t in this project.
Cloud SQL can do automatic daily backups of the database. This increases storage costs, but is likely to be less expensive than backing up to an external repository (which consumes outgoing bandwidth). If you want backups, make sure “Enable backups” is checked, and adjust the time range if needed. You can optionally store a MySQL binary log in addition to backups. The binary log contains every change event to the database, which is useful for restoring changes that were made since the last backup.
The “Activation policy” determines how the instance responds to usage. If you set this to On Demand, it will attempt to limit the instance running time by activating the instance for traffic then shutting it down after a few minutes without a connection. You can pair this with a per use billing plan and allow Cloud SQL to activate the instance as needed. The “Always On” option activates the instance as soon as you create it, and leaves it running even when idle. You might as well choose this option if you’ve selected package billing and expect the database to be used at least once a day. The “Never” option leaves the instance disabled, and only activates it in response to administrative commands.
If you intend to connect to this database with an external client and that client’s network does not support IPv6, select “Assign an IPv4 address to my Cloud SQL instance.” With an IPv4 address assigned, you are charged a small amount of money every hour the instance is idle.
Advanced options let you adjust the filesystem replication method, authorize specific IP ranges for clients, and set MySQL server flags for the instance. We’ll adjust the external authorization settings in the next section.
Set the options as you like them, then click Save. When the instance has been created, it appears with a status of either “Runnable” or “Running,” depending on how you set the activation policy.
Connecting to an Instance from Your Computer
Your new instance is ready to accept connections from your App Engine app. In theory, you could use code deployed to App Engine to connect to the database and create tables. More likely, you’ll want to do this with an administrative client or another tool running on your local computer. To connect to an instance using something other than App Engine, you must configure the instance to allow incoming connections from a specific IP address or address range.
Before doing anything else, set a root password for the instance. In the Cloud Console, locate and select the Cloud SQL instance. Select the Access Control tab. Under Set Root Password, enter a password, then click Set. You will use this password when connecting with the root account.
Next, you need to authorize incoming connections from your computer to the instance. You can configure the instance to accept connections from specific IP addresses or network ranges, using either IPv6 addresses or IPv4 addresses. You can only connect to the instance via its IPv6 address if your network has assigned a public IPv6 address to your computer. If your computer does not have an IPv6 address, you must request an IPv4 address for the instance, then authorize your computer’s IPv4 address to connect to it. There is an hourly charge to reserve an IPv4 address, but only if you leave it idle.
The easiest way to determine whether your computer has an IPv6 address is to visit the What’s My Web IP website using your browser. If this displays an IPv6 address, such as fba2:2c26:f4e4:8000:abcd:1234:b5ef:7051, then your computer connected to the site with an IPv6 address. You can confirm that this is your address using your computer’s Network settings panel, or by running the ipconfig command on Windows or the ifconfig command on Mac OS X or Linux.
If this displays an IPv4 address, such as 216.123.55.120, then your computer connected to the site with an IPv4 address. Even if your network assigned an IPv6 address to your computer, that address is only used locally and won’t be used to make the final connection to the Cloud SQL instance. You must request an IPv4 address for the instance in order to connect to it from your computer’s network.
If you need an IPv4 address, click Request an IP Address. An address is assigned to the instance and displayed.
To authorize your network to connect to the instance, click the Add Authorized Network button. Copy and paste your computer’s address into the form field, then click Add.
Finally, use the mysql command to connect to the instance. You can find the instance’s IPv6 address on the Overview tab, or use the IPv4 address you requested. Specify the address as the --host=… parameter:
# Using the IPv6 address of the instance:
mysql --host=2001:4860:4864:1:9e4a:e5a2:abcd:ef01 --user=root --password
# Or with an IPv4 address:
mysql --host=173.194.225.123 --user=root --password
When using an IPv6 address, you may get a message such as this:
ERROR 2013 (HY000): Lost connection to MySQL server at
'reading initial communication packet', system error: 22
If you do, try authorizing a wider portion of your network’s address space. For example, if your computer’s address is fba2:2c26:f4e4:8000:abcd:1234:b5ef:7051, take the first four groups, then add ::/64, like this: fba2:2c26:f4e4:8000::/64. Authorize that network, then try the mysql command again with the instance’s IPv6 address.
A successful connection results in a mysql> prompt. You can enter SQL commands at this prompt. For example, the show databases; command lists the databases currently on the instance. MySQL always starts with several databases it uses for maintenance and configuration:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.07 sec)
Type quit to terminate the connection and close the client.
If you requested an IPv4 address and do not intend to connect remotely on a regular basis, you can remove the IP address to save on costs. Click Remove next to the IP address in the Access Control tab. Removing the address abandons it, and you may not get the same address the next time you request one.
If your network assigns IP addresses dynamically or you change networks, you may need to authorize a new address the next time you connect.
TIP
Cloud SQL supports secure connections over SSL. You can set up SSL certificates from the Access Control tab for the instance. This is only needed if you want external clients to connect with SSL. Traffic between App Engine and Cloud SQL is always secure.
Setting Up a Database
Your Cloud SQL instance begins life with MySQL’s initial databases, but no database for your app to use. The next step is to create a database for the app, then create one or more accounts with passwords and appropriate privileges to access the new database.
We’ll continue using the mysql command-line client, but you might also use MySQL Workbench or some other tool to create the database and users. Remember that you will need to replicate these steps for your local server as well as the Cloud SQL instance, and possibly create more than one database with the same tables so you can use one for testing and another for the live app. Some web application frameworks have features to automate and replicate databases and tables for this purpose.
Set up a new connection with the mysql command as in the previous section, repeating the network authorization step if necessary. Use the root account and password. At the mysql> prompt, create a new database:
CREATE DATABASE mmorpg;
(As is traditional with SQL, we will capitalize SQL keywords, but you can type these in lowercase. Names and values are case sensitive. And don’t forget the semicolon.)
You must enter another command to tell the MySQL client to use the new database for subsequent commands in this session. For subsequent sessions, you can give the database name as an argument to the mysql command, or just type this as the first SQL command in the session:
USE mmorpg;
The root account you are currently using has maximum privileges across the Cloud SQL instance, including the powers to create and delete entire databases. It’s a good practice to use a separate account with limited privileges when connecting from your app. If a coding error in the app accidentally allows an attacker to run arbitrary SQL commands (a SQL injection attack), you can limit the damage to just the privileges granted to the app’s account. Most apps need permission to DELETE rows from a table, but few apps need to be able to DROP databases, GRANT privileges to other accounts, or SHUTDOWN the server.
Some web application frameworks automate the management of tables, and need an account with wider privileges to create and delete tables than would normally be used by the app while handling user requests. If your framework allows it, you can create a separate account specifically for these management tools, and use a more limited account within the app itself.
To create a user, enter the CREATE USER command. The following command creates a user named app with the password p4$$w0rd:
CREATE USER 'app' IDENTIFIED BY 'p4$$w0rd';
You can change the password for this account later (as root) with the SET PASSWORD command:
SET PASSWORD FOR 'app' = PASSWORD('new-p4$$w0rd');
App Engine does not need a password to connect to a Cloud SQL instance, even when the account has a password set. You only need the password for connecting to the Cloud SQL instance from outside of App Engine. Your app code does need a password when connecting to your local development database if the local account has a password.
A new account starts with no privileges. To grant the app account the ability to SELECT, INSERT, UPDATE, and DELETE rows in all tables in the mmorpg database:
GRANT SELECT, INSERT, UPDATE, DELETE ON mmorpg.* TO 'app';
For accounts that need to create and drop tables, the CREATE, DROP, and ALTER privileges are also needed. GRANT ALL ON mmorpg.* will give the account complete access to the database. See the documentation on the GRANT statement for more information about privileges.
We won’t go into detail about how to create tables here. For now, here is a simple example of creating a table with a few columns in the mmorpg database:
CREATE TABLE guild (id VARCHAR(20) PRIMARY KEY, title VARCHAR(50),
created_date DATETIME, min_level INT);
You can test the app account by disconnecting (type quit) then reconnecting using the app username and password:
mysql --host=... --user=app -p
Use the mmorpg database, then list the tables:
USE mmorpg;
SHOW TABLES;
The list of tables includes the guild table we created:
mysql> show tables;
+------------------+
| Tables_in_mmorpg |
+------------------+
| guild |
+------------------+
1 row in set (0.07 sec)
Still using the app account, try inserting a row:
INSERT INTO guild VALUES ('superawesomes', 'The Super Awesomes', NOW(), 7);
Select the rows of the table to see the newly added row:
SELECT * FROM guild;
Connecting to the Database from App Engine
It’s time to try connecting to the database from App Engine code. With Python and MySQLdb, you use the Cloud SQL instance as you would any other database. The only difference is the initial connection: App Engine provides a named socket based on the name you provided when you created the instance. You use this name as part of an argument to the MySQLdb.connect() function.
Unlike the App Engine services and Cloud Datastore, the App Engine development server does not attempt to emulate the presence of Cloud SQL on your local computer. You must add code to your app that detects whether it is running in the development server and react accordingly. It’s up to you whether you want the development server to connect to your local MySQL database, to a Cloud SQL instance or database reserved for testing, or to the Cloud SQL instance and database used by the live app. It’s a good idea to use some kind of test database that is separate from the live database for your regular development, regardless of whether it lives on a separate Cloud SQL instance or your local machine.
What follows is a simple example that reads and displays the guild table, and prompts the user to add a row. We’ll use a minimum of features to highlight the MySQLdb connection code. In a real app, you might use a framework to manage the SQL statements and form handling.
A file named main.py contains all of the code for connecting to the database, reading and displaying all rows in the table, and inserting a new row based on submitted form data:
import jinja2
import logging
import MySQLdb
import os
import re
import webapp2
INSTANCE_NAME = 'saucy-boomerang-123:mydb'
DATABASE = 'mmorpg'
DB_USER = 'app'
DB_PASSWORD = 'p4$$w0rd'
template_env = jinja2.Environment(
loader=jinja2.FileSystemLoader(os.getcwd()))
def get_db():
if os.environ.get('SERVER_SOFTWARE', '').startswith('Development'):
# This is a development server.
db = MySQLdb.connect(host='127.0.0.1', port=3306,
db=DATABASE, user=DB_USER, passwd=DB_PASSWORD)
else:
# This is on App Engine. A password is not needed.
db = MySQLdb.connect(unix_socket='/cloudsql/' + INSTANCE_NAME,
db=DATABASE, user=DB_USER)
return db
class MainPage(webapp2.RequestHandler):
def get(self):
guilds = []
db = get_db()
try:
cursor = db.cursor()
cursor.execute('SELECT id, title, created_date, '
'min_level FROM guild;')
for (id, title, created_date, min_level) incursor.fetchall():
guilds.append({
'id': id,
'title': title,
'created_date': created_date,
'min_level': min_level
})
finally:
db.close()
template = template_env.get_template('home.html')
context = {
'guilds': guilds,
}
self.response.out.write(template.render(context))
def post(self):
title = self.request.get('title').strip()
min_level_str = self.request.get('min_level').strip()
db = get_db()
try:
assert len(title) > 0
min_level = int(min_level_str)
key = re.sub(r'\W', '_', title.lower())
cursor = db.cursor()
cursor.execute('INSERT INTO guild VALUES (%s, %s, NOW(), %s);',
(key, title, min_level))
db.commit()
except (AssertionError, ValueError), e:
logging.info('Invalid value from user: title=%r min_level=%r',
title, min_level_str)
finally:
db.close()
self.redirect('/')
app = webapp2.WSGIApplication([('/', MainPage)], debug=True)
The file home.html contains the Jinja2 template for the list and form:
<!doctype html>
<html>
<head>
<title>Guild List</title>
</head>
<body>
{% if guilds %}
<p>Guilds:</p>
<ul>
{% for guild in guilds %}
<li>
{{ guild.title }},
minimum level: {{ guild.min_level }},
created {{ guild.created_date }}
</li>
{% endfor %}
</ul>
{% else %}
<p>There are no guilds.</p>
{% endif %}
<p>Create a guild:</p>
<form action="/" method="post">
<label for="title">Title:</label>
<input type="text" id="title" name="title" /><br />
<label for="min_level">Minimum level:</label>
<input type="text" id="min_level" name="min_level" /><br />
<input type="submit" name="Create Guild" />
</form>
</body>
</html>
The app.yaml for the app routes all requests to the app:
application: myapp
version: 1
runtime: python27
api_version: 1
threadsafe: true
handlers:
- url: .*
script: main.app
libraries:
- name: MySQLdb
version: latest
- name: jinja2
version: "2.6"
- name: markupsafe
version: "0.15"
In this example, we define the function get_db() to prepare the database connection based on whether the app is running in a development server or on App Engine. We test for this using the SERVER_SOFTWARE environment variable, which is set by both the development server and App Engine:
if os.environ.get('SERVER_SOFTWARE', '').startswith('Development'):
# This is a development server.
# ...
else:
# This is on App Engine.
# ...
The MySQLdb.connect() function makes the connection. To connect to the local MySQL instance from the development server, we simply provide the localhost address 127.0.0.1, the appropriate port number (3306 by default), the name of the database, and the user and password we created earlier:
db = MySQLdb.connect(host='127.0.0.1', port=3306,
db=DATABASE, user=DB_USER, passwd=DB_PASSWORD)
When running on App Engine, we connect to the Cloud SQL instance. Instead of a host and port, we provide the path to a Unix socket that App Engine uses to locate the instance. The socket path is /cloudsql/ followed by the name of the instance, including the project ID and a colon. For example, if the project ID is saucy-boomerang-123 and we created an instance named mydb, the complete socket path is /cloudsql/saucy-boomerang-123:mydb.
Provide this path to the MySQLdb.connect() function as the unix_socket argument. Do not provide a password in this case: doing so will convince the library to try to connect to localhost, and you’ll get a permissions error in your application logs. App Engine does not need a password when connecting to the instance, as it is specially authorized to do so. You still need to provide a username, which determines the database privileges for the app:
db = MySQLdb.connect(unix_socket='/cloudsql/' + INSTANCE_NAME,
db=DATABASE, user=DB_USER)
When you are done with the connection, be sure to call db.close() to free it:
try:
# ...
finally:
db.close()
You prepare a “cursor” object by calling the db.cursor() function, then execute a SQL statement by passing it to the cursor.execute() method. For statements that return results, you can fetch the results with a method such as cursor.fetchall():
cursor = db.cursor()
cursor.execute('SELECT id, title, created_date, '
'min_level FROM guild;')
for (id, title, created_date, min_level) incursor.fetchall():
# ...
When using values derived from an external source, such as data submitted by the user via a form, use parameter substitution: use a formatting placeholder such as %s in the SQL string for each value, then provide a tuple of values as the second argument to cursor.execute():
cursor.execute('INSERT INTO guild VALUES (%s, %s, NOW(), %s);',
(key, title, min_level))
Do not use a Python string formatting operator here! The value tuple must be the second argument to cursor.execute() for parameter substitution to be effective.
Parameter substitution prevents the data from being misconstrued as SQL statement syntax. This is especially important because it prevents SQL injection attacks from malicious users. (See “xkcd: Exploits of a Mom,” by Randall Munroe.)
The MySQLdb calling code conforms to the Python DB standard. For more information about this API, see PEP 249: Python Database API Specification v2.0.
Backup and Restore
If you opted for backups when you created the instance, Cloud SQL performs regular backups of your database automatically. You can enable and disable backups at a later time from the Cloud Console by editing the instance configuration. You can also adjust the time of day during which backups occur.
To restore from a recent backup via the Cloud Console, select your Cloud SQL instance, then scroll down to Backups. Recent backups are listed by timestamp. Find the backup you want to restore, then click Restore.
Backups are intended for convenient and automatic recovery of the Cloud SQL instance. If you want to copy data out of Cloud Platform for archival storage or offline processing, you must export the data. (We’ll cover this in the next section.)
Exporting and Importing Data
The Cloud Console provides a convenient way to import and export data from your database. The format for this data is a text file containing SQL statements, equivalent to using the mysqldump command.
Exports and imports use Google Cloud Storage for the data file. This can save you the cost of external bandwidth if you intend to manipulate the data file further using Google Cloud Platform, such as with a batch job running on App Engine or Compute Engine.
Each Cloud Storage object has a name (like a filename) and belongs to a bucket (like a directory). The path to a Cloud Storage object consists of gs://, the bucket name, a slash, and the object name:
gs://bucket-name/object-name
Bucket names must be unique across all of Cloud Storage, much like usernames. Cloud Storage reserves bucket names that look like domain names (containing dots) for the owners of the domains, so if you have verified ownership of your domain using Google Webmaster Tools, you can use your domain name as your bucket name. Otherwise, you can register any bucket name that hasn’t already been registered by someone else.
You must create a Cloud Storage bucket before you can export Cloud SQL data. To create the bucket, go to the Cloud Console, then navigate to Storage, Cloud Storage, “Storage browser.” Click “Create bucket” (or “Add bucket”), then enter a name for the bucket and click the Create button.
To export one or more databases from Cloud Console, navigate to the Cloud SQL instance, then click the Export… button. In the dialog, enter a Cloud Storage path using the bucket you just created, followed by a filename. A file of that name must not already exist in the bucket. Optionally, click “Show advanced options…” then enter the names of the databases to export. By default, all databases are exported. Click OK. The export takes a few moments to complete.
To access the exported data, return to the Storage browser, then select the bucket. The bucket contains the exported file you requested, as well as a log file. You can select these files in the Cloud Console to download them via your browser.
Importing data via the Cloud Console is similar. Go to the Storage browser, select the bucket, then click the “Upload files” button. Follow the prompts to select the data file on your local computer and upload it to the bucket. Next, navigate to the Cloud SQL instance, then click Import…. Enter the Cloud Storage path to the file you uploaded, then click OK. The import process reads the data file and executes the SQL statements inside it.
The file generated by an export includes SQL statements to drop tables before re-creating them. Importing such a file effectively resets the tables to the state they were in at the time of export. It does not merge old data with new, nor does it result in duplicate rows.
TIP
Because exported data is a file of SQL statements, you can save space and bandwidth by compressing the file. To export data compressed using the gzip file format, specify a file path that ends in .gz. To import a gzip-compressed file, make sure the path ends in .gz.
The gcloud sql Commands
All of the administrative features for Cloud SQL instances that you see in the Cloud Console are also available from the command line. The gcloud sql family of commands can create, delete, restart, and clone instances, as well as import and export data. These commands are an alternative to the Cloud Console for some interactive actions, and can also be used in scripts for automation.
Make sure gcloud is configured to use your project, you are signed in, and you have the sql component installed:
gcloud config set project project-id
gcloud auth login
gcloud components update sql
To list the current Cloud SQL instances for the project:
gcloud sql instances list
To get detailed information about the instance in YAML format:
gcloud sql instances describe instance-name
To get a list of automatic backups for the instance, each identified by a timestamp:
gcloud sql backups list --instance instance-name
To restore a backup from this list:
gcloud sql instances restore-backup --instance instance-name --due-time timestamp
You can initiate exports and imports from the command line as well. As with doing this from the Cloud Console, exports and imports use Cloud Storage for storing or reading data. To initiate an export of all databases on the instance to a given Cloud Storage path:
gcloud sql instances export instance-name gs://bucket-name/object-name
You can narrow the export to specific databases or tables using the --database and --table flags.
The command to import a file from Cloud Storage is similar:
gcloud sql instances import instance-name gs://bucket-name/object-name
Naturally, you can upload and download files to and from Cloud Storage from the command line as well. For this, you use the gsutil command. Make sure you have this command installed:
gcloud components update gsutil
To download a file from Cloud Storage:
gsutil cp gs://bucket-name/object-name .
Just like the cp command, gsutil cp takes a path to the file to copy, and a destination path. If the destination path is a directory path (like . for the current directory), the file is copied using its original filename.
To upload a file to Cloud Storage, use the gsutil cp command with a gs:// path as the second argument:
gsutil cp filename.gz gs://bucket-name/object-name
The Cloud SDK command-line interface has many features for fetching SQL status and configuration as structured data, as well as for waiting for initiating operations asynchronously and waiting for operations to complete. You can use the gcloud help command to browse information on these features. For example, to learn about creating Cloud SQL instances from the command line:
gcloud help sql instances create