MySQL Administration
=====
—–
Course Id : DATA-MYSQ
Duration : 40 Hours
Overview
—–
* MySQL is the most popular Relational Database Management System in open source database
* MySQL course covers advanced topics such as database fundamentals as well as MySQL features
* MySQL training covers MySQL Architecture, configuring and maintaining a MySQL server, backing up and recovering data and optimising query performance
Pre-Requisites
—–
* The participants should have the basic knowledge any database and SQL
* Working knowledge of any command-line program is recommended
Training Objectives
—–
* Understand the MySQL Architecture
* Learn to install MySQL and configure MySQL components
* Use different storage engines supported in MySQL
* Understand and maintain security of a MySQL installation via user management
* Work with the MySQL Administrator GUI
* Backup and restore operations using multiple tools
* Learn installation, database and queries
* Perform database replication in MySQL
Course Structure
—–
* We provide more focus on hands-on in our technical courses (typically 80% hands-on/20% theory)
* Students get the capability to apply the material they learn to real-world problems
Materials Provided
—–
* PDF of slides and hands-on exercises
* Access to instance with lab environment
Software Requirements
—–
* Any current internet browser
Hardware Requirements
—–
* Processor: 1.2 GHz
* RAM: 512 MB
* Disk space: 1 GB
* Network Connection with low latency (<250ms) to Internet
Course Outline
## Daywise course outline
—–
## Day 1
—–
* Unit 1 : Introduction to Database Concepts and MySQL
* Unit 2 : Installation, Configuration, and Upgrading
## Day 2
—–
* Unit 3 : Database Design
* Unit 4 : Using the mysql Command-Line Tool
## Day 3
—–
* Unit 5 : DDL: Data Definition Language
* Unit 6 : MySQL Storage Engines
* Unit 7 : Utilities
## Day 4
—–
* Unit 8 : Administering a Database and Users
* Unit 9 : Database Programmability
## Day 5
—–
* Unit 10 : Optimization and Performance
* Unit 11 : Optimizing Queries
* Unit 12 : MySQL Programming Interfaces
##Detailed course outline
—–
Unit 1 : Introduction to Database Concepts and MySQL
—–
* Features of a Relational Database
* Where does SQL Fit in?
* Database Access
* Why MySQL?
* The History of MySQL
Unit 2 : Installation, Configuration, and Upgrades
—–
* MySQL Software
* MySQL Software Features
* Preparing to Install MySQL
* Available Client Software
* After the Download
* Configuring the Server
* Starting the Server
* The Initial User Accounts
* Verifying Server Operation
* Upgrading
* Copying a Database Between Architectures
* Environment Variables
Unit 3 : Database Design
—–
* Developing the Design of a Database
* Database Entities
* The Primary Key
* Foreign Key Relationships
* Data Models and Normalization
* Second Normal Form (2NF)
* Third Normal Form (3NF) and Beyond
* Translate Data Model into a Database Design
Unit 4 : Using the mysql Command-Line Tool
—–
* Running the mysql Client
* Customizing the mysql Prompt
* mysql Commands
* Using the Help Command
* Some Useful mysql Options
* Working with a Database
* Examining Table Definitions
* Other SHOW Options
Unit 5 : DDL – Data Definition Language
—–
* DDL and DML Overview
* Building Table Definitions
* Identifiers
* Column Definitions
* Numeric Datatypes
* ENUM and SET Types
* Date and Time Datatypes
* AUTO_INCREMENT
* UNIQUE Constraints
* Primary Keys
* Modifying Tables
* Foreign Keys
* Renaming and Dropping Tables
Unit 6 : MySQL Storage Engines
—–
* Storage Engine Overview
* Other Storage Engine Types
* The Basics of Commonly Used Storage
* Engines
* MyISAM Limits and Features
* MyISAM Data File Format
* InnoDB and Hardware Limitations
* InnoDB Shared Tablespace
* Configuration
* InnoDB Per-Table Tablespaces
* InnoDB Data Management
* MEMORY and FEDERATED
* MERGE and ARCHIVE
Unit 7 : Utilities
—–
* Client Overview
* Specifying Options for Command-Line
* Clients
* Client Option Files
* Checking Tables with myisamchk and
* mysqlchk
* Using myisamchk and mysqlchk for
* Repairs
* mysqlshow and mysqlimport
* Using mysqldump
* The Query Browser
* MySQL Query Browser: Deeper
Unit 8 : MySQL Administrator: Basic Operations
—–
* Monitoring the Server
* User Administration
* Third Party Tools
* Administering a Database and Users
* The Server-Side Programs
* Starting the MySQL Server
* Using SET for Server Options
* Table Management
* Server Log Files
* mysqladmin
* Backup and Restore
* Miscellaneous Functions
* User Account Management
* Understanding User Privileges
* User Account Rights Management
* User Account Privileges
* Managing Access to the Database
* Environment
Unit 9 : Database Programmability
* Stored Routines: Basic Concepts
* Routine Creation and Use
* Flow Control Statement
* Writing Blocks of Code
* Triggers
* Stored Routines, Triggers, and the
* Binary Log
* Table HANDLERs
* Prepared Statements
Unit 10 : Optimization and Performance
—–
* Tuning
* Hardware Limitations
* Optimizing the MySQL Server’s
* Interaction with the External
* World
* Adjusting the MySQL Server
* Configuration
* Optimizing Your Database
Unit 11 : Optimizing Queries
—–
* The Use of Indexes to Support Queries
* Thinking about JOIN Queries
* Query Sorts, Indexes, and ShortCircuiting
* INSERT, UPDATE, DELETE, and
* Table Locks
* Some General Optimizations
* Optimizations Specific to MyISAM
* Optimizations Specific to InnoDB
Unit 12 : MySQL Programming Interfaces
—–
* Database Application Architectures
* Connecting MySQL to ODBC
* Connecting MySQL to MS/Office and
* MS/Access
* Connecting to MySQL from Perl
* Programming Perl to MySQL
* Connecting to MySQL from PHP
* Programming PHP to MySQL