MySQL for Administrators

This lesson describes the goals of the course and the classroom learning environment.

Lessons:

• Understand the multiple storage engines available in MySQL

• Maintain the integrity of a MySQL installation

• Maintain and manage MySQL tables

• Understand the DBA’s role concerning stored routines

• Perform backup and restore operations

• Use triggers for administration tasks

• Manage user maintenance and security

• Verify the security of the server

This lesson describes the nature and architecture of the MySQL Server/Client model, MySQL storage engines and memory and disk space usage.

Lessons:

• Describe the MySQL client/server model

• Explain the basics of how MySQL uses memory and disk space

• Understand communication protocols

• Lab:

• Understand how the server supports storage engines

• Quiz – Database Architecture

This lesson explains the available software distributions and the installation and configuration of MySQL.

Lessons:

• Choose the proper type of MySQL server software distribution

• Run multiple MySQL servers on a single host

• Install the MySQL server

• Lab:

• Describe the MySQL server post-installation file structure

• Install the MySQL server

• Start and stop the MySQL serve

• Start the MySQL server

• Upgrade MySQL

• Secure the MySQL server

This lesson explains the concepts of configuring MySQL server through the use of configuration files, server variables, SQL modes and log files.

Lessons:

• Set up MySQL server configuration files

• Explain binary logging

• Explain the purpose of dynamic server variables

• Configure audit logging with MySQL Enterprise Audi

• Review the server status variables that are available

• Lab:

• Configure operational characteristics of the MySQL server by using the SQL modes

• Quiz – Server configuration

• Describe the available log files

• Editing and Creating a Configuration File

• Additional Practice – Server Configuration

This lesson describes the use of various clients and tools for server administration.

Lessons:

• Describe the available clients for administrative tasks

• List the available APIs (drivers and connectors)

• Use MySQL administrative clients

• Lab:

• Use the mysql commandline client

• Invoking the mysql Client

• Use the mysqladmin command-line client for administrative tasks

• Invoking the mysqladmin Client

• Use the MySQL Workbench graphical client

• Viewing the MySQL Enterprise Monitor Demos

• Describe the available MySQL tools

• Performing System Administration Tasks with MySQL Workbench

This lesson explores the data types and character sets available within the MySQL database environment.

Lessons:

• Describe the major categories of data types

• Choose an appropriate data type

• Explain the meaning of NUL

• Lab:

• Describe column attributes

• Quiz – MySQL Data Types

• Explain character set usage with data types

• Setting a Data Type

This lesson explains the use of different interfaces and methods for obtaining database metadata.

Lessons:

• List the available metadata access methods

• Use the INFORMATION_SCHEMA tables to create shell commands and SQL statements

• Recognize the structure of the INFORMATION_SCHEMA database (schema)

• Lab:

• Use the available commands to view metadata

• Obtaining Metadata by Using INFORMATION_SCHEMA

• Describe the differences between SHOW statements and the INFORMATION_SCHEMA tables

• Obtaining Metadata by Using SHOW and DESCRIBE

• Use the mysqlshow client program

• Obtaining Metadata by Using mysqlshow

This lesson describes the use of transactional control statements and the concepts of transaction isolation and protection.

Lessons:

• Use transaction control statements to run multiple SQL statements concurrently

• Lab:

• Explain the ACID properties

• Quiz – Transactions and Locking

• Describe the transaction isolation levels

• Using Transaction Control Statements

• Use locking to protect transactions

• Additional Practice with Transactions and Locking

This lesson describes the general purpose and availability of storage engines within MySQL.

Lessons:

• Explain the general purpose of storage engines in MySQL

• Use foreign keys to attain referential integrity

• List the storage engines available for MySQL

• Explain InnoDB locking

• Describe the InnoDB storage engine

• Summarize other available storage engines

• Set the storage engine to InnoDB

• Lab:

• Illustrate the InnoDB tablespace storage system

• Quiz – InnoDB Storage Engine

• Configure the tablespace efficiently

• Setting and Confirming InnoDB Settings

This lesson describes the nature, implementation and usage of table partitioning within MySQL. Lessons:

• Define partitioning and its particular use in MySQL

• Use partitioning to improve performance

• Determine server partitioning support

• Explain storage engine implementation of partitioning

• List the reasons for using partitioning

• Explain how to lock a partitioned table

• Explain the types of partitioning

• Describe partitioning limitations

• Create partitioned tables

• Lab:

• Describe sub partitioning

• Quiz – MySQL Partitioning

• Obtain partitioning metadata

• Creating and Modifying a Partitioned Table

• Removing Partitions from a Table

This lesson explains creation and management of user accounts and account security.

Lessons:

• Describe the user connection and query process

• Use SHOW PROCESSLIST to display running threads

• List requirements for user authentication

• Disable server client access control

• Create, modify, and drop user accounts

• Set account resource limits

• Configure authentication plugins

• Lab:

• List requirements for user authorization

• Quiz – MySQL User Management

• Describe the levels of access privileges for users

• Creating, Verifying, and Dropping a User

• List the types of privileges

• Setting Up a User for the world_innodb Database

• Grant, modify, and revoke user privileges

• Using the PAM Authentication Plugin

• Implement user accounts and privileges

This lesson describes the various security risks that could affect MySQL.

Lessons:

• Recognize common security risks

• Explain how SSH enables a secure remote connection to the MySQL serve

• Describe security risks that are specific to the MySQL installation

• Find additional information about common security issues

• List security problems and counter-measures for networks, operating systems, file systems, and users

• Lab:

• Protect your data

• Quiz – MySQL Security

• Use SSL for secure MySQL server connections

• Determining the Status of SSL Connectivity

• Enabling MySQL Support for SSL Connections

This lesson explains how to perform table maintenance through the use of SQL statements and MySQL utilities.

Lessons:

• Recognize the types of table maintenance operations

• Lab:

• Execute SQL statements for table maintenance

• Quiz – Table Maintenance

• Use client and utility programs for table maintenance

• Using Table Maintenance SQL Statement

• Maintain tables according to their specific storage engine

• Using Table Maintenance Utilities

This lesson explains how to export and import MySQL data.

Lessons:

• Export data

• Lab:

• Import data

• Exporting MySQL Data

• Importing Data

This lesson describes how to create automated routines within MySQL.

Lessons:

• Create and execute stored routines

• Lab:

• Describe security for the execution of stored routines

• Creating Stored Routines

• Create and execute triggers

• Reviewing Stored Routines

• Create, alter, and drop events

• Creating a Trigger

• Explain the scheduling of event execution

• Creating and Testing an Event

This lesson describes the concepts, and creation of, MySQL backups.

Lessons:

• Describe backup basics

• Perform data recovery

• List the types of backups

• Lab:

• List MySQL backup tools and utilities

• Quiz – Introduction to Backups

• Make binary and text backups

• MySQL Enterprise Backup

• Explain the role of log and status files in backups

• Using mysqldump

• Backup and Recovery Using LVM Snapshots and the Binary Log

This lesson describes how to create high availability solutions through database replication.

Lessons:

• Describe MySQL replication

• Monitor MySQL replication

• Manage the MySQL binary log

• Troubleshoot MySQL replication

• Explain MySQL replication threads and logs

• Lab:

• Set up a MySQL replication environment

• Quiz – Replication

• Explain the role of replication in high availability and scalability

• Configuring Replication

• Design advanced replication topologies

• Adding a New Slave

• Perform a controlled switchover

• Enabling GTID and Configuring Circular Replication

• Configure replication with MySQL Utilities

• Using MySQL Utilities and Performing a Failover

This lesson describes how to address performance related issues on MySQL through query analysis and modification of server variables.

Lessons:

• List factors that affect performance

• Describe the process of tuning system variables

• Describe general table optimizations

• Lab:

• Analyze queries by using EXPLAIN and PROCEDURE ANALYSE

• Quiz – Introduction to Performance Tuning

• Monitor the most common status variables that affect performance

• Using the SQL EXPLAIN command

• Explain the most common server system variables that affect performance

• Using the ANALYZE procedure