Course 1Z0-873 Oracle Certified Professional MySQL 5 Database Administrator I
What you will learn:
The MySQL for Database Administrators course is the foundation course for database administrators and developers
who want to use this powerful database. In this course you will learn how to secure users privileges, set resource
limitations, and access controls. In addition, you will learn multiple new features such as creating and using Stored
Procedures, Triggers and Views.
Audience:
DBAs and developers who want to administer MySQL
Students wishing to prepare for the MySQL Database Administrator Certification Exams
Prerequisites:
Attend the MySQL for Beginners course or some experience with Relational Databases and SQL.
MySQL for Beginners (SQL-4401)
Course Objectives:
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
Course Topics:
Section 1: MySQL Architecture (10%)
Client/Server Overview
Communication Protocols
The SQL Parser and Storage Engine Tiers
How MySQL Uses Disk Space
How MySQL Uses Memory
Section 2: Starting, Stopping, and Configuring MySQL (20%)
Types of MySQL Distributions
Starting and Stopping MySQL Server on Windows
Starting and Stopping MySQL Server on Unix
Runtime MySQL Configuration
Log and Status Files
Loading Time Zone Tables
Security-Related Configuration
Setting the Default SQL Mode
Upgrading MySQL
Section 3: Client Programs for DBA Work (5%)
Overview of Administrative Clients
mysql
mysqladmin
mysqlimport
mysqldump
Client Program Limitations
Section 4: Character Set Support (5%)
Performance Issues
Choosing Data Types for Character Columns
Section 5: Locking (10%)
Locking Concepts
Explicit Table Locking
Advisory Locking
Section 6: Storage Engines (20%)
MySQL Storage Engines
The MyISAM Engine
The MERGE Engine
The InnoDB Engine
The MEMORY Engine
The FEDERATED Engine
The Cluster Storage Engine
Other Storage Engines
Section 7: Data (Table) Maintenance (10%)
Types of Table Maintenance Operations
SQL Statements for Table Maintenance
Client and Utility Programs for Table Maintenance
Repairing InnoDB Tables
Enabling MyISAM Auto-Repair
Section 8: The INFORMATION_SCHEMA Database (5%)
INFORMATION_SCHEMA Access Syntax
INFORMATION_SCHEMA Versus SHOW
Limitations of INFORMATION_SCHEMA
Section 9: Data Backup and Recovery Methods (15%)
Introduction
Binary Versus Textual Backups
Making Binary Backups
Making Text Backups
Backing Up Log and Status Files
Replication as an Aid to Backup
MySQL Cluster as Disaster Prevention
Data Recovery
Syllabus
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
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!
QA Session
