MySQL is used by WordPress but you don’t really interact directly with it. Maybe if you are manually backing up or migrating the database. You can also add some tables to store your own data.

Backup and Exporting/Importing

Essential to trusting any system you are about to load data into is the confidence in knowing it can be backed up, restored from a backup and portable. MySQL is a popular database options for cheap shared hosting. This is typically coupled with PHPMYADMIN. Crucial to exporting the database is selecting the database in phpMyAdmin. The default quick export is good enough. The same goes for re-importing from phpMyAdmin, select the appropriate database first.

An example with SiteGround hosting: You can import database dump form their UI but the file selection is not from your hard disk but from their file manger so this adds an extra step. It is better to just import from phpMyAdmin.

Ultimately you want this database backup to be automated with the help of CRON, (quick glance here)

Running Queries

Run queries in the SQL tab in phpMyAdmin or connect to SQL Workbench,

Note: MySQL WorkBench is a full featured administrative interface to execute SQL queries whereas phpMyAdmin is a web based tool to administer the MySQL database.

Adding Tables to MySQL WP Database

Good Resource: “Become a WP Dev” Course. Checkout the section notes here.

But what about using MySQL in a non-WP environment, hooking it up to a node app, or a Next.js app?

Using MySQL with Node

Traversy Media coming to the rescue again with this video here!

Use MySQL on the machine (Author using XAMPP). Using Express for routing. Import “mysql” package and code a simple server and install nodemon.

npm install mysqljs/mysql

const express = require('express')
const mysql = require('mysql')
// create connection
const db = mysql.createConnection({
  host: 'localhost',
  user: 'me',
  password: 'secret',
  // database: 'nodemysql'
})
// connect
db.connect((err) => {
  if(err) {
    throw err
  }
  console.log("MySql Connected...")
})
const app = express()
//create DB
app.get('/', (req, res) => {
  let sql = 'CREATE DATABASE nodemysql'
  db.query(sql, (err, result) => {
  if (err) {
  throw err
  }
  console.log(result)
  res.send("Database created...")
  })
})
app.listen('3000', () => {
  consoel.log("Server is running on port 3000")
})

Paused the video at 12 minute mark.

Using MySQL with Next.js

This is a next.js tutorial on how to fetch data data for a statically generated page using data fetched from a MYSQL data base.

Install the “serverless-mysql” package. They can handle bulk inserts as well using an array of arrays for each row (syntax shown here).

Random

In my travels I stumbled upon planetscale.

Sources

Derek Banas has an overview video here, (the back half of this two hour video is just fire with concepts such as stored procedures, functions, triggers and error handling). Doesn’t get into window functions or partitions.

Fireship here

Link to a cheatsheet pdf you can download here