Course Content
MySQL Architecture
- Describe the client/server model
- Understand communication protocols
- Understand how the server supports storage engines
- Explain the basics of how MySQL uses memory and disk space
System Administration
- Choose between types of MySQL distributions
- Install the MySQL Server
- Describe the MySQL Server installation file structure
- Start and stop the MySQL server
- Upgrade MySQL
- Run multiple MySQL servers on a single host
Server Configuration
- Set up MySQL server configuration files
- Explain the purpose of dynamic server variables
- Review the server status variables available
- Configure operational characteristics of the MySQL server
- Describe the available log files
- Explain binary logging
Clients and Tools
- Describe the available clients for administrative tasks
- Use MySQL administrative clients
- Use the mysql command line clients
- Use the mysqladmin for administrative tasks
- Describe available MySQL tools
- List the available APIs (drivers and connectors)
Data Types
- Describe the major categories of data types
- Explain the meaning of NULL
- Describe column attributes
- Explain character set usage with data types
Choose an appropriate data type
- Obtaining Metadata
- List the various metadata access methods available
- Recognize the structure of the INFORMATION_SCHEMA database schema
- Use the available commands to view metadata
- Describe differences between SHOW statements and INFORMATION_SCHEMA tables
- Use the mysqlshow client program
- Use INFORMATION_SCHEMA to create shell commands and SQL statements
Transaction and Locking
- Use transaction control statement to run multiple SQL statements concurrently
- Explain the ACID properties
- Describe the transaction isolation levels
- Use locking to protect transactions
InnoDB Storage Engine
- Describe the InnoDB storage engine
- Set the storage engine to InnoDB
- Illustrate the InnoDB tablespace storage system
- Efficiently configure the tablespace
- Use foreign keys to attain referential integrity
- Explain InnoDB locking
Other Storage Engines
- Explain the general purpose of storage engines in MySQL
- List the storage engines available for MySQL
- Describe the key features of the MyISAM storage engine
- Describe the key features of the MEMORY storage engine
- Give an overview of other storage engines: FEDERATED, ARCHIVE,BLACKHOLE, NDBCLUSTER
- Choose an appropriate storage engine
Partitioning
- Define partitioning and its particular use in MySQL
- Determine server partitioning support
- List the reasons for using partitioning
- Explain the types of partitioning
- Create partitioned tables
- Describe sub partitioning
- Obtain partitioning metadata
- Use partitioning to improve performance
User Management
- Depict the user connection and query process
- List requirements for user authentication
- Use SHOW PROCESSLIST to show which threads are running
- Create, modify and drop user accounts
- List requirements for user authorization
- Describe the levels of access privileges for users
- List the types of privileges
- Grant, modify and revoke user privileges
Security
- Recognize common security risks
- Describe security risks specific to the MySQL installation
- List security problems and counter-measures for network, operating system, filesystem and users
- Protect your data
- Use SSL for secure MySQL server connections
- Explain how SSH enables a secure remote connection to the MySQL server
- Find additional information for common security issues
Table Maintenance
- Recognize types of table maintenance operations
- Execute SQL statements for table maintenance
- Client and utility programs for table maintenance
- Maintain tables according to specific storage engines
Exporting and Importing Data
- Exporting Data using SQL
- Importing Data using SQL
MySQL Backup and Recovery
- Describing backup basics
- Types of backups
- Backup tools and utilities
- Making binary and text backups
- Role of log and status files in backups
- Data Recovery
Introduction to Replication
- Describing MySQL Replication
- Managing the MySQL Binary Log
- Explaining MySQL replication threads and files
- Setting up a MySQL Replication Environment
- Monitoring MySQL Replication
- Troubleshooting MySQL Replication
Introduction to Performance Tuning
- Using EXPLAIN to Analyze Queries
- General Table Optimizations
- Monitoring status variables that affect performance
- Setting and Interpreting MySQL server Variables
