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.
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.
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.
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.
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.
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.
The
INFORMATION_SCHEMA Database (5%), which includes using INFORMATION_SCHEMA as
opposed to SHOW, INFORMATION schema syntax and the limitations INFORMATION
SCHEMA database.
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
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.
Securing the
server (10%). Covers operating system, network and filesystem security, as well
as security issues relating to log files and FEDERATED tables.
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
Schemas (15%). Another key section which covers optimizations specific to InnoDB,
MyISAM, MEMORY and MERGE tables, as well as general optimizations, and database
normalization.
Optimizing the
Server (10%). Covers determining the load on the server, making sense of mysqld
data, using the query cache and tuning memory parameters.
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
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.
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!
» 
Comment and Contribute