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

MySQL Professional Certification

Last month we looked at the MySQL Core certification, aimed at MySQL users and developers. For people wanting to go even further, there is another MySQL certification - the MySQL Professional certification, aimed at database administrators. The Core certification is a prerequisite for the Professional certification. The Pro test is more difficult, and requires experience in administrating and configuring a MySQL database server, so it is not for everybody. This month we provide an outline of the Pro certification, and list of resources you will find invaluable in preparing for the test.

MySQL Professional Certification Outline

The test covers MySQL 4.1, and tests the typical skills required by a MySQL administrator. In general, this includes installing (on different platforms), configuring optimizing and securing installations, as well as in-depth knowledge of the MyISAM and InnoDB table types.

The complete list of exam topics (as defined by MySQL in May 2005) follows, as well as links to relevant Database Journal and other articles that can help you study this topic: As of February 1st, 2005, questions on the certification exam pertain to MySQL 4.1.

• MySQL Architecture (15%). Includes client-server issues, choosing and connecting clients, hard drive and memory footprints, log and status files and table types.
• Restoring lost data from the Binary Update log - Database Journal
• MySQL's Over-looked and Under-worked Slow Query Log - Database Journal
• More MySQL Logs - Database Journal
• Table Types in MySQL: Part 1 - HEAP tables - Database Journal (note, HEAP tables are now called MEMORY tables).
• The MySQL log files
• MySQL Storage engines and table types
• MySQL client and utility programs
• Installing and configuring MySQL (20%). Includes installing (on Windows and Linux, from binaries and source), starting up and shutting down (on Windows and Unix), configuring, compiling, upgrading and optimizing MySQL, configuring disks and choosing hardware.
• Optimizing MySQL: Hardware and the Mysqld Variables - Database Journal
• General Installation issues
• Installing MySL Using a binary distribution
• Installing MySQL on Windows
• Installing MySQL on Linux
• Installing MySQL on other Unix-like systems
• Installing MySQL using a source distribution
• Post installation setup and testing
• Upgrading MySQL
• The MySQL Server and Server Startup Scripts
• Configuring the MySQL server
• The MySQL Server shutdown process
• Disk issues
• Security Issues (15%). Includes securing MySQL, client access and managing user accounts.
• An introduction to MySQL permissions - Database Journal
• MySQL user account management
• The MySQL access privilege system
• General security issues
• Optimizing for Query Speed (15%). Includes using and optimizing indexes, using EXPLAIN for analysis, enhancing query performance and optimizing the logical database structure.
• Optimizing MySQL: Queries and Indexes - Database Journal
• Optimizing Database Structure
• Optimizing SELECT statements and other queries
• MyISAM Tables (10%). Includes MyISAM optimizations, locking, disaster recovery, checking, repairing and maintaining MyISAM tables.
• MySQL Backups - Database Journal
• Repairing Database Corruption in MySQL - Database Journal
• Disaster prevention and recovery
• Locking issues
• InnoDB Tables (10%). Includes ACID compliance, the transaction model, versioning, concurrency and isolation levels, InnoDB optimizations, locking, disaster recovery, checking, repairing and maintaining InnoDB tables .
• Table Types in MySQL: Part 2 - InnoDB Tables - Database Journal
• MySQL Backups - Database Journal
• Transactions in MySQL - Database Journal
• MySQL Transactions, Part II - Transaction Isolation Levels - Database Journal
• The InnoDB table type
• Disaster prevention and recovery
• Locking issues
• Advanced Features (15%). Includes understanding the mysqld server information, measuring load, tuning server parameters, the query cache, using multiple servers and replication.
• Optimizing the mysqld variables - Database Journal
• Database Replication in MySQL - Database Journal
• MySQL's Query cache - Database Journal
• MySQL replication
• Running multiple servers on the same machine
• The MySQL Query cache
• Optimizing the MySQL server
• SHOW VARIABLES syntax
• SHOW STATUS syntax
• SHOW WARNINGS syntax
• SET syntax

Other resources

• A Database Journal Guide to MySQL Certification: Part 1, Core Certification
• Exam Contents - MySQL Professional certification
• MySQL Certification Study Guides
• MySQL 4 certification study guide addendum

» See All Articles by Columnist Ian Gilfillan