Thank you for sending your enquiry! One of our team members will contact you shortly.
Thank you for sending your booking! One of our team members will contact you shortly.
Course Outline
Introduction
- Overview of MySQL, its product suite, and associated services
- Support options and service offerings
- Compatibility with supported operating systems
- Structured training pathways and curricula
- Access to MySQL documentation and resources
MySQL Architecture
- The client/server architectural model
- Underlying communication protocols
- Functionality of the SQL layer
- Structure of the storage layer
- Server-level support for various storage engines
- Utilization of memory and disk resources by MySQL
- The MySQL plugin interface mechanism
System Administration
- Selecting the appropriate MySQL distribution
- Process for installing the MySQL Server
- Understanding the server installation file structure
- Procedures for starting and stopping the server
- Strategies for upgrading MySQL versions
- Hosting multiple MySQL instances on a single machine
Server Configuration
- Available MySQL server configuration parameters
- Management of system variables
- Configuring SQL operating modes
- Overview of available log files
- Implementation of binary logging
Clients and Tools
- Client options for administrative tasks
- Dedicated MySQL administrative clients
- Usage of the mysql command-line interface
- Utilizing the mysqladmin command-line tool
- Exploring the MySQL Workbench graphical interface
- Overview of standard MySQL tools
- Available APIs, drivers, and connectors
Data Types
- Primary categories of data types
- Understanding the implication of NULL values
- Definition of column attributes
- Applying character sets to data types
- Criteria for selecting the optimal data type
Obtaining Metadata
- Methods for accessing metadata
- Structure of the INFORMATION_SCHEMA database
- Commands for viewing metadata information
- Distinctions between SHOW statements and INFORMATION_SCHEMA tables
- Utilizing the mysqlshow client utility
- Generating shell commands and SQL queries from INFORMATION_SCHEMA data
Transactions and Locking
- Executing concurrent SQL statements via transaction control
- The ACID properties inherent to transactions
- Levels of transaction isolation
- Applying locking mechanisms to safeguard transactions
Storage Engines
- Overview of storage engines in MySQL
- Detailed examination of the InnoDB engine
- InnoDB system and file-per-table tablespace management
- Integration of NoSQL and the Memcached API
- Efficient configuration of tablespaces
- Achieving referential integrity through foreign keys
- InnoDB locking mechanisms
- Comparative features of available storage engines
Partitioning
- Application of partitioning within MySQL
- Rationale for implementing table partitioning
- Various types of partitioning strategies
- Creation of partitioned tables
- Techniques for subpartitioning
- Retrieving partition metadata
- Optimizing partitions for enhanced performance
- Partitioning support across different storage engines
User Management
- Criteria for user authentication
- Monitoring active threads using SHOW PROCESSLIST
- Administration of user account creation, modification, and removal
- Use of alternative authentication plugins
- Requirements for user authorization
- Hierarchical levels of user access privileges
- Categorization of privilege types
- Processes for granting, modifying, and revoking privileges
Security
- Identification of common security vulnerabilities
- Security risks specific to MySQL deployments
- Countermeasures for network, OS, filesystem, and user-related risks
- Strategies for data protection
- Securing MySQL server connections with SSL
- Enabling secure remote access via SSH
- Sourcing additional information for security issues
Table Maintenance
- Categories of table maintenance operations
- SQL commands for table maintenance
- Client and utility programs for upkeep
- Maintenance practices for alternative storage engines
- Processes for exporting and importing data
- Techniques for data export
- Methods for data import
Programming Inside MySQL
- Development and execution of Stored Routines
- Security considerations for stored routine execution
- Creation and execution of triggers
- Management of events: creation, alteration, and deletion
- Scheduling event execution
MySQL Backup and Recovery
- Fundamentals of backup operations
- Different types of backups
- Tools and utilities for backup management
- Creating binary and text backups
- The role of log and status files in backup integrity
- Data recovery procedures
Replication
- Management of the MySQL Binary Log
- Replication threads and associated files
- Establishing a MySQL Replication Environment
- Architecture design for complex replication topologies
- Implementing Multi-Master and Circular Replication
- Executing a Controlled Switchover
- Monitoring and diagnosing replication issues
- Replication leveraging Global Transaction Identifiers (GTIDs)
Introduction to Performance Tuning
- Query analysis using EXPLAIN
- General strategies for table optimization
- Tracking status variables impacting performance
- Configuration and interpretation of MySQL server variables
- Fundamentals of the Performance Schema
Conclusion
Q&A Session
Requirements
There are no specific prerequisites, though a foundational understanding of database concepts is advantageous for the participant.
Audience:
IT professionals seeking to transition into DBA roles or database support positions for MySQL on Linux and Windows platforms.
Format: 40% theoretical instruction, 60% practical hands-on laboratory work
28 Hours
Testimonials (1)
Was carefully tailored to our needs, very responsive to live questions and situations, gave us lots of practice repeating what we were learning.
