Automate Posting Hugo Blog to Social Sites (with a db) Part 3

Jul 12, 2024

Automate Blog with GCP MYSQL Server

So I am just going to create my own CMS. I know there are other solutions.. but I am nearly there.. I also want to manage content like i manage a linux system.. So I am going to do this with a db.. bashscripts.. and linux servers.

Create the MYSQL Instance

I could just use a db wihtin a container.. but i want to scale this out..I have a few ideas.. So I will be initializing the server on gcp

Write an initialization script

#!/bin/bash

# Source the .env file to load environment variables
set -o allexport
source .env
set -o allexport

# Set other variables
PROJECT_ID="smart-axis-421517"
INSTANCE_NAME="jnapolitano-db"
REGION="us-west2" # e.g., us-central1
DATABASE_NAME="jnapolitano"
BUILDS_SQL_FILE="builds.sql" # Name of your builds SQL file
FEED_SQL_FILE="feed.sql"   # Name of your feeds SQL file

# Authenticate with GCP (make sure you have gcloud SDK installed and authenticated)
gcloud auth login

# Set the project
gcloud config set project $PROJECT_ID


# Enable the Cloud SQL Admin API
gcloud services enable sqladmin.googleapis.com


# Create a Cloud SQL instance
gcloud sql instances create $INSTANCE_NAME \
    --database-version=MYSQL_8_0 \
    --cpu=2 \
    --memory=7680MB \
    --region=$REGION

# Set the root password using environment variable
gcloud sql users set-password root \
    --host=% \
    --instance=$INSTANCE_NAME \
    --password=$ROOT_PASSWORD

# Create a user called 'cobra' using environment variable
gcloud sql users create cobra \
    --host=% \
    --instance=$INSTANCE_NAME \
    --password=$COBRA_PASSWORD

# Grant superuser privileges to 'cobra'
gcloud sql connect $INSTANCE_NAME --user=root --quiet << EOF
ALTER USER 'cobra'@'%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'cobra'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EOF

# Optional: Create a database (uncomment if needed)
# DATABASE_NAME="your-database-name"
# gcloud sql databases create $DATABASE_NAME --instance=$INSTANCE_NAME

# Create a database
gcloud sql databases create $DATABASE_NAME --instance=$INSTANCE_NAME

# Execute the SQL files to create the 'builds' and 'feeds' tables
gcloud sql connect $INSTANCE_NAME --user=cobra --database=$DATABASE_NAME --quiet < $BUILDS_SQL_FILE
gcloud sql connect $INSTANCE_NAME --user=cobra --database=$DATABASE_NAME --quiet < $FEED_SQL_FILE


echo "MySQL instance $INSTANCE_NAME created successfully in project $PROJECT_ID with superuser 'cobra' and executed SQL files '$BUILDS_SQL_FILE' and '$FEEDS_SQL_FILE'."

Create a .env file to save your passwords

There are a number of ways to keep passwords out of github. In this case I am just going to add the passes to a .env file and source it

the file looks like

# .env
ROOT_PASSWORD="your-pass"
COBRA_PASSWORD="your-pass"

Run

Chmod


chmod +x your-script

Write the sql files used to create the feed and builds tables

I added a submodule that contains my scripts gh link

Builds

CREATE TABLE builds (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    link VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    generator VARCHAR(255),
    language VARCHAR(10),
    copyright VARCHAR(255),
    lastBuildDate TIMESTAMP,
    atom_link_href VARCHAR(255),
    atom_link_rel VARCHAR(50),
    atom_link_type VARCHAR(50)
);

Feed


CREATE TABLE feed (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    link VARCHAR(255) NOT NULL,
    pubDate TIMESTAMP,
    guid VARCHAR(255),
    description TEXT
);

Run the script

./yourscript

The script should work and create your basic files.. I have some more work to do to create an api to update the tables.