Chapter 4. Cloud SQL:
Managed Relational Storage
What is Cloud SQL?
Configuring a production-grade SQL instance
Deciding whether Cloud SQL is a good fit
Choosing between Cloud SQL and MySQL on a VM
SQL forms of structured data storage.
Relational database the idea that these databases store related data.
Allow you to combine it to ask complex questions.
“How old are this year’s top five highest paid employees?”.
Great general-purpose storage systems.
In Google Cloud, this is called Cloud SQL.
Various flavors of relational databases (such as MySQL or PostgreSQL).
Highlight the things that Cloud SQL does differently.
Cloud SQL is a fully-managed database service that helps you set up, maintain, manage, and administer your relational databases.
4.1. What’s Cloud SQL?
Cloud SQL is a VM(Virtual Machine) that’s hosted on Google Compute Engine, managed by Google, running a version of the MySQL binary.
Change all of those settings in the Cloud Console, Cloud SDK command-line tool OR the REST API.
Cloud SQL currently supports MySQL, PostgreSQL and SQL Server.
Integrating with Cloud SQL involves nothing more than changing the hostname in your configuration to point at a Cloud SQL instance.
Cloud SQL automates some of the more tedious tasks.
Upgrading to a newer version of MySQL
Running recurring backups,
Securing your Cloud SQL instance accepts connections you trust.
4.2. Interacting with Cloud SQL
Cloud Console and the Cloud SDK to turn on a Cloud SQL instance .
Store your To-Do List data in Cloud SQL and run a few example queries.
Be sure to pick a region that’s nearby, so your queries won’t be traveling around the world and back.
Cloud console select SQL
CREATE INSTANCE
MySQL
Select MySQL
Instance name is todo-list
Password set as uconnstamford
Choosing the lowest cost options
Enterprise
Production as environment as opposed to development
I
Region us-east1 (default that shows up)
Single Zone
Customize machine
Choose smallest machine possible
1 CPU - .614 gig
Storage HDD cheaper than SSD
Drive size 10 Gigabytes (lowest allowed)
Region is us-east1
Zonal availability set to Single Zone
Create Instance
I used the lowest possible machine power to reduce costs
Standard Machine 1 vCPU, 3.75 GB
Storage HDD
Storage capacity 10GB
Leave remaining settings alone
MySQL uses password authentication
Create new users.
Users may have different access rights to various parts of the database. E.g. Personnel dept can see salaries where marketing would not.
Cloud Console by clicking on the Cloud SQL instance and choosing the Users tab.
Next we want to OPEN CLOUD SHELL
In the Connect to this instance section.
Click on.
Open using Cloud Shell.
Note: The SQL that you create here is located on a virtual machine different then the cloud shell linux machine you build when you login to your cloud account initially.
A string will appear in your shell and all you need to do is hit return.
gcloud sql connect todo-list --user=root --quiet
Welcome to Cloud Shell! Type "help" to get started.
Your Cloud Platform project in this session is set to uconn-engr.
Use “gcloud config set project [PROJECT_ID]” to change to a different project.
john_iacovacci1@cloudshell:~ (uconn-engr)$ gcloud sql connect todolist --user=root --quiet
Once the instance is connected it will prompt you for the password.
Use the password entered when you created the SQL instance.
Note: the cursor should be a solid block and when entering the password no characters are displayed so enter it and hit return
Connecting to database with SQL user [root].Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 129
Server version: 5.7.32-google-log (Google)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
Database schema
Description
The database schema of a database is its structure described in a formal language supported by the database management system. The term "schema" refers to the organization of data as a blueprint of how the database is constructed.
You are now in the MySQL environment.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
Create a database for your app.
Step 1.
mysql> CREATE DATABASE todo;
Query OK, 1 row affected (0.00 sec)
CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database.
https://dev.mysql.com/doc/refman/5.7/en/create-database.html
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_option] ...
create_option: [DEFAULT] {
CHARACTER SET [=] charset_name
| COLLATE [=] collation_name
}
Create a table for your To-Do Lists.
Step 2.
mysql> use todo;
Database changed
https://dev.mysql.com/doc/refman/8.0/en/use.html
The USE statement tells MySQL to use the named database as the default (current) database for subsequent statements. This statement requires some privilege for the database or some object within it.
Syntax
USE db_name;
Step 3.
mysql>create table todolists (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL,PRIMARY KEY (id));
Query OK, 0 rows affected (0.02 sec)
CREATE TABLE creates a table with the given name. You must have the CREATE privilege for the table.
Field Attribute NOT NULL is being used because we do not want this field to be NULL. So, if a user will try to create a record with a NULL value, then MySQL will raise an error.
Field Attribute AUTO_INCREMENT tells MySQL to go ahead and add the next available number to the id field.
Keyword PRIMARY KEY is used to define a column as a primary key. You can use multiple columns separated by a comma to define a primary key.
create the example lists
Step 4.
mysql> INSERT INTO todolists (`name`) VALUES ("Groceries"), ("Christmas shopping"), ("Vacation plans");
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
INSERT inserts new rows into an existing table. The INSERT ... VALUES and INSERT ... SET forms of the statement insert rows based on explicitly specified values.
you can use a SELECT query to check if the lists are there, as follows.
Step 5.
mysql> SELECT * FROM todolists;
+----+--------------------+
| id | name |
+----+--------------------+
| 1 | Groceries |
| 2 | Christmas shopping |
| 3 | Vacation plans |
+----+--------------------+
3 rows in set (0.00 sec)
Lastly, do the same thing again, but this time for to-do items for each checklist
You can use the select() method to query for and return records from a table in a database.
Database changed
MySQL> CREATE TABLE todoitems (id INT(11) NOT NULL AUTO_INCREMENT, todolist_id INT(11) NOT NULL REFERENCES todolists(id), name varchar(255) NOT NULL, done BOOL NOT NULL DEFAULT '0', PRIMARY KEY (id));
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO todoitems (`todolist_id`,`name`, `done`) VALUES (1, "Milk", 0), (1, "Eggs", 0), (1, "Orange juice", 1), (1, "Egg salad", 0);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
'
MySQL> select * from todoitems;
+----+-------------+--------------+------+
| id | todolist_id | name | done |
+----+-------------+--------------+------+
| 1 | 1 | Milk | 0 |
| 2 | 1 | Eggs | 0 |
| 3 | 1 | Orange juice | 1 |
| 4 | 1 | Egg salad | 0 |
+----+-------------+--------------+------+
4 rows in set (0.00 sec)
Delete in the Cloud Console
4.3. Configuring Cloud SQL for production
Run Cloud SQL in a production-grade environment.
The environment that’s safe for you to run a business in.
reliable backups, failover procedures, and proper security practices.
4.3.2. Connecting over SSL
SSL stands for Secure Sockets Layer and, in short, it's the standard technology for keeping an internet connection secure and safeguarding any sensitive data that is being sent between two systems, preventing criminals from reading and modifying any information transferred, including potential personal details.
Transport Layer Security.
Description
Transport Layer Security, and its now-deprecated predecessor, Secure Sockets Layer, are cryptographic protocols designed to provide communications security over a computer network
SSL (Secure Sockets Layer) is nothing more than a standard way of sending data from point A to point B over an untrusted wire.
To get the server’s CA certificate, click the aptly named View Server CA Certificate button.
Cloud SQL supports connecting to an instance using the Transport Layer Security (SSL/TLS) protocol.
Cloud SQL uses a self-signed, per-instance server certificate and a certificate (public/private key pair).
Certificates work together to enable the server (instance) and client (application) to encrypt their communication.
Click Allow only SSL connections.
Certificate has an expiration date.
Cloud SQL provides a way to rotate your server certificate.
4.3.3. Maintenance windows
The need to upgrade software.
Like security patches or upgrades to newer versions.
Tell Google when it’s OK to do things like system upgrades, so your customers don’t notice the database disappearing or getting slower in the middle of the day.
Cloud SQL lets you set a specific day of the week and time of the day.
Google doesn’t know what your business is.
Maintenance Window
Picking a day of the week.
Pick a single-hour window
4.4. Scaling up (and down)
starting out on a small VM type (maybe a single-core VM) and then moving to a larger, more powerful VM later on.
two things go into determining the performance of your Cloud SQL instance:
Computing power (for example, the VM instance type)
Disk performance (for example, the size of the disk, because size and performance are tied)
4.4.1. Computing power
Cloud SQL instance details page
change the machine type
Save, you’ll have to restart your database.
4.4.2. Storage
.
Disk size and performance are tied together
A larger disk not only can store more bytes, it provides more IOPS to access those bytes.
IOPS (input/output operations per second) is the standard unit of measurement for the maximum number of reads and writes to non-contiguous storage locations.
By default, disks used as part of Cloud SQL have automatic growth enabled.
Edit Instance page, under the Configuration Options, you should see a section called Configure Machine Type and Storage
you can increase the size of your database, but you can’t decrease it.
4.5. Replication
Fundamental component to designing highly available systems is removing any single points of failure.
running without any service interruptions.
Cloud SQL makes it easy to implement the most basic forms of replication. It does so by providing two different push-button replica types: read replicas and failover replicas.
read replica is a clone of your Cloud SQL instance that follows the primary or master instance, pulling in any changes made to the master
A failover replica is similar to a read replica, except its primary job is to be ready as a replacement primary instance in case of some sort of disaster
4.6. Backup and restore
Cloud SQL does a solid job of making backups simple so that you don’t have to think about them until you need them.
4.6.1. Automated daily backups
The simplest, quickest, and probably most useful backup for Cloud SQL is the automatic one that occurs daily at a time you specify when you create the Cloud SQL instance.
Choose a backup window when creating your Cloud SQL instance
cloud SQL will snapshot all of your data to disk every day and keep a copy of that snapshot for seven days on a rolling window
Backup itself is a disk-level snapshot, which begins with a special user (cloudsqladmin) sending a FLUSH TABLES WITH READ LOCK query to your instance. This command tells MySQL to write all data to disk and prevents writes to your database while that’s happening. If a backup is in progress.
4.6.2. Manual data export to Cloud Storage
Cloud SQL provides a managed import and export of your data that relies on Google Cloud Storage to store the backup.
Instance details page for your Cloud SQL instance, and click the Export button at the top of the page.
Click the Browse button next to the field for the file path, and at the top of the new dialog that opens up
you can create a new location
Walk through how to restore it into your Cloud SQL instance
Import on the instance details page
Importing is nothing more than executing a set of SQL statements against your Cloud SQL instance and allowing you to use Cloud Storage as the source of the input.
4.7. Understanding pricing
Google Cloud considers two basic principles of pricing for computing resources: computing time and storage.
Slight markup on CPU time for managing the MySQL binary and configuration for you.
A small Cloud SQL instance would cost about 5¢ per hour, and the top-of-the-line, high-memory, 16-core, 104 GB memory machine would cost about $2 per hour.
The going price is the same as persistent SSD storage, which is 17¢ per GB per month.
concept of sustained-use discounts for computing resources
Running instances around the clock costs about 30% less than the sticker price.
Table 4.3. Different sizes of Cloud SQL instances and costs
4.8. When should I use Cloud SQL?
4.8.1. Structure
Most relational databases store highly structured data with a complete schema defined ahead of time that’s strictly enforced.
can prevent data corruption errors that happen when different people make different assumptions about how types are cast from one to the other.
database can optimize your data a bit more because it has more information about both the data that exists currently and the data that’ll be added later.
Cloud SQL scores high on this metric, so if your data is or can easily be fit to a schema, Cloud SQL is definitely a good option.
4.8.3. Durability
The general consensus is that relational storage systems (like MySQL) are capable of providing a high level of durability.
stores all the data on Persistent Disk, you benefit from the higher levels of durability and availability that Persistent Disk offers.
4.8.4. Speed (latency)
the latency of a query over your data is a function of the amount of data that your database needs to analyze to come up with your answer.
data grows, your queries may get slower and slower.
as queries start stacking up in your database, future queries will pile up on top of each other, effectively making a long line of people all asking for data and not getting answers.
have hundreds of gigabytes of data, you may want to consider different storage strategies
4.8.5. Throughput
The topic of performance, relational storage provides strong locking and consistency guarantees—the data is never stale—but with these guarantees come things like pessimistic locking, where the database tries to prevent lots of people from all writing at the same time, lowering the overall throughput for the database.
migrating to a different system as your data and concurrency requirements increase beyond what’s reasonably possible with something like MySQL.
4.9. Cost
Cloud SQL’s costs also are on the same level as running any database yourself on Compute Engine (such as your own MySQL instance)
Cloud SQL comes in very low on the cost scale for data sets that are suitable for a MySQL database. F
4.9.1. Overall
Table 4.5. To-Do List application storage needs
Summary
Relational databases are great for storing data that relates to other data using foreign key references, such as a customer database.
Cloud SQL is MySQL in a box that runs on top of a Compute Engine.
When choosing your storage capacity, don’t forget that size is directly related to performance. It’s OK (and expected) to have lots of empty space.
When you have enough Cloud SQL instances to justify hiring a DBA, it might make sense to manage MySQL yourself on Compute Engine instances.
Always configure Cloud SQL to encrypt traffic using an SSL certificate to avoid eavesdropping on the internet.
Don’t worry if you chose too slow of a VM. You can always change the computing power later. You also can increase the storage space, but it’s more work to decrease it if you overshoot.
Use failover replicas if you want your system to be up even when a zone goes down.
Enable daily backups if you want to be sure to never lose data.
No comments:
Post a Comment