Course Code: bspmysql
Duration: 14 hours
Prerequisites:
  • An understanding of ___.
  • Experience with ___.
  • ___ programming experience.

Audience

  • ___
  • ___
  • ___
Course Outline:

Module 1. Introduction to MySQL 

  • Installing and Upgrading MySQL Community 
  • Installing MySQL on Linux 
  • Postinstallation Setup and Testing 
  • Initializing the Data Directory 
  • Starting and Testing the Server 
  • Upgrading MySQL 
  • Upgrade Paths 
  • What the MySQL Upgrade Process Upgrades 
  • Preparing Your Installation for Upgrade 
  • Upgrade Troubleshooting

Module 2. User accounts and permissions 

  • Understanding General Security Issues 
  • Access Control and Account Management 
  • Adding Accounts, Assigning Privileges, and Dropping Accounts 
  • Password Management 
  • Setting Account Resource Limits 
  • Using Encrypted Connections 
  • Configuring MySQL to Use Encrypted Connections 
  • Creating SSL and RSA Certificates and Keys 
  • Security Components and Plugins 
  • Managing audit

Module 3. Backup and Recovery 

  • Backup and Recovery Types 
  • Database Backup Methods 
  • Establishing a Backup Policy 
  • Using mysqldump for Backups 
  • Dumping Data in SQL Format with mysqldump 
  • Reloading SQL-Format Backups 
  • Dumping Data in Delimited-Text Format with mysqldump 
  • Reloading Delimited-Text Format Backups 
  • mysqldump Tips 
  • Point-in-Time (Incremental) Recovery Using the Binary Log 
  • Point-in-Time Recovery Using Event Times 
  • Point-in-Time Recovery Using Event Positions

Module 4. Group replication 

  • Group Replication Use Cases and Requirements 
  • Group Replication Details 
  • Deploying Group Replication in Single-Primary Mode 
  • Monitoring Group Replication 
  • Understanding Group Replication Server States 
  • Main Group Replication Operations 
  • Group Replication Security 
  • Group Replication Performance 
  • Features of Percona XtraBackup for MySQL 
  • Installing and using Percona xtra backup for MySQL

Module 5. Performance Tuning 

  • Optimizing server settings 
  • How MySQL's Configuration Works 
  • Configuring Memory Usage 
  • Understanding per-connection memory needs 
  • Allocating memory for caches 
  • The InnoDB Buffer Pool 
  • The Thread and Table caches 
  • Configuring MySQL concurrency 
  • Optimzing data access 
  • Indexing and Partitioning in MySQL 
  • Best practices for memcached 
  • Best practices for data partitioning 
  • Best practices for queries and indexing

Module 6. Monitoring and troubleshooting MySQL 

  • Introduction to MySQL Performance schema 
  • Using Performance Schema System Variables 
  • Using Performance Schema Status Variables 
  • Using the Performance Schema to Diagnose Problems 
  • MySQL troubleshooting approach 
  • Most common MySQL errors 
  • Too many connections and out of memory errors 
  • Issues with file permissions 
  • MySQL crashes prevention

Module 7. Managing MySQL databases in a DevOps manor 

  • Why you need database DevOps 
  • Introduction to the MySQL Shell 
  • Writing scripts that can be automated 
  • Interaction with the database through the session object 
  • The shell and the continuos integration 
  • Introduction to the AdminAPI