J. S. Framestore CFC
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.
Installation, Connecting, Screens.
Hands on: Using MySQL Workbench.
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.
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 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 accounts, creating users, Renaming users, changing passwords, dropping users, privileges, The User Table, Connection Validation,
Hands on: Creating, Managing and Dropping Users.
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, Query Analysis candidates, using Explain to Analyse queries, Explain Output. Explain Extended.
Hands on: Tuning queries
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.
General optimizations, Myisam/Innodb/other specific optimizations.
Hands on: Tuning tables.
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.
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.
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.
Failover, utilities, Replication files and threads, Monitoring and troubleshooting.
Hands on: Master to Slave failover.
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
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.