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 products and services
- MySQL services and support options
- Supported operating systems
- Training curriculum paths
- Resources for MySQL documentation
MySQL Architecture
- The client/server model
- Communication protocols
- The SQL layer
- The storage layer
- How the server supports storage engines
- MySQL's use of memory and disk space
- The MySQL plug-in interface
System Administration
- Selecting appropriate MySQL distributions
- Installing the MySQL server
- Structure of the MySQL server installation files
- Starting and stopping the MySQL server
- Upgrading MySQL
- Running multiple MySQL servers on a single host
Server Configuration
- MySQL server configuration options
- System variables
- SQL modes
- Available log files
- Binary logging
Clients and Tools
- Clients available for administrative tasks
- MySQL administrative clients
- The mysql command-line client
- The mysqladmin command-line client
- The MySQL Workbench graphical client
- MySQL tools
- Available APIs (drivers and connectors)
Data Types
- Major categories of data types
- Understanding NULL
- Column attributes
- Using character sets with data types
- Selecting appropriate data types
Obtaining Metadata
- Methods for accessing metadata
- Structure of INFORMATION_SCHEMA
- Using available commands to view metadata
- Differences between SHOW statements and INFORMATION_SCHEMA tables
- The mysqlshow client program
- Using INFORMATION_SCHEMA queries to generate shell commands and SQL statements
Transactions and Locking
- Using transaction control statements to run multiple SQL statements concurrently
- ACID properties of transactions
- Transaction isolation levels
- Using locking to protect transactions
Storage Engines
- Storage engines in MySQL
- InnoDB storage engine
- InnoDB system and file-per-table tablespaces
- NoSQL and the Memcached API
- Efficiently configuring tablespaces
- Using foreign keys for referential integrity
- InnoDB locking mechanisms
- Features of available storage engines
Partitioning
- Partitioning and its application in MySQL
- Reasons for using partitioning
- Types of partitioning
- Creating partitioned tables
- Subpartitioning
- Obtaining partition metadata
- Modifying partitions to enhance performance
- Storage engine support for partitioning
User Management
- Requirements for user authentication
- Using SHOW PROCESSLIST to display running threads
- Creating, modifying, and dropping user accounts
- Alternative authentication plugins
- Requirements for user authorization
- Levels of access privileges for users
- Types of privileges
- Granting, modifying, and revoking user privileges
Security
- Recognizing common security risks
- Security risks specific to MySQL installations
- Security issues and counter-measures for network, OS, filesystem, and users
- Protecting your data
- Using SSL for secure MySQL server connections
- How SSH enables secure remote connections to the MySQL server
- Resources for addressing common security issues
Table Maintenance
- Types of table maintenance operations
- SQL statements for table maintenance
- Client and utility programs for table maintenance
- Maintaining tables for other storage engines
- Exporting and importing data
- 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 basics
- Types of backup
- Backup tools and utilities
- Creating binary and text backups
- The role of log and status files in backups
- Data recovery
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 affect performance
- Setting and interpreting MySQL server variables
- Overview of the Performance Schema
Conclusion
Q&A Session
Requirements
No specific prerequisites are required, though prior knowledge of databases is beneficial for students.
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.
