MySQL

MySQL for Database Administrators

Learn about MySQL from Instructors with Real World Expertise.

A 5 day intermediate course to equip administrators to use all the features of MySQL to get the most out of their web, cloud and embedded applications.

MySQL Training Overview:

The MySQL for Database Administrators course is for MySQL DBAs and other professionals who want to install and optimize MySQL Server, set up replication and security, perform database backups and performance tuning, and protect MySQL databases. After taking this course, you will be equipped to use all the features of MySQL to get the best out of your Web, Cloud, and embedded applications, whether you work with the command line or graphical tools such as MySQL Workbench and MySQL Enterprise Monitor, whether your application uses complex queries or the NoSQL API, and whether your
preferred challenge is replicated servers or highly-tuned transactional systems.

Extensive hands-on practices guide you through each concept. You will install MySQL products, partition data, check and repair tables, perform backups and configure replication, create secured users and enable SSL security, and much more. Explore real-world problems and discover best practices as you work with the tools and techniques used by professional MySQL database administrators.

Course Objectives:

  • Install and configure the MySQL server
  • Secure users and data
  • Understand the use of the InnoDB storage engine with MySQL
  • Configure privileges and access controls
  • Work with stored procedures and triggers
  • Plan for disaster recovery
  • Configure the server for performance and high availability.

Target Audience:

Administrators wanting to learn more about MySQL Database Administration

Prerequisites:

Have some experience with relational databases and SQL.

Duration: 5Days

Course Outline:

Introduction

Overview:

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

MySQL Architecture

Overview:
  • 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

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

Overview:

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

Clients and Tools

Overview:
  • 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

Overview

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

Obtaining Metadata

Overview

  • 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

Overview

  • 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

Overview

  • 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

Overview

  • 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

Overview

  • 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

Overview

  • 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

Overview

  • 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

Overview

  • Exporting Data
  • Importing Data

Programming Inside MySQL

Overview

  • 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

Overview

  • 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

Overview

  • Managing the MySQL Binary Log
  • MySQL replication threads and files
  • Using MySQL Utilities for Replication
  • 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

Overview

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

Conclusion

Overview

  • Course Overview
  • MySQL Curriculum
  • Course Evaluation
  • Q&A Session

Looking for something else? View all courses

Start Learning

Learn from Instructors with Real World Expertise.