MySQL for Database Administrators Ed 5.1 -

MySQL for Database Administrators Ed 5.1

Duration : 5 Days (40 Hours)

Overview

Course Details

Prerequisites

MySQL for Database Administrators Ed 5.1 Course Overview:

The MySQL for Database Administrators Ed 5.1 certification is a recognized qualification that validates an individual’s ability to install, configure, maintain, secure, and troubleshoot MySQL databases. It also demonstrates their expertise in implementing and administering a MySQL server, including configuring replication and backup solutions.

Industries use this certification to ensure that their hired Database Administrators possess the necessary skills to optimize database performance, protect data, and set up robust database systems. By acquiring this certification, DBAs enhance their skills and knowledge, increasing their credibility and value in the job market. It serves as a benchmark for their capability in handling MySQL-based database projects and reflects their proficiency in managing MySQL databases effectively.

Intended Audience:

Experienced IT professionals seeking to enhance their database management skills.
System administrators needing to administer MySQL databases.
Database administrators responsible for installing, configuring, and maintaining MySQL databases.
IT consultants working on MySQL-based projects.
IT managers supervising the use and implementation of MySQL databases.
Database engineers wanting to gain more insight into MySQL’s capabilities.
Software developers integrating MySQL into their applications.

Learning Objectives of MySQL for Database Administrators Ed 5.1:

The objectives of the MySQL course for Database Administrators Ed 5.1 are to provide learners with essential skills to effectively manage the MySQL server. The course covers a wide range of topics, including:

Installing and configuring the MySQL server, understanding its underlying architecture.
Performing backup and recovery procedures to safeguard data.
Managing the database’s security to prevent unauthorized access and ensure data integrity.
Setting up replication and clustering for improved data availability and reliability.
Troubleshooting issues that may arise in the MySQL environment.
Optimizing performance to enhance the database’s efficiency and responsiveness.
Utilizing various features and tools to maintain the database effectively.

Module 1: Introduction to MySQL

Objectives
Course Goals
Course Lesson Map
Introductions
Classroom Environment
MySQL Powers the Web
MySQL Market Share: DB-Engines
MySQL Enterprise Edition
Oracle Premier Support for MySQL
MySQL and Oracle Integration
MySQL Websites
Community Resources
Oracle University: MySQL Training
MySQL Certification
Summary
Practices

Module 2: Installing and Upgrading MySQL

Objectives
Topics
Installation Sequence
Installing MySQL from Downloaded Packages
MySQL RPM Installation Files for Linux
MySQL RPM Installation Process
MySQL DEB Installation
Linux Distribution–Specific Repositories
Installing MySQL by Using a Package Manager
Adding a Yum Repository
Configuring Yum Repository Versions
Adding an APT Repository
Configuring Repository Versions
Manually Configuring the APT Repositories
Installing MySQL on Windows
Installing on Windows: MySQL Installer
Installing on Windows: Selecting Products and Features
Installing on Windows: Product Configuration
Installing MySQL as a Windows Service
Installing MySQL from Source
Installing MySQL from Binary Archive
Deploying MySQL Server with Docker
Quiz
Topics
Linux MySQL Server Installation Directories
Windows MySQL Server Installation Directory
MySQL Programs
mysqld: MySQL Server Process
Installation Programs
Utility Programs
mysql_config_editor
.mylogin.cnf Format
Login Paths
Command-Line Client Programs
Launching Command-Line Client Programs
Topics
Configuring Mandatory Access Control
SELinux Example
AppArmor: Example
Changing the root Password
Using mysqladmin to Change the root Password
Quiz
Topics
Starting and Stopping MySQL
Stopping MySQL with mysqladmin
MySQL Service Files
Starting and Stopping MySQL on Windows
Starting and Stopping MySQL on Windows: MySQL Notifier
Topics
Upgrading MySQL
Reading Release Notes
MySQL Shell Upgrade Checker Utility
Using In-Place Upgrade Method
Using Logical Upgrade Method
mysql_upgrade
Summary
Practices

Module 3: Understanding MySQL Architecture

Objectives
Topics
Architecture
Client/Server Connectivity
MySQL Server Process
Terminology
Server Process
Topics
Connection Layer
Connection Protocols
Local and Remote Connection Protocol: TCP/IP
Local Connection Protocol in Linux: Socket
MySQL and localhost
Local Connection Protocols in Windows: Shared Memory and Named Pipes
SSL by Default
Connection Threads
Quiz
Topics
SQL Layer
SQL Layer Components
SQL Statement Processing
Topics
Storage Layer
Storage Engines Provided with MySQL
Storage Engines: Function
SQL and Storage Layer Interactions
Features Dependent on Storage Engine
InnoDB Storage Engine
MyISAM Storage Engine
MEMORY Storage Engine
ARCHIVE Storage Engine
NDBCluster Storage Engine
BLACKHOLE Storage Engine
Storage Engines Feature Summary
How MySQL Uses Disk Space
Data Directory
Topics
What Is a Data Dictionary?
Types of Metadata
Data Dictionary in Earlier Versions of MySQL
Transactional Data Dictionary in MySQL 8
Transactional Data Dictionary: Features
Serialization of the Data Dictionary
Dictionary Object Cache
Topics
InnoDB Tablespaces
InnoDB System Tablespace
File-per-Table Tablespaces
General Tablespaces
Choosing Between File-Per-Table and General Tablespaces
Locating Tablespaces Outside the Data Directory
Temporary Tablespaces
Topics
Redo Logs
Undo Logs
Undo Tablespace
Temporary Table Undo Log
Quiz
Topics
How MySQL Uses Memory
Global Memory
Session Memory
Log Files and Buffers
InnoDB Buffer Pool
Configuring the Buffer Pool
Topics
MySQL Plugin Interface
MySQL Component Interface
Summary
Practices

Module 4: Configuring MySQL

Objectives
Topics
MySQL Configuration Options
Deciding When to Use Options
Displaying Configured Server Options
Option Naming Convention
Using Command-Line Options
Topics
Reasons to Use Option Files
Option File Locations
Option Files That Each Program Reads
Standard Option Files
Option File Groups
Option Groups That Each Program Reads
Option Group Names
Client Options: Examples
Writing Option Files
Option File Contents: Example
Option Precedence in Option Files
Loading or Ignoring Option Files from the Command Line
Loading Option Files with Directives
Displaying Options from Option Files
Quiz
Topics
Server System Variables
System Variable Scope: GLOBAL and SESSION
Dynamic System Variables
Changing Variable Values
Persisting Global Variables
Displaying System Variables
Viewing Variables with Performance Schema
Topics
Launching Multiple Servers on the Same Host
Settings That Must Be Unique
mysqld_multi
mysqld_multi: Example Configuration File
systemd: Multiple MySQL Servers
Quiz
Summary
Practices

Module 5: Monitoring MySQL

Objectives
Topics
Monitoring MySQL with Log Files
Log File Characteristics
Error Log
Binary Log
General Query Log
General Query Log: Example
Slow Query Log
Slow Query Log: Logging Administrative and Replicated Statements
Filtering Slow Query Log Events
Slow Query Log: Example
Viewing the Slow Query Log with mysqldumpslow
mysqldumpslow: Example
Specifying TABLE or FILE Log Output
Log File Rotation
Flushing Logs
Quiz
Topics
Status Variables
Displaying Status Information
Monitoring Status with mysqladmin
Topics
Performance Schema
Performance Schema Table Groups
Configuring Performance Schema
Performance Schema Setup Tables
Performance Schema Instruments
Top-Level Instrument Components
Accessing Performance Schema Metrics
The sys Schema
Using the sys Schema Example 1
Using the sys Schema: Example 2
Topics
MySQL Enterprise Audit
Installing MySQL Enterprise Audit
Audit Log File Configuration
Audit Log Filtering
Audit Log Filter Definitions
Audit Log File Format
Audit Log File: New-Style XML Format
Audit Log File: JSON Format
Audit Record Values
Quiz
Topics
MySQL Enterprise Monitor
Installing MySQL Enterprise Monitor
Installing the Service Manager
Post-Installation Configuration
Installing Agents
MySQL Enterprise Monitor: Managing Multiple Servers
MySQL Enterprise Monitor: Timeseries Graphs
MySQL Enterprise Monitor: Advisors
MySQL Enterprise Monitor: Events
Topics
SHOW PROCESSLIST
Performance Schema Threads Table
Killing Processes
Limiting User Activity
Setting Resource Limits
Resetting Limits to Default Values
Summary
Practices

Module 6: Managing MySQL Users

Objectives
Objectives
Importance of User Management
Authentication and Authorization
User Connection and Query Process
Viewing User Account Settings
Pluggable Authentication
Local Connection
Remote Connection
Topics
Account Names
Host Name Patterns
Creating a User Account
Roles
Creating a Role
Manipulating User Accounts and Roles
Topics
Setting the Account Password
Dual Password Support
Expiring Passwords Manually
Configuring Password Expiration
Changing Expired Passwords
Quiz
Topics
Pluggable Authentication
Cleartext Client-Side Authentication Plugin
Loadable Authentication Plugins
Enterprise Authentication Plugins
PAM Authentication Plugin
Configuring the PAM Authentication Plugin
Creating Users that Authenticate with PAM
Creating PAM Proxied Users
Logging In with PAM Accounts
Topics
Authorization
Determining Appropriate User Privileges
Privilege Scope
Granting Administrative Privileges
Dynamic Privileges
Special Privileges
GRANT Statement
Granting Permissions on Columns
Granting Roles to Users
Displaying GRANT Privileges
Displaying Privileges for Another User
Displaying Privileges for a Role
Revoking Account Privileges
REVOKE: Examples
Partial Revoke
User Privilege Restrictions
Quiz
Topics
Using Role Privileges
Activating Roles at Server Level
Activating Roles at User Level
Activating Roles at Session Level
Mandatory Roles
Topics
Grant Tables
Grant Table Contents
Use of Grant Tables
Effecting Privilege Changes
Summary
Practices

Module 7: Securing MySQL

Objectives
Topics
Security Risks
MySQL Installation Security Risks
Topics
Securing MySQL from Public Networks
Preventing Network Security Risks
Securing MySQL in Private Networks
Topics
Secure Connections
Secure Connection: Overview
Generating a Digital Certificate
Server Security Defaults
SSL Is Enabled by Default with MySQL Clients
Disabling SSL on MySQL Server
Setting Client Options for Secure Connections
Client –ssl-mode Option: Example
Setting the Permitted Versions for SSL/TLS for the Server
Setting the Permitted Versions for SSL/TLS for the Client
Setting the Cipher to Use for Secure Connections
Global System Variable and Session Status Variables for Ciphers
Cipher System and Status Variables: Example 1
Cipher System and Status Variables: Example 2
Setting Client SSL/TLS Options by User Account
Generating a Digital Certificate
SSL Server Variables for Digital Certificates
SSL Client Options for Digital Certificates
Securing a Remote Connection to MySQL
Quiz
Topics
Preventing MySQL Password Security Risks
How Attackers Derive Passwords
Password Validation Componen
Validate Password Component Variables
Changing the Default Password Validation Variables
Other Password Considerations
Locking an Account
Pluggable Authentication
Preventing Application Password Security Risks
Connection-Control Plugin
Installing the Connection-Control Plugin
Monitoring Connection Failures
Using the CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS Plugin
Quiz
Topics
Limiting Operating System Usage
Limiting Operating System Accounts
Operating System Security
File System Security
Preventing File System Security Risks
Topics
Keyring
Deploying a Keyring
Key Management Functions
Encrypting InnoDB Tablespaces
Encrypting InnoDB Redo Logs and Undo Logs
InnoDB Encryption Keys
Encrypting Binary Log and Relay Log
Binary Log Encryption Keys
Topics
Protecting Your Data from SQL Injection Attacks
SQL Injection: Example
Detecting Potential SQL Injection Attack Vectors
Preventing SQL Injection Attacks
Topics
MySQL Enterprise Firewall
Enterprise Firewall Plugins
Enterprise Firewall Database Components
Installing MySQL Enterprise Firewall
Registering Accounts with the Firewall
Training the Firewall
Statement Digests
Enabling Firewall Protection
Disabling the Firewall
Monitoring the Firewal
Quiz
Summary
Practices

Module 8: Maintaining a Stable System

Objectives
Topics
Stable Systems
Measuring What You Manage
Establishing a Baseline
Application Profiling
Topics
Asking “What Could Go Wrong?”
Components in a MySQL Server Installation
Server Hardware
Problems with Hardware
Virtualized Environment
Operating System
Coexistent Applications
Network Failures
Application Failures
Force Majeure
Topics
Capacity Planning
Monitoring Table Size
Calculating Logical Size: Data and Indexes
Calculating Physical Size: Querying Information Schema
Calculating Physical Size: Reading the File System
Scalability
Scaling Up and Scaling Out
Quiz
Topics
Establishing the Nature of a Problem
Identifying the Problem
Common Problems
Resolving Problems
Topics
Identifying the Causes of Server Slowdowns
Investigating Slowdowns
Quiz
Topics
How MySQL Locks Rows
Identifying Lock Contention
InnoDB Table Locks
InnoDB Row Locks
Troubleshooting Locks by Using SHOW PROCESSLIST
Monitoring Data Locks with Information Schema and Performance Schema
Information Schema INNODB_TRX View
Performance Schema data_locks Table
Performance Schema data_lock_waits Table
sys.innodb_lock_waits View
sys.innodb_lock_waits: Example Query
Performance Schema metadata_locks Table
sys.schema_table_lock_waits View
Topics
InnoDB Recovery
Using –innodb_force_recovery
Summary
Practices

Module 9: Optimizing Query Performance

Objectives
Topics
Identifying Slow Queries
Choosing What to Optimize
Topics
Using EXPLAIN to See Optimizer’s Choice of Index
EXPLAIN: Example
EXPLAIN Output
Common type Values
Displaying Query Rewriting and Optimization Actions
EXPLAIN Example: Table Scan
EXPLAIN Example: Primary Key
EXPLAIN Example: Non-unique Index
EXPLAIN and Complex Queries
EXPLAIN Example: Simple Join
Explanation of Simple Join Output
EXPLAIN FORMAT
EXPLAIN FORMAT: JSON Example
EXPLAIN ANALYZE
Hash Join Optimization
Topics
Index Types
Creating Indexes to Improve Query Performance
Creating Indexes on Existing Tables
Dropping Indexes on Existing Tables
Displaying Indexes Metadata
Invisible Indexes
Topics
Maintaining InnoDB Index Statistics
Automatically Updating Index Statistics
Using ANALYZE TABLE
Rebuilding Indexes
mysqlcheck Client Program
Histograms
Example: The Query
Example: Creating a Histogram
Example: The Query with Histogram Data
Topics
MySQL Query Analyzer
Query Response Time Index
Query Analyzer User Interface
Quiz
Summary
Practices

Module 10: Choosing a Backup Strategy

Objectives
Topics
Reasons to Back Up
Backup Types
Hot Backups
Cold Backups
Warm Backups
Quiz
Topics
Backup Techniques
Logical Backups
Logical Backup Conditions
Logical Backup Performance
Physical Backups
Physical Backup Files
Physical Backup Conditions
Online Disk Copies
Snapshot-Based Backups
Performing a Snapshot
Replication-Based Backups
Binary Log Backups
Binary Logging and Incremental Backups
Quiz
Topics
Comparing Backup Methods
Deciding a Backup Strategy
Backup Strategy: Decision Chart
More Complex Strategies
Summary
Practices

Module 11: Performing Backup

Objectives
Objectives
Backup Tools: Overview
Topics
MySQL Enterprise Backup
MySQL Enterprise Backup: Storage Engines
MySQL Enterprise Backup: InnoDB Files
MySQL Enterprise Backup: Non-InnoDB Files
Full Backups
Single-File Backups
Backup Process
Incremental Backups
Differential Backups
Validate Operations
Restore Operations
Restore Commands
Restoring Incremental Backups
Update Operations
Single-File Operations
Basic Privileges Required for MySQL Enterprise Backup
Granting Required Privileges
Quiz
Topics
mysqldump and mysqlpump
mysqldump
Ensuring Data Consistency with mysqldump
mysqldump Options for Creating Objects
mysqldump Options for Dropping Objects
mysqldump General Options
Restoring mysqldump Backups
Using mysqlimport
Privileges Required for mysqldump
Privileges Required for Reloading Dump Files
mysqlpump
Specifying Objects to Back Up with mysqlpump
Parallel Processing with mysqlpump
Quiz
Topics
Physical InnoDB Backups: Overview
Portability of Physical Backups
Physical InnoDB Backup Procedure
Recovering from Physical InnoDB Backups
Using Transportable Tablespaces for Backup
Transportable Tablespaces: Copying a Table to Another Instance
Physical MyISAM and ARCHIVE Backups
Physical MyISAM and ARCHIVE Backup Procedure
Recovering from Physical MyISAM or Archive Backups
LVM Snapshots
LVM Backup Procedure
Backing Up Log and Status Files
Topics
Replication as an Aid to Backup
Backing Up from a Replication Slave
Backing Up from Multiple Sources to a Single Server
Topics
Processing Binary Log Contents
Selective Binary Log Processing
Point-in-Time Recovery
Configuring MySQL for Restore Operations
Quiz
Summary
Practices

Module 12: Configuring a Replication Topology

Objectives
Objectives
MySQL Replication
Replication Masters and Slaves
Relay Slaves
Complex Topologies
Quiz
Topics
Replication Conflicts
Replication Conflicts: Example Scenario with No Conflict
Replication Conflicts: Example Scenario with Conflict
Topics
Replication Use Cases
Replication for Horizontal Scale-Out
Replication for Business Intelligence and Analytics
Replication for Geographic Data Distribution
Replicating with the BLACKHOLE Storage Engine
Replication for High Availability
Topics
Configuring Replication
Configuring Replication Masters
Configuring Replication Slaves
CHANGE MASTER TO
Finding Log Coordinates
Global Transaction Identifiers (GTIDs)
Identifying the Source Server
Logging Transactions
Replication with GTIDs
Replication Filtering Rules
Applying Filtering Rules
Quiz
Topics
Binary Log Formats
Row-Based Binary Logging
Statement-Based Binary Logging
Mixed Format Binary Logging
Replication Logs
Crash-Safe Replication
Topics
Asynchronous Replication
Semisynchronous Replication
Advantages and Disadvantages of Semisynchronous Replication
Enabling Semisynchronous Replication
Multi-Source Replication
Configuring Multi-Source Replication for a GTID-Based Master
Configuring Multi-Source Replication for a Binary Log Based Master
Controlling Slaves in a Multi-Source Replication Topology
Topics
MySQL Clone Plugin
Installing the Clone Plugin
Granting Permissions to Users
Cloning Local Data
Cloning Remote Data
Cloning for Replication
Clone Plugin Limitations
Summary
Practices

Module 13: Administering a Replication Topology

Objectives
Topics
Failover with Log Coordinates
Potential Problems When Executing a Failover with Log Coordinates
Avoiding Problems When Executing a Failover with Log Coordinates
Failover with GTIDs
Topics
Replication Threads
The Master’s Binlog Dump Thread
Single-Threaded Slaves
Multithreaded Slaves
Controlling Slave Threads
Resetting the Slave
Quiz
Topics
Monitoring Replication
Slave Thread Status
Master Log Coordinates
Relay Log Coordinates
Replication Slave I/O Thread States
Replication Slave SQL Thread States
Monitoring Replication by Using Performance Schema
Replication Tables in Performance Schema
MySQL Enterprise Monitor Replication Dashboard
Topics
Troubleshooting MySQL Replication
Examining the Error Log
SHOW SLAVE STATUS Error Details
Checking I/O Thread States
Monitoring Multi-Source Replication
Summary
Practices

Module 14: Achieving High Availability with MySQL InnoDB Cluster

Objective
Topics
What Is MySQL InnoDB Cluster?
Architecture
MySQL Group Replication Plugin
How Group Replication Works
Single-Primary Mode
Multi-Primary Mode
Conflict Resolution
Consensus and Quorum
Use Cases
Group Replication: Requirements and Limitations
Quiz
Topics
MySQL Shell (mysqlsh)
Using MySQL Shell to Execute a Script
MySQL Router (mysqlrouter)
Topics
Deployment Scenarios
Deploying Sandbox Instances and Creating the Cluster
Production Deployment
Distributed Recovery
Connecting Clients to the Cluster
Quiz
Topics
Managing Sandbox Instances
Checking the Status of a Cluster
Viewing the Structure of a Cluster
Checking the State of an Instance
Updating a Cluster Metadata
Removing Instances from the Cluster
Rejoining an Instance to the Cluster
Restoring Quorum Loss
Recovering the Cluster from a Major Outage
Dissolving a Cluster
Disabling super_read_only
Customizing a MySQL InnoDB Cluster
Customizing an Instance
Configuring Secure Connection in a Cluster
Creating a Server Whitelist
Summary
Practices

Module 15: Conclusion

Course Goals
Oracle University: MySQL Training
MySQL Websites
Your Evaluation
Thank You
Q&A Session

MySQL for Database Administrators Ed 5.1 Course Prerequisites:

Basic understanding of relational databases.
Fundamental knowledge of SQL and MySQL syntax.
Familiarity with data storage and retrieval methods.
Hands-on experience with MySQL installation, configuration, and administration.
Understanding of MySQL replication and security measures.
Know-how of MySQL backup procedures and recovery techniques.

Discover the perfect fit for your learning journey

Choose Learning Modality

Live Online

Convenience
Cost-effective
Self-paced learning
Scalability

Classroom

Interaction and collaboration
Networking opportunities
Real-time feedback
Personal attention

Onsite

Familiar environment
Confidentiality
Team building
Immediate application

Training Exclusives

This course comes with following benefits:

Practice Labs.
Get Trained by Certified Trainers.
Access to the recordings of your class sessions for 90 days.
Digital courseware
Experience 24*7 learner support.

Request Free Demo

Got more questions? We’re all ears and ready to assist!