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