Advanced MySQL Database Administration

Course details

MySQL is a powerful tool for managing and analyzing relational databases, and a skill that every analyst and BI professional should have in the arsenal. In this course, take your database DBA skills to the next level and expand into advanced concepts like replication, backup, recovery, and security, along with automation tools like stored procedures, triggers, and scheduled events.

Instructor John Pauler starts by introducing the course structure, installing MySQL Community and Workbench, and reviewing key database administration (DBA) tasks. Learn about creating and editing schemas and tables, inserting and deleting records, and reviewing the key principles of normalized databases.

John then covers advanced DBA concepts like replication, backup and recovery, and how to leverage automation via stored procedures, triggers, and scheduled events.

Throughout the course, you'll be playing the role of lead DBA for a new firm, building out an entire database and populating tables from the ground up, just like you would in the real world.

Concepts

Introduction

Course structure and outline
Introducing the course project
Setting expectations

Prerequisite Skills

Prerequisite skills review

MySQL Intro and Setup

MySQL installation overview
Mac download - Community Server
Mac download - MySQL Workbench
PC Download - Community Server plus Workbench
Connecting Workbench to the server
MySQL Workbench interface
Preparing Workbench

Importing Data Files to the Database

Overview of create, insert, and delete
Review - Creating a schema
Review - Creating a table
Review - Inserting records into a table
Review - Deleting records
Importing data from a file
Challenge - Importing order item data
Solution - Importing order item data
Challenge - Importing refund data
Solution - Importing refund data
Challenge - Deleting records
Solution - Deleting records
Challenge - Import remaining 2012 data
Solution - Import remaining 2012 data
Bonus - Load data

Altering, Updating, and Mapping Keys

Overview of alter, update, and key relationships
Review - Altering tables
Review - Updating records
Review - Table relationships and cardinality
Challenge - Introducing a new product
Solution - Introducing a new product
Challenge - Adding a product to order items
Solution - Adding a product to order items
Challenge - Updating product data in order items
Solution - Updating product data in order items
Challenge - Primary key to foreign key mapping
Solution - Primary key to foreign key mapping
Challenge - Import 2013 Q1 data
Solution - Import 2013 Q1 data

Replication, Backup, and Recovery

Replication
Backup and recovery
Challenge - Backup and recovery
Solution - Backup and recovery

Mid-Course Project

Challenge - Mid-course project
Solution - Mid-course project

Trigger Automation

Review - Triggers basics
Triggers deep dive
Challenge - Creating and updating orders
Solution - Creating and updating orders
Challenge - Triggers
Solution - Triggers
Challenge - Testing your trigger system
Solution - Testing your trigger system

Creating Views

Views
Challenge - Creating website sessions
Solution - Creating website sessions
Challenge - Creating views
Solution - Creating views

The EER Diagram Editor

Review - The EER diagram editor
Reverse engineering EER diagrams
Challenge - The EER diagram editor
Solution - The EER diagram editor

Stored Procedures and Scheduled Events

Stored procedures
Scheduled events
Challenge - Stored procedures
Solution - Stored procedures

Automating Data from Website Activity

Writing data from website activity
Challenge - Writing data from website activity
Solution - Writing data from website activity
Challenge - Importing website pageview data
Solution - Importing website pageview data

Security

Security threats
Security best practices
Challenge - Database security
Solution - Database security

Final Project

Challenge - Final project
Solution - Final project