MySQL Administration

MySQL Database Administrators -Introduction 

Introductions

Clientage

MySQL Database Server Editions

MySQL Enterprise Edition

MySQL Connectors and APIs

MySQL Services

Community Support

MySQL-Supported Operating Systems

MySQL Websites

MySQL Certification

MySQL Online Documentation

Architecture

MySQL Architecture

MySQL Interface Programs

Utility Programs

MySQL Server Architecture

Server Process

Connection Layer

Communication Protocols

SQL Layer

SQL Statement Processing

Storage Layer

Storage Engine:

How MySQL Uses Disk Space

How MySQL Uses Memory

Memory Structures

System Administration

MySQL Binary Distributions

MySQL RPM Installation Files for Linux

Linux MySQL RPM Installation Process

Linux MySQL Server Installation Directories

Starting the MySQL Server on Linux

Stopping the MySQL Server on Linux

Improving Installation Security

Windows MySQL Server Installation Directory

Running MySQL on Windows

Data Directory

MySQL Server Releases

Server Configuration

MySQL Configuration Options

Reasons to Use Option Files

Option File Groups

Writing an Option File

Option File Locations

Sample Option Files

Displaying Options from Option Files

Obscuring Authentication Options

Server System Variables

Dynamic System Variables

Displaying Dynamic System Variables

Structured System Variables

Server Status Variables

SQL Modes

Setting the SQL Mode

Common SQL Modes

Log Files

Log File Usage Matrix

Binary Logging

Binary Logging Formats

List Binary Log Files

View Binary Log Contents

Deleting Binary Logs

Clients and Tools

Command-Line Client Programs

Invoking Command-Line Clients

Connection Parameter Options

Invoking mysql Client

mysql Client: Safe Updates

mysql Client: Output Formats

mysql Client: MySQL Client Commands

mysql Client: SQL Statements

mysql Client: Help on SQL Statements

mysql Client: SQL Statement Terminators

mysql Client: Special Statement Terminators

mysql Client: Redefining the Prompt

mysql Client: Using Script Files

mysqladmin Client

Invoking the mysqladmin Client

MySQL Tools

MySQL Workbench

MySQL Workbench: GUI Window

MySQL Workbench: Access

MySQL Connectors

Third-Party APIs

Data Types

Data Types: Overview

Creating Tables with Data Types

Numeric Data Types

Character String Data Types

Character Set and Collation Support

Binary String Data Types

Temporal Data Types

Spatial Data Types

Setting Data Types to NULL

Creating Tables with Column Attributes

Column Attributes

Choosing Data Types

Obtaining Metadata

Metadata Access Methods

INFORMATION_SCHEMA Database

INFORMATION_SCHEMA Tables

INFORMATION_SCHEMA Table Columns

Using SELECT with INFORMATION_SCHEMA

INFORMATION_SCHEMA: Examples

Creating Shell Commands with INFORMATION_SCHEMA Tables

Creating SQL Statements with INFORMATION_SCHEMA Tables

MySQL-Supported SHOW Statements

SHOW Statement: Examples

Additional SHOW Statement Examples

DESCRIBE Statement mysqlshow Client mysqlshow: Examples

Transactions and Locking

Transactions

Transaction Diagram

ACID

Transaction SQL Control Statements

SQL Control Statements Flow: Example

AUTOCOMMIT Mode

Implicit Commit

Transactional Storage Engines

Transaction Isolation Problems

Isolation Levels

Isolation Level Problems

Setting the Isolation Level

Global Isolation Level

Transaction Example: Isolation

Locking Concepts

Explicit Row Locks

Deadlocks

Transaction Example: Deadlock

Implicit Locks

Storage Engines

Storage Engines and MySQL

Available Storage Engines

InnoDB Storage Engine

InnoDB as Default Storage Engine

InnoDB Features

Displaying the Storage Engine Setting

Setting the Storage Engine

Converting Existing Tables to InnoDB

InnoDB System Tablespace

Data Tablespaces

Tablespace Directory Structure

Shared Tablespace Configuration

Log Files and Buffers: Diagram

Configuring Buffer Pools for Performance

NoSQL and the memcached API

Configuring memcached

Key/Value Data

Using NoSQL with MySQL Databases

Referential Integrity

Multi-Versioning

Locking

Reduce Deadlocks

MyISAM Storage Engine

MEMORY Storage Engine

ARCHIVE Storage Engine

BLACKHOLE Storage Engine

Partitioning

Partitioning

Partitioning Analogy

MySQL-Specific Partitioning

Partitioning Types

Partitioning Support

Improving Performance with Partitioning

Improving Performance with Partitioning: Pruning

Performance Challenges with Partitioning

Basic Partition Syntax

RANGE Partitioning

LIST Partitioning

HASH Partitioning

KEY Partitioning

Subpartitioning

Obtaining Partition Information

Altering a Partition

Redefining the Partitioning Type

Dropping Partitions

Issues with Using DROP PARTITION

Removing Partitioning

Performance Effects of Altering a Partition

Partitioning: Storage Engine Features

Partitioning and Locking

Partitioning Limitations

Partition Expression Limitations

User Management

Importance of User Management

User Account Verification

Viewing User Account Settings

Native Authentication

Creating a User Account

Host Name Patterns

Setting the Account Password

Confirming Passwords

Manipulating User Accounts

Pluggable Authentication

Client-Side Cleartext Authentication Plugin

Loadable Authentication Plugins

PAM Authentication Plugin

Password Validation Plugin

Authorization

Determining Appropriate User Privileges

Granting Administrative Privileges

GRANT Statement

Display GRANT Privileges

User Privilege Restrictions

Grant Tables

Use of Grant Tables

Effecting Privilege Changes

Revoking Account Privileges

SHOW PROCESSLIST

Disabling Client Access Control

Setting Account Resource Limits

Security

Security Risks

MySQL Installation Security Risks

Network Security

Password Security

Operating System Security

File System Security

Protecting Your Data

Using Secure Connections

SSL Protocol

Using SSL with the MySQL Server

Starting the MySQL Server with SSL

Requiring SSL-Encrypted Connections

Checking SSL Status

Advantages and Disadvantages of Using SSL

Secure Remote Connection to MySQL

MySQL Security FAQ

Table Maintenance

Implementation of Table Maintenance

SQL for Table Maintenance Operations

ANALYZE TABLE Statement

CHECK TABLE Statement

CHECKSUM TABLE Statement

OPTIMIZE TABLE Statement

REPAIR TABLE Statement

mysqlcheck Client Program

Options for mysqlcheck and myisamchk

InnoDB Table Maintenance

MyISAM Table Maintenance

MEMORY Table Maintenance

ARCHIVE Table Maintenance

Exporting and Importing Data

Exporting and Importing Data

Exporting Data by Using SELECT with INTO OUTFILE

Using Data File Format Specifiers

Escape Sequences

Importing Data by Using LOAD DATA INFILE

Importing Data from the Client Host

Skipping or Transforming Input Data

Duplicate Records

Programming Inside MySQL

Stored Routines

Uses of Stored Routines

Stored Routines: Issues

Executing Stored Routines

Stored Procedure: Example

Stored Function: Example

Examine Stored Routines

Stored Routines and Execution Security

Quiz

Triggers

Create Triggers

Trigger Events

Trigger Error Handling

Examining Triggers

Dropping Triggers

Restrictions on Triggers

Trigger Privileges

Quiz

Events

Schedule

Event Scheduler and Privileges

Event Execution Privileges

Examining Events

Dropping Events

MySQL Backup and Recovery

Backups with MySQL

Logical (Textual) Backups

Physical (Raw or Binary) Backups

Replication-Based Backups

Binary Logging and Incremental Backups

Backup Tools: Overview

MySQL Enterprise Backup mysqlbackup

Restoring a Backup with mysqlbackup mysqlbackup Single-File Backups

Restoring mysqlbackup Single-File Backups

Privileges Required for mysqlbackup

mysqlhotcopy

Raw InnoDB Backups

Raw MyISAM and ARCHIVE Backups

mysqldump

Consistency with mysqldump

mysqldump Output Format Options

Restoring mysqldump Backups

Privileges Required for mysqldump

Backing Up Log and Status Files

Replication as an Aid to Backup

Comparing Backup Methods

Backup Strategy

Processing Binary Log Contents

Replication

MySQL Replication

Replication Masters and Slaves

Complex Topologies

Replication Use Cases

Replication for High Availability

Configuring Replication

CHANGE MASTER TO

Failover with Log Coordinates

Global Transaction Identifiers (GTIDs)

Replication with GTIDs

Failover with GTIDs

Replication Filtering Rules

MySQL Utilities

MySQL Utilities for Replication

Asynchronous Replication

Replication Logs

Crash-Safe Replication

Replication Threads

Controlling Slave Threads

Monitoring Replication

Replication Slave I/O Thread States

Replication Slave SQL Thread States

Troubleshooting MySQL Replication

Introduction to Performance Tuning

Factors That Affect Performance

Monitoring

Performance Schema

Instruments, Instances, Events, and Consumers

General Database Optimizations

PROCEDURE ANALYSE

EXPLAIN

EXPLAIN Formats

Examining Server Status

Top Status Variables

Tuning System Variables

Top Server System Variables

Preparing for Tuning

Tuning in Practice

Tuning Example: Sort Buffer Size

Conclusion

Course Goals

MySQL Certification

MySQL Resources 19-7