Course Code: oraclemysql
Duration: 28 hours
Prerequisites:

Fundamental knowledge of any database and SQL language.

Overview:

The MySQL for Database Administrators course teaches DBAs and other database professionals how to maximize their organization's investment in MySQL and about the benefits of using the MySQL Cloud Service. Learn to configure the MySQL Server, set up replication and security, perform database backups, optimize query performance, and ensure high availability.

The training aims at preparing the delegates for the MySQL Database Administration Certification test from Oracle.

Topics to be covered:

MySQL Architecture

  • Use MySQL client programs to interface with the MySQL Server interactively and in batch
  • Describe how MySQL uses disk and memory resources
  • List and describe key characteristics of standard MySQL storage engines including InnoDB, NDB, MyISAM, MEMORY, FEDERATED

MySQL Server Installation, Configuration and Maintenance

  • Select, deploy, start and stop MySQL using appropriate binary packages for Windows and Linux platforms
  • Configure MySQL Server deployments using options files, command-line options and server variables.
  • Identify, configure, locate and describe appropriate use cases for MySQL the error, binary, general query and slow query log.

MySQL Security

  • Describe appropriate steps to secure a MySQL deployment at the operating system, filesystem and network levels.
  • Create and maintain user accounts with appropriate privileges and configuration.
  • Deploy and configure MySQL Enterprise Audit

Diagnostic Data and Metadata Sources in MySQL

  • Configure and leverage PERFORMANCE_SCHEMA tables to identify and diagnose performance problems.
  • Obtain MySQL metadata from INFORMATION_SCHEMA tables

Optimizing MySQL Performance

  • Demonstrate ability to diagnose and optimize poorly-performing queries.
  • Tune MySQL Server configuration for best performance.
  • Apply best practices in optimizing schema objects
  • Demonstrate understanding of locking concepts as applied to MySQL Server and storage engines.
  • Create and utilize table PARTITIONING

Backups and Recovery in MySQL

  • Create and restore logical backups using mysqldump
  • Create and restore binary backups
  • Use backups for data recovery

High Availability Techniques for MySQL

  • Describe, configure, deploy and troubleshoot MySQL replication
  • Describe and contrast characteristics of common MySQL high availability solutions (MySQL Cluster, DRBD, Windows Failover Clustering, Solaris Cluster, OVM Template for MySQL Enterprise)

After taking this course, you will be equipped to use all the features of MySQL to get the best out of your Web, Cloud, and embedded applications, whether you work with the command line or graphical tools such as MySQL Workbench and MySQL Enterprise Monitor, whether you use on-premise or MySQL Cloud-based instances, and whether your challenge is complex replication requirements, or highly-tuned transactional systems.

Also, you can appear for the Certification Exam at any Pearson VUE center near you.

Course Outline:

Introduction to MySQL

  • MySQL overview
  • MySQL Enterprise Edition
  • MySQL on the Web
  • MySQL in the Cloud
  • The MySQL community

Installing MySQL

  • Installing MySQL
  • Installed Files and Directories
  • Initial Configuration
  • Starting and Stopping MySQL
  • Upgrading MySQL

MySQL Architecture

  • Architectural Overview
  • How MySQL Transmits Data
  • How MySQL Processes Requests
  • How MySQL Stores Data
  • Tablespaces
  • Redo and Undo Logs
  • How MySQL Uses Memory
  • Plug-in Interface

Configuring MySQL

  • Server Options, Variables, and the Command Line
  • Option Files
  • System Variables
  • Launching Multiple Servers on the Same Host

Monitoring MySQL

  • Monitoring MySQL with Log Files
  • Monitoring MySQL with Status Variables
  • Monitoring MySQL with Performance Schema
  • MySQL Enterprise Audit
  • MySQL Enterprise Monitor
  • Monitoring User Activity

User Management

  • MySQL Privilege System
  • Creating and Modifying User Accounts
  • Configuring Passwords and Account Expiration
  • Authentication Plug-Ins
  • Granting Permissions
  • Grant Tables
  • Resetting a Forgotten Root Password

MySQL Security

  • Security Risks
  • Network Security
  • Secure Connections
  • Password Security
  • Operating System Security
  • Protecting Against SQL Injections
  • MySQL Enterprise Firewall

Maintaining a Stable System

  • Stability
  • Why Databases Fail
  • Capacity Planning
  • Troubleshooting
  • Identifying the Causes of Server Slowdowns
  • InnoDB Recovery

Optimizing Query Performance

  • Identifying Slow Queries
  • The EXPLAIN statement
  • Working with Indexes
  • Index Statistics

Backup Strategies

  • Understanding Backups
  • Backup Techniques
  • Creating a Backup Strategy

Performing Backups

  • MySQL Backup Tools
  • Raw Backup Methods
  • Techniques that Use the Binary Log

Configuring a Replication Topology

  • Overview of Replication
  • Replication Conflicts
  • When to Use Replication
  • Configuring Replication

Administering a Replication Topology

  • Failover
  • MySQL Utilities
  • Replication Threads
  • Monitoring Replication
  • Troubleshooting Replication

Group Replication

  • Overview
  • Single-Primary and Multi-Primary Modes
  • Configuring Group Replication
  • Monitoring Group Replication

Conclusion

  • Course Overview
  • NobleProg - Course Evaluation
  • Q&A Session
  • Oracle Exam Voucher Distribution