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 products, and associated services
- Details on MySQL services and support structures
- List of supported operating systems
- Recommended training curriculum paths
- Essential MySQL documentation resources
MySQL Architecture
- Understanding the client/server model
- Communication protocols used
- Functions of the SQL Layer
- Functions of the Storage Layer
- How the server interfaces with storage engines
- Utilization of memory and disk space in MySQL
- The MySQL plug-in interface
System Administration
- Selecting the appropriate MySQL distribution
- Procedures for installing the MySQL Server
- Understanding the MySQL Server installation file structure
- Processes for starting and stopping the MySQL server
- Methods for upgrading MySQL
- Deploying multiple MySQL servers on a single host
Server Configuration
- Reviewing MySQL server configuration options
- Managing system variables
- Configuring SQL Modes
- Overview of available log files
- Implementation of binary logging
Clients and Tools
- Clients available for administrative tasks
- Specific MySQL administrative clients
- Using the mysql command-line client
- Using the mysqladmin command-line client
- Utilizing the MySQL Workbench graphical interface
- Overview of various MySQL tools
- Exploring available APIs, including drivers and connectors
Data Types
- Primary categories of data types
- Implications of NULL values
- Column attributes and their significance
- Interaction between character sets and data types
- Strategies for selecting appropriate data types
Obtaining Metadata
- Methods for accessing metadata
- Structure of the INFORMATION_SCHEMA
- Commands for viewing metadata
- Distinctions between SHOW statements and INFORMATION_SCHEMA tables
- Functionality of the mysqlshow client program
- Leveraging INFORMATION_SCHEMA queries to generate shell commands and SQL statements
Transactions and Locking
- Using transaction control statements to execute concurrent SQL operations
- The ACID properties underlying transactions
- Transaction isolation levels
- Applying locking mechanisms to safeguard transactions
Storage Engines
- Overview of storage engines within MySQL
- Details on the InnoDB storage engine
- InnoDB system and file-per-table tablespaces
- NoSQL integration and the Memcached API
- Efficient configuration of tablespaces
- Achieving referential integrity through foreign keys
- InnoDB locking mechanisms
- Features of various available storage engines
Partitioning
- Application of partitioning in MySQL
- Motivations for implementing partitioning
- Various types of partitioning
- Creation of partitioned tables
- Concepts of subpartitioning
- Retrieving partition metadata
- Adjusting partitions to enhance performance
- Partitioning support across different Storage Engines
User Management
- Standards for user authentication
- Utilizing SHOW PROCESSLIST to monitor active threads
- Processes for creating, updating, and removing user accounts
- Exploring alternative authentication plugins
- Criteria for user authorization
- Hierarchy of access privileges for users
- Categories of privileges
- Granting, adjusting, and revoking user privileges
Security
- Identifying common security threats
- Security vulnerabilities specific to MySQL deployments
- Countermeasures for network, OS, filesystem, and user-related security issues
- Strategies for data protection
- Implementing SSL for secure MySQL server connections
- Using SSH for secure remote MySQL server access
- Resources for further security information
Table Maintenance
- Categories of table maintenance tasks
- SQL commands for table maintenance
- Client and utility software for maintenance
- Maintaining tables in alternative storage engines
- Processes for exporting and importing data
- Techniques for data export
- Techniques 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 of event execution
MySQL Backup and Recovery
- Fundamentals of backup procedures
- Different backup types
- Available backup tools and utilities
- Creating binary and text-based backups
- The role of log and status files in the backup process
- Data recovery procedures
Replication
- Administration of the MySQL Binary Log
- Understanding MySQL replication threads and files
- Establishing a MySQL Replication Environment
- Architecting complex replication topologies
- Implementing Multi-Master and Circular Replication
- Executing controlled failovers
- Monitoring and resolving issues in MySQL Replication
- Replication using Global Transaction Identifiers (GTIDs)
Introduction to Performance Tuning
- Utilizing EXPLAIN for query analysis
- General strategies for table optimization
- Monitoring status variables that impact performance
- Configuration and interpretation of MySQL server variables
- Overview of the Performance Schema
Conclusion
Q&A Session
Requirements
There are no strict prerequisites, though prior familiarity with database concepts is beneficial.
Audience:
IT professionals aiming to become DBAs or database support specialists working with MySQL on Linux or Windows platforms.
Format: 40% theoretical instruction, 60% practical hands-on labs
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.
