MySQL DBA

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