Speak to our team now +44 (0)1737 821590

MySQL server administration

A 5 day
HANDS ON
training course

MySQL server admin training course description

This MySQL Server administration course is designed for MySQL Server administrators who have a basic understanding of a MySQL database and a good working knowledge of SQL commands. The course provides an understanding of MySQL Architecture and practical experience in setting up, configuring and maintaining a MySQL server, backing up and recovering data and optimising query performance. This MySQL Server administration course also covers MySQL replication and its role in high availability and scalability.

Key outcomes from this course

By the end of the course delegates will be able to:
  • Use MySQL Workbench.
  • Install and configure a MySQL Server .
  • Administer and tune a MySQL server.
  • Configure log files and interpret error messages..
  • Secure the Server.
  • Administer a replication topology.

MySQL server admin training course details

Who will benefit:
Database administrators, Database developers and Technical consultants.
Prerequisites:
Introduction to MySQL
Duration:
5 days

Training approach

This structured course uses Instructor Led Training to provide the best possible learning experience. Small class sizes ensure students benefit from our engaging and interactive style of teaching with delegates encouraged to ask questions throughout the course. Quizzes follow each major section allowing checking of learning. Hands on sessions are used throughout to allow delegates to consolidate their new skills.

Overall rating:

Customer reviews


"Hard concepts were explained very simply."
J. S. Framestore CFC
"Excellent presentation - very good course structure."
B. M. London Internet Exchange

MySQL server admin training course contents

This course teaches the major topics for the Oracle MySQL Database administrator exam (1Z0-883)


The server, clients and tools:
Versions of MySQL and MariaDB, MySQL architecture, MySQL Server, MySQL Tools, MySQL Admin.
Hands on: Using client programs.

MySQL Workbench:
Installation, Connecting, Screens.
Hands on: Using MySQL Workbench.

Obtaining Metadata:
What is Metadata?, mysqlshow, SHOW, DESCRIBE, Information_Schema.
Hands on: MySQL Information gathering.

MySQL server installation and configuration:
MySQL distributions, Installing on Windows/Linux, Starting/Stopping. Status files, Server upgrades, Time Zones.
Hands on: Installing, controlling.

Configuring the server:
MySQL Server options and variables, status variables, MySQL Enterprise Audit.
Hands on: Configuring MySQL.

SQL modes, log files and logging:
Error messages, SHOW Errors, SHOW Warnings, SQL Modes, Note Messages, PERROR. Logs: General, Error Slow Query, Binary.
Hands on: Interpreting errors. Configuring logs.

MySQL architecture:
Client/Server, protocols, the SQL Parser and Storage Engine Tiers, MySQL Disk space and memory.
Hands on: Examining the Architecture.

Transactions and locking:
Locking Concepts, Explicit Table Locking, Advisory Locking.
Hands on: Locking.

Innodb storage engine:
Innodb features, transactions, Referential Integrity, Innodb tables, System tablespace configuration, Log file and buffer configuration, Innodb Status.
Hands on: Using the InnoDB engine.

Other storage engines:
MYISAM Engine, Merge Engine, Other Engines: Archive, Aria, Memory, CSV, Blackhole, Cluster Engine. ColumnStore, MyRocks and Spider Engines.
Hands on: Using Storage Engines.

Table maintenance:
Table maintenance operations, Check Table, Repair Table, Analyze Table, Optimize Table, MySQL Check, MyISAM Table Maintenance and Repair Utilities. Hands on: Maintaining Tables.

Backup and recovery:
Strategies, Backup methods, Binary Backups of tables, recovery, Import and Export, mysqldump, mysqlimport, other Backup tools.
Hands on: Backing Up and Recovery.

User management:
User accounts, creating users, Renaming users, changing passwords, dropping users, privileges, The User Table, Connection Validation,
Hands on: Creating, Managing and Dropping Users.

Privileges:
Types, revocation, resource limits, SHOW Grants.
Hands on: Granting and revoking privileges.

Security and upgrades:
Security risks, users, OS, file system, network security, SSL with MySQL, connecting to MySQL using SSH.
Hands on: Securing the Server.

Tuning queries:
Tuning, Query Analysis candidates, using Explain to Analyse queries, Explain Output. Explain Extended.
Hands on: Tuning queries

Index tuning:
Indexes for performance, Creating and Dropping Indexes, Obtaining Index Metadata, Indexing principles, Indexing and Joins, Fulltext Indexes, MyISAM Index caching.
Hands on: Creating and Using Indexes.

Table tuning:
General optimizations, Myisam/Innodb/other specific optimizations.
Hands on: Tuning tables.

Server tuning:
Status and server variables, Performance Schema, Query cache,
Hands on: Tuning.

The Event scheduler:
Event scheduler concepts, configuration, creating, altering and dropping events, Event scheduler monitoring, Events and privileges,
Exercises: Using the event scheduler.

Partitioned tables:
Concepts, Metadata, Types of Partitioning, Subpartitioning, Maintenance of partitioned tables,
Hands on: Using partitioned tables.

Cluster storage engines:
Clustering and Performance, NDBCluster Engine, Galera Cluster, Percona XtraDB Cluster, MySQL InnoDB Cluster, Federated Engine, FederatedX Engine, Other HA techniques, NOSQL and MemCached, Features of Memcached with MySQL.

High Availability:
HA goals, concepts, design for HA, Definition of Availability, HA terminology.

Master Slave replication:
Replication, when to use, testing, Excluding items from Replication, Master Slave Replication setup, Replication Using GTIDs, controlled switchover.
Hands on: Setting Up and Testing, Replication and Switchover.

Replication administration:
Failover, utilities, Replication files and threads, Monitoring and troubleshooting.
Hands on: Master to Slave failover.

Group replication:
Overview, Single Primary Mode, Multi-primary Mode, Configure Group Replication, Monitor Group Replication.

Why Choose Us

SNT trainers score an average of over 90% on the three main areas of:
  • Ability to teach
  • Technical knowledge
  • Answering questions
“Excellently presented by a very knowledgeable and enthusiastic trainer.” P.D. General Dynamics

We limit our maximum class size to 8 delegates; often we have less than this. This ensures optimal interactivity between delegates and instructor.
"Excellent course. The small class size was a great benefit…" M.B. IBM

We write our own courses; courseware does not just consist of slides and our slides are diagrams not bullet point text. A typical chapter provides clearly defined objectives with a chapter overview, slides with text underneath, a quiz at the end to check the learning of the students. Hands on exercises are at the end and are used to reinforce the theory.

See Dates & Prices for this course

To enquire about this course

To reserve this course online