Becoming A Production Mysql Dba

An awesome journey towards world's most popular open source database to become a Production MySQL DBA.

What you'll learn

You will learn how to install latest version of MySQL Server including MariaDB and Percona Server for MySQL.

You will learn how to secure the installation of MySQL and how to start/stop/restart MySQL service using systemd.

You will learn how to customize MySQL server configuration, how to store InnoDB log files, binary log files, MySQL server log files in separate locations.

You will learn how to connect to MySQL locally as well as remotely and how to perform database administration.

You will learn how to perform minor and major MySQL upgrades, how to install new components and remove plugins.

You will learn how to setup GTID-based master-slave replication, and how to setup filtered replication.

You will learn how to take logical as well as physical backup, how to restore MySQL backups, how to setup a new slave from backup.

You will learn how to troubleshoot MySQL server issues.

You will learn how to run MySQL in Docker

Requirements

You must have basic understanding of MySQL or any RDBMS

You must know the basics of linux operating system.

Description

******* Course Overview *******Welcome to this amazing course on MySQL Server Administration. You will learn all aspects of MySQL Server including setting it up, managing it, troubleshooting it. You will learn how to upgrade MySQL, how to setup replication, how to migrate to AWS cloud, and much more!******* Course Contents *******MySQL Server IntroductionWhy MySQL?Meet Bob, The future DBAMySQL WebsitesMySQL Server InstallationInstalling MySQL Server Community EditionInstalling MariaDB Database ServerInstalling Percona MySQL Serverinstalling Specific MySQL VersionRemoving MySQLExploring MySQL ServerMySQL ArchitectureMySQL Installed File LocationsMySQL Executable ProgramsMySQL Shell CommandsMySQL Socket FileMySQL Global & Session VariablesMySQL Remove vs Local ConnectionsMySQL User AdministrationMySQL AccountsCreating DBA Account WITH GRANT OPTIONMySQL RolesGranting Permissions to RolesGranting Roles to UsersMySQL Database AdministrationStoring Auth Credentials using mysql_config_editorMySQL Administrative Programsmysqlimport, mysqlshow, mysqlcheck utilitiesLoading Time Zone DataDownloading Example DatabasesMySQL Server ConfigurationMySQL Default Option FilesAdding Option FilesOption File InclusionsMySQL Error LogMySQL Binary Log FilesRelocating InnoDB System TablespacesMoving Redo Log FilesUpgrading MySQL ServerPerforming MySQL Minor Version UpgradePerforming MySQL Major Version UpgradeRunning Pre-Upgrade ChecksVersion Comparison Between Primary & ReplicaMySQL ReplicationPerforming Traditional Position-Based RelicationPerforming GTID ReplicationIgnoring Databases for ReplicationExcluding Tables from ReplicationMySQL Storage EnginesBLACKHOLE MEMORYINNODBCSVMySQL InnoDB Storage Engine ConfigurationInnoDB ArchitectureMemory-Based ArchitectureInnoDB Buffer PoolDedicated ServerDoublewrite BufferSystem TablespacesMySQL Backup & RestorePerforming Hot BackupPerforming Cold BackupPerforming Logical BackupsRestoring from Hot/Cold BackupsRunning MySQL on DockerInstalling Docker EnginePulling MySQL ImageRunning MySQL ContainerDBA Tips & Tricks ClubCustomizing MySQL Prompt

Overview

Section 1: Introduction

Lecture 1 Course Introduction

Lecture 2 Why MySQL?

Lecture 3 Meet Bob, The Future DBA

Lecture 4 Download Course Slides

Section 2: MySQL Server Installation

Lecture 5 Installing MySQL Server Community Edition

Lecture 6 Installing MariaDB Database Server

Lecture 7 Installing Percona Server for MySQL Server

Lecture 8 Removing MySQL

Lecture 9 Installing Specific Version of MySQL

Lecture 10 Demo - Installing Specific Version of MySQL

Lecture 11 Manually Download RPMs and Locally Install

Lecture 12 Demo - Manually Download RPMs and Locally Install

Lecture 13 Performing MySQL Secure Installation

Lecture 14 Demo - Performing MySQL Secure Installation

Lecture 15 Linux Utilities

Lecture 16 Demo - Linux Utilities

Lecture 17 Section Recap - What Bob has learned so for

Section 3: Exploring MySQL Server

Lecture 18 MySQL Architecture

Lecture 19 MySQL Installed File Locations

Lecture 20 Demo - MySQL Installed File Locations

Lecture 21 MySQL Executable Programs

Lecture 22 MySQL Service under SystemD

Lecture 23 MySQL Shell Commands

Lecture 24 Demo - MySQL Shell Commands

Lecture 25 MySQL Socket File

Lecture 26 Demo - MySQL Socket File

Lecture 27 MySQL GLOBAL Variables

Lecture 28 Demo - Global Variables

Lecture 29 MySQL SESSION Variables

Lecture 30 Demo - MySQL SESSION Variables

Lecture 31 Getting System Variables Help

Lecture 32 MySQL SHOW Command

Lecture 33 Demo - MySQL SHOW Command

Lecture 34 MySQL System Databases

Lecture 35 Demo - MySQL System Databases

Lecture 36 MySQL Local vs Remote Connections

Lecture 37 Demo - MySQL Local vs Remote Connections

Lecture 38 MySQL Shell

Lecture 39 Section Recap - What Bob has learned…

Section 4: Basic MySQL Database Administration

Lecture 40 Storing MySQL Authentication Credentials

Lecture 41 Assignment - Login to MySQL Without Credentials

Lecture 42 mysqladmin - MySQL Administration Program

Lecture 43 Assignment - Perform DBA tasks with mysqladmin

Lecture 44 Executing SQL Files

Lecture 45 Assignment - Execute employees.sql SQL File

Lecture 46 Importing data with mysqlimport

Lecture 47 Assignment - Import staff.txt file

Lecture 48 Maintaining Integrity with mysqlcheck

Lecture 49 Assignment - Perform mysqlcheck on staff table

Lecture 50 Displaying useful Information with mysqlshow

Lecture 51 Assignment - Get Report on employees database and its tables

Lecture 52 Time Zone Tables

Lecture 53 Assignment - Load Time Zone Tables into MySQL

Lecture 54 MySQL Example Databases

Lecture 55 Assignment - Download World Database

Lecture 56 Listing Binary Logs Events with mysqlbinlog

Lecture 57 Assignment - Investigate When Database was dropped

Lecture 58 Section Recap - What Bob has learned…

Section 5: MySQL Storage Engines

Lecture 59 Storage Engines

Lecture 60 Exploring Storage Engines

Lecture 61 FEDERATED Storage Engine

Lecture 62 MEMORY Storage Engine

Lecture 63 Assignment - MEMORY Storage Engine

Lecture 64 BLACKHOLE Storage Engine

Lecture 65 Assignment - BLACKHOLE Storage Engine

Lecture 66 CSV Storage Engine

Lecture 67 Assignment - CSV Storage Engine

Lecture 68 MyISAM Storage Engine

Lecture 69 Assignment - MyISAM Storage Engine

Lecture 70 ARCHIVE Storage Engine

Lecture 71 Assignment - ARCHIVE Storage Engine

Lecture 72 InnoDB Storage Engine

Lecture 73 Assignment - InnoDB Storage Engine

Lecture 74 Checking Storage Engine Status

Lecture 75 Switching Storage Engine

Lecture 76 Installing New Storage Engine

Lecture 77 Disabling Storage Engine

Lecture 78 Section Recap - What Bob has learned…

Section 6: MySQL User Administration

Lecture 79 DBA Account

Lecture 80 MySQL Permissions

Lecture 81 WITH GRANT OPTION

Lecture 82 Assignment - Create DBA Account

Lecture 83 Connecting to MySQL

Lecture 84 Exploring MySQL Workbench

Lecture 85 Creating Regular MySQL Users

Lecture 86 Grant Permissions

Lecture 87 Lock/Unlock MySQL Account

Lecture 88 mysql_native_password & caching_sha2_password auth plugins

Lecture 89 MySQL Roles

Lecture 90 Assignment - MySQL Roles

Lecture 91 Section Recap - What Bob has learned…

Section 7: MySQL Server Configuration

Lecture 92 MySQL Default Configuration File

Lecture 93 Assignment - Locate Default Option File

Lecture 94 MySQL Option/Configuration File Syntax

Lecture 95 Assignment - Re-Write Default Option File

Lecture 96 Variable or Option in Option File?

Lecture 97 Changing Default Option Files Location

Lecture 98 Assignment - Change Default Location of Option File

Lecture 99 STRACE & LSOF With MySQL

Lecture 100 Demo - STRACE & LSOF

Lecture 101 Option File Inclusions

Lecture 102 Assignment - Option File Inclusions

Lecture 103 DATA_DIR MySQL Data Directory

Lecture 104 Assignment - Move DATA DIRECTORY

Lecture 105 Binary Log Files

Lecture 106 Purging Binary Log Files

Lecture 107 Assignment - Disable Binary Logging

Lecture 108 Assignment - Enable Binary Logging

Lecture 109 Binary Logs Retention

Lecture 110 MySQL Error Log File

Lecture 111 Assignment - Change MySQL Error Log File Location

Lecture 112 Adjusting Timestamp of MySQL Error Log File

Lecture 113 MySQL TEMP Directory

Lecture 114 Assignment - Change TMPDIR Location

Lecture 115 Removing Double-Entry for MySQL Error Log File

Lecture 116 Section Recap - What Bob has learned…

Section 8: InnoDB Storage Engine Configuration

Lecture 117 InnoDB Storage Engine

Lecture 118 InnoDB Architecture

Lecture 119 InnoDB Buffer Pool

Lecture 120 Demo - InnoDB Buffer Pool

Lecture 121 InnoDB Log Buffer

Lecture 122 Assignment - InnoDB Log Buffer

Lecture 123 InnoDB Flush Method

Lecture 124 O_DIRECT OR O_DIRECT_NO_FSYNC

Lecture 125 Assignment - Change InnoDB Flush Method

Lecture 126 Doublewrite Buffer

Lecture 127 Assignment - Doublewrite Buffer

Lecture 128 Flushing Logs at Transaction Commit

Lecture 129 Assignment - Setting value of innodb_flush_log_at_trx_commit

Lecture 130 InnoDB Redo Log Files

Lecture 131 Assignment 1 - InnoDB Redo Log Files

Lecture 132 Assignment 2 - InnoDB Redo Log Files

Lecture 133 Assignment 3 - InnoDB Redo Log Files

Lecture 134 System Tablespace

Lecture 135 Assignment - System Tablespace

Lecture 136 Undo Tablespaces

Lecture 137 Demo - Undo Tablespaces

Lecture 138 Temporary Tablespaces

Lecture 139 General Tablespaces

Lecture 140 File-Per-Table Tablespaces

Lecture 141 Dedicated MySQL Server

Lecture 142 Turning Dedicated Server ON

Lecture 143 Overriding Dedicated Server Settings

Lecture 144 Section Recap - What Bob has learned…

Section 9: MySQL Backup & Restore

Lecture 145 MySQL Backups

Lecture 146 Physical/Cold Backup

Lecture 147 Assignment - Perform Physical/Cold Backup

Lecture 148 Assignment - Restore From Physical/Cold Backup

Lecture 149 Files needed for Cold Backup

Lecture 150 Logical Backups

Lecture 151 MySQLDUMP Backup Program

Lecture 152 Assignment - Take Backup with MySQLDUMP

Lecture 153 Restoring from MySQLDUMP

Lecture 154 MySQLPUMP Backup Program

Lecture 155 Demo 1 - MySQLPUMP

Lecture 156 Demo 2 - MySQLPUMP

Lecture 157 Assignment - Backing Up MySQL Accounts

Lecture 158 Assignment - Restore MySQL Account

Lecture 159 Compressing MySQL Backups

Lecture 160 Assignment - Compress MySQL Backup

Lecture 161 Assignment - Restore MySQL Compressed Backup

Lecture 162 Creating Consistent Data Dump

Lecture 163 CREATE TABLE LIKE SQL Statement

Lecture 164 MySQL Hot Backup

Lecture 165 MySQL Hot Backup Tools

Lecture 166 XtraBackup Hot Backup Tool

Lecture 167 Assignment - Download & Install XtraBackup

Lecture 168 Assignment - Backup with XtraBackup

Lecture 169 Assignment - XtraBackup Backup Files

Lecture 170 Preparing Hot Backup Restore

Lecture 171 Assignment - Restore From Hot Backup

Lecture 172 Section Recap - What Bob has learned…

Section 10: MySQL Server Replication

Lecture 173 Replication Concepts

Lecture 174 Replication Methods

Lecture 175 Replication Formats

Lecture 176 Replication Formats Comparison

Lecture 177 General Replication Setup Requirements

Lecture 178 Primary Server

Lecture 179 Assignment - Configure Primary Server

Lecture 180 Cleanup Options from Option File

Lecture 181 Demo 1 - Install MySQL On Replica Server

Lecture 182 Demo 2- Install MySQL On Replication Server

Lecture 183 Start MySQL on Replica

Lecture 184 Hot Backup on Primary for Replication

Lecture 185 Copy Hot Backup from Primary to Replica

Lecture 186 Restore Hot Backup on Replica

Lecture 187 Replica Server Configuration

Lecture 188 Assignment - Configure Replica

Lecture 189 Configure Binary Log Position Based Replication

Lecture 190 Assignment - Configure Binary Log Replication

Lecture 191 Set Report Host on Replica

Lecture 192 SHOW REPLICA STATUS

Lecture 193 Demo - Replica IO_THREAD

Lecture 194 Demo - Replica SQL_THREAD

Lecture 195 Demo - Ignore Database

Lecture 196 Demo - Ignore Table

Lecture 197 Cleanup Traditional Replication

Lecture 198 Setting up GTID Replication

Lecture 199 GTID Replication Requirements

Lecture 200 Prepare GTID Replication

Lecture 201 Assignment - Setup GTID Replication

Lecture 202 Start Replica Until Command

Lecture 203 Demo - Start Replica with Until

Lecture 204 Primary Replica Failover

Lecture 205 Prepare Primary Replica Failover

Lecture 206 Assignment - Failover Primary to Replica

Lecture 207 Assignment - Configure Old Primary to Replica

Lecture 208 Section Recap - What Bob has learned…

Section 11: Upgrading MySQL Server

Lecture 209 Types of MySQL Version Upgrades

Lecture 210 Preparing for Minor Version Upgrade

Lecture 211 Assignment - Perform Minor Version Upgrade

Lecture 212 Demo - Primary & Replica Version Compatibility

Lecture 213 Installing MySQL 5.7

Lecture 214 Download World Example Database

Lecture 215 mysqlcheck - Pre-Upgrade Check

Lecture 216 mysqlsh script - Pre-Upgrade Check

Lecture 217 Demo - Perform MySQL Major Version Upgrade

Lecture 218 Section Recap - What Bob has learned…

Section 12: Docker and MySQL

Lecture 219 Introduction

Lecture 220 Install Docker

Lecture 221 Pull & Inspect MySQL Docker Image

Lecture 222 Run MySQL as Container

Lecture 223 MySQL Container Logs

Lecture 224 MySQL Container Host

Lecture 225 Removing MySQL Container

Lecture 226 Exposing Port From Container to Host

Lecture 227 Create DBA User Bob

Lecture 228 Remotely Login to MySQL Container

Lecture 229 Demo - Data Loss at Container Removal

Lecture 230 Bind Volume For Data Persistence

Lecture 231 Demo - Persistant Volume

Lecture 232 Section Recap - What Bob has learned…

Section 13: DBA Tips & Tricks Club

Lecture 233 Welcome to the DBA Club

Lecture 234 DBA Tip - Customizing MySQL Prompt

Lecture 235 Demo - Customizing MySQL Prompt

Section 14: Course Completion

Lecture 236 Thank You & Goodbye

Junior MySQL DBAs looking to improve and advance their DBA skills.,Anyone interested to jump start their career as a Database Administrator.,System Administrators looking to learn Database Administration skills.