A Database Journal Guide to MySQL 5 Certification: The DBA Stream

Introduction

Last time we looked at
the Developer
Stream of the MySQL 5 certification. Now, in the last part of this series,
we look at the DBA stream. Passing the DBA exam, (the title you can use once
qualified is the Certified MySQL 5.0
Database Administrator (DBA)), requires more in-depth knowledge of MySQL’s
intricacies than the Developer exam. Although the two streams have been
separated, and it’s possible to follow either stream without necessarily having
to do the other, the DBA exam is the successor to the old MySQL 4 Professional
exam. Anyone holding that certification can upgrade to the DBA stream in one
exam, while those who hold the old Core title would need to write both DBA
exams in order to upgrade.

The next two sections
will list all the topics in the two DBA exams, mentioning how large a part they
play in the entire exam (the percentage figure in brackets), and provide a list
of useful sources for studying up in that area.

MySQL DBA I

• MySQL
  Architecture (10%). This section broadly tests knowledge of the way MySQL is
  structured, including the client/server model, MySQL’s use of disk space and
  memory, protocols used for communicating, the SQL parser and the various
  storage engine tiers.
  • Client
    and utility programs

  • MySQL threads

  • Using
    MySQL programs

  • Connectors

• Starting,
  Stopping and Configuring MySQL (20%). No self-respecting DBA will have
  difficulty with this core element! This section tests knowledge of the
  different MySQL distributions, stopping and starting the server (under both
  Windows and Unix), loading the time zone tables, configuring MySQL at runtime
  and for security, upgrading MySQL, log and status files, and how to set the
  default SQL mode.
  • More
    MySQL logs (Database Journal)

  • Choosing which
    MySQL distribution to install

  • Starting
    the server for the first time

  • Starting
    MySQL from the Windows command line

  • Starting
    MySQL as a Windows service

  • mysqld_safe –
    MySQL Server startup script

  • mysql.server –
    MySQL Server startup script

  • mysqld.multi –
    Manage multiple MySQL servers

  • Starting and
    troubleshooting the MySQL server

  • Installing MySQL
    on Linux

  • Upgrading MySQL

  • Using option
    files

  • MySQL Server logs

  • MySQL
    Server Time Zone support

  • The Server
    SQL Mode

• DBA-related
  client programs (5%). These include the mysql, mysqladmin and mysqlimport
  binaries, as well as MySQL Administrator, and a general overview of admin
  clients, as well as client limitations.
  • MySQL
    Administrator (Database Journal)

  • MySQL Administrator

  • mysql – The MySql
    command-line tool

  • mysqladmin –
    Client for Administering a MySQL Server

  • mysqlimport – a
    data import program

  • mysqldump – a
    database backup program

  • Overview
    of client and utility programs

• Character Set
  Support (5%). Multiple character sets have their own complexities, and this
  section covers the impact on performance, and choosing data types for character
  data.
  • SHOW
    CHARACTER SET syntax

  • CHARACTER SET
    support

• Locking (10%).
  This section covers general locking concepts, advisory locking and explicit
  table locking.
  • Locking
    Methods

  • Table Locking
    Issues

  • Concurrent
    INSERT’s

  • LOCK TABLES and
    UNLOCK TABLES syntax

  • Miscellaneous
    functions

• Storage
  Engines (20%). MySQL prides itself on its storage engine model, and this major
  section covers the main storage engines, which include the MyISAM, InnoDB,
  FEDERATED, Cluster, MEMORY and MERGE engines. You also need to know about the
  existence of the other less important ones.
  • MySQL
    5 Storage Engines (Database Journal)

  • SHOW ENGINES
    syntax

  • Storage
    Engines and Table Types

  • The
    MyISAM Storage Engine

  • The InnoDB Storage
    Engine

  • The
    MERGE Storage Engine

  • The
    MEMORY Storage Engine

  • The
    FEDERATED Storage Engine

  • MySQL Cluster

• Table
  Maintenance (10%). Preventative and restorative maintenance are key DBA roles,
  and this section covers the various client and utility programs used in
  maintenance, types of maintenance operations, maintenance SQL statements, the
  repair of InnoDB tables and MyISAM auto-repair.
  • Using
    myisamchk for crash recovery

  • How to check MyISAM
    tables for errors

  • How to repair tables

  • Table
    optimization

  • Getting
    information about a table

  • Setting
    up a table maintenance schedule

  • mysqlcheck – A
    Table Maintenance and Repair Program

  • myisamchk – MyISAM
    Table-Maintenance Utility

  • ANALYZE TABLE
    Syntax

  • BACKUP TABLE
    Syntax

  • CHECK TABLE
    Syntax

  • CHECKSUM TABLE
    Syntax

  • OPTIMIZE
    TABLE Syntax

  • 13.5.2.6 REPAIR
    TABLE Syntax

  • 13.5.2.7
    RESTORE TABLE Syntax

  • Backing up and
    recovering an InnoDB database

• The
  INFORMATION_SCHEMA Database (5%), which includes using INFORMATION_SCHEMA as
  opposed to SHOW, INFORMATION schema syntax and the limitations INFORMATION
  SCHEMA database.
  • The
    INFORMATION_SCHEMA SCHEMATA Table

  • The
    INFORMATION_SCHEMA TABLES Table

  • The
    INFORMATION_SCHEMA COLUMNS Table

  • The
    INFORMATION_SCHEMA STATISTICS Table

  • The
    INFORMATION_SCHEMA USER_PRIVILEGES Table

  • The
    INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table

  • The
    INFORMATION_SCHEMA TABLE_PRIVILEGES Table

  • The
    INFORMATION_SCHEMA COLUMN_PRIVILEGES Table

  • The
    INFORMATION_SCHEMA CHARACTER_SETS Table

  • The
    INFORMATION_SCHEMA COLLATIONS Table

  • The
    INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table

  • The
    INFORMATION_SCHEMA TABLE_CONSTRAINTS Table

  • The
    INFORMATION_SCHEMA KEY_COLUMN_USAGE Table

  • The
    INFORMATION_SCHEMA ROUTINES Table

  • The
    INFORMATION_SCHEMA VIEWS Table

  • The
    INFORMATION_SCHEMA TRIGGERS Table

  • Other
    INFORMATION_SCHEMA Tables

  • Extensions to
    SHOW Statements

  • SHOW STATEMENTS
    and INFORMATION SCHEMA

• Backing Up and
  Restoring data (15%). Every DBA will have to face the eventuality of something
  going wrong, and will need to know all about backing up and restoring,
  specifically using replication to assist in a backup strategy, making binary
  and text backups (and the differences between the two types), backing up log
  and status files, data recovery, and disaster prevention with MySQL Cluster
  • MySQL
    Backups (Database Journal)

  • Restoring
    lost data from the Binary Update Log (Database Journal)

  • mysqlhotcopy –
    A Database Backup Program

  • mysqldump – A
    Database Backup Program

  • Example
    Backup and Recovery Strategy

  • Database Backups

  • Point-in-time
    recovery

  • Table
    Maintenance and Crash Recovery

  • SELECT syntax

  • Online
    Backup of MySQL Cluster

  • Replication

MySQL DBA II

• Stored
  Procedures (5%). A few years ago, MySQL DBA’s could get away with no knowledge
  of Stored Procedures. Now it’s key. This section covers the use of stored
  routines and triggers for performance and security.
  • Stored
    Procedures: Part III (Database Journal)

  • Stored
    Procedures: Part II (Database Journal)

  • Stored
    Procedures: Part I (Database Journal)

  • Stored
    Procedures, Functions, Triggers, and Replication: Frequently Asked Questions

  • Stored
    Procedures and Functions

• User
  management (20%). Managing user access is critical, and this key section covers
  client access and managing user accounts.
  • What the
    Privilege System Does

  • How the Privilege
    System Works

  • Privileges
    Provided by MySQL

  • Connecting to the
    MySQL Server

  • Access
    Control, Stage 1: Connection Verification

  • Access
    Control, Stage 2: Request Verification

  • When
    Privilege Changes Take Effect

  • 5.8.8 Causes
    of Access denied Errors

  • 5.8.9
    Password Hashing as of MySQL 4.1

  • MySQL Usernames
    and Passwords

  • Adding New User
    Accounts to MySQL

  • Removing User
    Accounts from MySQL

  • Limiting
    Account Resources

  • Assigning Account
    Passwords

  • Keeping
    Your Password Secure

  • Using
    Secure Connections

• Securing the
  server (10%). Covers operating system, network and filesystem security, as well
  as security issues relating to log files and FEDERATED tables.
  • Securing
    the initial MySQL accounts

  • General
    security guidelines

  • Making
    MySQL secure against attackers

  • Security-related
    mysqld options

  • How to use
    FEDERATED tables

• Security
  issues around upgrades (5%). Covers upgrading the privilege tables, and
  security issues around SQL mode values.
  • Upgrade
    MySQL system tables

  • The Server
    SQL mode

• Optimizing
  Queries (15%). Although more key for developers, a good DBA will need to take
  responsibility (at they usually take the blame when something goes wrong) for
  rooting out underperforming queries. This section covers using EXPLAIN to
  analyze queries, identifying queries that need further analysis, using SHOW
  WARNINGS for optimization and MyISAM index caching.
  • Optimizing MySQL Queries
    and Indexes (Web Developer’s Virtual Library)

  • MyISAM key
    cache

  • Optimizing Queries
    with EXPLAIN

  • SHOW WARNINGS
    syntax

  • SHOW
    PROCESSLIST syntax

• Optimizing
  Schemas (15%). Another key section which covers optimizations specific to InnoDB,
  MyISAM, MEMORY and MERGE tables, as well as general optimizations, and database
  normalization.
  • Database Normalization
    (Web Developer’s Virtual Library)

  • Design Choices

  • Make Your Data as
    Small as Possible

  • Procedure Analyse

  • MyISAM
    formats

  • The
    MEMORY storage engine

  • The InnoDB storage
    engine

  • The
    MERGE storage engine

• Optimizing the
  Server (10%). Covers determining the load on the server, making sense of mysqld
  data, using the query cache and tuning memory parameters.
  • MySQL’s
    Query Cache (Database Journal)

  • Optimizing
    MySQL, Hardware and the mysqld variables (Web Developer’s Virtual Library)

  • The MySQL Query
    cache

  • SHOW
    VARIABLES syntax

  • SET syntax

  • SHOW STATUS
    syntax

  • SHOW
    PROCESSLIST syntax

  • Tuning
    server parameters

  • The MySQL Server
    cache

• Interpreting
  Error Messages (5%). Covers use of the slow query log, the error log, and other
  diagnostic sources
  • The slow
    query log

  • The error log

• Optimizing the
  Environment (5%). The physical environment also needs consideration when
  optimizing. This section covers configuring disks, optimizing the operating
  system and choosing hardware for use with MySQL
  • Optimization
    overview

  • Disk issues

  • Using
    symbolic links

  • Operating
    system-specific notes

  • File
    not found

  • System factors and
    startup parameter tuning

• Scaling MySQL
  (10%). All the optimizations in the world may not be enough, and at some point
  you may need to look beyond a single server. This section covers using multiple
  servers, and replication.
  • Database
    replication in MySQL (Database Journal)

  • Replication

  • Running
    multiple MySQL servers on the same machine

Conclusion

You should be aware now
that achieving the DBA certification is not going to be easy, and that the
exams test a broad range of knowledge. But, with all the available resources,
and sufficient practical experience, there’s no reason you can’t succeed in
achieving it. Good luck!

Other resources

• A
  Database Journal Guide to MySQL 5 Certification: The Developer stream

• A
  Database Journal Guide to MySQL Certification: Part 1, Core Certification

• A
  Database Journal Guide to MySQL Certification: Part 2, Professional
  Certification

• MySQL 5
  Certification Study Guide

• MySQL 5
  certification candidate guide

• MySQL 5 certification
  overview

»


See All Articles by Columnist Ian Gilfillan