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