MySQL CMDBA Certified MySQL Database Administrator

What You'll Learn

• Video 1: Series Overview|20:43

  Understanding the Series. Tackling the DBA I Exam.

• Video 2: MySQL Architecture Part 1|43:40

  Communication Protocols. The SQL Parser and Storage Engine Tiers. Relevant Exam Objectives: Communication Protocols; The SQL Parser and Storage Engine Tiers. Description: In this nugget we peek under the hood in MySQL and examine some of its internals.

• Video 3: MySQL Architecture Part 2|47:36

  MySQL Disk Space Usage. MySQL Memory Usage. Relevant Exam Objectives: How MySQL Uses Disk Space; How MySQL Uses Memory. Description: In this nugget we learn how the various MySQL storage engines manifest files in the host OS file system. We also learn about the various memory caches used by MySQL to increase performance.

• Video 4: Configuring MySQL Part 1|36:34

  Types of MySQL Distributions. Starting and Stopping MySQL Server on Windows. Starting and Stopping MySQL Server on Linux. Relevant Exam Objectives: Types of MySQL Distributions; Starting and Stopping MySQL Server on Windows; Starting and Stopping MySQL Server on Unix. Description: In this nugget we differentiate among the potentially confusing array of MySQL Server distributions that are available at the MySQL.com Web site. We also learn how to start, stop, and restart the MySQL daemon on Windows and *NIX.

• Video 5: Configuring MySQL Part 2|51:51

  Runtime MySQL Configuration. Managing MySQL Log Files. Administering Time Zone Tables. Relevant Exam Objectives: Runtime MySQL Configuration; Log and Status Files; Loading Time Zone Tables. Description: In this nugget we learn how to control the MySQL daemon at runtime. We also learn how to view and manage server log files and time zone data.

• Video 6: Configuring MySQL Part 3|40:11

  Setting the Default SQL Mode. Upgrading MySQL. Relevant Exam Objectives: Setting the Default SQL Mode; Upgrading MySQL. Description: In this nugget we learn all about SQL mode, which deals with how MySQL Server handles invalid and/or out-of-bounds data. We also consider how to upgrade an existing MySQL server to a newer version.

• Video 7: Using MySQL Client Programs Part 1|46:53

  Understanding the MySQL Administrative Clients. The mysql Client. The mysqladmin Client. Relevant Exam Objectives: Overview of Administrative Clients; mysql; mysqladmin; Client Program Limitations. Description: In this nugget we conduct a global overview of the MySQL administrative clients. We then perform a deep-dive into the mysql and mysqladmin client utilities.

• Video 8: Using MySQL Client Programs Part 2|24:37

  Using the mysqlimport Utility. Using the mysqldump Utility. Relevant Exam Objectives: Overview of Administrative Clients; mysqlimport; mysqldump; Client Program Limitations. Description: In this nugget we learn how to use the mysqlimport and mysqldump utilities to perform data imports and exports, respectively, with MySQL Server.

• Video 9: Implementing MySQL Security|41:12

  Managing User Accounts. Granting and Revoking User Privileges. Relevant Exam Objectives: None (bonus content). Description: In this nugget we learn the basics of user account security in MySQL Server.

• Video 10: Managing Character Sets|30:28

  What are Character Sets and Collations, Anyway? Performance Issues Related to Character Sets. Choosing Data Types for Character Columns. Relevant Exam Objectives: Performance Issues; Choosing Data Types for Character Columns. Description: In this nugget we learn exactly what character sets and collations are and how they work together. We then learn how to optimize database and table performance by selecting the proper character set(s) for each table and the proper data type for each column.

• Video 11: Administering Locking in MySQL|30:39

  Locking Concepts. Implicit vs. Explicit Locking. Advisory Locking. Relevant Exam Objectives: Locking Concepts; Explicit Table Locking; Advisory Locking. Description: In this nugget we learn the fundamentals of locking in MySQL. We differentiate between implicit and explicit locking, and understand the rudiments of advisory locking and how this relates to MySQL application development.

• Video 12: Managing Storage Engines Part 1|31:20

  MySQL Storage Engines Overview. The MyISAM Engine. The InnoDB Engine. Relevant Exam Objectives: MySQL Storage Engines; The MyISAM Storage Engine; The InnoDB Storage Engine. Description: In this nugget we learn what storage engines are and precisely how and why they are so useful to MySQL DBAS. We then undertake a detailed considersation of the MyISAM and InnoDB storage engines.

• Video 13: Managing Storage Engines Part 2|36:29

  The MERGE Storage Engine. The MEMORY Storage Engine. The FEDERATED Storage Engine. Relevant Exam Objectives: MySQL Storage Engines; The MERGE Storage Engine; The MEMORY Engine; The FEDERATED Engine. Description: In this nugget we learn a great deal about the MERGE, MEMORY, and FEDERATED storage engines.

• Video 14: Managing Storage Engines Part 3|21:15

  The Cluster Storage Engine. The CSV Engine. The BLACKHOLE Storage Engine. Relevant Exam Objectives: MySQL Storage Engines; The Cluster Storage Engine; Other Storage Engines. Description: In this nugget we learn about the Cluster, CSV, and BLACKHOLE storage engines.

• Video 15: MySQL Table Maintenance Part 1|25:42

  Table Maintenance Options. Table Maintenance via SQL. Table Maintenance via Client Applications. Relevant Exam Objectives: Types of Table Maintenance Operations; SQL Statements for Table Maintenance; Client and Utility Programs for Table Maintenance. Description: In this nugget we examine the native table maintenance tools that are available to DBAs in MySQL Server.

• Video 16: MySQL Table Maintenance Part 2|20:33

  Repairing Corrupted InnoDB Tables. Automating MyISAM Table Repair. Relevant Exam Objectives: Repairing InnoDB Tables; Enabling MyISAM Auto-Repair. Description: In this nugget we learn how to repair corrupted InnoDB tables by using first- and third-party tools. We also learn how to enable MyISAM table auto-repair.

• Video 17: Using the INFORMATION_SCHEMA Database|30:15

  Accessing INFORMATION_SCHEMA Metadata. Differentiating INFORMATION_SCHEMA from SHOW. Relevant Exam Objectives: INFORMATION_SCHEMA Access Syntax; INFORMATION_SCHEMA Versus SHOW; Limitations of INFORMATION_SCHEMA. Description: In this nugget we learn how to access MySQL metadata by leveraging the INFORMATION_SCHEMA virtual database. We also weigh the relative pros and cons of INFORMATION_SCHEMA versus the MySQL SHOW statements.

• Video 18: MySQL Disaster Recovery Part 1|31:35

  Binary Versus Text-Based Backups. Performing Binary Backups. Relevant Exam Objectives: Binary Versus Textual Backups; Making Binary Backups. Description: In this nugget we discuss the differences between binary and text backups in MySQL Server. We then learn how to perform binary backups.

• Video 19: MySQL Disaster Recovery Part 2|22:35

  Performing Text-Based Backups. Using MySQL Replication as a Means of Backup. Relevant Exam Objectives: Making Text Backups; Replication as an Aid to Backup. Description: In this nugget we learn how to perform text backups, and understand the rudiments of MySQL replication with a focus on leveraging replication for disaster recovery purposes.

• Video 20: MySQL Disaster Recovery Part 3|22:13

  Backing up the Log and Status Files. MySQL Cluster as Means of Backup. The Data Restore Process. Relevant Exam Objectives: Backing Up Log and Status Files; MySQL Cluster as Disaster Prevention; Data Recovery. Description: In this nugget we learn how to back up the MySQL Server log and status files. We then consider how backup is performed in a MySQL Cluster environment. Finally, we examine how the data restore process works.

• Video 21: Series Overview|22:42

  Untangling the MySQL Certification Program. Understanding the Series. Tackling the DBA II Exam. Description: In this nugget we describe the course and introduce the student to the MySQL certification program in general and the DBA II exam in particular.

• Video 22: Understanding Identifiers|31:08

  MySQL Identifier Syntax Rules. Using Qualified Object Names. Description: In this nugget we cover the foundational topic of MySQL object naming conventions.

• Video 23: Creating Stored Procedures|36:05

  What are Stored Procedures? Creating, Altering, and Dropping Stored Procedures. Relevant Exam Objectives: Using Stored Routines and Triggers for Security Purposes, Using Stored Routines to Enhance Performance. Description: In this nugget we learn the ground rules of stored procedures: what they are, why they are crucial in a relational database management system (RDBMS), and how we can create and manage them in MySQL 5.x.

• Video 24: Creating Triggers|24:25

  What are Triggers? Creating, Altering, and Dropping Triggers. Relevant Exam Objectives: Using Stored Routines and Triggers for Security Purposes, Using Stored Routines to Enhance Performance. Description: In this nugget we learn all about triggers: what they are, why they are important in business, and how to create and manage them in MySQL Server.

• Video 25: Using Stored Routines for Administration|18:41

  Enhancing Security with Stored Procedures. Enhancing Performance with Stored Procedures. Relevant Exam Objectives: Using Stored Routines and Triggers for Security Purposes, Using Stored Routines to Enhance Performance. Description: In this nugget we build upon what we learned in the previous two nuggets and learn how to improve the security and efficiency of our MySQL server by intelligently deploying stored procedures and triggers.

• Video 26: MySQL User Account Administration|36:25

  User Account Management. Client Access Control. Relevant Exam Objectives: User Account Management, Client Access Control. Description: In this nugget we learn the basics of user account management and client access control in MySQL. By the end of this nugget you will learn how to create users, grant and revoke privileges, and you will fully understand how privileges are applied throughout the MySQL connection and data access processes.

• Video 27: MySQL Security Basics Part 1|30:59

  Overview of MySQL Security Issues. Securing the Host Operating System. Relevant Exam Objectives: Security Issues, Operating System Security. Description: In this nugget we begin a three-part mini-series on MySQL security fundamentals. In this nugget we look at MySQL security from a “bird’s eye” perspective. Next, we focus on best practices for securing the host operating system, whether that host OS be Microsoft Windows, Linux/UNIX, or Apple Mac OS X.

• Video 28: MySQL Security Basics Part 2|40:15

  Securing the File System. Protecting MySQL Log Files. Relevant Exam Objectives: Filesystem Security, Log Files and Security. Description: In this nugget we continue our journey through MySQL security best practices, this time concentrating on securing file system resources, including the MySQL table files and log files.

• Video 29: MySQL Security Basics Part 3|35:18

  Securing the Network. FEDERATED Table Security. Relevant Exam Objectives: Network Security, FEDERATED Table Security. Description: In this nugget we complete our consideration of MySQL security by learning how to secure the network for MySQL access. We also learn how to implement the FEDERATED storage engine.

• Video 30: Upgrade-Related Security Considerations|26:50

  Upgrading the Grant Tables. Security-Related SQL Mode Values. Relevant Exam Objectives: Upgrading the Privilege Tables, Security-Related SQL Mode Values.

• Video 31: Optimizing Queries Part 1|36:11

  Basics of Query Analysis. Analyzing Queries with EXPLAIN. Relevant Exam Objectives: Identifying Candidates for Query Analysis, Using EXPLAIN to Analyze Queries. Description: In this nugget we undertake a study of the basics of query analysis, including a detailed tutorial on how the EXPLAIN statement can help us DBAs to better. understand how the MySQL query optimizer parses SQL query statements.

• Video 32: Optimizing Queries Part 2|29:52

  Using SHOW WARNINGS. Enabling MyISAM Index Caching. Relevant Exam Objectives: Using SHOW WARNINGS for Optimization, MyISAM Index Caching. Description: In this nugget we learn how to use the SHOW WARNINGS statement to gain a deeper understanding of MySQL parser-related problems. We also learn how to improve the performance of our MyISAM-based tables by tweaking the MyISAM index cache.

• Video 33: Optimizing Databases and Tables|28:10

  Optimizing Tables. The Normalization Process. Relevant Exam Objectives: General Table Optimizations, Normalization. Description: In this nugget we pick up some useful tips ‘n’ tricks for optimizing tables. We then tackle the often daunting subject of database normalization.

• Video 34: Storage Engine-Specific Optimizations Part 1|34:24

  Optimizing MyISAM. Optimizing InnoDB. Relevant Exam Objectives: MyISAM-Specific Optimizations, InnoDB-Specific Optimizations. Description: In this nugget we learn how to maximize the performance of the two most commonly used storage engines in MySQL: the default MyISAM and the transaction-safe InnoDB.

• Video 35: Storage Engine-Specific Optimizations Part 2|25:31

  Optimizing MERGE. Optimizing MEMORY. Relevant Exam Objectives: MERGE-Specific Optimizations, MEMORY-Specific Optimizations. Description: In this nugget we learn how to tune the MERGE and MEMORY storage engines for peak performance and security.

• Video 36: Performance Tuning MySQL Server|33:07

  Assessing Server Status and Load. Tweaking Memory Parameters. Tuning the Query Cache. Relevant Exam Objectives: Interpreting MySQL Server Information, Measuring Server Load, Tuning Memory Parameters, Using the Query Cache. Description: In this nugget we introduce you to a variety of in-box tools to help you “put your finger on the pulse” of your MySQL server. Specifically, we learn to assess server variables and status, tweak memory parameters, and adjust the various and sundry query cache parameters offered by MySQL 5.x.

• Video 37: Diagnosing MySQL Problems|27:34

  Using the Error Log. Using The Slow Query Log. Relevant Exam Objectives: Using the Error Log for Diagnostic Purposes, Using the Slow Query Log for Diagnostic Purposes. Description: In this nugget we seek diagnostic and performance-tuning information from the MySQL error log and the slow query log.

• Video 38: Optimizing the MySQL Server Environment Part 1|20:20

  Selecting Hardware for MySQL. Configuring Disks for MySQL. Relevant Exam Objectives: Choosing Hardware for MySQL Use, Configuring Disks for MySQL Use. Description: In this nugget we examine suggestions offered from the MySQL development team regarding hardware and disk choice/configuration for MySQL server.

• Video 39: Optimizing the MySQL Server Environment Part 2|18:21

  Optimizing the Network. Optimizing the Operating System for MySQL. Relevant Exam Objectives: Network Issues, Optimizing the Operating System for MySQL Use. Description: In this nugget we revisit and expand upon earlier discussions concerning how to maximize network and operating system performance for MySQL server.

• Video 40: Scaling Out MySQL|34:30

  Multi-Server MySQL. MySQL Replication. Relevant Exam Objectives: Using Multiple Servers, Replication. Description: In this nugget we learn how to successfully install more than one instance of MySQL on a single physical or virtual server. We also learn how to implement database replication.