Last time we looked at
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
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.
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.
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.
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.
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
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.
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.
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
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!