MySQL Database Administration Online Training
Course Introduction CLICK HERE FOR ENQUIRY
- Administration and Course Materials
- Course Structure and Agenda
- Delegate and Trainer Introductions
- Introduction
- Client Program Limitations
- mysql
- MySQL Admin
- Exercises: Using Client Programs
THE INFORMATION_SCHEMA DATABASE
- What is Metadata?
- The mysqlshow Utility
- The SHOW and DESCRIBE Commands
- The Information_Schema Database
- Exercises: Obtaining Information about MySQL
MYSQL ADMINISTRATOR
- Installation
- Connecting
- Server Information
- Service Control
- User Administration
- Privileges
- Health
- Backup and Restore
- Catalogs
- Exercises: Using the MySQL Administrator Tool
INSTALLING, CONFIGURING, STARTING AND STOPPING
- MySQL Distributions
- Installing on Windows
- Installing on Linux and UNIX
- Starting and Stopping on Windows
- Starting and Stopping on UNIX/Linux
- Configuration
- Log and Status Files
- The Default SQL Mode
- Time Zone Tables
- Some Security Issues
- Upgrading
- Exercises: Installing, configuring, stopping and starting
INTERPRETING ERROR AND DIAGNOSTIC INFORMATION
- MySQL Error Messages
- The SHOW Statement
- SQL Modes
- The PERROR Utility
- The Log
- The Error Log
- The Slow Query Log
- Exercises: Interpreting Error and Diagnostic Information
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
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
- Performance Issues with Character sets
- Choosing Data Types for Character Columns
- Exercises: Creating and Maintaining Tables
LOCKING
- Locking Concepts
- Explicit Table Locking
- Advisory Locking
- Preventing Locking Problems
- Exercises: Locking
STORAGE ENGINES
- Introduction
- The MYISAM Engine
- Locking with MYISAM Tables
- The Merge Engine
- Other Engines: Archive, Memory, Federated, Blackhole, NDBCluster
- Exercises Using Storage Engines
THE INNODB ENGINE
- Introduction
- Features of Innodb
- Transactions
- Referential Integrity
- Physical Characteristics of Innodb Tables
- Tablespace Configuration
- Log File and Buffer Configuration
- Innodb Status
- Exercises Using the InnoDB Engine
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
BACKUP AND RECOVERY
- Planning and Implementing a Backup and Recovery Strategy
- Defining a Disaster Recovery Plan
- Testing a Backup and Recovery Plan
- 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
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
PRIVILEGES
- Introduction
- Types of Privileges
- Revoking Privileges
- Resource Limits
- The MySQL Database
- The Show Grants Command
- Exercises: Granting and Revoking Privileges
USER VARIABLES AND PREPARED STATEMENTS
- User Variables
- Prepared Statements
- Exercises: User Variables and Prepared Statements
STORED ROUTINES FOR ADMINISTRATION
- Types of Stored Routines
- Benefits of Stored Routines
- Stored Routines Features
- Stored Routine Maintenance
- Stored Routine Privileges and Execution Security
- Exercises: Creating and Using Stored Routine
- DML Triggers
- The Create Trigger Statement
- Managing Triggers
- Exercises: Creating and Using Triggers
- Security Issues
- Operating System Security
- Filesystem Security
- Log Files and Security
- Network Security
- Upgrade-related Security Issues
- Upgrading the Privilege Tables
- Security-Related SQL_Mode Values
- Exercises: Securing the Server
- Optimization Overview
- Optimization Process
- Planning a Routine Monitoring Regime
- Setting Suitable Goals
- Identifying Candidates for Query Analysis
- Using Explain to Analyze Queries
- Meaning of Explain Output
- Using Explain Extended
- Exercises: Explaining and Optimizing Queries
- Indexes for Performance
- Creating and Dropping Indexes
- Obtaining Index Metadata
- Indexing Principles
- Indexing and Joins
- MyIsam Index Caching
- Exercises: Using Indexes for Optimization
- Normalisation
- General Table Optimizations
- Myisam Specific Optimizations
- Innodb Specific Optimizations
- Other Engine Specific Optimizations
- Exercises: Optimizing Schemas
- Measuring Server Load
- System Factors
- Server Parameters
- Query Optimizer Performance
- The Query Cache
- Exercises: Optimizing the Server
- Choosing the Platform
- Hardware Configurations
- Disk Issues on Linux
- Symbolic Links
- Optimizing the Operating System
- Exercises: Optimizing the Environment
- Event scheduler concepts
- Event scheduler configuration
- Creating, altering and dropping events
- Event scheduler monitoring
- Events and privileges
- Exercises: Using the event scheduler]
- Partitioned tables concepts
- Range partitioning
- Hash partitioning
- Key partitioning
- List partitioning
- Composite partitioning or subpartitioning
- Maintenance of partitioned tables
- Exercises: Using partitioned tables
- Using Multiple Servers
- Replication
