MySQL for Database Administrators Ed 1 (SQL-4502)

Požadované predpokladané znalosti

• Attend the MySQL for Beginners course or some experience with Relational Databases and SQL.
• MySQL for Beginners (SQL-4401)

Ciele kurzu

• Install and Upgrade MySQL for the most common operating systems
• Utilize the MySQL Administrator Graphical User Interface (GUI) to manage a MySQL server
• Use the INFORMATION_SCHEMA database to access metadata
• Perform the MySQL start and shutdown operations
• Configure MySQL server options at runtime
• Evaluate data types and character sets for performance issues
• Understand data locking concepts and the different levels of locking in MySQL
• Differentiate between the multiple storage engines available in MySQL
• Perform backup and restore operations utilizing multiple MySQL tools
• Maintain integrity of a MySQL installation utilizing security protocols
• Use stored routines and triggers for administration tasks
• Manage, apply and understand the reason for using views
• Improve performance through query optimization
• Optimize Schemas (Databases) utilizing multiple techniques
• Utilize MySQL monitoring tools to improve server performance
• Compare multiple environment options to improve server performance

Témy kurzu

Introduction

• MySQL Overview, Products, Services
• MySQL Enterprise Services
• Supported Operating Services
• MySQL Certification Program
• Training Curriculum Paths
• MySQL Website
• Installing MySQL
• Installing the 'world' Database

MySQL Architecture

• Client/Server Architecture
• MySQL Architecture Overview
• How MySQL Uses Disk Space
• How MySQL Uses Memory
• The MySQL Plug-In Interface

The MySQL Server

• Types of MySQL Distributions
• Starting and Stopping MySQL on Windows
• Starting and Stopping MySQL on Linux
• Upgrading MySQL
• Managing Multiple Servers

Configuring the MySQL Server

• MySQL Configuration
• Dynamic Server Variables
• Server SQL Modes
• Log and Status Files
• Binary Logging

MySQL Clients

• Overview of Administrative Clients
• Invoking MySQL Client Programs
• Using the mysql Client
• The mysqladmin Client
• MySQL Connectors
• Third-Party APIs

Overview of Data Types

• Data Types
• Numeric Data Types
• Character String Data Types
• Binary String Data Types
• Temporal Data Types
• NULLs
• Column Attributes

Metadata

• Metadata Access Methods
• The INFORMATION_SCHEMA Database/Schema
• Using SHOW and DESCRIBE
• The mysqlshow Command

Storage Engines

• Storage Engine Overview
• MyISAM, InnoDB, and MEMORY Storage Engines
• Other Storage Engines
• Choosing Appropriate Storage Engines
• Using Multiple Storage Engines
• Storage Engine Comparison Chart

Partitioning

• Overview of Partitioning and reasons for using Partitioning
• Creating a Partitioned Table
• Obtaining Partition Information
• Modifying and Removing Partitions
• Partition Modification Performance Effects
• Partition Pruning
• Storage Engine Partition Information
• Partitioning and Locking and Limitations

Transactions and Locking

• Transactions
• Transaction Control statements
• Isolation Levels
• Locking

Security and User Management

• Security Risks
• Security Measures
• Privileges
• Access Levels, including: 1 - User Accounts, 2 - Databases, 3 - Tables, 4 - Columns, 5 - Stored Routines
• User Account Maintenance
• Client Access Control
• Using Secure Connections

Table Maintenance

• Table Maintenance
• SQL Statements for maintenance operations
• Client and Utility Programs for table maintenance
• Table Maintenance per Storage Engine

Exporting and Importing Data

• Exporting and Importing Data
• Exporting and Importing Data Using SQL
• Import Data with the SQL scripts

Programming with MySQL

• Defining, Executing and Examining Stored Routines
• Stored Routines and Execution Security
• Defining, Creating, and Deleting Triggers
• Trigger Restrictions and Privileges
• Defining Events
• Schedule Events
• DBA's Use of MySQL Programming
• Backup Stored Routines

Views

• What is a view?
• Creating Views
• Updatable Views
• Managing Views

Backup and Recovery

• Planning for Recovery Backup
• Backup Tools Overview
• Making Raw Backups
• Making Logical (Text) Backups
• Backup Log and Status Files
• Replication as an Aid to Backup
• Backup Method Comparison
• Data Recovery

Introduction to Performance Tuning

• Using EXPLAIN to Analyze Queries
• General Table Optimizations
• Setting and Interpreting MySQL Server Variables

Introduction to High Availability

• MySQL Replication

Conclusion

• Course Overview
• Training and Certification Website
• Course Evaluation
• Thank You!
• Q&A Session