INTRODUCTION
In this article, you set up a Cloud SQL server and discover how to use a proxy to connect an application to it over an external connection. You can also set up a connection using a private IP link, improving security and performance. Although we used WordPress as the example application in this lab, the knowledge and best practices may be applied to any application that requires SQL Server.
By the end of this experiment, you will have two functional Wordpress frontend instances connected to their SQL instance backend using two distinct connection types, as illustrated in this diagram:
ARCHITECTURAL DIAGRAM
PROCEDURE
Task 1. Create a Cloud SQL database
Configure a SQL server according to Google Cloud best practices and create a Private IP connection.
1.On the Navigation menu, click SQL.
2.Click Create instance.
3.Click Choose MySQL.
4.Specify and leave the remaining settings as their defaults
Note: Note the root password; it will be used in a later step and referred to as [ROOT_PASSWORD].
5.Expand Show configuration options.
6.Expand the Machine configuartion section.
7.Provision the right amount of vCPU and memory. To choose a Machine configuration, click the dropdown menu, and then explore your options.
8.For this lab, select Dedicated core from the dropdown menu, and then select 1 vCPU, 3.75 GB.
9.Next, expand the Storage section and then choose Storage type and Storage capacity.
10.Click each of the capacity options to see how it affects the throughput. Reset the option to 10GB.
11.Expand the Connections section.
12.Select Private IP.
13.In the Network dropdown, select default.
14.Click the Set up Connection button that appears.
15.In the panel to the right, click Enable API, click Use an automatically allocated IP range, click Continue, and then click Create Connection.
16.Click Create Instance at the bottom of the page to create the database instance.
Task 2. Configure a proxy on a virtual machine
1.On the Navigation menu click Compute Engine.
2.Click SSH next to wordpress-proxy.
3.Download the Cloud SQL Proxy and make it executable:
wget https://dl.google.com/cloudsql/cloud_sql_proxy.linux.amd64 -O cloud_sql_proxy && chmod +x cloud_sql_proxy
In order to start the proxy, you need the connection name of the Cloud SQL instance. Keep your SSH window open and return to the Cloud Console.
4.On the Navigation menu, click SQL.
5.Click on the wordpress-db instance and wait for a green checkmark next to its name, which indicates that it is operational (this could take a couple of minutes).
6.Note the connection name it will be used later and referred to as [SQL_CONNECTION_NAME].
7.In addition, for the application to work, you need to create a table. Click Databases.
8.Click Create database, type wordpress, which is the name the application expects, and then click Create.
9.Return to the SSH window and save the connection name in an environment variable, replacing [SQL_CONNECTION_NAME] with the unique name you copied in a previous step:
export SQL_CONNECTION=[SQL_CONNECTION_NAME]
10.To verify that the environment variable is set, run:
echo $SQL_CONNECTION
The connection name should be printed out.
11.To activate the proxy connection to your Cloud SQL database and send the process to the background, run the following command:
./cloud_sql_proxy -instances=$SQL_CONNECTION=tcp:3306 &
The expected output is:
Listening on 127.0.0.1:3306 for [SQL_CONNECTION_NAME]
Ready for new connections
12.Press ENTER.
Task 3. Connect an application to the Cloud SQL instance
Connect a sample application to the Cloud SQL instance.
1.Configure the Wordpress application. To find the external IP address of your virtual machine, query its metadata:
curl -H "Metadata-Flavor: Google" http://169.254.169.254/computeMetadata/v1/instance/network-interfaces/0/access-configs/0/external-ip && echo
2.Go to the wordpress-proxy external IP address in your browser and configure the Wordpress application.
3.Click Let's Go.
4.Specify and replacing [ROOT_PASSWORD] with the password you configured upon machine creation, and leave the remaining settings as their defaults
5.Click Submit.
6.When a connection has been made, click Run the installation to instantiate Wordpress and its database in your Cloud SQL. This might take a few moments to complete.
7.Populate your demo site's information with random information and click Install Wordpress. You won't have to remember or use these details.
8.When a 'Success!' window appears, remove the text after the IP address in your web browser's address bar and press ENTER.
You'll be presented with a working Wordpress Blog!
Task 4. Connect to Cloud SQL via internal IP
1.In the Cloud Console, on the Navigation menu, click SQL.
2.Click wordpress-db.
3.Note the Private IP address of the Cloud SQL server; it will be referred to as [SQL_PRIVATE_IP].
4.On the Navigation menu, click Compute Engine.
5.Copy the external IP address of wordpress-private-ip, paste it in a browser window, and press ENTER.
6.Click Let's Go.
7.Specify and leave the remaining settings as their defaults
8.Click Submit.
9.Click Run the installation.
An 'Already Installed!' window is displayed, which means that your application is connected to the Cloud SQL server over private IP.
10.In your web browser's address bar, remove the text after the IP address and press ENTER.
You'll be presented with a working Wordpress Blog!
CONCLUSION
We have successfully created a Cloud SQL database and configured it with a secure proxy and Private IP for better security and performance. Private IP works only if the application and Cloud SQL server share the same region and VPC. For different regions, VPCs, or projects, use a proxy for a secure external connection.
Top comments (0)