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 Products and Services
- MySQL Services and Support Options
- Supported Operating Systems
- Training Curriculum Paths
- Accessing MySQL Documentation Resources
MySQL Architecture
- Understanding the client/server model
- Communication protocols used by MySQL
- The SQL processing layer
- The storage layer
- How the server interfaces with storage engines
- Memory and disk space utilization strategies
- The MySQL plugin interface
System Administration
- Selecting the appropriate MySQL distribution type
- Installing the MySQL Server
- Understanding the MySQL Server installation file structure
- Starting and stopping the MySQL server
- Performing MySQL upgrades
- Running multiple MySQL server instances on a single host
Server Configuration
- MySQL server configuration options
- System variables
- SQL Modes
- Overview of available log files
- Binary logging implementation
Clients and Tools
- Available clients for administrative tasks
- MySQL administrative client utilities
- The mysql command-line client
- The mysqladmin command-line client
- The MySQL Workbench graphical interface
- Additional MySQL tools
- Available APIs (drivers and connectors)
Data Types
- Major categories of data types
- Understanding NULL values
- Column attributes
- Applying character sets with data types
- Strategies for selecting appropriate data types
Obtaining Metadata
- Methods for accessing metadata
- Structure of the INFORMATION_SCHEMA
- Using commands to view metadata
- Differences between SHOW statements and INFORMATION_SCHEMA tables
- Utilizing the mysqlshow client program
- Leveraging INFORMATION_SCHEMA queries to generate shell commands and SQL statements
Transactions and Locking
- Using transaction control statements to execute multiple SQL statements concurrently
- The ACID properties of transactions
- Transaction isolation levels
- Utilizing locking mechanisms to protect transactions
Storage Engines
- Storage engines available in MySQL
- InnoDB storage engine details
- InnoDB system and file-per-table tablespaces
- NoSQL capabilities and the Memcached API
- Efficient configuration of tablespaces
- Using foreign keys to ensure referential integrity
- InnoDB locking mechanisms
- Feature comparison of available storage engines
Partitioning
- Partitioning concepts and usage in MySQL
- Benefits of using partitioning
- Types of partitioning
- Creating partitioned tables
- Subpartitioning strategies
- Retrieving partition metadata
- Modifying partitions to enhance performance
- Storage engine support for partitioning
User Management
- Requirements for user authentication
- Using SHOW PROCESSLIST to monitor active threads
- Creating, modifying, and dropping user accounts
- Alternative authentication plugins
- Requirements for user authorization
- Access privilege levels for users
- Types of privileges
- Granting, modifying, and revoking user privileges
Security
- Identifying common security risks
- Security risks specific to MySQL installations
- Addressing security issues and countermeasures for network, OS, filesystem, and users
- Protecting sensitive data
- Using SSL for secure MySQL server connections
- Leveraging SSH for secure remote MySQL server access
- Resources for resolving common security issues
Table Maintenance
- Types of table maintenance operations
- SQL statements for table maintenance
- Client and utility programs for maintenance tasks
- Maintaining tables for various storage engines
- Data Export and Import
- Exporting Data
- Importing Data
Programming Inside MySQL
- Creating and executing Stored Routines
- Security aspects of stored routine execution
- Creating and executing triggers
- Creating, altering, and dropping events
- Scheduling event execution
MySQL Backup and Recovery
- Backup fundamentals
- Types of backups
- Backup tools and utilities
- Creating binary and text backups
- The role of log and status files in backups
- Data Recovery strategies
Replication
- Managing the MySQL Binary Log
- MySQL replication threads and files
- Setting up a MySQL Replication Environment
- Designing Complex Replication Topologies
- Multi-Master and Circular Replication
- Performing a Controlled Switchover
- Monitoring and Troubleshooting MySQL Replication
- Replication with Global Transaction Identifiers (GTIDs)
Introduction to Performance Tuning
- Using EXPLAIN to Analyze Queries
- General Table Optimizations
- Monitoring status variables that impact performance
- Setting and Interpreting MySQL server Variables
- Overview of the Performance Schema
Conclusion
Q&A Session
Requirements
No strict prerequisites are required, although having a foundational understanding of databases is beneficial.
Target Audience:
IT professionals aiming to transition into Database Administrator (DBA) or database support roles within MySQL environments on Linux or Windows platforms.
Format: 40% theoretical instruction, 60% practical hands-on lab exercises
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.
