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 available services
- Support options and service levels for MySQL
- Compatible operating systems
- Recommended training pathways and curriculum structure
- Accessing essential MySQL documentation resources
MySQL Architecture
- Understanding the client-server model
- Details on communication protocols
- Structure and function of the SQL Layer
- Components of the Storage Layer
- How the server interacts with and supports storage engines
- Memory and disk space management in MySQL
- Overview of the MySQL plug-in interface
System Administration
- Selecting the appropriate MySQL distribution type
- Steps for installing the MySQL Server
- Understanding the server installation file structure
- Procedures for starting and stopping the MySQL server
- Methods for upgrading MySQL instances
- Deploying multiple MySQL servers on a single host
Server Configuration
- Configuring MySQL server options
- Managing system variables
- Configuring SQL Modes
- Reviewing available log files
- Setting up binary logging
Clients and Tools
- Overview of available clients for administrative tasks
- Introduction to MySQL administrative clients
- Using the mysql command-line client
- Utilizing the mysqladmin command-line client
- Working with the MySQL Workbench graphical interface
- Exploring other MySQL utility tools
- Available APIs, drivers, and connectors
Data Types
- Primary categories of data types
- Interpreting the meaning of NULL values
- Defining column attributes
- Applying character sets to data types
- Selecting the most appropriate data type for specific use cases
Obtaining Metadata
- Methods for accessing metadata
- Examining the structure of INFORMATION_SCHEMA
- Using commands to view metadata details
- Comparing SHOW statements with INFORMATION_SCHEMA tables
- Utilizing the mysqlshow client program
- Leveraging INFORMATION_SCHEMA queries to generate shell commands and SQL statements
Transactions and Locking
- Executing multiple SQL statements concurrently using transaction control statements
- Understanding the ACID properties of transactions
- Managing transaction isolation levels
- Implementing locking mechanisms to protect transactions
Storage Engines
- Overview of storage engines in MySQL
- Deep dive into the InnoDB storage engine
- Configuring InnoDB system and file-per-table tablespaces
- NoSQL integration and the Memcached API
- Efficient configuration of tablespaces
- Using foreign keys to ensure referential integrity
- InnoDB locking mechanisms
- Features and capabilities of available storage engines
Partitioning
- Application of partitioning in MySQL
- Benefits and reasons for using partitioning
- Different types of partitioning strategies
- Creating partitioned tables
- Implementing subpartitioning
- Retrieving partition metadata
- Modifying partitions to enhance performance
- Partitioning support across various Storage Engines
User Management
- Authentication requirements for user accounts
- Using SHOW PROCESSLIST to monitor active threads
- Creating, modifying, and deleting user accounts
- Implementing alternative authentication plugins
- Authorization requirements for users
- Defining levels of access privileges
- Understanding different types of privileges
- Granting, altering, and revoking user privileges
Security
- Identifying common security risks
- Addressing security risks specific to MySQL installations
- Mitigating security issues related to networks, operating systems, file systems, and user accounts
- Strategies for protecting data
- Implementing SSL for secure MySQL server connections
- Using SSH for secure remote connections to the MySQL server
- Resources for investigating additional common security issues
Table Maintenance
- Types of table maintenance operations
- SQL statements used for table maintenance
- Client and utility programs for maintenance tasks
- Maintaining tables in non-InnoDB storage engines
- Processes for exporting and importing data
- Techniques for exporting data
- Methods for importing data
Programming Inside MySQL
- Creating and executing Stored Routines
- Managing security for stored routine execution
- Defining and executing triggers
- Creating, modifying, and removing events
- Scheduling event execution
MySQL Backup and Recovery
- Fundamentals of backup processes
- Different types of backups
- Available backup tools and utilities
- Creating binary and text-based backups
- The role of log and status files in backup integrity
- Data recovery procedures
Replication
- Managing the MySQL Binary Log
- Understanding MySQL replication threads and associated files
- Configuring a MySQL Replication Environment
- Designing complex replication topologies
- Implementing Multi-Master and Circular Replication
- Executing controlled failovers or switchovers
- Monitoring and troubleshooting MySQL Replication
- Replication using Global Transaction Identifiers (GTIDs)
Introduction to Performance Tuning
- Analyzing queries using EXPLAIN
- General strategies for table optimization
- Monitoring status variables that impact performance
- Configuring and interpreting MySQL server variables
- Overview of the Performance Schema
Conclusion
Q&A Session
Requirements
While there are no strict prerequisites, it is beneficial if students possess some prior knowledge of database systems.
Audience:
IT professionals aiming to become DBAs or database support specialists for MySQL on Linux or Windows platforms.
Format: 40% theoretical/lectures, 60% practical/hands-on lab 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.
