Detailed Course Outline
Course Contents - DAY 1
Course Introduction
- Administration and Course Materials
- Course Structure and Agenda
- Delegate and Trainer Introductions
Session 1: CLIENTS AND TOOLS
- Introduction
- MySQL
- MySQL Admin
- Exercises: Using Client Programs
Session 2: OBTAINING METADATA
- What is Metadata?
- The mysqlshow Client Program
- The SHOW and DESCRIBE Commands
- The Information_Schema
- Show Command and Information_Schema Differences
- Exercises: Obtaining Information about MySQL
Session 3: CONFIGURING THE SERVER
- MySQL Server Options and Variables
- MySQL Status Variables
- Configuring MySQL Enterprise Audit
- Exercises: Configuring the Server
Session 4: MYSQL SERVER INSTALLATION AND CONFIGURATION
- MySQL Distributions
- Installing on Windows
- Installing on Linux and UNIX
- Starting and Stopping on Windows
- Starting and Stopping on UNIX/Linux
- Status Files
- Upgrading the Server
- Time Zone Tables
- Exercises: Installing, stopping and starting
Session 5: SQL MODES, LOG FILES AND BINARY LOGGING
- MySQL Error Messages
- The SHOW Errors Statement
- The SHOW Warnings Statement
- SQL Modes
- Note Messages
- The PERROR Utility
- The General Log
- The Error Log
- The Slow Query Log
- The Binary Logs
- Exercises: Interpreting Errors and Configuring the Logs
Course Contents - DAY 2
Session 6: MYSQL ARCHITECTURE
- Client/Server Overview
- Communication Protocols
- The SQL Parser and Storage Engine Tiers
- How MySQL Uses Disk Space
- How MYSQL Uses Memory
- Exercises: Examining the Architecture
Session 7: TABLES, DATA TYPES AND CHARACTER SET SUPPORT
- Table Properties
- Creating Tables
- Altering Tables
- Dropping Tables
- Emptying Tables
- Obtaining Table Metadata
- Column Attributes
- Bit Data Type
- Numeric Data Types
- Character String Data Types
- Binary String Data Types
- Enum and Set Data Types
- Temporal Data Types
- Auto_Increment
- Handling Missing or Invalid Data Values
- Exercises: Creating and Maintaining Tables
Session 8: TRANSACTIONS AND LOCKING
- Locking Concepts
- Explicit Table Locking
- Advisory Locking
- Exercises: Locking
Session 9: STORAGE ENGINES
- Introduction
- The MYISAM Engine
- The Merge Engine
- Other Engines: Archive, Memory, Federated, Blackhole, CSV
- Cluster Engine Overview
- Overview of High Availability Techniques
- Memcached and NoSQL Overview
- Exercises: Using Storage Engines
Course Contents - DAY 3
Session 10: THE INNODB ENGINE
- Introduction
- Features of Innodb
- Transactions
- Referential Integrity
- Physical Characteristics of Innodb Tables
- System Tablespace Configuration
- Log File and Buffer Configuration
- Innodb Status
- Exercises: Using the InnoDB Engine
Session 11: TABLE MAINTENANCE
- Table Maintenance Operations
- Check Table
- Repair Table
- Analyze Table
- Optimize Table
- MySQL Check
- MYISAMCHK
- Repairing Innodb Tables
- Enabling MYISAM AutoRepair
- Exercises: Maintaining Tables
Session 12: BACKUP AND RECOVERY
- The Advantages and Disadvantages of Different Methods
- Binary Backups of MYISAM Tables
- Binary Backups of Innodb Tables
- Recovery
- Import and Export Operations
- Exporting Using SQL
- Importing Using SQL
- Exporting from the Command Line using mysqldump
- Importing from the Command Line using mysqlimport
- Exercises: Backing Up and Recovery
Session 13: USER MANAGEMENT
- Introduction
- User Accounts
- Creating Users
- Renaming Users
- Changing Passwords
- Dropping Users
- Granting Privileges
- The User Table
- Connection Validation
- Exercises: Creating, Managing and Dropping Users
Course Contents - DAY 4
Session 14: PRIVILEGES
- Introduction
- Types of Privileges
- Revoking Privileges
- Resource Limits
- The MySQL Database
- The Show Grants Command
- Exercises: Granting and Revoking Privileges
Session 15: USER VARIABLES AND PREPARED STATEMENTS
- User Variables
- Prepared Statements
- Exercises: User Variables and Prepared Statements
Session 16: STORED ROUTINES FOR ADMINISTRATION
- Types of Stored Routines
- Benefits of Stored Routines
- Stored Routines Features
- Stored Routine Maintenance
- Obtaining Stored Routine Metadata
- Stored Routine Privileges and Execution Security
Session 17: TRIGGERS
- DML Triggers
Session 18: SECURITY
- Security Risks
- Users, Operating System, File System and Network Security
- Using SSL With MySQL
- Remote Connecting to the MySQL Server Using SSH
- Exercises: Securing the Server
Session 19: TUNING QUERIES
- Tuning Overview
- Identifying Candidates for Query Analysis
- Using Explain to Analyze Queries
- Meaning of Explain Output
- Explain Extended
- Exercises: Explaining and Tuning Queries
Session 20: TUNING AND INDEXES (1)
- Indexes for Performance
- Creating and Dropping Indexes
- Obtaining Index Metadata
- Indexing Principles
- Exercises: Creating and Using Indexes
Session 21: TUNING AND INDEXES (2)
- Indexing and Joins
- Fulltext Indexes
- MyISAM Index Caching
- Exercises: Creating and Using Indexes
Session 22: TUNING AND TABLES
- General Table Optimizations
- Myisam Specific Optimizations
- Innodb Specific Optimizations
- Other Engine Specific Optimizations
- Exercises: Tuning Tables
Course Contents - DAY 5 Session 23: TUNING THE SERVER
- Status Variables
- Server Variables
- Performance Schema Overview
- The Query Cache
- Exercises: Tuning the Server
Session 24: THE EVENT SCHEDULER
- Event scheduler concepts
- Event scheduler configuration
- Creating, altering and dropping events
- Event scheduler monitoring
- Events and privileges
- Exercises: Using the event scheduler
Session 25: PARTITIONED TABLES
- Partitioned tables concepts
- Obtaining Partitioned Table Metadata
- Types of Partitioning
- Subpartitioning
- Maintenance of partitioned tables
- Exercises: Using partitioned tables
Session 26: REPLICATION
- Replication Overview
- Testing Replication
- Monitoring and Troubleshooting Replication
- Files and Threads Involved in Replication
- Excluding Databases or Tables From Replication
- Complex Replication Topologies
- Example: Setting Up a Master Slave Replication
- Replication Using GTIDs
- MySQL Replication Utilities
- Controlled Switchover
- Exercises: Setting Up and Testing Replication
Session 27: MYSQL WORKBENCH
- Installation
- Connecting
- Screens
- Exercises: Using MySQL Workbench