MySQL DBA Training - Join Now

 

Course Synopsis

First of all, this course, MySQL DBA Training,  is designed for MySQL Database Administrators who have a basic understanding of a MySQL database and SQL commands. Therefore, the course provides practical experience in setting up and maintaining a MySQL server, including backing up, recovery, configuration and optimization.Mysq is the most widely used open-source database in the world. The Mysql DBA Course will assist you in understanding the fundamental concepts & advanced techniques & tools for managing data and administering the MySQL Database.

 

Course Objectives

Objective of MySQL DBA Training Course is to give administrators the knowledge and skills needed to maintain successful and efficient day-to-day operations of MySQL databases and services. Hence, Administrators will gain practical experience in setting up, administering and troubleshooting MySQL databases. As a result, t

hey will have in depth knowledge on Replication, High availability features of MySQL and in depth knowledge Security.

 

To learn MySQL Online you must have a basic understanding of how databases and computer systems work, and if you're looking for MySQL Certification, then only a MySQL DBA Tutorial won't do.

Because as in-demand MySQL is, it's not that easy

Mysql Tutorial

to learn, so business owners will doubt your skills for sure if you don't have MySQL Certification.

Practical Includes in MySQL DBA Training:

  • Starting, Stopping and Configuring MySQL
  • Security-Related Configuration
  • Database Creation
  • Using Client Programs for DBA Work
  • Setting up Character Set Support
  • Transactions and Locking
  • Using Storage Engines
  • Table Maintenance
  • Obtaining Data from the Information_Schema Database
  • Backup and Recovery
  • Using Stored Procedures for Database Administration Tasks
  • User Management
  • Securing the Server
  • Upgrade-Related Security Issues
  • Optimizing Queries and Schemas
  • Optimizing the Server
  • The Event Scheduler
  • Partitioned Tables
  • Interpreting Error Messages
  • Optimizing the Environment
  • Scaling MySQL
  • MYSQL Cluster

 

Topics to be Covered in MySQL DBA Training Course

 

Section 1 Getting Started with MySQL 

 

Session 1 Introduction to DBMS and RDBMS

 

  • Flat File Databases
  • Drawbacks of Flat File Databases
  • Database Management System
  • Usage of DBMS
  • Functionalities of database system
  • Data models
  • RDBMS and 12 Rules
  • Properties of Relations
  • Keys and Referential Integrity

 

Session 2 Schema Normalization

  • Normalization – Definition
  • Implementation of Normalization Process
  • A Practical Example on
    • First Normal Form
    • Second Normal Form
    • Third Normal Form

 

Session 3. MySQL Architecture

  • Client/Server Overview
  • Communication Protocols
  • The SQL Parser and Storage Engine Tiers
  • How MySQL Uses Disk Space
  • How MYSQL Uses Memory

Lab Assignment: Examining the Architecture

 

Session 4. Installing, Configuring, Starting and Stopping

  • MySQL Distributions
  • Installing on Windows
  • Installing on Linux and UNIX
  • Starting and Stopping on Windows
  • Starting and Stopping on UNIX/Linux
  • Configuration
  • Log and Status Files
  • The Default SQL Mode
  • Time Zone Tables
  • Some Security Issues
  • Upgrading

Lab Assignment: Installing, configuring, stopping and starting

 

Session 5. Interpreting Error and Diagnostic Information

  • MySQL Error Messages
  • The SHOW Statement
  • SQL Modes
  • The PERROR Utility
  • The Log
  • The Error Log
  • The Slow Query Log

Lab Assignment: Interpreting Error and Diagnostic Information

 

Section 2 Data Management in MySQL

 

Session 6. Storage Engines

  • Introduction
  • The MYISAM Engine
  • Locking with MYISAM Tables
  • The Merge Engine
  • Other Engines

Lab Assignment: Using Storage Engines

 

Session 7. The Innodb Engine

  • Introduction
  • Features of Innodb
  • Transactions
  • Referential Integrity
  • Physical Characteristics of Innodb Tables
  • Tablespace Configuration
  • Log File and Buffer Configuration
  • Innodb Status

Lab Assignment: Using the InnoDB Engine

 

Session 8. Obtaining Metadata

  • Available metadata access methods
  • Structures of information_ schema
  • Using available commands to view metadata
  • Differences between SHOW statements and INFORMATION_SCHEMA tables
  • The MYSQL show client program
  • Using INFORMATION_SCHEMA queries to create shell commands and SQL statements

 

Session 9. Locking Concepts in MySQL

  • Using Transaction control statement to run multiple SQL statement concurrently
  • The ACID properties of transactions
  • Using locking to protect transactions
  • Explicit Table Locking
  • Advisory Locking
  • Preventing Locking Problems
  • Transaction isolation levels

Lab Assignment: Working with Transactions and Locks

 

Session 10. Table Maintenance

  • Table Maintenance Operations
  • Check Table
  • Repair Table
  • Analyze Table
  • Optimize Table
  • MySQL Check
  • MYISAMCHK
  • Repairing Innodb Tables
  • Enabling MYISAM AutoRepair
  • Exercises: Maintaining Tables

 

Session 11. Backup and Recovery

  • Planning and Implementing a Backup and Recovery Strategy
  • Defining a Disaster Recovery Plan
  • Testing a Backup and Recovery Plan
  • The Advantages and Disadvantages of Different Methods
  • Binary Backups of MYISAM Tables
  • Binary Backups of Innodb Tables
  • Recovery
  • Import and Export Operations
  • Exporting Using SQL
  • Importing Using SQL
  • Exporting from the Command Line
  • Importing from the Command Line

Lab Assignment: Backing up and Recovery

 

Section 3. MySQL Security and Programming

 

Session 12. User Management

  • Introduction
  • User Accounts
  • Creating Users
  • Renaming Users
  • Changing Passwords
  • Dropping Users
  • Granting Privileges
  • The User Table
  • Connection Validation
  • Exercises: Creating, Managing and Dropping Users

 

Session 13. Privileges

  • Introduction
  • Types of Privileges
  • Revoking Privileges
  • Resource Limits
  • The MySQL Database
  • The Show Grants Command
  • Exercises: Granting and Revoking Privileges

 

Session 14. User Variables and Prepared Statements

  • User Variables
  • Prepared Statements
  • Exercises: User Variables and Prepared Statements

 

Session 15. Stored Routines for Administration

  • Types of Stored Routines
  • Benefits of Stored Routines
  • Stored Routines Features, Maintenance and Privileges and Execution Security
  • Exercises: Creating and Using Stored Routine

 

Session 16. Triggers

  • DML Triggers
  • The Create Trigger Statement
  • Managing Triggers
  • Exercises: Creating and Using Triggers

 

Session 17. Partitioned tables

  • Overview of portioning and reasons for portioning
  • Range partitioning
  • Hash partitioning
  • Key partitioning
  • List partitioning
  • Composite partitioning or subpartitioning
  • Creating a portioned tables
  • Obtaining partitioned information
  • Modifying and removing partitions
  • Partition modification performance effects
  • Partition pruning
  • Storage engines partition information
  • Partition and locking and Limitations

Exercises: Using partitioned tables

 

Session 18. Securing the Server

  • Security Issues
  • Recognizing common security risks
  • Operating System Security
  • Security risks specific to MYSQL installation
  • Filesystem Security
  • Log Files and Security
  • Network Security
  • Upgrade-related Security Issues
  • Upgrading the Privilege Tables
  • Security-Related SQL_Mode Values
  • Security problems and counter measures for network, operating systems, file sys, and users
  • Using SSL for secure MYSQL server connections
  • How SSH enables a secure remote connection to the MySQL server
  • Finding additional information for common security issues

Exercises: Securing the Server

 

Section 4 Optimization and Performance Tuning

 

Session 19. Optimizing MySQL Queries

  • Optimization Overview
  • Optimization Process
  • Planning a Routine Monitoring Regime
  • Setting Suitable Goals
  • Identifying Candidates for Query Analysis
  • Using Explain to Analyze Queries
  • Meaning of Explain Output
  • Using Explain Extended
  • Exercises: Explaining and Optimizing Queries

 

Session 20. Optimization and Indexes

  • Indexes for Performance
  • Creating and Dropping Indexes
  • Obtaining Index Metadata
  • Indexing Principles
  • Indexing and Joins
  • MyIsam Index Caching
  • Exercises: Using Indexes for Optimization

 

Session 21. Optimizing Schemas

  • Normalisation
  • General Table Optimizations
  • Myisam Specific Optimizations
  • Innodb Specific Optimizations
  • Other Engine Specific Optimizations
  • Exercises: Optimizing Schemas

 

Session 22. Optimizing the Server

  • Measuring Server Load
  • System Factors
  • Server Parameters
  • Query Optimizer Performance
  • The Query Cache
  • Exercises: Optimizing the Server

 

Session 23. Optimizing the Environment

  • Choosing the Platform
  • Hardware Configurations
  • Disk Issues on Linux
  • Symbolic Links
  • Optimizing the Operating System
  • Exercises: Optimizing the Environment

 

Section 5 Scaling and High Availability

 

Session 24. MySQL Replication Concepts

  • Important HA terms
  • High Availability Levels
  • Major HA Solutions Using MySQL
  • Advantages and Disadvantages
  • How Replication Works

Session 25.  GTID Based Replication

  • How GTID Replication Works
    • What is GTID?
    • How to Configure GTID Replication?
    • GTID Replication Basics
    • Coordinate Replication Failover
    • GTID Replication Failover

 

Session 26.  MySQL Clusters Introduction

  • Overview of MySQL Cluster
    • Design Goals, Evolution, Workloads, Users
    • Architecture and Core Technology
  • New Features & Capabilities

 

Session 27. MySQL Cluster

  • Cluster’s components;
    • Management Node
    • SQL/API Node
    • Data/Storage Node
  • Configurations File;
    • Local
    • Global
    • Connect String
  • Cluster Partitioning
    • Partitions
    • Fragments
  • Disk Tables
  • In-Memory Tables

 

Session 28. Tips for Optimization

MySQL For Developers

Session 29 Working with Views

  • Create a view from single or multiple underlying tables
  • Access a view
  • Determine if a view is updatable or insertable
  • Check a view
  • Alter or drop a view
  • Display metadata for a view

Session 30. Working with Strings

  • Apply string values in expressions
  • Manipulate strings with MySQL functions
  • Find string values based on patterns
  • Use MySQL’s Full-text search capability

Session 31. Generating Reports from Data

  • Find the smallest and largest values in a column
  • Calculate totals and averages
  • Summarize data based on expressions
  • Use aggregates and window functions
  • Use summary tables to improve query performance
  • Create crosstab reports
  • Create a bar chart with SQL
  • Create a decision table

Session 32. Working with Spatial Data in MySQL

  • Describe the support for spatial data in MySQL
  • Create different geometries to populate spatial columns
  • Describe Spatial Reference Systems (SRSs) and SRIDs
  • Create spatial indexes
  • Use spatial data functions

Session 33. Working with JSON Data in MySQL

  • Describe the benefits and characteristics of JavaScript Object Notation (JSON)
  • Use the native MySQL JSON data type
  • Use JSON functions to create and manipulate JSON data
  • Index JSON data with generated columns

Session 34. NoSQL with MySQL

  • Explain how MySQL implements NoSQL
  • Describe the Document Store
  • Explain X DevAPI functions and chaining
  • Use X DevAPI with PHP to access a document store
  • Use X DevAPI with Connector/J to access a document store
  • Use X DevAPI with Connector/Python to access a document store
  • Use MySQL Shell to access JSON
  • Issue Python statements from MySQL Shell

After successful completion of MySQL DBA Training Course, you will get certification from The Skill Pedia.

Most Popular Lecture on MySQL Architecture on Youtube by the Instructor

https://www.youtube.com/watch?v=Xvhi63cZADo