How to create a GCP Sql Instance using your command line

  1. Cloud SDK installed
  2. Admin access on you machine
  3. Google Cloud Platform Account
  4. Internet
  1. Create the sql instance.
  2. Create your database instance.
  3. Create the user.
  4. Create a GCP compute instance and authorize it to connect to the Sql instance.
  5. Connect to the Sql instance.
  6. Setup the google cloud storage location and update the compute instance to host this image.
gcloud sql instances create blog-db --password="admin" --sql-version=MYSQL_5_7" --zone=us-central1-a
  • Second Generation (recommended)
    ○ Up to 7x throughput and 20x storage capacity of First Generation
    ○ Up to 208 GB of RAM and 10 TB of data storage
    ○ MySQL 5.6 or 5.7
    ○ InnoDB only
  • First Generation
    ○ Up to 16 GB of RAM and 500 GB of data storage
    ○ MySQL 5.5
    ○ IPv6 connectivity and On Demand activation policy
  • postgres 9.6
  • postgres 11
  • SQL Server 2017 Standard
  • SQL Server 2017 Enterprise
  • SQL Server 2017 Express
  • SQL Server 2017 Web
gcloud sql databases create host-db --instance="blog-db"
gcloud sql users create blogdbuser --instance=blog-db -i blog-db --host='%' --password='password'
  • The above commands and their arguments are self explanatory but for the “%” that refers to the host ip address that this user can interact with via the “help” documents of gcloud: “ Cloud SQL user’s host name expressed as a specific IP address or address range. % denotes an unrestricted host name. Applicable flag for MySQL instances; ignored for PostgreSQL instances. Note, if you connect to your instance using IP addresses, you must add your client IP address as an Authorized Address, even if your host name is
    unrestricted. For help on how to do so, read:
    https://cloud.google.com/sql/docs/mysql/configure-ip
  • Create a file of type “.sh”, in linux the extension don’t really matter. They just tell us what the file does. Call the file anything you like I will call mine “apache-install.sh”, and add these lines to it
#! /bin/bash
apt-get update
apt-get install apache2 php php-mysql -y
service apache2 start
  • Create for Linux users:
gcloud compute instances create hostvm --machine-type=g1-small --zone us-central1-a --tags="http-server" --metadata-from-file='/path/to/apache-install.sh'
  • Create for Windows user:
gcloud compute instances create hostvm --machine-type=g1-small --zone us-central1-a --tags="http-server" --metadata-from-file="C:\Path\to\apache-install.sh"
  • Authorize, here we get the IP address and then authorize:
gcloud compute instances list
gcloud sql instances patch blog-db --authorized-networks="34.66.179.46/32"
  • SSH into the compute instance:
gcloud compute ssh hostvm --zone us-central1-a
  • Change into the folder of interest:
cd /var/www/html
  • Edit the file using “nano” test editor:
sudo nano index.php
  • Use the below command to get the IP address of the Sql instance and use that in the variable “$dbserver”:
gcloud sql instances list
  • Paste the lines below into this file:
<html>
<head><title></title></head>
<body>
<?php
$dbuser = "blogdbuser";
$dbserver = "134.34.222.23";
$dbpassword = "password";
$conn = new mysqli($dbserver, $dbuser, $dbpassword);
if (mysqli_connect_error()){
echo ("Database connection error: " . mysqli_connect_error());
} else {
echo ("Database connection succeeded");
}
?></body></html>
  • Non-mac systems:
Ctrl + x
Shift + y
Enter
  • Mac systems:
Command + x
Shift + y
Enter
sudo service apache2 restart
  • Create the bucket
  • Upload an image
  • SSH into your gcp compute instance and modify the “index.php” file to host the image and and restart the server once more.
gsutil mb -l us gs://project_id
  • Windows users:
gsutil cp "C:\Path\to\image" gs://project_id
  • Linux users:
gsutil cp "/path/to/image" gs://project_id
  • Make the image publicly accessible:
gsutil acl ch -u AllUsers:R gs://project_id/*
<img src='https://storage.googleapis.com/<project_id>/<image_file_name>' />

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
george udosen

george udosen

DevOps | FullStack developer | Python::Flask | GCP Cloud Certified | AWS & AZURE Cloud Savy | Linux Sysadmin | Google IT Support