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 services
- MySQL service offerings and support structures
- Supported operating system environments
- Recommended training curriculum paths
- Essential MySQL documentation resources
MySQL Architecture
- The client-server architectural model
- Communication protocols
- Components of the SQL Layer
- Structure of the Storage Layer
- Server integration with storage engines
- Memory and disk space utilization
- The MySQL plug-in interface
System Administration
- Evaluating different MySQL distribution types
- Installation procedures for the MySQL Server
- Anatomy of the MySQL Server installation files
- Procedures for starting and stopping the server
- Process for upgrading MySQL
- Hosting multiple MySQL servers on a single machine
Server Configuration
- Exploring MySQL server configuration parameters
- Understanding system variables
- Managing SQL Modes
- Reviewing available log files
- Implementation of binary logging
Clients and Tools
- Available clients for administrative operations
- MySQL-specific administrative clients
- Usage of the mysql command-line client
- Usage of the mysqladmin command-line client
- The MySQL Workbench graphical interface
- Overview of MySQL utility tools
- Available APIs, drivers, and connectors
Data Types
- Primary categories of data types
- The significance of NULL values
- Understanding column attributes
- Interaction between character sets and data types
- Selecting the optimal data type for use cases
Obtaining Metadata
- Methods for accessing metadata
- The structure of INFORMATION_SCHEMA
- Utilizing commands to retrieve metadata
- Distinctions between SHOW statements and INFORMATION_SCHEMA
- The mysqlshow client utility
- Generating shell commands and SQL statements from INFORMATION_SCHEMA queries
Transactions and Locking
- Managing concurrent SQL execution via transaction control
- The ACID properties inherent to transactions
- Understanding transaction isolation levels
- Implementing locking mechanisms for transaction protection
Storage Engines
- The role of storage engines in MySQL
- In-depth look at the InnoDB storage engine
- InnoDB system vs. file-per-table tablespaces
- Integration of NoSQL and the Memcached API
- Strategies for efficient tablespace configuration
- Achieving referential integrity through foreign keys
- Locking mechanisms within InnoDB
- Features of various available storage engines
Partitioning
- The application of partitioning in MySQL
- Advantages and reasons for using partitioning
- Different types of partitioning strategies
- Procedure for creating partitioned tables
- Concepts of subpartitioning
- Retrieving partition metadata
- Adjusting partitions to enhance performance
- Partitioning support across storage engines
User Management
- Standards for user authentication
- Monitoring active threads using SHOW PROCESSLIST
- Management of user accounts: creation, modification, and deletion
- Exploring alternative authentication plugins
- Requirements for user authorization
- Hierarchies of user access privileges
- Categorization of privilege types
- Granting, altering, and revoking user privileges
Security
- Identifying prevalent security threats
- Security risks unique to MySQL installations
- Countermeasures for network, OS, filesystem, and user-related vulnerabilities
- Strategies for data protection
- Securing MySQL server connections with SSL
- Enabling secure remote access via SSH
- Locating resources for common security issues
Table Maintenance
- Categories of table maintenance tasks
- SQL commands for maintaining tables
- Client and utility programs for maintenance
- Maintenance procedures for alternative storage engines
- Data export and import processes
- Techniques for exporting data
- Techniques for importing data
Programming Inside MySQL
- Development and execution of Stored Routines
- Security considerations for stored routine execution
- Creating and managing triggers
- Creation, modification, and removal of events
- Scheduling the execution of events
MySQL Backup and Recovery
- Fundamentals of backup operations
- Different types of backup strategies
- Essential 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 replication threads and associated files
- Establishing a MySQL Replication Environment
- Architecting complex replication topologies
- Implementing Multi-Master and Circular Replication
- Executing controlled failover or switchover
- Monitoring and troubleshooting replication issues
- Utilizing Global Transaction Identifiers (GTIDs) for replication
Introduction to Performance Tuning
- Query analysis using EXPLAIN
- General optimization techniques for tables
- Tracking status variables impacting performance
- Configuration and interpretation of MySQL server variables
- Introduction to the Performance Schema
Conclusion
Q&A Session
Requirements
While no formal prerequisites are required, prior familiarity with database concepts is beneficial.
Target Audience:
IT professionals aspiring to specialize as Database Administrators (DBAs) or database support engineers working with MySQL on Linux or Windows platforms.
Delivery Format: 40% theoretical instruction, 60% hands-on practical 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.
