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.
Duration 28 hours
Course Outline
Introduction
- Overview of MySQL, its products, and services
- MySQL service offerings and support channels
- Supported operating systems
- Recommended training curriculum paths
- Accessing MySQL documentation resources
MySQL Architecture
- Understanding the client-server model
- Communication protocols
- The SQL processing layer
- The storage layer
- Server support for storage engines
- Memory and disk space utilization in MySQL
- The MySQL plug-in interface
System Administration
- Selecting the appropriate MySQL distribution
- Installing the MySQL server
- Understanding the MySQL server file structure
- Starting and stopping the MySQL server
- Upgrading the MySQL instance
- Running multiple MySQL instances on a single host
Server Configuration
- Exploring MySQL server configuration options
- Managing system variables
- Configuring SQL modes
- Reviewing available log files
- Implementing binary logging
Clients and Tools
- Available clients for administrative tasks
- Overview of MySQL administrative clients
- Using the mysql command-line client
- Using the mysqladmin command-line client
- Working with the MySQL Workbench graphical interface
- Utilizing various MySQL tools
- Available APIs, drivers, and connectors
Data Types
- Major categories of data types
- Understanding the meaning of NULL
- Column attributes
- Character set usage with data types
- 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
- Using the mysqlshow client program
- Leveraging INFORMATION_SCHEMA queries to generate shell commands and SQL statements
Transactions and Locking
- Using transaction control statements for concurrent SQL execution
- The ACID properties of transactions
- Transaction isolation levels
- Employing locking mechanisms to protect transactions
Storage Engines
- Overview of storage engines in MySQL
- The InnoDB storage engine
- InnoDB system and file-per-table tablespaces
- NoSQL considerations and the Memcached API
- Efficiently configuring tablespaces
- Achieving referential integrity using foreign keys
- InnoDB locking mechanisms
- Features of available storage engines
Partitioning
- Partitioning concepts and their application in MySQL
- Reasons for implementing partitioning
- Different types of partitioning
- Creating partitioned tables
- Subpartitioning
- Retrieving partition metadata
- Modifying partitions to enhance performance
- Partitioning support across storage engines
User Management
- Requirements for user authentication
- Using SHOW PROCESSLIST to monitor running threads
- Creating, modifying, and dropping user accounts
- Alternative authentication plugins
- Requirements for user authorization
- Hierarchical levels of user access privileges
- Categories of privileges
- Granting, modifying, and revoking user privileges
Security
- Identifying common security risks
- Security risks specific to MySQL installations
- Countermeasures for network, operating system, filesystem, and user security issues
- Strategies for data protection
- Using SSL for secure MySQL server connections
- Enabling secure remote connections via SSH
- Locating additional resources for common security concerns
Table Maintenance
- Types of table maintenance operations
- SQL statements for table maintenance
- Client and utility programs for maintaining tables
- Maintenance procedures for other storage engines
- Exporting and importing data
- Exporting data
- Importing data
Programming Inside MySQL
- Creating and executing stored routines
- Security considerations for stored routine execution
- Creating and executing triggers
- Creating, altering, and dropping events
- Scheduling event execution
MySQL Backup and Recovery
- Fundamentals of backup
- Types of backups
- Backup tools and utilities
- Performing binary and text backups
- The role of log and status files in backups
- Data recovery procedures
Replication
- Managing the MySQL binary log
- MySQL replication threads and files
- Setting up a MySQL replication environment
- Designing complex replication topologies
- Implementing multi-master and circular replication
- Performing a controlled switchover
- Monitoring and troubleshooting MySQL replication
- Replication using Global Transaction Identifiers (GTIDs)
Introduction to Performance Tuning
- Analyzing queries using EXPLAIN
- General table optimization techniques
- Monitoring status variables that impact performance
- Setting and interpreting MySQL server variables
- Overview of the Performance Schema
Conclusion
Q&A Session
Requirements
While there are no strict prerequisites, having foundational knowledge of databases is beneficial.
Audience:
This course is designed for IT professionals aspiring to become Database Administrators (DBAs) or database support specialists working with MySQL on Linux and Windows platforms.
Format: 40% theoretical instruction, 60% hands-on practical labs
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.
