Call : (+91) 99 8080 3767
Mail : info@EncartaLabs.com
EncartaLabs

MySQL DBA - Comprehensive

( Duration: 5 Days )

This MySQL Database Administration Training is designed for MySQL Database Administrators who have a basic understanding of a MySQL database and SQL commands. This course provides practical experience in setting up and maintaining a MySQL server, including backing up, recovery, configuration and optimization.

By attending MySQL Database Administration workshop, Participants will learn:

  • Starting, Stopping and Configuring MySQL
  • Security-Related Configuration
  • MySQL Database Creation
  • Using Client Programs for MySQL DBA Work
  • Setting up Character Set Support
  • Locking
  • Using Storage Engines, including the MyISAM and InnoDB engines
  • Table Maintenance
  • Obtaining Data from the Information_Schema Database
  • Backup and Recovery, including Exporting (mysqldump) and Importing (mysqlimport)
  • Using Stored Procedures and Triggers for Database Administration Tasks
  • User Management
  • Securing the Server
  • Upgrade-Related Security Issues
  • Optimizing Queries
  • Optimizing Schemas
  • Optimizing the Server
  • The Event Scheduler
  • Partitioned Tables
  • Interpreting Error Messages
  • Optimizing the MySQL Environment
  • Scaling MySQL, including Replication

  • Knowledge of MySQL

MySQL DBA class is suitable for anyone who needs to administer, monitor and support MySQL databases and servers.

COURSE AGENDA

1

Client/Server Concepts

  • General MySQL Architecture
  • Invoking Client Programs
  • Server SQL Modes
2

MySQL Client Program

  • Using mysql Interactively
  • Statement Terminators
  • The mysql Prompts
  • Editing Keys in mysql
  • Using Script Files with MySQL
  • mysql Output Formats
  • Client Commands and SQL Statements
  • Using Server-Side Help
  • Using the -safe-updates Option
3

Data Types

  • Numeric Data Types
  • The BIT Data Type
  • String Data Types
  • Temporal Data Types
  • Column Attributes
  • Using the AUTO_INCREMENT Column Attribute
  • Handling Missing or Invalid Data Values
4

Identifiers

  • Identifier Syntax
  • Case Sensitivity
  • Using Qualified Names
  • Reserved Words as Identifiers
5

Databases

  • Database Properties
  • Creating Databases
  • Altering Databases
  • Dropping Databases
  • Obtaining Database Metatdata
6

Tables and Indexes

  • Table Properties
  • Creating Tables
  • Altering Tables
  • Emptying Tables
  • Dropping TablesIndexes
  • Indexes
  • Dropping Indexes
  • Obtaining Table and Index Metadata
7

Querying for Data

  • Using SELECT to Retrieve Data
  • Specifying Which Columns to Retrieve
  • Specifying Which Rows to Retrieve
  • Aggregating Results
  • Grouping Results
  • Using UNION
8

SQL Expressions

  • Components of SQL Expressions
  • Numeric Expressions
  • String Expressions
  • Temporal Expressions
  • NULL Values
  • Functions in SQL Expressions
  • Functions in SQL Expressions
  • Comments in SQL Statements
9

Updating Data

  • Update Operations
  • The INSERT Statement
  • The REPLACE Statement
  • The UPDATE Statement
  • The DELETE and TRUNCATE TABLE Statements
  • Privileges Required for Update Statements
10

Joins

  • Overview
  • Writing Inner Joins
  • Writing Outer Joins
  • Resolving Name Clashes Using Qualifiers and Aliases
  • Multiple-Table UPDATE and DELETE Statements
11

Multiple-Table UPDATE and DELETE Statements

  • Types of Subqueries
  • Subqueries as Scalar Expressions
  • Correlated Subqueries
  • Comparing Subquery Results to Outer Query Columns
  • Comparison Using Row Subqueries
  • Using Subqueries in the FROM Clause
  • Converting Subqueries to Joins
  • Using Subqueries in Updates
12

Views

  • Reasons to Use Views
  • Creating Views
  • Altering Views
  • Dropping Views
  • Checking Views
  • Displaying Information About Views
  • Privileges for Views
13

Importing and Exporting Data

  • Import and Export Operations
  • Importing and Exporting Using SQL
  • Importing and Exporting Data from the Command Line
14

User Variables

  • User Variable Syntax
  • User Variable Properties
15

Prepared Statements

  • Benefits of Prepared Statements
  • Using Prepared Statements from the mysql Client
  • Preparing a Statement
  • Executing a Prepared Statement
  • Deallocating a Prepared Statement
16

Stored Routines

  • Benefits of Stored Routines
  • Differences Between Stored Procedures and Functions
  • The Namespace for Stored Routines
  • Defining Stored Routines
  • Creating Stored Routines
  • Altering Stored Routines
  • Dropping Stored Routines
  • Invoking Stored Routines
  • Obtaining Stored Routine Metadata
  • Stored Routine Privileges and Execution Security
17

Triggers

  • Reasons to Use Triggers
  • Trigger Concepts
  • Creating a Trigger
  • Restrictions on Triggers
  • Referring to Old and New Column Values
  • Destroying a Trigger
  • Privileges Required for Triggers
18

Obtaining Database Metadata

  • Overview of Metadata Access Methods
  • Using INFORMATION_SCHEMA to Obtain Metadata
  • Using SHOW and DESCRIBE to Obtain Metadata
  • Using mysqlshow to Obtain Metadata
19

Debugging MySQL Applications

  • Interpreting Error Messages
  • The SHOW WARNINGS Statement
  • The SHOW ERRORS Statement
  • The perror Utility
20

Basic Optimizations

  • Overview of Optimization Principles
  • Using Indexes for Optimization
  • General Query Enhancement
  • Choosing Appropriate Storage Engines
  • Normalization
21

MySQL Architecture

  • Client/Server Overview
  • Communication Protocols
  • The SQL Parser and Storage Engine Tiers
  • How MySQL Uses Disk Space
  • How MySQL Uses Memory
22

Starting, Stopping, and Configuring MySQL

  • 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
23

Client Programs for DBA Work

  • Overview of Administrative Clients
  • mysql
  • mysqladmin
  • mysqlimport
  • mysqldump
  • Client Program Limitations
24

Character Set Support

  • Performance Issues
  • Choosing Data Types for Character Columns
25

Locking

  • Locking Concepts
  • Explicit Table Locking
  • Advisory Locking
26

Storage Engines

  • 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
27

Data (Table) Maintenance

  • Types of Table Maintenance Operations
  • SQL Statements for Table Maintenance
  • Client and Utility Programs for Table Maintenance
  • Repairing InnoDB Tables
  • Enabling MyISAM Auto-Repair
28

The INFORMATION_SCHEMA Database

  • INFORMATION_SCHEMA Access Syntax
  • INFORMATION_SCHEMA Versus SHOW
  • Limitations of INFORMATION_SCHEMA
29

Data Backup and Recovery Methods

  • 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
30

Stored Routines

  • Using Stored Routines and Triggers for Security Purposes
  • Using Stored Routines to Enhance Performance
31

User Management

  • User Account Management
  • Client Access Control
32

Securing the Server

  • Security Issues
  • Operating System Security
  • Filesystem Security
  • Log Files and Security
  • Network Security
  • FEDERATED Table Security
33

Upgrade-Related Security Issues

  • Upgrading the Privilege Tables
  • Security-Related SQL Mode Values
34

Optimizing Queries

  • Identifying Participants for Query Analysis
  • Using EXPLAIN to Analyze Queries
  • Using SHOW WARNINGS for Optimization
  • MyISAM Index Caching
35

Optimizing Schemas

  • General Table Optimizations
  • Normalization
  • MyISAM-Specific Optimizations
  • InnoDB-Specific Optimizations
  • MERGE-Specific Optimizations
  • MEMORY-Specific Optimizations
36

Optimizing the Server

  • Interpreting mysqld Server Information
  • Measuring Server Load
  • Tuning Memory Parameters
  • Using the Query Cache
37

Interpreting Error Messages

  • Sources of Diagnostic Information
  • Using the Error Log for Diagnostic Purposes
  • Using The Slow Query Log for Diagnostic Purposes
38

Optimizing the Environment

  • Choosing Hardware for MySQL Use
  • Configuring Disks for MySQL Use
  • Network Issues
  • Optimizing the Operating System for MySQL Use
39

Scaling MySQL

  • Using Multiple Servers
  • Replication
40

Clustering Concepts

  • High Availability
  • High Availability
  • Clustering Architectures
41

Architecture and Organization

  • Components
  • Organization
42

Software Processes

  • Local Replication
  • Cluster Partitioning
  • Failure Detection/Recovery
43

The NDB Storage Engine

  • Usage
  • Transactions
  • Indexes
  • Disk Based Storage
  • Limitations
44

Cluster Deployment

  • General
  • Physical Configuration
  • Hardware Requirements/Recommendations
  • Topologies
45

Configuration

  • Configuration Files
  • Connectstring
  • Generic Options
  • Storage Options
46

Starting, Stopping and Monitoring a Cluster

  • Start/Stop/Restart
  • Adding/Removing Nodes
  • Monitor/Management
47

Security

48

Backup and Recovery

  • General
  • Native Backup
  • Mysqldump
49

Global Replication

  • Global Replication (General)
  • Binlog
  • Setting Up Replication
50

Performance and Tuning

  • Data Access Methods
  • Configuration
  • Query Cache
  • Network Issues
  • Mysqld Options
51

Troubleshooting and Problem Solving

  • Monitoring
  • Node Failure
  • General Issues

Encarta Labs Advantage

  • One Stop Corporate Training Solution Providers for over 4,000 Modules on a variety of subjects
  • All courses are delivered by Industry Veterans
  • Get jumpstarted from newbie to production ready in a matter of few days
  • Trained more than 50,000 Corporate executives across the Globe
  • All our trainings are conducted in workshop mode with more focus on hands-on sessions

View our other course offerings by visiting http://encartalabs.com/course-catalogue-all.php

Contact us for delivering this course as a public/open-house workshop/online training for a group of 10+ candidates.

Top