MySQL for Database Administrator

MySQL for Database Administrator

Course Outline

+Introduction

  • Describing MySQL
  • Listing MySQL Products and Professional Services
  • Describing MySQL Enterprise Subscription
  • Currently Supported Operating Systems
  • Describing MySQL Certification Program
  • Listing Available MySQL Courses
  • Describing Installation of MySQL
  • Describing Installation of 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

+Partitioning

  • Overview of Partitioning and 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 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

+Data Replication

  • Database Replication
  • Setting Up Replication in MySQL
  • Data Encryption
  • Column-Level Database Encryption
  • High Availability

+Conclusion

  • MySQL Quiz
  • Assignment
  • Discussion
  • Q&A Session