MySQL Administration Training Course

Duration

28 hours (usually 4 days including breaks)

Requirements

No specific prerequisites, however good if student has some prior knowledge of databases.

Overview

Audience: Any IT professionals who aspire to become DBAs or database support professionals on MySql Database on linx/windows platforms.

Format: 40% theoretical/lectures, 60%Practical/hands on lab

Course Outline

Introduction

  • MySQL Overview, Products, Services
  • MySQL Services and Support
  • Supported Operating Services
  • Training Curriculum Paths
  • MySQL Documentation Resources

MySQL Architecture

  • The client/server model
  • Communication protocols
  • The SQL Layer
  • The Storage Layer
  • How the server supports storage engines
  • How MySQL uses memory and disk space
  • The MySQL plug-in interface

System Administration

  • Choosing between types of MySQL distributions
  • Installing the MySQL Server
  • The MySQL Server installation file structure
  • Starting and stopping the MySQL server
  • Upgrading MySQL
  • Running multiple MySQL servers on a single host

Server Configuration

  • MySQL server configuration options
  • System variables
  • SQL Modes
  • Available log files
  • Binary logging

Clients and Tools

  • Available clients for administrative tasks
  • MySQL administrative clients
  • The mysql command-line client
  • The mysqladmin command-line client
  • The MySQL Workbench graphical client
  • MySQL tools
  • Available APIs (drivers and connectors)

Data Types

  • Major categories of data types
  • Meaning of NULL
  • Column attributes
  • Character set usage with data types
  • Choosing an appropriate data type

Obtaining Metadata

  • Available metadata access methods
  • Structure of INFORMATION_SCHEMA
  • Using the available commands to view metadata
  • Differences between SHOW statements and INFORMATION_SCHEMA tables
  • The mysqlshow client program
  • Using INFORMATION_SCHEMA queries to create shell commands and SQL statements

Transactions and Locking

  • Using transaction control statement to run multiple SQL statements concurrently
  • The ACID properties of transactions
  • Transaction isolation levels
  • Using locking to protect transactions

Storage Engines

  • Storage engines in MySQL
  • InnoDB storage engine
  • InnoDB system and file-per-table tablespaces
  • NoSQL and the Memcached API
  • Configuring tablespaces efficiently
  • Using foreign keys to attain referential integrity
  • InnoDB locking
  • Features of available storage engines

Partitioning

  • Partitioning and its use in MySQL
  • Reasons for using partitioning
  • Types of partitioning
  • Creating partitioned tables
  • Subpartitioning
  • Obtaining partition metadata
  • Modifying partitions to improve performance
  • Storage Engine Support of Partitioning

User Management

  • Requirements for user authentication
  • Using SHOW PROCESSLIST to show which threads are running
  • Creating, modifying and dropping user accounts
  • Alternative authentication plugins
  • Requirements for user authorization
  • Levels of access privileges for users
  • Types of privileges
  • Granting, modifying and revoking user privileges

Security

  • Recognizing common security risks
  • Security risks specific to the MySQL installation
  • Security problems and counter-measures for network, operating system, filesystem and users
  • Protecting your data
  • Using SSL for secure MySQL server connections
  • How SSH enables a secure remote connection to the MySQL server
  • Finding additional information for common security issues

Table Maintenance

  • Types of table maintenance operations
  • SQL statements for table maintenance
  • Client and utility programs for table maintenance
  • Maintaining tables for other storage engines
  • Exporting and Importing Data
  • Exporting Data
  • Importing Data

Programming Inside MySQL

  • Creating and executing Stored Routines
  • Describing stored routine execution security
  • Creating and executing triggers
  • Creating, altering and dropping events
  • Event execution scheduling

MySQL Backup and Recovery

  • Backup basics
  • Types of backup
  • Backup tools and utilities
  • Making binary and text backups
  • Role of log and status files in backups
  • Data Recovery

Replication

  • Managing the MySQL Binary Log
  • MySQL replication threads and files
  • Setting up a MySQL Replication Environment
  • Designing Complex Replication Topologies
  • Multi-Master and Circular Replication
  • Performing a Controlled Switchover
  • Monitoring and Troubleshooting MySQL Replication
  • Replication with Global Transaction Identifiers (GTIDs)

Introduction to Performance Tuning

  • Using EXPLAIN to Analyze Queries
  • General Table Optimizations
  • Monitoring status variables that affect performance
  • Setting and Interpreting MySQL server Variables
  • Overview of Performance Schema

Conclusion

Q&A Session

Leave a Reply

Your email address will not be published. Required fields are marked *