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, including its products and services
- Details on MySQL services and support structures
- Supported operating systems and environments
- Recommended training curriculum paths
- Essential MySQL documentation resources
MySQL Architecture
- The client-server model and its fundamentals
- Communication protocols used in MySQL
- Understanding the SQL Layer
- The structure of the Storage Layer
- How the server integrates and supports various storage engines
- Utilization of memory and disk space by MySQL
- The MySQL plug-in interface mechanism
System Administration
- Selecting the appropriate MySQL distribution type
- Procedures for installing the MySQL Server
- Understanding the MySQL Server installation file structure
- Methods for starting and stopping the MySQL server
- Processes involved in upgrading MySQL
- Running multiple MySQL instances on a single host
Server Configuration
- Exploring MySQL server configuration options
- Managing system variables
- Understanding and applying SQL Modes
- Review of available log files
- Implementation of binary logging
Clients and Tools
- Available clients for performing administrative tasks
- Overview of MySQL administrative clients
- Usage of the mysql command-line client
- Usage of the mysqladmin command-line client
- The MySQL Workbench graphical interface
- Essential MySQL tools
- Available APIs, including drivers and connectors
Data Types
- Primary categories of data types
- The concept and significance of NULL values
- Understanding column attributes
- Application of character sets with data types
- Criteria for choosing the most suitable data type
Obtaining Metadata
- Methods available for accessing metadata
- The structure of INFORMATION_SCHEMA
- Utilizing commands to view metadata
- Distinguishing between SHOW statements and INFORMATION_SCHEMA tables
- Use 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 multiple SQL statements concurrently
- The ACID properties governing transactions
- Understanding transaction isolation levels
- Employing locking mechanisms to safeguard transactions
Storage Engines
- Overview of storage engines in MySQL
- Deep dive into the InnoDB storage engine
- InnoDB system and file-per-table tablespaces
- NoSQL concepts and the Memcached API
- Efficient configuration of tablespaces
- Achieving referential integrity using foreign keys
- InnoDB locking mechanisms
- Key features of available storage engines
Partitioning
- The role of partitioning in MySQL
- Reasons for implementing partitioning
- Different types of partitioning strategies
- Creating partitioned tables
- The concept of subpartitioning
- Retrieving partition metadata
- Modifying partitions to enhance performance
- Storage Engine Support for Partitioning
User Management
- Requirements for user authentication
- Using SHOW PROCESSLIST to monitor running threads
- Operations for creating, modifying, and dropping user accounts
- Alternative authentication plugins
- Requirements for user authorization
- Hierarchical levels of user access privileges
- Categorization of privilege types
- Granting, modifying, and revoking user privileges
Security
- Identifying common security risks
- Security risks specific to MySQL installations
- Security challenges and counter-measures for networks, operating systems, filesystems, and users
- Strategies for protecting data
- Implementing SSL for secure MySQL server connections
- Enabling secure remote connections via SSH
- Resources for finding additional information on common security issues
Table Maintenance
- Varieties of table maintenance operations
- SQL statements used for table maintenance
- Client and utility programs for maintaining tables
- Maintenance practices for other storage engines
- Data Export and Import processes
- Techniques for exporting data
- Techniques for importing data
Programming Inside MySQL
- Creating and executing Stored Routines
- Understanding security considerations for stored routine execution
- Developing and executing triggers
- Managing events: creating, altering, and dropping
- Scheduling event execution
MySQL Backup and Recovery
- Fundamentals of backup strategies
- Different types of backups
- Essential backup tools and utilities
- Creating binary and text backups
- The role of log and status files in backup processes
- Data Recovery procedures
Replication
- Managing the MySQL Binary Log
- Understanding MySQL replication threads and files
- Setting up a MySQL Replication Environment
- Designing Complex Replication Topologies
- Implementing Multi-Master and Circular Replication
- Executing a Controlled Switchover
- Monitoring and Troubleshooting MySQL Replication
- Replication using Global Transaction Identifiers (GTIDs)
Introduction to Performance Tuning
- Utilizing EXPLAIN to analyze query performance
- General strategies for table optimization
- Monitoring status variables that impact performance
- Configuring and interpreting MySQL server variables
- An overview of the Performance Schema
Conclusion
Q&A Session
Requirements
No specific prerequisites are required, although prior knowledge of databases is beneficial for students.
Audience:
IT professionals aspiring to become Database Administrators (DBAs) or database support specialists working with MySQL on Linux and Windows platforms.
Format: 40% theoretical lectures, 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.
