курс MySQL for Database Administrators Ed 3.1

Описание проекта

Cource Description

The MySQL for Database Administrators training is designed for DBAs andother database professionals. Expert Oracle University instructors will teachyou how to install and configure the MySQL Server, set up replication andsecurity, perform database backups and performance tuning and protect MySQLdatabases.

Audience

• Database Administrators

• Ÿ Developer

Course Objectives

• Describe the MySQLArchitecture

• Ÿ Install and Upgrade MySQL

• Ÿ Use the INFORMATION_SCHEMAdatabase to access metadata

• Ÿ Perform the MySQL start andshutdown operations

• Ÿ Configure MySQL server optionsat runtime

• Ÿ Use available tools includingMySQL Workbench

• Ÿ Evaluate data types andcharacter sets for performance issues

• Ÿ Understand data locking inMySQL

• Ÿ Understand the use of theInnoDB storage engine with MySQL

• Ÿ Maintain integrity of a MySQLinstallation

• Ÿ Use triggers foradministration tasks

• Ÿ Use Enterprise Audit and PluggableAuthentication

• Ÿ Configure advanced replicationtopologies for high availability

• Ÿ Describe introductoryperformance tuning techniques

• Ÿ Perform backup and restoreoperations

• Ÿ Automate administrative taskswith scheduled events

Prerequisites

• Havesome experience with relational databases and SQL

Course content:

Introduction

• MySQL Overview, Products,Services

• Ÿ MySQL Services and Support

• Ÿ Supported Operating Services

• Ÿ MySQL Certification Program

• Ÿ Training Curriculum Paths

• Ÿ MySQL Documentation Resources

MySQL Architecture

• The client/server model

• Ÿ Communication protocols

• Ÿ The SQL Layer

• Ÿ The Storage Layer

• Ÿ How the server supportsstorage engines

• Ÿ How MySQL uses memory and diskspace

• Ÿ The MySQL plug-in interface

System Administration

• Choosing between types ofMySQL distributions

• Ÿ Installing the MySQL Server

• Ÿ The MySQL Server installationfile structure

• Ÿ Starting and stopping theMySQL server

• Ÿ Upgrading MySQL

• Ÿ Running multiple MySQL serverson a single host

Server Configuration

• MySQL server configurationoptions

• Ÿ System variables

• Ÿ SQL Modes

• Ÿ Available log files

• Ÿ Binary logging

• Ÿ Enterprise Audit

Clients and Tools

• Available clients foradministrative tasks

• Ÿ MySQL administrative clients

• Ÿ The mysql command-line client

• Ÿ The mysqladmin command-lineclient

• Ÿ The MySQL Workbench graphicalclient

• Ÿ MySQL tools

• Ÿ Available APIs (drivers andconnectors)

Data Types

• Major categories of data types

• Ÿ Meaning of NULL

• Ÿ Column attributes

• Ÿ Character set usage with datatypes

• Ÿ Choosing an appropriate datatype

Obtaining Metadata

• Available metadata accessmethods

• Ÿ Structure ofINFORMATION_SCHEMA

• Ÿ Using the available commandsto view metadata

• Ÿ Differences between SHOWstatements and INFORMATION_SCHEMA tables

• Ÿ The mysqlshow client program

• Ÿ Using INFORMATION_SCHEMAqueries to create shell commands and SQL statements

Transactions and Locking

• Using transaction controlstatement to run multiple SQL statements concurrently

• Ÿ The ACID properties oftransactions

• Ÿ Transaction isolation levels

• Ÿ Using locking to protecttransactions

Storage Engines

• Storage engines in MySQL

• Ÿ InnoDB storage engine

• Ÿ InnoDB system andfile-per-table tablespaces

• Ÿ NoSQL and the Memcached API

• Ÿ Configuring tablespacesefficiently

• Ÿ Using foreign keys to attainreferential integrity

• Ÿ InnoDB locking

• Ÿ Features of available storageengines

Partitioning

• Partitioning and its use inMySQL

• Ÿ Reasons for using partitioning

• Ÿ Types of partitioning

• Ÿ Creating partitioned tables

• Ÿ Subpartitioning

• Ÿ Obtaining partition metadata

• Ÿ Modifying partitions toimprove performance

• Ÿ Storage Engine Support ofPartitioning

User Management

• Requirements for userauthentication

• Ÿ Using SHOW PROCESSLIST to showwhich threads are running

• Ÿ Creating, modifying and droppinguser accounts

• Ÿ Alternative authenticationplugins

• Ÿ Requirements for userauthorization

• Ÿ Levels of access privilegesfor users

• Ÿ Types of privileges

• Ÿ Granting, modifying andrevoking user privileges

Security

• Recognizing common securityrisks

• Ÿ Security risks specific to theMySQL installation

• Ÿ Security problems andcounter-measures for network, operating system, filesystem and users

• Ÿ Protecting your data

• Ÿ Using SSL for secure MySQLserver connections

• Ÿ How SSH enables a secureremote connection to the MySQL server

• Ÿ Finding additional informationfor common security issues

Table Maintenance

• Types of table maintenanceoperations

• Ÿ SQL statements for tablemaintenance

• Ÿ Client and utility programsfor table maintenance

• Ÿ Maintaining tables for otherstorage engines

Exporting and Importing Data

• Exporting Data

• Ÿ Importing Data

Programming Inside MySQL

• Creating and executing StoredRoutines

• Ÿ Describing stored routineexecution security

• Ÿ Creating and executingtriggers

• Ÿ Creating, altering anddropping events

• Ÿ Event execution scheduling

MySQL Backup and Recovery

• Backup basics

• Ÿ Types of backup

• Ÿ Backup tools and utilities

• Ÿ Making binary and text backups

• Ÿ Role of log and status filesin backups

• Ÿ Data Recovery

Replication

• Managing the MySQL Binary Log

• Ÿ MySQL replication threads andfiles

• Ÿ Using MySQL Utilities forReplication

• Ÿ Designing Complex ReplicationTopologies

• Ÿ Multi-Master and CircularReplication

• Ÿ Performing a ControlledSwitchover

• Ÿ Monitoring and TroubleshootingMySQL Replication

• Ÿ Replication with GlobalTransaction Identifiers (GTIDs)

Introduction to Performance Tuning

• Using EXPLAIN to AnalyzeQueries

• Ÿ General Table Optimizations

• Ÿ Monitoring status variablesthat affect performance

• Ÿ Setting and Interpreting MySQLserver Variables

• Ÿ Overview of Performance Schema

• Ÿ Tuning the MySQL Server

Conclusion

• Course Overview

• Ÿ MySQL Curriculum

• Ÿ Course Evaluation

• Ÿ Thank You!

• Ÿ QA Session