MySQL Certification Candidate Guide

Copyright 2005-2008 MySQL AB

Note: The information contained in this guide is informative in nature. MySQL AB reserves the right to change the content, without notice.

This document was last updated on June 6th, 2008

Table of Contents

What Is a MySQL Certification?
   Benefits of Certification to Individuals
   Benefits of Certification to Companies
Other Sources of Certification Information
The MySQL Certification Program
   MySQL Certification Tracks
Registering for Exams
   How to Register for or Reschedule Exams With Pearson VUE
   Other Opportunities
   Exam Prices
   Beta Exams
Preparing for the Exams
How the Exams Proceed
   Testing at Pearson VUE
   Testing with User Group or Other Event
After the Exam
   Retaking Exams
Staying Updated
How Long Are the Certifications Valid?
Certified MySQL Associate (CMA)
   When Should I Certify?
   What Knowledge Is Tested?
   Exam Contents - Certified MySQL Associate
   Preparing for the Associate Exam
Certified MySQL 5.0 Developer (CMDEV)
   When Should I Certify?
   What Knowledge Is Tested?
   Exam Contents - Certified MySQL Developer - I
   Exam Contents - Certified MySQL Developer - II
Certified MySQL 5.0 Database Administrator (CMDBA)
   When Should I Certify?
   What Knowledge Is Tested?
   Exam Contents - Certified MySQL DBA - I
   Exam Contents - Certified MySQL DBA - II
Certified MySQL 5.1 Cluster Database Administrator (CMCDBA)
   When Should I Certify?
   What Knowledge Is Tested?
   Exam Contents - Certified MySQL 5.1 Cluster DBA
Pearson VUE Contact Information
MySQL AB Certification NDA and LUA
English Exams
   MySQL AB Certification Non-Disclosure and Logo Usage Agreement
   Agreement
MySQL AB Certification NDA and LUA
Chinese and Japanese Exams

What Is a MySQL Certification?

A MySQL® certification is the credential that validates your knowledge, experience, and skills with MySQL Server and related products.

Benefits of Certification to Individuals

As a professional, holding one or more MySQL certifications shows your commitment to keeping your technical skills current and upgraded at all times. A certification is the recognized proof required to show that you have the skills and credibility to move upward in your organization, handle greater responsibilities, manage larger projects, and merit better compensation. Several independent studies prove that certified individuals, on average, are placed higher in the organization and each month bring home a larger paycheck than their noncertified colleagues.

If you're an independent contractor, a MySQL certification title on your business card is sure to increase your value in the eyes and minds of your customers. They'll see the title as a guarantee that your skills are current and your knowledge up to date, before allowing you access to their data.

Benefits of Certification to Companies

Knowing the certification level of individual employees makes it easier for management to plan who will be doing what in existing and upcoming projects because differences in experience and knowledge can be more readily identified. The task of hiring new employees is also made easier because each candidate's certification level provides the information needed to judge how well a candidate will fit into a new team.

Certification is also a proven method of testing the effectiveness of the training given to each candidate. Training might have been obtained on the job or through one of the many training courses provided by MySQL AB and its training partners (see http://www.mysql.com/training).

MySQL training partners can be found on the MySQL Partner Solutions home page (http://solutions.mysql.com).

For companies that sell MySQL-related products, having MySQL certified employees not only inspires greater customer confidence in your services and products, it's also a proof of the company's commitment to make sure that all employees are kept current with the latest breakthroughs in the technology.

Other Sources of Certification Information

This Candidate Guide is the primary source of information on the MySQL Certification program. There are also three other sources of which you should be aware:

• The certification area of the MySQL Web site (http://www.mysql.com/certification) is always the first place you should go go to find the latest news on MySQL certification. Of special interest on the Web site is the Certification FAQ, which answers most common questions not answered in this guide.
• To help you study for the exam, MySQL AB has published the MySQL 5.0 Certification Study Guide. This book covers the contents of both the Developer and Database Administrator (DBA) exams. It is available from technical booksellers worldwide. More information is available at http://www.mysql.com/training/certification/studyguides.
• For MySQL Cluster certification, a MySQL 5.1 Cluster DBA Certification Study Guide is available. This book covers the contents of the MySQL 5.1 Cluster Administrator exam. More information is available at http://www.mysql.com/training/certification/studyguides.
• To make sure that you're kept current on all things going on in the world of MySQL certification, subscribe to the certification mailing list provided by MySQL AB. This is a low-volume list on which messages are sent out every few weeks to announce the latest news on the certification program. To subscribe to this mailing list, send an email to certification-subscribe@lists.mysql.com. You'll then receive a confirmation email to which you must reply.

The MySQL Certification Program

There's more to the MySQL Certification program than passing exams: passing certification exams is a testimony to the knowledge, skills and experience you've gained working with MySQL products. When you certify, your efforts are immediately repaid with the benefits outlined earlier in this guide.

MySQL Certification is tightly integrated with the official MySQL training program. MySQL training classes not only cover the majority of the topics you will find on the certification exams, they also give you hands-on experience for the skills you must possess in your daily work as a developer or DBA.

• For general information on MySQL training classes, see http://www.mysql.com/training
• Upcoming MySQL 5.0 Developer classes
• Upcoming MySQL 5.0 DBA classes
• Upcoming MySQL Cluster classes

Before taking an exam, you should read through Preparing for the Exams to ensure that you have all the necessary prerequisites in place. How the Exams Proceed describes the details of the exam-taking process.

When you're certified, you not only get the benefits already described earlier, you also receive a welcome letter and access to even more material from MySQL. After the Exam describes this in more detail.

MySQL Certification Tracks

In 2007, the following exams are available (or will become so during the course of the year):

• CMDEV: Certified MySQL Developer (currently available for MySQL 5.0);
• CMDBA: Certified MySQL Database Administrator (currently available for MySQL 5.0);
• CMCDBA: Certified MySQL Cluster DBA (available for MySQL 5.1);
• CMA: Certified MySQL Associate

All MySQL Certifications, with the exception of the CMA certification, are version specific.

MySQL 5.0 Certifications

Two different certifications may be attained for MySQL 5.0, the MySQL 5.0 Developer Certification and the MySQL Database Administrator (DBA) Certification.

A given MySQL 5.0 certification does not imply an advancement level over the other certification. Each certification covers a distinct area of expertise, aimed at different job roles. You may pick the certification that best suits your current or aspired professional position. Some candidates follow both tracks to become an all-round MySQL professional.

To attain either of these two certifications, two exams must be passed. To attain Developer certification, you must pass the Developer-I and Developer-II exams. Likewise, for Database Administrator certification, you must pass the DBA-I and DBA-II exams. The exam part numbers I and II do not express an advancement level. Each part covers distinct areas of expertise, and the exams may be taken in any order. A certification is attained only when both exams for that certification are passed.

Each certification focuses on specific application areas, expertise, and job skills. The developer certification is targeted at candidates who will be developing applications that use MySQL 5.0 as back-end storage, whereas the DBA certification is targeted at candidates who will take the administrative role of tuning, planning, and optimizing data layout for one or many servers, but do not write many applications.

Be sure to certify for the expertise level and job function relevant for you. This will help both you and your employer prove that you have the skills sought by many companies.

MySQL 5.1 Cluster Certification

The MySQL 5.1 Cluster DBA Certification (CMCDBA) is a specialization exam for those that design, deploy and administer MySQL Cluster setups. Cluster certification is part of the DBA track, and represents an advancement level exceeding CMDBA certification. CMCDBA can be attained only by Certified MySQL DBAs. In order to attain CMCDBA certification, you must attain CMDBA certification and pass one CMCDBA exam.

MySQL 5.1 Cluster certification exams will be available starting in April 2007.

MySQL Associate Certification

The MySQL Certified Associate credential provides a foundation for further MySQL certification. Unlike other MySQL certifications, the Associate certification is not tied to any particular version of MySQL, but focuses on details of using MySQL that are universal no matter what version of the software you are using.

The Associate certification will be available in Q2 2007.

Registering for Exams

How to Register for or Reschedule Exams With Pearson VUE

MySQL AB certification exams are mainly delivered through Pearson VUE and are currently available in English and Japanese. There are three ways to register for exams:

• On the Pearson VUE Web site: http://www.vue.com/mysql
• By registering directly at a test center
• By calling a Pearson VUE call center

You can find more contact information for Pearson VUE at the end of this guide.

Subject to test center availability, you may register for an exam as late as the day on which you want to take the exam. The Pearson VUE FAQ page has additional information (see http://www.vue.com/faqs). This is also the place to look if you need to reschedule an exam.

Other Opportunities

There are a number of other opportunities for taking MySQL certification exams. Exams are offered at the yearly MySQL User's Conference, and some local MySQL User Groups also offer MySQL certification exams. Such events will be announced on the MySQL web site or locally.

Exam Prices

The price for taking any MySQL certification exam is your local equivalent of US$ 200 plus any local taxes. In some areas, discounts are applied to this price. Please check the Pearson VUE Web site, or call your regional Pearson VUE test center for exact information on pricing in your area. (Addresses and telephone numbers are listed at the end of this guide).

If your organization requires certification of multiple employees, you may consider using volume-discounted vouchers to pre-pay for exams. Contact your regional Pearson VUE office for further information (see the list of contact addresses and telephone numbers at the end of this guide).

Beta Exams

At times, MySQL AB might announce the possibility of taking part in beta testing of new or updated certification exams. Beta exams are offered at a discounted price.

Just as regular exams, beta exams count toward certification. However, results of beta exams must first be evaluated, so you won't receive the usual immediate response that is provided for regular exams. Results from a beta exam are available 3 to 4 months after the exam takes place.

Details of upcoming beta exams are published on the MySQL certification Web site and on the certification mailing list as information becomes available.

Preparing for the Exams

Make sure that you carefully read through the description for your exam that is provided later in this guide.

For each subject, read and make sure that you understand the corresponding sections in the manual.

The best way to prepare for the MySQL Certification exams is to take part in one of the training courses offered by MySQL AB. These courses are offered world-wide several times per year. See http://www.mysql.com/training for more information on upcoming courses.

Other excellent sources of preparation material are the MySQL 5.0 Certification Study Guide and MySQL 5.1 Cluster Certification Study Guide described earlier in this guide.

How the Exams Proceed

No matter whether you take an certification exam at a Pearson VUE testing center or other event such as the yearly MySQL User's Conference, the procedure is the same. The following section outlines the general procedure for taking certification exams in general. Specifics for individual testing provides are then outlined below.

All exams are designed to be "closed book" exams; you are not allowed to (and will not need to) bring books or any other materials into the testing room. The test center will provide you with a pen and note paper (which you must leave behind you when you leave the testing station).

On the day of your exam, make sure that you arrive at the test center well ahead of the appointed time. When you arrive, the test center will inform you how much time you should expect to spend to complete all steps necessary for testing.

Before taking the exam, you will be required to show two forms of identification. One of these must be a photo ID (driver's license, passport, company ID, and so on).

After you register at the test center, you'll be taken to your testing station. When the exam begins, you must first read and confirm that you understand and will adhere to the MySQL AB Certification Non-Disclosure Agreement and Logo Usage Agreement. This document is also provided at the end of this guide; it is strongly recommended that you read it and make sure you understand it before going to the exam. That way, you need not spend time reading it at the exam itself.

Most exams consist of 70 multiple-choice questions that must be answered in no more than 90 minutes. If you finish the exam before your time is up, you may leave the testing station. If you need to leave the testing station for some period of time to tend to personal needs, most testing centers will allow you to do so. Such break-time is not added to the exam ("the clock doesn't stop").

Details regarding the content of each exam are found at the end of this guide.

You should attempt to answer all questions because an unanswered question counts as a wrong answer. You can move back and forth between questions, which enables you to skip the ones you're unsure of initially, and return to any question later in the exam as time permits.

The following question types may be presented to you during the exams:

• Multiple-choice questions with a single correct answer
• Multiple-choice questions for which one or more answers are correct

On the Cluster certification exam, you will find only questions with a single correct answer.

You should always answer all the questions presented to you during the exam. Even if you don't know the answer, choosing a random answer is better than not answering at all.

Testing at Pearson VUE

Testing at Pearson VUE testing centers is computer-based. All questions are answered on a computer screen as in the following illustration.

Figure 1. Example of question display screen. The example shows a question and possible answers from a MySQL 4.0 certification exam. You may either use the mouse to click on the correct answer or type the corresponding key on the keyboard. The lower-left part of the screen tells you whether only one answer is possible or if several answers may be correct. Note that you can mark a question for review. All questions so marked are presented when you have completed the last question, allowing you to go back and review any questions you have marked.

After you complete your exam, you'll receive immediate notification whether you passed or failed. (If you are taking part in a Beta exam, the result will not be immediately available. See notes on Beta exams above.

The score report that you receive contains your pass/fail grade as well as a section-by-section breakdown on how many questions were answered right or wrong within each part of the exam.

Testing with User Group or Other Event

Exams given through user groups or at other events (such as the MySQL Conference and Expo), are all paper-based. You are given a booklet of questions and an answer sheet with "bubbles" to be filled in with a pencil. Pencil, eraser and scratch paper will be provided to you at the time of testing.

After you complete the exam, the exam papers are sent to MySQL where they are graded. This process can take several weeks, after which you will receive your result.

After the Exam

After you complete an exam, MySQL AB is notified of all results from the individual test centers within a few days.

After we receive notice of your results, we send a letter to you with details on how to access the Certification Candidate part of the MySQL Web site. Please allow 4 to 6 weeks delivery time for this letter.

If you have not received your certificate 8 weeks after attaining your certification, please send an e-mail to certification @ mysql.com. Please include details such as the exam number and your candidate ID number in the e-mail.

If you passed the exam, the letter will also contain your certification diploma and instructions on how to obtain logos for use on your business cards, stationary, personal home page, blog, personal e-mail signature, and so on. With these proofs of knowledge at your disposal, you're well equipped to enhance your career path.

Retaking Exams

Exams may be retaken at any time. However, if you fail an exam, MySQL AB strongly recommends that you spend at least two weeks doing extra studies for an exam before you retake it.

All exams are created on-the-fly from a large pool of questions, so it's unlikely that you'll be presented with any of the questions you answered in the first try. For that reason, it is essential that you prepare yourself well for a re-take of an exam.

Staying Updated

Passing exams and obtaining certification diplomas for the MySQL Developer and DBA certifications are important steps in your career path, but it doesn't stop there. MySQL Server and associated products are continually evolving, new features are being added, and so on. As these changes take place, the certification program changes as well to assist you in always being able to demonstrate that your knowledge is up to date. New and specialized certifications will be added over time, so you should check back on the MySQL certification Web site at regular intervals to stay updated. You can also keep yourself informed by subscribing to the MySQL Newsletter, which provides timely MySQL-related news items.

Staying updated is not just a question of working with MySQL Server; you need to plan to work with the topics covered on each exam, to gain the experience required for passing. In an ideal situation, your employer will take an interest in your training needs and make sure you get to work on projects related to your next exam. However, this will not always be possible even for the best of employers. Allocate time to try things out on your own, and plan this carefully so you gain sufficient experience in time for your next exam.

How Long Are the Certifications Valid?

Most MySQL certifications are tied to the version numbers of MySQL Server and related products. A change in the version number is made by MySQL AB when significant new features are introduced in MySQL Server.

As older versions become outdated, MySQL AB will no longer offer certifications related to these versions. Candidates will be notified well in advance when certification exams will no longer be available. In general, certification exams will be retracted when a product reaches the end of its Active Support timeframe. See the official MySQL Lifecycle Policy for more information. While MySQL certifications are never explicitly retired, candidates are expected to update their certifications to reflect the current production version of MySQL on a regular basis.

The exception to the above statements is the Certified MySQL Associate exam, which is not tied to any particular MySQL version. This certification is considered perpetually valid once you have obtained it.

At MySQL AB, we believe that although certifications help you prove your knowledge, certification is no substitute for experience. Nonetheless, prospective employers look more favorably at candidates who continuously update their knowledge, and recertifying helps you demonstrate that you do.

Certified MySQL Associate (CMA)

The MySQL Associate certification (CMA) is an entry level certification. It is intended for those that are relatively new to using the MySQL database server and covers basic database management system concepts as well as basic SQL. We recommend the CMA certification for MySQL users that know the basics, but have not yet obtained the experience gained by professional MySQL DBAs or Developers.

The CMA certification may be seen as a stepping stone towards the more advanced Developer (CMDEV) and Database Administrator (CMDBA) certification. Holding CMA certification is not a formal prerequisite to attaining other certifications, you should consider starting your MySQL certification path with the CMA certification for a number of reasons:

• Gain experience and familiarity in taking MySQL Certification exams
• Ensure the prerequisites at are in place before investing into more expensive CMDEV and/or CMDBA certification

When Should I Certify?

The MySQL Associate Certification is aimed at users with either these qualifications:

• Develop or administrate simple, small MySQL databases
• Have working knowledge of basic SQL, but no or only little experience managing production databases
• Planning to proceed towards Developer or DBA certification

What Knowledge Is Tested?

The sections covered by the exams are listed below, along with the relative weight of each section in the final scoring. The weights indicate the percentage of the exam that you should expect to be devoted to questions from each section.

The exam consists of 50 questions. For each question, the candidate must choose exactly one answer out of four provided possible answers. Candidates must complete the exam in no more than 60 minutes (one hour).

Exam Contents - Certified MySQL Associate

• Theory, Terminology and Concepts (10%)
  • Client/Server Concepts
  • Database and Database Objects
• Data Definition using SQL (25%)
  • Databases
  • Data Types
  • Tables
  • Constraints and Indexes
  • Views
• Basic Data Manipulation using SQL (25%)
  • Recurring SQL Constructs
  • Adding data
  • Modifying data
  • Removing data
  • Searching data
• Advanced Data Manipulation using SQL (20%)
  • Expressions
  • Grouping and Aggregate Functions
  • Joining Tables
• Transactions (10%)
  • Transaction Concepts
  • SQL for working with Transaction
• Import/Export (10%)
  • Tools for Import/Export
  • SQL for Import/Export

Preparing for the Associate Exam

The best way to prepare for the Certified MySQL Associate exams is to take part in the MySQL for Beginners training course offered by MySQL AB. This and other courses are offered world-wide several times per year. See http://www.mysql.com/training for more information on upcoming courses.

It is also possible to prepare for the certified MySQL Associate exam through self-study. Candidates that choose to prepare themselves through self-study should consider purchasing the MySQL 5.0 Certification Study Guide. This is the same book that is used to prepare for the more advanced Certfified MySQL Developer and Certified MySQL Administrator exams, so purchasing this book is an excellent first step in becoming a fully certified MySQL professional.

For the Certified MySQL Associate exam, the following parts of the study guide are required reading:

• Chapter 1: Sections 1.1 and 1.2
• Chapter 5: Sections 5.1 through 5.4, 5.5 (but not 5.5.2 and 5.5.3), 5.6 and 5.7
• Chapter 7: All
• Chapter 8: All
• Chapter 9: All, except for 9.6
• Chapter 10: 10.5
• Chapter 11: All
• Chapter 14: Sections 14.1, 14.2 (but not 14.2.1 through 14.2.3), 14.3, 14.4, 14.6
• Chapter 15: All, except 15.2.3 and 15.3.1
• Chapter 29: Parts 29.4.2 and 29.4.3
• Chapter 32: Section 32.4

Sections 5.4.1, 5.4.3 and 5.6.2 are required reading. Usage of character sets and collations is not part of the associate exam, but these sections must be studied to understand issues surrounding string data types in general.

Some sections mentioned above include information on how to obtain MySQL metadata using SHOW commands and the INFORMATION_SCHEMA database. Knowledge of the INFORMATION_SCHEMA database is not required for the Associate exam, but is highly recommended none the less.

In addition, we strongly recommend studying the following parts:

• Chapter 2 (all)
• Chapter 5: parts 5.4.1, 5.4.3
• Chapter 20: 20.3
• Chapter 25: 25.5, 25.6
• Chapter 29: 29.4.6
• Chapter 31: All

The parts that are recommended reading will aid in understanding the parts that are required reading. The exam questions directly relate to the subject matter covered in the parts that are required reading.

Certified MySQL 5.0 Developer (CMDEV)

With the MySQL Developer Certification in hand, you have proof that you're capable of creating and using databases and tables, and maintaining (inserting, retrieving, modifying, deleting) data stored in a MySQL database.

The job role of a person holding the MySQL Developer Certification typically will be to maintain data in company databases, perform data analysis, import and export data, and so forth.

When Should I Certify?

The MySQL Developer Certification is aimed at users with these qualifications:

• Have used MySQL Server for one man-month (150 hours) or more
• Often find themselves in situations where they must pull data from a MySQL database or perform operations such as importing large amounts of data into the database
• Have done some semi-advanced reporting using features such as LIMIT, WHERE, inner and outer joins, aggregate functions, subqueries, stored procedures and the like.

What Knowledge Is Tested?

MySQL Developer Certification is achieved by passing two exams. The sections covered by the exams are listed below, along with the relative weight of each section in the final scoring. The weights indicate the percentage of the exam that you should expect to be devoted to questions from each section.

The exam consists of 70 questions, and you have 90 minutes in which to answer them. (If you're taking part in a Beta exam, there are 100 questions, and you have 2 1/2 hours to answer them).

Note: In the MySQL 5.0 Certification Study Guide and in earlier versions of this document, it was expected that questions on MySQL Query Browser would be part of the exam. For a number of technical reasons, questions on the MySQL Query Browser do not appear on the final version of the exams.

Exam Contents - Certified MySQL Developer - I

• Client/Server Concepts (5%)
  • General MySQL Architecture
  • Invoking Client Programs
  • Server SQL Modes
• The mysql Client Program (10%)
  • Using mysql Interactively
  • Statement Terminators
  • The mysql Prompts
  • Editing Keys in mysql
  • Using Script Files with MySQL
  • mysql Output Formats
  • Client Commands and SQL Statements
  • Using Server-Side Help
  • Using the --safe-updates Option
• Connectors (5%)
  • MySQL Client Interfaces
  • Connector/ODBC
  • Connector/J
  • Connector/NET
• Data Types (15%)
  • Numeric Data Types
  • The BIT Data Type
  • String Data Types
  • Temporal Data Types
  • Column Attributes
  • Using the AUTO_INCREMENT Column Attribute
  • Handling Missing or Invalid Data Values
• Identifiers (5%)
  • Identifier Syntax
  • Case Sensitivity
  • Using Qualifed Names
  • Reserved Words as Identifiers
• Databases (5%)
  • Database Properties
  • Creating Databases
  • Altering Databases
  • Dropping Databases
  • Obtaining Database Metatdata
• Tables and Indexes (15%)
  • Table Properties
  • Creating Tables
  • Altering Tables
  • Emptying Tables
  • Dropping Tables
  • Indexes
  • Dropping Indexes
  • Obtaining Table and Index Metadata
• Querying for Data (15%)
  • Using SELECT to Retrieve Data
  • Specifying Which Columns to Retrieve
  • Specifying Which Rows to Retrieve
  • Aggregating Results
  • Grouping Results
  • Using UNION
• SQL Expressions (15%)
  • Components of SQL Expressions
  • Numeric Expressions
  • String Expressions
  • Temporal Expressions
  • NULL Values
  • Functions in SQL Expressions
  • Comments in SQL Statements
• Updating Data (10%)
  • Update Operations
  • The INSERT Statement
  • The REPLACE Statement
  • The UPDATE Statement
  • The DELETE and TRUNCATE TABLE Statements
  • Privileges Required for Update Statements

Exam Contents - Certified MySQL Developer - II

• Joins (10%)
  • Overview
  • Writing Inner Joins
  • Writing Outer Joins
  • Resolving Name Clashes Using Qualifiers and Aliases
  • Multiple-Table UPDATE and DELETE Statements
• Subqueries (10%)
  • Types of Subqueries
  • Subqueries as Scalar Expressions
  • Correlated Subqueries
  • Comparing Subquery Results to Outer Query Columns
  • Comparison Using Row Subqueries
  • Using Subqueries in the FROM Clause
  • Converting Subqueries to Joins
  • Using Subqueries in Updates
• Views (15%)
  • Reasons to Use Views
  • Creating Views
  • Altering Views
  • Dropping Views
  • Checking Views
  • Displaying Information About Views
  • Privileges for Views
• Importing and Exporting Data (5%)
  • Import and Export Operations
  • Importing and Exporting Using SQL
  • Importing and Exporting Data from the Command Line
• User Variables (5%)
  • User Variable Syntax
  • User Variable Properties
• Prepared Statements (5%)
  • Benefits of Prepared Statements
  • Using Prepared Statements from the mysql Client
  • Preparing a Statement
  • Executing a Prepared Statement
  • Deallocating a Prepared Statement
• Stored Routines (20%)
  • Benefits of Stored Routines
  • Differences Between Stored Procedures and Functions
  • The Namespace for Stored Routines
  • Defining Stored Routines
  • Creating Stored Routines
  • Altering Stored Routines
  • Dropping Stored Routines
  • Invoking Stored Routines
  • Obtaining Stored Routine Metadata
  • Stored Routine Privileges and Execution Security
• Triggers (5%)
  • Reasons to Use Triggers
  • Trigger Concepts
  • Creating a Trigger
  • Restrictions on Triggers
  • Referring to Old and New Column Values
  • Destroying a Trigger
  • Privileges Required for Triggers
• Obtaining Database Metadata (10%)
  • Overview of Metadata Access Methods
  • Using INFORMATION_SCHEMA to Obtain Metadata
  • Using SHOW and DESCRIBE to Obtain Metadata
  • Using mysqlshow to Obtain Metadata
• Debugging MySQL Applications (5%)
  • Interpreting Error Messages
  • The SHOW WARNINGS Statement
  • The SHOW ERRORS Statement
  • The perror Utility
• Basic Optimizations (10%)
  • Overview of Optimization Principles
  • Using Indexes for Optimization
  • General Query Enhancement
  • Choosing Appropriate Storage Engines
  • Normalization

Certified MySQL 5.0 Database Administrator (CMDBA)

With the title of Certified MySQL DBA on your résumé, you have proof that you master server-related issues, such as installing a server from scratch, keeping the server running smoothly at all times, using the the storage engine for a given task, and analyzing the trouble spots of other users' queries.

The typical job role of a MySQL DBA is in setting up, managing and optimizing one or more MySQL servers in an organization, often helping a team of one or more users and programmers of MySQL products.

When Should I Certify?

MySQL DBA Certification attests that the person holding the certification knows how to maintain and optimize an installation of one or more MySQL servers, and perform administrative tasks such as monitoring the server and making backups. Other tasks may include:

• Installing a MySQL server without assistance
• Troubleshooting queries that take too long to run
• Granting other users access to some of the databases on the server in a secure manner
• Ability to utilize the MySQL server architecture, the pluggable storage engine architecture, supported index types and replication setups.
• Planning and implementing backup and recovery plans.

What Knowledge Is Tested?

MySQL DBA Certification is achieved by passing two exams. The sections covered by the exams are listed below, along with the relative weight of each section in the final scoring. The weights indicate the percentage of the exam that you should expect to be devoted to questions from each section.

The exam consists of 70 questions, and you have minutes in which to answer them. (If you're taking part in a Beta exam, there are 100 questions, and you have 2 1/2 hours to answer them).

Note: In the MySQL 5.0 Certification Study Guide and in earlier versions of this document, it was expected that questions on MySQL Administrator would be part of the exam. For a number of technical reasons, questions on the MySQL Administrator do not appear on the final version of the exams.

Exam Contents - Certified MySQL DBA - I

• MySQL Architecture (10%)
  • Client/Server Overview
  • Communication Protocols
  • The SQL Parser and Storage Engine Tiers
  • How MySQL Uses Disk Space
  • How MySQL Uses Memory
• Starting, Stopping, and Configuring MySQL (20%)
  • Types of MySQL Distributions
  • Starting and Stopping MySQL Server on Windows
  • Starting and Stopping MySQL Server on Unix
  • Runtime MySQL Configuration
  • Log and Status Files
  • Loading Time Zone Tables
  • Security-Related Configuration
  • Setting the Default SQL Mode
  • Upgrading MySQL
• Client Programs for DBA Work (5%)
  • Overview of Administrative Clients
  • mysql
  • mysqladmin
  • mysqlimport
  • mysqldump
  • Client Program Limitations
• Character Set Support (5%)
  • Performance Issues
  • Choosing Data Types for Character Columns
• Locking (10%)
  • Locking Concepts
  • Explicit Table Locking
  • Advisory Locking
• Storage Engines (20%)
  • MySQL Storage Engines
  • The MyISAM Engine
  • The MERGE Engine
  • The InnoDB Engine
  • The MEMORY Engine
  • The FEDERATED Engine
  • The Cluster Storage Engine
  • Other Storage Engines
• Data (Table) Maintenance (10%)
  • Types of Table Maintenance Operations
  • SQL Statements for Table Maintenance
  • Client and Utility Programs for Table Maintenance
  • Repairing InnoDB Tables
  • Enabling MyISAM Auto-Repair
• The INFORMATION_SCHEMA Database (5%)
  • INFORMATION_SCHEMA Access Syntax
  • INFORMATION_SCHEMA Versus SHOW
  • Limitations of INFORMATION_SCHEMA
• Data Backup and Recovery Methods (15%)
  • Introduction
  • Binary Versus Textual Backups
  • Making Binary Backups
  • Making Text Backups
  • Backing Up Log and Status Files
  • Replication as an Aid to Backup
  • MySQL Cluster as Disaster Prevention
  • Data Recovery

Exam Contents - Certified MySQL DBA - II

• Stored Routines (5%)
  • Using Stored Routines and Triggers for Security Purposes
  • Using Stored Routines to Enhance Performance
• User Management (20%)
  • User Account Management
  • Client Access Control
• Securing the Server (10%)
  • Security Issues
  • Operating System Security
  • Filesystem Security
  • Log Files and Security
  • Network Security
  • FEDERATED Table Security
• Upgrade-Related Security Issues (5%)
  • Upgrading the Privilege Tables
  • Security-Related SQL Mode Values
• Optimizing Queries (15%)
  • Identifying Candidates for Query Analysis
  • Using EXPLAIN to Analyze Queries
  • Using SHOW WARNINGS for Optimization
  • MyISAM Index Caching
• Optimizing Schemas (15%)
  • General Table Optimizations
  • Normalization
  • MyISAM-Specific Optimizations
  • InnoDB-Specific Optimizations
  • MERGE-Specific Optimizations
  • MEMORY-Specific Optimizations
• Optimizing the Server (10%)
  • Interpreting mysqld Server Information
  • Measuring Server Load
  • Tuning Memory Parameters
  • Using the Query Cache
• Interpreting Error Messages (5%)
  • Sources of Diagnostic Information
  • Using the Error Log for Diagnostic Purposes
  • Using The Slow Query Log for Diagnostic Purposes
• Optimizing the Environment (5%)
  • Choosing Hardware for MySQL Use
  • Configuring Disks for MySQL Use
  • Network Issues
  • Optimizing the Operating System for MySQL Use
• Scaling MySQL (10%)
  • Using Multiple Servers
  • Replication

Certified MySQL 5.1 Cluster Database Administrator (CMCDBA)

A CMCDBA is capable of administering databases built on the MySQL Cluster technology. In addition to being a Certified MySQL Database Administrator, a CMCDBA can design, deploy, configure and maintain MySQL Clusters, to provide a highly available and scalable database. Specific associated skills include addition of Cluster nodes, performing software upgrades, and setting up replication without suffering downtime.

The typical job role for a CMCDBA is maintaining and performing continuous service on one or more MySQL Cluster setups, in cooperation with users and developers. The CMCDBA understands the underlying logic and influence of hardware components in a Cluster system, and is able to provide the necessary degree of security and performance throughout the system.

When Should I Certify?

You must hold the Database Administrator (CMDBA) certification before you can attain CMCDBA certification. Once you have the CMDBA certification, you can proceed to study for the Cluster certification exam.

As with other MySQL certification exams, it is strongly recommended that you obtain a good amount of practical experience before taking the exam. You can study on your own, or you can attend a MySQL training course and combine that with self-study using the MySQL Cluster Certification Study Guide to get the experience you need to pass the exam.

What Knowledge Is Tested?

MySQL Cluster DBA Certification is achieved by first attaining the Database Administrator Certification, then passing the Certified MySQL Cluster Administrator exam. The sections covered by the exams are listed below, along with the relative weight of each section in the final scoring. The weights indicate the percentage of the exam that you should expect to be devoted to questions from each section.

The exam consists of 70 questions that must be answered in 90 minutes or less.

Exam Contents - Certified MySQL 5.1 Cluster DBA

• Clustering Concepts (5%)
  • High Availability
  • Scalability
  • Clustering Architectures
• Architecture and Organization (10%)
  • Components
  • Organization
• Software Processes (5%)
  • Local Replication
  • Cluster Partitioning
  • Failure Detection/Recovery
• The NDB Storage Engine (15%)
  • Usage
  • Transactions
  • Indexes
  • Disk Based Storage
  • Limitations
• Cluster Deployment (5%)
  • General
  • Physical Configuration
  • Harware Requirements/Recommendations
  • Topologies
• Configuration (10%)
  • Configuration Files
  • Connectstring
  • Generic Options
  • Storage Options
• Starting, Stopping and Monitoring a Cluster (10%)
  • Start/Stop/Restart
  • Adding/Removing Nodes
  • Monitor/Management
• Security (5%)
  • Security
• Backup and Recovery (10%)
  • General
  • Native Backup
  • Mysqldump
• Global Replication (5%)
  • Global Replication (General)
  • Binlog
  • Setting Up Replication
• Performance and Tuning (10%)
  • Data Access Methods
  • Configuration
  • Query Cache
  • Network Issues
  • Mysqld Options
• Troubleshooting and Problem Solving (10%)
  • Monitoring
  • Node Failure
  • General Issues

Pearson VUE Contact Information

There are three ways to register for MySQL AB exams at Pearson VUE:

• On the Pearson VUE Web site: http://www.vue.com/mysql
• By registering directly at a test center
• By calling a Pearson VUE call center (see the following information)

MySQL AB strongly recommends that you register through the Pearson VUE Web site at http://www.vue.com/mysql. Note that if you aren't already registered as a MySQL candidate at Pearson VUE, your registration might take up to 24 hours to complete.

If you want to register directly at a test center, you can find a list of test centers at http://www.vue.com/mysql. Click on the Test Centers link about halfway down the page.

Lastly, you can call one of the Pearson VUE call centers to register for an exam. Use one of the following telephone numbers to reach the Pearson VUE call center in your area.

Americas

• Canada: (800) 247-8715
• United States: (800) 247-8715
• From outside Canada or USA: +1 952 681-3807

Asia-Pacific Region

• Australia: 1-800-356-022
• Hong Kong: 800-930-988
• Japan: 0120-355-173
• Korea, South: 00798-6136-1048
• Malaysia: 1800-808-578
• New Zealand: 0800-445-884
• Philippines: 1800-1110-0796
• Singapore: 800-6161-888
• Taiwan 0080-1611-289
• From outside one of the listed countries: +61-2-9478-5400

Europe, Middle East, Africa

• Austria: 0800-292150
• Belgium - Dutch: 0800-74174
• Belgium - French: 0800-74175
• France: 0800-904757
• Germany: 0800-0826499
• Ireland: 1-800-552131
• Israel: 1-80-9453797
• Italy: 800-790521
• Netherlands: 0800-0235323
• Portugal: 800-831429
• South Africa: 0800-995044
• Spain: 900-993190
• Sweden: 020-798690
• Switzerland: 0800-837550
• Turkey: 00800448822063
• United Kingdom: 0800-7319905
• From outside one of the listed countries: +44-161-855-7455

MySQL AB Certification NDA and LUA
English Exams

Before taking any test for the certification program, you are required to accept the combined MySQL AB Certification Non-Disclosure Agreement (NDA) and Certification Logo Usage Agreement (LUA).

Below is a copy of the text, which you'll be required to accept at the test center as the first action after the test has been started.

We strongly suggest that you read through and make sure that you understand the document now, so that you do not spend valuable time on this after the test has begun.

MySQL AB Certification Non-Disclosure and Logo Usage Agreement

Introduction

Before taking any test under the MySQL AB Certification Program (a "Certification Exam"), you will be required to accept the terms of this MySQL AB Certification Non-Disclosure and Logo Usage Agreement (this "Agreement").

In addition, before using any Certification Logo (as defined below), you will be required to pass a Certification Exam, and you must receive electronic or written notice from MySQL AB stating that you may use that particular Certification Logo.

This Agreement is created to protect your certification. By adhering to this Agreement (that is, keeping questions, answers and other information related to the Certification Exams confidential and by ensuring that the Certification Logos are used only by you), you help maintain the integrity of the MySQL AB Certification Program. This helps maintain the value of your Certification(s).

By clicking the NEXT button below, you agree that the terms and conditions of this Agreement shall be valid and binding upon you.

Agreement

1. Non-Disclosure Agreement

I understand that the MySQL AB Certification Program and Certification Exam(s) are proprietary to MySQL AB. Therefore, I agree to the following terms and conditions:

1. I will not copy, disclose, publish or transmit to any person or entity any details of any Certification Exam, or information pertaining to it, in any form or by any means.
2. I alone must complete in full all the requirements for MySQL AB certification and/or assessment.
3. All work submitted in connection with the MySQL AB Certification Program will be my own, and not the work of any other individual, group or entity.
4. All MySQL AB certification information, including (without limitation) exams, questions, answers and related information, is made available to me strictly for the purpose of certification. All such information is proprietary material that is wholly owned by MySQL AB and protected under copyright, trade secret and other laws.

2. Logo Usage Agreement

I understand that MySQL AB may, from time to time, develop a logo for limited use by those individuals who have passed a particular Certification Exam (a "Certification Logo"). Therefore, I agree to the following terms and conditions:

1. This Agreement permits me to use only the Certification Logos expressly identified in an electronic or written notice provided to me by MySQL AB, and only in the manner described herein.
2. I may only use a Certification Logo that pertains to a Certification level that I, as an individual, have achieved, and only to identify me as an individual who is certified by MySQL AB. I may use a Certification Logo in printed and/or electronic forms as long as I always use a Certification Logo in close proximity to my personal name. I will not use a Certification Logo, or any imitation thereof, in any other place, such as on product packaging. My certification is personal and does not apply to or benefit any company or organization that I may own, or for whom I may work or otherwise be associated. Such company or organisation may not use a Certification Logo without prior written permission from MySQL AB,
3. I may not alter a Certification Logo in any way, including (without limitation) by resizing, translating, or altering the colours used in, the Certification Logo. A Certification Logo may be scaled proportionally in order to adhere to the conditions of this Agreement.
4. My certification will expire as published by MySQL AB from time to time. In the event that my certification expires or is otherwise terminated, I will immediately discontinue my usage of the applicable Certification Logo.
5. I may not display or use the Certification Logo in a manner that suggests that I am an employee or other representative of MySQL AB, or that either my company or I are affiliated with, or endorsed by, MySQL AB. Moreover, everywhere I use a Certification Logo, my name, or the name of my company, must appear more prominently than, and clearly distinguished from, the Certification Logo.
6. My use of any and all Certification Logos shall further be restricted by, and subject to, the MySQL Trademark Policy ("The Trademark Policy") posted on the MySQL AB Web site (www.mysql.com, the "Web site"). MySQL AB may from time to time change the Trademark Policy, and I agree to check the Web site for changes to the Trademark Policy on a periodic basis. I shall be deemed to have knowledge of any changes to the Trademark Policy as soon as they are posted on the Web site. MySQL AB reserves any and all rights not expressly granted herein. I agree that any and all goodwill pertaining to my use of a Certification Logo shall inure to the exclusive benefit of MySQL AB.

3. Miscellaneous

1. I hereby declare that: (a) I am the individual registered and authorised to complete the Certification Exam, identified by such unique information as my MySQL AB Certification user id and password, electronic mail address, given and family names, or such other means used by MySQL AB to uniquely identify an individual; and (b) I am at least eighteen (18) years old and competent to sign this Agreement.
2. I understand that violation of any of the terms of this Agreement will result in: (a) the immediate revocation of any existing MySQL AB certification; (b) my permanent ineligibility to obtain any MySQL certification; and/or (c) other punitive or legal action deemed appropriate by MySQL AB.
3. If the test center at which I am accepting this Agreement is located in the United States of America, then this Agreement shall be deemed to have been executed in the United States of America and shall be governed by the laws of the State of Delaware, without regard to the conflict of laws provisions thereof. If the test center at which I am signing this Agreement is located anywhere other than the United States of America, then this Agreement shall be deemed to have been executed in Sweden and shall be governed by the laws of Sweden, without regard to the conflict of laws provisions thereof. I have agreed to execute this Agreement in the English language. In the event of any dispute in connection with this Agreement, the English language version of the Agreement will control for all purposes. Any action brought under this Agreement shall be conducted in the English language.
4. By clicking the "NEXT" button displayed below, I am confirming that I understand and accept all of the terms and conditions of this Agreement. I understand that I am under no obligation to accept these terms, but that my acceptance is required to proceed with this Certification Exam.

You must accept the above terms and click "NEXT" in order to proceed with the exam.

If you do not accept the above terms, click "END EXAM". You will not be able to proceed with the exam.

MySQL AB Certification NDA and LUA
Chinese and Japanese Exams

When taking the MySQL certification exam, you agreed to the following
Non-Disclosure and Logo Usage Agreement. This is a copy for your reference.

Introduction

Before taking any test under the MySQL Certification Program (a "Certification Exam"), you will be required to accept the terms of this MySQL Certification Non-Disclosure and Logo Usage Agreement (this "Agreement").

In addition, before using any Certification Logo (as defined below), you will be required to pass a Certification Exam, and you must receive electronic or written notice from MySQL AB or its affiliates (collectively “MySQL”) stating that you may use that particular Certification Logo.

This Agreement is created to protect your certification. By adhering to this Agreement (that is, keeping questions, answers and other information related to the Certification Exams confidential and by ensuring that the Certification Logos are used only by you during the permitted term), you help maintain the integrity of the MySQL Certification Program. This helps maintain the value of your Certification(s).

By clicking the NEXT button below, you irrevocably agree that the terms and conditions of this Agreement shall be valid and binding upon you.

Agreement

1. Non-Disclosure Agreement

I understand that the MySQL Certification Program and Certification Exam(s) are proprietary and confidential to MySQL. Therefore, I agree to the following terms and conditions:

1. I will use any information furnished under the MySQL Certification Program and Certification Exam(s), including but not limited to exams, questions, answers and related information (collectively “Protected Information”) only for the purpose of pursuing my own certification. I shall treat all Protected Information as strictly confidential and prevent its unauthorized access or disclosure.
2. I shall not copy, modify, translate, disclose, publish, transmit or make otherwise available to any person or entity any unchanged or modified Protected Information, in whole or in part, or information pertaining to it, in any form or by any means unless such disclosure is required by judicial or governmental order.
3. I alone must complete in full all the requirements for MySQL certification and/or assessment.
4. All work submitted in connection with the MySQL Certification Program will be my own, and not the work of any other individual, group or entity.
5. All Protected Information is proprietary material that is wholly owned by MySQL and protected under copyright, trade secret and other laws worldwide.

2. Logo Usage Agreement

I understand that MySQL may, from time to time, develop a logo for limited use by those individuals who have passed a particular Certification Exam (a "Certification Logo"). Therefore, I agree to the following terms and conditions:

1. This Agreement permits me to use only the Certification Logos expressly identified in an electronic or written notice provided to me by MySQL, and only in the manner described herein.
2. I may only use a Certification Logo that pertains to a Certification level that I, as an individual, have achieved, and only to identify me as an individual who is certified by MySQL. I may use a Certification Logo in printed and/or electronic forms as long as I always use a Certification Logo in close proximity to my personal name. I will not use a Certification Logo, or any imitation thereof, in any other place, such as on product packaging. My certification is personal and does not apply to or benefit any company or organization that I may own, or for whom I may work or otherwise be associated. Such company or organization may not use a Certification Logo without prior written permission from MySQL.
3. I may not alter a Certification Logo in any way, including (without limitation) by resizing, translating, or altering the colors used in, the Certification Logo. A Certification Logo may be scaled proportionally in order to adhere to the conditions of this Agreement.
4. In the event that my certification expires or is otherwise terminated, I will immediately discontinue my usage of the applicable Certification Logo.
5. I may not display or use the Certification Logo in a manner that suggests that I am an employee or other representative of MySQL, or that either my company or I are affiliated with, or endorsed by, MySQL. Moreover, everywhere I use a Certification Logo, my name, or the name of my company, must appear more prominently than, and clearly distinguished from, the Certification Logo.
6. My use of any and all Certification Logos shall further be restricted by, and subject to, the MySQL Trademark Policy ("The Trademark Policy") posted on the MySQL website (www.mysql.com, the "Website"). MySQL may from time to time change the Trademark Policy, and I agree to check the Website for changes to the Trademark Policy on a periodic basis. I shall be deemed to have knowledge of any changes to the Trademark Policy as soon as they are posted on the Website. I agree that any and all goodwill pertaining to my use of a Certification Logo shall inure to the exclusive benefit of MySQL.

3. Miscellaneous

1. MySQL reserves any and all rights not expressly granted herein.
2. I hereby declare that: (a) I am the individual registered and authorized to complete the Certification Exam, identified by such unique information as my MySQL Certification user id and password, electronic mail address, given and family names, or such other means used by MySQL to uniquely identify an individual; and (b) I am at least eighteen (18) years old and competent to sign this Agreement.
3. I understand that violation of any of the terms of this Agreement will result in: (a) the immediate revocation of any existing MySQL certification; (b) my permanent ineligibility to obtain any MySQL certification; and/or (c) other punitive or legal action deemed appropriate by MySQL.
4. If the testing center at which I am accepting this Agreement is located in the United States of America, then this Agreement shall be deemed to have been executed in the United States of America and shall be governed by the laws of the State of California, without regard to the conflict of laws provisions thereof, and the exclusive venue shall be Santa Clara County, US. If the testing center at which I am signing this Agreement is located outside the United States of America, then this Agreement shall be deemed to have been executed in Sweden and shall be governed by the laws of Sweden, without regard to the conflict of laws provisions thereof, and the exclusive venue shall be Stockholm, Sweden. Notwithstanding the foregoing, MySQL may seek injunctive or other equitable relief in any jurisdiction in order to protect its intellectual property rights.
5. I have agreed to execute this Agreement in the English language. If I am located in France or Quebec, Canada, the following clause applies: The parties hereby confirm that they have requested that this Agreement be drafted in English. Les parties contractantes confirment qu’elles ont exigé quele présent contrat et tous les documents associés soient redigés en anglais. Any action brought under this Agreement shall be conducted in the English language.
6. By clicking the “NEXT” button displayed below, I am confirming that I understand and accept all of the terms and conditions of this Agreement. I understand that I am under no obligation to accept these terms, but that my acceptance is required to proceed with this Certification Exam.

You must accept the above terms and click "NEXT" in order to proceed with the exam.

If you do not accept the above terms, click "END EXAM". You will not be able to proceed with the exam.